"오라클 FK 생성 프로시져"의 두 판 사이의 차이
DB CAFE
162번째 줄: | 162번째 줄: | ||
/ | / | ||
</source> | </source> | ||
+ | [[Category:oracle]] |
2019년 12월 22일 (일) 15:01 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 사용법[편집]
1.1 FK 생성[편집]
BEGIN
SP_DBM_FK_RECOVER(p_owner => 'RTIS',p_exec => 1);
END;
1.2 실패한 FK 생성[편집]
BEGIN
SP_DBM_FK_RECOVER(p_owner => 'RTIS',p_exec => 1, p_status=>'DISABLE');
END;
1.3 FK 생성 결과 확인[편집]
-- 성공
SELECT *
-- 'ALTER TABLE '||OWNER||'.'||TABLE_NAME|| ' ENABLE CONSTRAINT '||CONSTRAINT_NAME||';'
FROM DBA_CONSTRAINTS A
WHERE OWNER = 'RTIS'
AND A.CONSTRAINT_TYPE ='R'
AND STATUS <> 'ENABLED'
;
1.4 실패한 FK 재생성[편집]
-- 실패
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME|| ' ENABLE CONSTRAINT '||CONSTRAINT_NAME||';'
FROM DBA_CONSTRAINTS A
WHERE OWNER = 'RTIS'
AND A.CONSTRAINT_TYPE ='R'
AND STATUS <> 'ENABLED'
;
1.5 FK 생성 프로시져[편집]
CREATE OR REPLACE PROCEDURE RTIS_MIG.sp_dbm_fk_recover
( p_owner in varchar2 default null
, p_tname in varchar2 default '%'
, p_fkname in varchar2 default '%'
, p_status in varchar2 default null
, p_exec in number default 0)
/*==============================================================================*
SP NAME : SP_DBM_FK_RECOVER
SP DESC.:
- 테이블에 저장된 정보를 사용하여 FK를 생성한다.
- set serverout on이 설정되어야 실행 결과 또는 스크립트 출력을 확인할 수 있다.
PARAM :
p_owner : 테이블 소유자
p_tname : 테이블 명칭. like 조건으로 검색되며 기본값은 %로 소유자의 모든 테이블이다.
p_fkname: FK 명칭. like 조건으로 검색되며 기본값은 %로 해당 테이블의 모든 FK이다.
p_status: 생성시 FK 옵션을 입력한다. ENABLE/DISABLE/ENABLE NOVALIDATE
p_exec : 1 : FK를 생성한다.
0 : FK를 생성하는 스크립트를 출력한다.
*==============================================================================*/
is
v_sqltxt varchar2(4000);
v_owner varchar2(100);
v_tname varchar2(100);
v_fkname varchar2(100);
v_cols varchar2(2000);
v_rcols varchar2(2000);
v_status varchar2(20);
begin
v_owner := upper(p_owner);
v_tname := upper(p_tname);
v_fkname := upper(p_fkname);
v_status := upper(p_status);
if v_owner is null then
dbms_output.enable(100);
dbms_output.put_line('usage: sp_dbm_fk_recover(p_owner=>, p_tname=>''%'', p_fkname=>''%'', p_status=>null, p_exec=>0)');
dbms_output.put_line(' p_status=> enable, disable, enable novalidate');
return;
end if;
for icur in (select a.owner, a.tname, a.fkname, a.cols, a.rowner, a.rtname, a.rcols
, a.drule, nvl(v_status, a.status) status
from RTIS_MIG.dbm_fk a
where a.owner=v_owner and a.tname like v_tname and a.fkname like v_fkname
and not exists(select 1 from dba_constraints b -- all_constraints b
where a.owner=b.owner and b.table_name=a.tname and b.constraint_name=a.fkname)
)
loop
begin
v_sqltxt := 'ALTER TABLE '||icur.owner||'.'||icur.tname||' ADD CONSTRAINT '||icur.fkname
||' FOREIGN KEY ('||icur.cols||') REFERENCES '||icur.rowner||'.'||icur.rtname
||'('||icur.rcols||') '||icur.drule||' '||icur.status;
dbms_output.enable(500);
if p_exec = 1 then
execute immediate v_sqltxt;
dbms_output.put_line(v_sqltxt||'; -- done');
else
dbms_output.put_line(v_sqltxt||';');
end if;
exception when others then
dbms_output.enable(500);
dbms_output.put_line('Error at: '||v_sqltxt);
dbms_output.put_line('-->'||SQLERRM);
end;
end loop;
end;
/
2 관련 테이블 생성[편집]
CREATE TABLE RTIS_MIG.DBM_FK
(
OWNER VARCHAR2(100 BYTE) NOT NULL,
TNAME VARCHAR2(100 BYTE) NOT NULL,
FKNAME VARCHAR2(100 BYTE) NOT NULL,
COLS VARCHAR2(2000 BYTE) NOT NULL,
ROWNER VARCHAR2(100 BYTE) NOT NULL,
RTNAME VARCHAR2(100 BYTE) NOT NULL,
RCOLS VARCHAR2(2000 BYTE) NOT NULL,
DRULE VARCHAR2(20 BYTE),
STATUS VARCHAR2(20 BYTE) NOT NULL,
TMPCHG VARCHAR2(20 BYTE),
LOGDT DATE DEFAULT sysdate
)
CREATE UNIQUE INDEX RTIS_MIG.PK_DBM_FK ON RTIS_MIG.DBM_FK
(OWNER, TNAME, FKNAME)
ALTER TABLE RTIS_MIG.DBM_FK ADD (
CONSTRAINT PK_DBM_FK
PRIMARY KEY
(OWNER, TNAME, FKNAME)
USING INDEX RTIS_MIG.PK_DBM_FK
ENABLE VALIDATE);
3 관련 트리거[편집]
CREATE OR REPLACE TRIGGER RTIS_MIG.TR_DBM_FK_DEL
after delete on RTIS_MIG.dbm_fk
for each row
declare
v_nval number(1) default null;
begin
select 1 into v_nval from dbm_fk_hist
where owner = :old.owner and tname = :old.tname and fkname = :old.fkname
and cols = :old.cols and rowner = :old.rowner and rtname = :old.rtname and rcols = :old.rcols
and rownum=1;
exception
when NO_DATA_FOUND then
insert into dbm_fk_hist
values(:old.owner, :old.tname, :old.fkname, :old.cols, :old.rowner, :old.rtname, :old.rcols
, :old.drule, :old.status, :old.tmpchg, :old.logdt);
when others then
--raise; --SQLCODE, SQLERRM
RAISE_APPLICATION_ERROR(-20001, '('||to_char(SQLCODE)||':'||SQLERRM||')');
end;
/