"오라클 FK 생성 프로시져"의 두 판 사이의 차이

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;
/