테이블 창원유기견보호소 생성 SQL : 연결명(DOG), 테이블명
테이블 생성 + 데이터 삽입 SQL&gt회원 CREATE TABLE "DOG"."회원"
 ( "회원아이디" VARCHAR2(30 BYTE),
"회원이름" VARCHAR2(20 BYTE),
"나이" NUMBER(3,0),
"전화번호" VARCHAR2(30 BYTE),
"주소" VARCHAR2(20 BYTE)
 ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into DOG."회원"
SET DEFINE OFF;
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('aa12','김민철',20,'','서울');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('bb32','송경호',21,'','마산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('cc15','김예나',40,'','서울');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('dd45','김종국',35,'','창원');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('ee65','한혜진',28,'','창원');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('ff78','한효주',64,'','경주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('gg78','김지민',26,'','진주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('hh65','전정국',55,'','부산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('ii12','장원영',45,'','부산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('jj32','이영지',20,'','진주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('kk45','김래원',21,'','경주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('ll26','이준호',40,'','울산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('mm55','김우영',35,'','부산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('nn78','김영우',28,'','서울');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('oo17','우영우',64,'','마산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('pp32','천지훈',26,'','서울');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('qq33','도봉순',55,'','창원');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('rr77','김필',45,'','창원');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('ss89','박혜원',20,'','경주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('tt33','김진주',21,'','진주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('uu75','허경환',40,'','부산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('vv32','김철수',35,'','부산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('xx31','김동현',28,'','진주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('yy11','박수찬',64,'','경주');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('zz47','김해성',26,'','울산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('ww99','김진솔',55,'','부산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('abc785','김태형',45,'','서울');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('xzs777','김정원',45,'','마산');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('ere66','이진수',32,'','서울');
Insert into DOG."회원" ("회원아이디","회원이름","나이","전화번호","주소") values ('zsd123','김진수',45,'','창원');
--------------------------------------------------------
--DDL for Index SYS_C008433
--------------------------------------------------------

CREATE UNIQUE INDEX "DOG"."SYS_C008433" ON "DOG"."회원" ("회원아이디")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
--Constraints for Table 회원
--------------------------------------------------------

ALTER TABLE "DOG"."회원" MODIFY ("회원아이디" NOT NULL ENABLE);
ALTER TABLE "DOG"."회원" MODIFY ("회원이름" NOT NULL ENABLE);
ALTER TABLE "DOG"."회원" MODIFY ("전화번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."회원" ADD PRIMARY KEY ("회원아이디")
USING INDEX 창원유기견보호소 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"ENABLE;&gt보호소CREATE TABLE "DOG"."보호소"
 ( "보호기관번호" VARCHAR2(30 BYTE),
"보호기관명" VARCHAR2(30 BYTE),
"주소" VARCHAR2(20 BYTE),
"전화번호" VARCHAR2(15 BYTE),
"보호 중인 유기견 수" NUMBER DEFAULT 0
 ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into DOG."보호소"
SET DEFINE OFF;
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r001','하나보호소','서울','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r002','둘보호소','마산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r003','셋보호소','경주','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r004','넷보호소','광주','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r005','다섯보호소','울산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r006','여섯보호소','김해','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r007','일곱보호소','진해','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r008','여덟보호소','진주','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r009','아홉보호소','창원','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r010','열보호소','부산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r011','열하나보호소','양산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r012','열둘보호소','대전','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r013','열셋보호소','대구','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r014','열넷보호소','서울','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r015','열다섯보호소','마산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r016','열어섯보호소','경주','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r017','열일곱보호소','광주','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r018','열여덟보호소','울산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r019','열아홉보호소','김해','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r020','스물보호소','진해','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r021','스물하나보호소','진주','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r022','스물둘보호소','창원','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r023','스물셋보호소','부산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r024','스물넷보호소','양산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 창원유기견보호소 수") values ('r025','스물다섯보호소','대전','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r026','스물여섯보호소','대구','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r027','스물일곱보호소','서울','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r028','스물여덟보호소','마산','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r029','스물아홉보호소','경주','',0);
Insert into DOG."보호소" ("보호기관번호","보호기관명","주소","전화번호","보호 중인 유기견 수") values ('r030','서른보호소','광주','',0);
--------------------------------------------------------
--DDL for Index SYS_C008437
--------------------------------------------------------

CREATE UNIQUE INDEX "DOG"."SYS_C008437" ON "DOG"."보호소" ("보호기관번호")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
--Constraints for Table 보호소
--------------------------------------------------------

ALTER TABLE "DOG"."보호소" MODIFY ("보호기관번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."보호소" MODIFY ("보호기관명" NOT NULL ENABLE);
ALTER TABLE "DOG"."보호소" MODIFY ("주소" NOT NULL ENABLE);
ALTER TABLE "DOG"."보호소" ADD PRIMARY KEY ("보호기관번호")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"ENABLE;> 유기견--------------------------------------------------------
--DDL for Table 유기견
--------------------------------------------------------

CREATE TABLE "DOG"."유기견"
 ( "유기견번호" NUMBER,
"유기견이름" VARCHAR2(30 BYTE),
"유기견품종" VARCHAR2(30 BYTE),
"성별" VARCHAR2(20 BYTE),
"나이" VARCHAR2(10 BYTE),
"몸무게" VARCHAR2(20 BYTE),
"구조일자" VARCHAR2(30 BYTE),
"발견장소" VARCHAR2(100 BYTE),
"보호등록번호" VARCHAR2(30 BYTE),
"보호기관번호" VARCHAR2(30 BYTE)
 ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into DOG."유기견"
SET DEFINE OFF;
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111601,'아롱이','치와와','암컷','2살','2kg','11/06/2022','부산 서구 대영로 17 거리','prot01','r010');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111602,'몽이','말티즈','수컷','5살','2.4kg','11/06/2022','인천 남동구 경신상로 28 남동문화공원','prot02','r001');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111603,'네오','달마시안','암컷','3살','21kg','11/07/2022','경남 창원시 성산구 단정로 98번길 새소망감리교회 앞','prot03','r009');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111604,'가나','자이언트 푸들','암컷','4살','20.8kg','11/07/2022','경남 창원시 퇴촌로25번길 소나무공원','prot04','r009');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111605,'자두','시츄','수컷','8살','6.3kg','11/07/2022','대구 중구 국채보상로 139길','prot05','r013');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111606,'뽀삐','토들 푸들','암컷','10살','1.7kg','11/07/2022','대구 북구 동북로 131 미니스톱 앞','prot06','r026');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111607,'하루','스피츠','수컷','1살','10.2kg','11/08/2022','대전 보라매 공원 근처 골목','prot07','r012');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111608,'니모','페키니즈','수컷','4살','5.3kg','11/08/2022','광주 서구 창원유기견보호소 화운로 23번길 금호타운 아파트 단지 내','prot08','r004');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111609,'깜순이','닥스훈트','암컷','6살','4kg','11/08/2022','울산 남구 산업로355번길 111 야음초등학교 내','prot09','r005');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111610,'까미','닥스훈트','수컷','7살','4.2kg','11/09/2022','투썸플레이스 김해외동점 옆 골목','prot10','r006');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111611,'몽실이','비숑프리제','암컷','3살','5kg','11/09/2022','경기 안산시 단원구 광덕대로 157 안산문화광장 내','prot11','r014');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111612,'몽글이','비숑프리제','수컷','7살','4.7kg','11/09/2022','경기 안산시 단원구 이삭6길','prot12','r014');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111613,'두부','사모예드','암컷','11살','22kg','11/09/2022','광주 북구 무등산천왕봉길 460 무등산국립공원 내','prot13','r017');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111614,'백설이','사모예드','수컷','2살','16kg','11/09/2022','전남 보성군 보성읍 동산길 9 보성우체국 근처','prot14','r030');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111615,'초코','치와와','암컷','6살','2.5kg','11/10/2022','인천 남동구 구월동 모래내시장역 1번 출구 앞','prot15','r001');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111616,'코기','웰시코기','수컷','9살','12.9kg','11/10/2022','경기 남양주시 금곡로 57 ','prot16','r027');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111617,'쿠키','웰시코기','암컷','3살','10.4kg','11/10/2022','전남 보성군 보성읍 동산길 15 보성 지역아동센터 근처','prot17','r030');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111618,'뽀미','비숑프리제','수컷','12살','4.6kg','11/11/2022','경남 창원시 성산구 용지로169번길 26 용지공원 입구','prot18','r022');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111619,'요미','말티즈','수컷','4살','2.4kg','11/11/2022','경남 창원시 의창구 명서로141번길 22 골목길','prot19','r022');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111620,'누렁이','자이언트 푸들','수컷','8살','7.7kg','11/11/2022','경기 수원시 영통구 매탄로 126번길 66 매화공원 내','prot20','r001');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111621,'둥이','치와와','암컷','4살','3kg','11/11/2022','경기 평택시 평남로 862 CGV앞 골목','prot21','r027');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111622,'망고','치와와','수컷','9살','1.4kg','11/13/2022','경기 평택시 평남로 862 CGV앞 골목','prot22','r027');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111623,'똘이','말티즈','암컷','2살','3.9kg','11/13/2022','마산합포구 합성동 다이소 앞','prot23','r015');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111624,'미키','시츄','수컷','7살','6kg','11/13/2022','경기 수원시 영통구 영통로 111 동수원자이3차아파트 단지 내','prot24','r027');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111625,'달이','달마시안','암컷','3살','22.3kg','11/14/2022','울산 북구 당수골6길 매곡1공원 근처','prot25','r018');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111626,'크림이','사모예드','암컷','11살','24.3kg','11/15/2022','대전 유성구 엑프로로 448 엑스포5단지아파트 내','prot26','r025');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111627,'나나','불독','수컷','4살','17.1kg','11/15/2022','경북 구미시 백산로5길 백산공원','prot27','r013');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111628,'아띠','포메라니안','암컷','3살','3kg','11/15/2022','부산 사하구 신산북로 61 세븐일레븐 옆','prot28','r023');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111629,'소금이','스피츠','수컷','3살','12.9kg','11/16/2022','부산 서구 대영로 45번길 골목길 내 ','prot29','r010');
Insert into DOG."유기견" ("유기견번호","유기견이름","유기견품종","성별","나이","몸무게","구조일자","발견장소","보호등록번호","보호기관번호") values (22111630,'모카','골든 리트리버','암컷','6살','27kg','11/16/2022','전북 진주시 진주로28 이마트24 앞','prot30','r021');
--------------------------------------------------------
--DDL for Index SYS_C008427
--------------------------------------------------------

