행위

오라클 FK 생성 프로시져

DB CAFE

1 사용법

1.1 FK 생성

BEGIN 
    SP_DBM_FK_RECOVER(p_owner => 'XXX',p_exec => 1);
END;

1.2 실패한 FK 생성

BEGIN 
    SP_DBM_FK_RECOVER(p_owner => 'XXX',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 = 'XXX'
  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 = 'XXX'
  AND A.CONSTRAINT_TYPE ='R'
  AND STATUS <> 'ENABLED'
  ;

1.5 FK 생성 프로시져

CREATE OR REPLACE PROCEDURE XXX_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 XXX_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 XXX_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 XXX_MIG.PK_DBM_FK ON XXX_MIG.DBM_FK
(OWNER, TNAME, FKNAME)

ALTER TABLE XXX_MIG.DBM_FK ADD (
  CONSTRAINT PK_DBM_FK
  PRIMARY KEY
  (OWNER, TNAME, FKNAME)
  USING INDEX XXX_MIG.PK_DBM_FK
  ENABLE VALIDATE);

3 관련 트리거

CREATE OR REPLACE TRIGGER XXX_MIG.TR_DBM_FK_DEL
    after delete on XXX_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;
/