CREATE UNIQUE INDEX "DOG"."SYS_C008427" ON "DOG"."유기견" ("유기견번호")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
--Constraints for Table 유기견
--------------------------------------------------------

ALTER TABLE "DOG"."유기견" MODIFY ("유기견번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" MODIFY ("유기견이름" NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" MODIFY ("성별" 창원유기견보호소 NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" MODIFY ("몸무게" NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" MODIFY ("구조일자" NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" MODIFY ("발견장소" NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" MODIFY ("보호등록번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" MODIFY ("보호기관번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."유기견" ADD PRIMARY KEY ("유기견번호")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"ENABLE;
--------------------------------------------------------
--Ref Constraints for Table 유기견
--------------------------------------------------------

ALTER TABLE "DOG"."유기견" ADD CONSTRAINT "보호기관번호" FOREIGN KEY ("보호기관번호")
REFERENCES "DOG"."보호소" ("보호기관번호") ENABLE;> 신고기록--------------------------------------------------------
--DDL for Table 신고기록
--------------------------------------------------------

CREATE TABLE "DOG"."신고기록"
 ( "동물등록번호" VARCHAR2(30 BYTE),
"반려견이름" VARCHAR2(30 BYTE),
"반려견품종" VARCHAR2(30 BYTE),
"성별" VARCHAR2(20 BYTE),
"특이사항" VARCHAR2(100 BYTE),
"실종장소" VARCHAR2(50 BYTE),
"실종된시간" VARCHAR2(30 BYTE),
"실종된날짜" VARCHAR2(20 BYTE),
"회원아이디" VARCHAR2(50 BYTE)
 ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into DOG."신고기록"
SET DEFINE OFF;
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB401','뽀삐','토이푸들','암컷','실종 당시 빨간색 옷 입고 있었음','남산타워 근처 ','08:20','221101','aa12');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB402','초코','토이푸들','수컷','한 쪽 다리를 절뚝거림','합성동 근처','10:10','221101','bb32');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB403','보리','골든 리트리버','수컷','사람을 잘 따름','홍대 근처','11:02','221101','cc15');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB404','코코','웰시코기','암컷','달리기가 빠름','용지공원 근처','15:39','221101','dd45');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB405','콩이','잭 러셀 테리어','수컷','오른쪽 얼굴만 갈색이며 귀는 하얌','은아아파트 근처','20:12','221101','ee65');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB406','까미','닥스훈트','암컷','실종 당시 하늘색 목줄을 하고 있었음','불국사 근처','22:02','221101','ff78');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB407','몽이','사모예드','수컷','얼마 전에 곰돌이 컷으로 미용함','고성 공룡박물관 근처','12:00','221102','gg78');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB408','해피','보더콜리','암컷','경계심이 심함','해운대 근처','13:11','221102','hh65');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB409','별이','시바견','수컷','공격성이 다분함','광안리 근처','13:13','221102','ii12');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB410','두부','사모예드','암컷','사람을 잘 따름, 덩치가 평균보다 큼','고성 공룡박물관 근처','20:22','221102','jj32');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB411','쿠키','토이푸들','수컷','실종 당시 팜비치로 미용함','국립경주문화재연구소 근처','22:18','221104','kk45');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB412','호두','웰시코기','수컷','단미(꼬리절단수술)를 하지 않음','고래박물관 근처','11:34','221105','ll26');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB413','구름이','비숑프리제','암컷','겁이 많음','벡스코 근처 ','14:22','221105','mm55');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB414','토리','토이푸들','수컷','매우 똑똑함, 창원유기견보호소 낯선 사람 경계 심함','인사동 근처','14:30','221105','nn78');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB415','마루','시베리안 허스키','수컷','평균 허스키 크기보다 작음','회원동 근처','15:20','221105','oo17');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB416','사랑이','진돗개','수컷','애교가 많음, 낯선 사람도 잘 따름','마포구 근처','09:01','221106','pp32');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB417','모찌','스피츠','암컷','볼쪽 연지곤지 염색 되어있음','용지공원 근처','11:45','221106','qq33');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB418','둥이','핏불 테리어','수컷','실종 당시 뿔달린 목줄을 하고 있었음','용지공원 근처','11:47','221106','rr77');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB419','호두','자이언트 푸들','암컷','귀를 연 보라색으로 염색함','대릉원 근처 ','13:19','221106','ss89');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB420','망고','시바견','수컷','실종 당시 파란 스카프 매고 있음','함안군 근처 ','20:21','221107','tt33');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB421','희망이','말티즈','암컷','말티즈치고는 사람을 매우 잘 따름, 얌전함','송정 근처','20:39','221107','uu75');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB422','몰리','포메라니안','암컷','실종 당시 곰돌이 컷을 했었음','송정 근처','22:21','221110','vv32');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB423','레오','도베르만 핀셔','수컷','귀 수술(단이)을 하지 않음','내서읍 근처','18:02','221111','xx31');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB424','루이','비글','암컷','한 쪽 귀에 물린 상처 있음','대릉원 근처 ','20:24','221111','yy11');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB425','아롱이','비글','암컷','등에 검정색 하트 모양이 있음','삼산 근처','22:50','221111','zz47');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB426','우유','포메라니안','수컷','평균 포메라니안보다 덩치가 큼','광안리 근처','23:10','221112','ww99');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB427','두유','자이언트 푸들','수컷','실종 당시 노랑색 털 양말 신고 있었음','종로구 근처','15:38','221113','abc785');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB428','연탄','치와와','암컷','호기심이 많음, 몸은 하얗고 얼굴은 까맘','제일여고 근처','17:20','221115','xzs777');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB429','뽀미','시츄','수컷','실종 당시 귀에 리본을 하고 있었음','서울숲 근처','21:19','221116','ere66');
Insert into DOG."신고기록" ("동물등록번호","반려견이름","반려견품종","성별","특이사항","실종장소","실종된시간","실종된날짜","회원아이디") values ('DEUDB430','뭉치','핏불 테리어','암컷','뼈다귀 모양 목걸이를 하고 있음','용지공원 근처','22:10','221116','zsd123');
--------------------------------------------------------
--DDL for Index SYS_C008457
--------------------------------------------------------

CREATE UNIQUE INDEX "DOG"."SYS_C008457" ON "DOG"."신고기록" ("동물등록번호")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
--Constraints for Table 신고기록
--------------------------------------------------------

ALTER TABLE "DOG"."신고기록" MODIFY ("동물등록번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("반려견이름" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("반려견품종" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("성별" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("특이사항" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("실종장소" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("실종된시간" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("실종된날짜" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" MODIFY ("회원아이디" NOT NULL ENABLE);
ALTER TABLE "DOG"."신고기록" ADD PRIMARY KEY 창원유기견보호소 ("동물등록번호")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"ENABLE;
--------------------------------------------------------
--Ref Constraints for Table 신고기록
--------------------------------------------------------

ALTER TABLE "DOG"."신고기록" ADD CONSTRAINT "회원아이디" FOREIGN KEY ("회원아이디")
REFERENCES "DOG"."회원" ("회원아이디") ENABLE;&gt입양신청--------------------------------------------------------
--DDL for Table 입양신청
--------------------------------------------------------

CREATE TABLE "DOG"."입양신청"
 ( "입양신청번호" VARCHAR2(30 BYTE),
"입양신청일자" DATE,
"입양유기견이름" VARCHAR2(30 BYTE),
"입양유기견품종" VARCHAR2(30 BYTE),
"입양유기견성별" VARCHAR2(20 BYTE),
"보호기관번호" VARCHAR2(30 BYTE),
"회원아이디" VARCHAR2(30 BYTE)
 ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into DOG."입양신청"
SET DEFINE OFF;
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A1',to_date('20/10/10','RR/MM/DD'),'뭉치','치와와','암컷','r001','aa12');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A2',to_date('21/10/06','RR/MM/DD'),'똘이','말티즈','수컷','r001','bb32');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A3',to_date('20/08/01','RR/MM/DD'),'구름이','푸들','암컷','r002','dd45');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A4',to_date('22/11/17','RR/MM/DD'),'꼬미','시츄','수컷','r002','ee65');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A5',to_date('19/01/19','RR/MM/DD'),'코코','푸들','암컷','r003','cc15');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A6',to_date('22/11/16','RR/MM/DD'),'망고','푸들','수컷','r006','gg78');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A7',to_date('20/06/06','RR/MM/DD'),'초롱이','비숑프리제','암컷','r004','hh65');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A8',to_date('22/09/07','RR/MM/DD'),'토토','푸들','수컷','r007','cc15');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A9',to_date('21/11/30','RR/MM/DD'),'레오','닥스훈트','암컷','r007','kk45');
Insert into DOG."입양신청" ("입양신청번호","입양신청일자","입양유기견이름","입양유기견품종","입양유기견성별","보호기관번호","회원아이디") values ('A10',to_date('22/04/06','RR/MM/DD'),'둥이','말티즈','암컷','r008','ll26');
--------------------------------------------------------
--DDL for Index SYS_C008436
--------------------------------------------------------

CREATE UNIQUE INDEX "DOG"."SYS_C008436" ON "DOG"."입양신청" ("입양신청번호")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
--Constraints for Table 입양신청
--------------------------------------------------------

ALTER TABLE "DOG"."입양신청" MODIFY ("입양신청번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."입양신청" MODIFY ("입양신청일자" NOT NULL ENABLE);
ALTER TABLE "DOG"."입양신청" MODIFY ("입양유기견이름" NOT NULL ENABLE);
ALTER TABLE "DOG"."입양신청" MODIFY ("입양유기견품종" NOT NULL ENABLE);
ALTER TABLE "DOG"."입양신청" MODIFY ("입양유기견성별" NOT NULL ENABLE);
ALTER TABLE "DOG"."입양신청" MODIFY ("보호기관번호" NOT NULL ENABLE);
ALTER TABLE "DOG"."입양신청" MODIFY ("회원아이디" NOT NULL ENABLE);
ALTER TABLE "DOG"."입양신청" ADD PRIMARY KEY ("입양신청번호")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"ENABLE;
--------------------------------------------------------
--Ref Constraints for Table 입양신청
--------------------------------------------------------

ALTER TABLE "DOG"."입양신청" ADD 창원유기견보호소 FOREIGN KEY ("보호기관번호")
REFERENCES "DOG"."보호소" ("보호기관번호") ENABLE;
ALTER TABLE "DOG"."입양신청" ADD FOREIGN KEY ("회원아이디")
REFERENCES "DOG"."회원" ("회원아이디") ENABLE;프로시저&gtP_입양신청create or replace PROCEDURE P_입양신청(
pi_보호기관번호 IN 보호소.보호기관번호%TYPE,
pi_입양자아이디 IN 회원.회원아이디%TYPE,
pi_입양희망유기견번호 IN 유기견.유기견번호%TYPE
)
AS
유기견이름 유기견.유기견이름%TYPE;
유기견품종 유기견.유기견품종%TYPE;
유기견성별 유기견.성별%TYPE;
입양신청번호 VARCHAR(20);
입양신청일자 DATE;
BEGIN
SELECT 유기견이름,유기견품종,성별
INTO 유기견이름,유기견품종,유기견성별
FROM 유기견
where 유기견번호 = pi_입양희망유기견번호;
SELECT SYSDATE INTO 입양신청일자 FROM DUAL;
INSERT INTO 입양신청(입양신청번호,회원아이디,입양유기견이름,입양유기견품종,입양유기견성별,입양신청일자,보호기관번호)
VALUES ('A'||입양번호.NEXTVAL,pi_입양자아이디,유기견이름,유기견품종,유기견성별,입양신청일자,pi_보호기관번호);
END;&gtP_특정지역유기견목록 : 참조CREATE OR REPLACE PROCEDURE P_특정지역유기견목록(Pi_주소 IN 보호소.주소%TYPE, out유기견 OUT SYS_REFCURSOR)
AS
BEGIN
OPEN out유기견 FOR
SELECT a.유기견번호, a.유기견이름, a.유기견품종, a.성별, a.나이, a.몸무게, a.구조일자, a.발견장소, a.보호등록번호, b.보호기관명
FROM 유기견 a INNER JOIN 보호소 b ON a.보호기관번호 = b.보호기관번호 WHERE b.주소 = pi_주소;
END;


SET SERVEROUTPUT ON;
VAR out유기견 REFCURSOR;
EXEC P_특정지역유기견목록('서울', :out유기견)
PRINT out유기견
&gtSP_유기견수카운터create or replace NONEDITIONABLE PROCEDURE SP_유기견수카운터
AS
V_보호기관번호 유기견.보호기관번호%TYPE :=0;
V_유기견수 보호소."보호 중인 유기견 수"%TYPE :=0;
CURSOR D IS SELECT 보호기관번호, COUNT(*) FROM 유기견 GROUP BY 보호기관번호;
BEGIN
OPEN D;
LOOP
FETCH D INTO V_보호기관번호,V_유기견수;
EXIT WHEN D%NOTFOUND;
UPDATE 보호소 SET "보호 중인 유기견 수" = V_유기견수 WHERE 보호기관번호=V_보호기관번호;
END LOOP;
CLOSE D;
DBMS_OUTPUT.PUT_LINE('보호소별 유기견수 업데이트 완료');
END;트리거&gtT_유기견수변경-- 유기견 테이블에 삽입,삭제가 발생했을 때 보호소 테이블의 보호중인 유기견 수 변경
CREATE OR REPLACE TRIGGER T_유기견수변경
AFTER INSERT OR DELETE
ON 유기견
FOR EACH ROW
BEGIN
IF :NEW.보호기관번호 is null THEN -- delete문 수행
UPDATE 보호소 SET "보호 중인 유기견 수" = "보호 중인 유기견 수" - 1 WHERE "보호기관번호" = :OLD.보호기관번호
ELSE --insert문 수행
UPDATE 보호소 SET "보호 중인 유기견 수" = "보호 중인 유기견 수" + 1 WHERE "보호기관번호" = :NEW.보호기관번호;
END IF;
END;&gtT_입양자격검사-- 마지막 입양일로부터 6개월이 지났는지 판별
CREATE OR REPLACE TRIGGER T_입양자격검사
BEFORE INSERT
ON 입양신청
FOR EACH ROW
DECLARE
adopt_date DATE-- 이전 입양 기록이 존재한다면 해당 입양 기록에 관한 입양 일자 추출
month_diff NUMBER-- 마지막 입양일로부터 현재 날짜까지 기간 계산(6개월 지났는지 판별)
CURSOR c IS SELECT 입양신청.입양신청일자 INTO adopt_date FROM "입양신청" WHERE :NEW.회원아이디 = 입양신청.회원아이디-- 입양 신청한 회원의 이전 입양 기록 추출
BEGIN
OPEN c;
LOOP
FETCH c INTO adopt_date;
EXIT WHEN c%NOTFOUND;
SELECT MONTHS_BETWEEN(SYSDATE, adopt_date) INTO month_diff FROM dual;
IF month_diff <= 6 THEN -- 마지막 입양일로부터 6개월이 지나지 않았을 때 입양 신청 불가능
RAISE_APPLICATION_ERROR(-20999,'자격 검사 위배 입력 시도 발견');
END IF;
END LOOP;
CLOSE c;
END;