다른 명령
ORACLE 프로시져
프로시져
프로시져 재 컴파일
ALTER PROCEDURE [PROCEDURE NAME] COMPILE;
SELECT 'ALTER PROCEDURE '||OBJECT_NAME||' COMPILE;' DSQL FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'PROCEDURE' ORDER BY OBJECT_NAME ;
프로시져 소스 조회
SELECT * FROM DBA_SOURCE WHERE TYPE='PROCEDURE' AND TEXT LIKE '%PROD_ID%';
패키지에서 주석처리 되지 않은 항목 조회
오라클 사용자가 사용하는 패키지의 BODY 소스를 검색하여 주석이 없거나 패턴이 맞지 않는 항목을 조회 한다.
SELECT * FROM DBA_OBJECTS B WHERE B.OWNER =:IN_OWNER AND B.OBJECT_TYPE = 'PACKAGE BODY' AND B.STATUS <> 'INVALID' -- VALID 상태만 조회, 만약 INVALID 된다고 해도 패키지를 수행하는 순간 컴파일 됨. AND NOT EXISTS (SELECT 1 FROM DBA_SOURCE A WHERE A.OWNER = B.OWNER AND A.TYPE = B.OBJECT_TYPE AND A.NAME = B.OBJECT_NAME AND A.LINE <= 5 AND A.TEXT LIKE '%NAME%');
ORACLE 프로시져 추출쿼리
== 프로시져 추출쿼리 ==
- 프로시져 추출
select OWNER , NAME , TYPE , LINE , decode(line,1,'CREATE OR REPLACE ' ||replace(text,'PACKAGE BODY ','PACKAGE BODY ' ||owner ||'.'),text) AS PKG_SOURCE from dba_source where type='PROCEDURE' and owner='$ownerName';
프로시져_샘플
기본 프로시져
DECLARE --변수,상수 선언 BEGIN --실행 가능 SQL문,PL/SQL문 EXCEPTION --에러처리 END; /* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */ Declare v_sale number := 2000; begin update test set a = v_sale; delete from test where a = v_sale; commit; end; /* Procedure에서 Procedure를 호출하는 방법 */ A프로시져에서 "B프로시져명(변수, 변수2);" PROCEDURE 리턴 여러개 FUNCTION 리턴 한개 SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE'; /* 프로시저나 함수 조회 */ SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION'; /* PROCEDURE & FUNCTION 삭제하기 */ DROP FUNCTION lee2; DROP PROCEDURE lee2;
프로시져 실행 옵션
- DBA권한이 있는데도 ORA-00942 에러 발생
AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT) AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용.
- PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가
- EXECUTE IMEDIATE DDL명령 실행시 DDL명령에 선언된 OWNER(예를들어 SCOTT.XXX) 가 프로시져를 실행한 유저(예를 들어 DBADM)가 아닐때 ORA-00942에러 발생
- EXCUTE IMMEDIATE 'SQL구문'; 실행시 'SQL구문' 내부에 세미콜론은 ';' 없어야 수행됨. (중요-삽질의 시작..)
커서 활용 샘플
CREATE OR REPLACE PROCEDURE RTIS_DBA.SP_OBJ_INVALID_TO_VALID /* * */ ( IN_DATE IN VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD') ) AUTHID CURRENT_USER IS CURSOR INVALID_OBJECT IS SELECT A.GRANTEE, A.OWNER, A.TABLE_NAME, A.GRANTOR, A.PRIVILEGE,A.OBJECT_TYPE FROM TB_MGR_GRANT A ; -- V_SQL VARCHAR2(200); V_G_SQL VARCHAR2(200); -- V_L_SQL VARCHAR2(2000); V_GRANTEE VARCHAR2(100); V_OWNER VARCHAR2(100); V_TABLE_NAME VARCHAR2(100); V_GRANTOR VARCHAR2(100); V_PRIVILEGE VARCHAR2(100); V_OBJECT_TYPE VARCHAR2(100); V_GRANT_REVOKE_GBN VARCHAR2(100); V_MSG long; BEGIN DBMS_OUTPUT.ENABLE; FOR V_ROW IN INVALID_OBJECT LOOP -- 블록 에러 발생시에도 계속 실행토록 BEGIN V_GRANTEE:= V_ROW.GRANTEE; V_OWNER:= V_ROW.OWNER; V_TABLE_NAME:= V_ROW.TABLE_NAME; V_GRANTOR:= V_ROW.GRANTOR; V_PRIVILEGE:= V_ROW.PRIVILEGE; V_OBJECT_TYPE:= V_ROW.OBJECT_TYPE; V_GRANT_REVOKE_GBN := 'GRANT TO USER'; -- 권한 추가 V_G_SQL := 'GRANT '||V_ROW.PRIVILEGE||' ON '||V_ROW.OWNER||'.'||V_ROW.TABLE_NAME||' TO '||V_ROW.GRANTEE; EXECUTE IMMEDIATE V_G_SQL; -- LOG 기록 INSERT INTO TB_MGR_GRANT_LOG (GRANTEE , OWNER , TABLE_NAME , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN) VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN); COMMIT; EXCEPTION WHEN OTHERS THEN V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); INSERT INTO TB_MGR_GRANT_LOG (GRANTEE , OWNER , TABLE_NAME , GRANTOR , PRIVILEGE ,OBJECT_TYPE ,GRANT_REVOKE_GBN,GRANT_REVOKE_SQL) VALUES (V_GRANTEE,V_OWNER,V_TABLE_NAME,V_GRANTOR,V_PRIVILEGE,V_OBJECT_TYPE,V_GRANT_REVOKE_GBN,V_MSG); COMMIT; END; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); END; /
Procedure 사용자 정의 예외 예제
입력한 부서의 사원이 5명보다 적으면 사용자 정의 예외가 발생하는 예제 입니다.
SQL>CREATE OR REPLACE PROCEDURE User_Exception (v_deptno IN emp.deptno%type ) IS -- 예외의 이름을 선언 user_define_error EXCEPTION; -- STEP 1 cnt NUMBER; BEGIN DBMS_OUTPUT.ENABLE; SELECT COUNT(empno) INTO cnt FROM emp WHERE deptno = v_deptno; IF cnt < 5 THEN -- RAISE문을 사용하여 직접적으로 예외를 발생시킨다 RAISE user_define_error; -- STEP 2 END IF; EXCEPTION -- 예외가 발생할 경우 해당 예외를 참조한다. WHEN user_define_error THEN -- STEP 3 RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..'); END; /
오라클 프로시져 함수 목록
저장프로시저 명세
(1) 저장프로시저 정보
SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME, TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME FROM SYS.USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE' ORDER BY OBJECT_NAME ASC;
(2) 저장프로시저 파라미터 정보
SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME, b.SEQUENCE, b.ARGUMENT_NAME, b.DATA_TYPE , (CASE b.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(b.DATA_LENGTH) WHEN 'DATE' THEN ' ' ELSE TO_CHAR(b.DATA_LENGTH) END) AS DATA_LENGTH , b.DATA_TYPE || (CASE b.DATA_TYPE WHEN 'NUMBER' THEN '(' || TO_CHAR(b.DATA_LENGTH) || ',' || TO_CHAR(b.DATA_PRECISION) || ')' WHEN 'DATE' THEN ' ' ELSE '(' || b.DATA_LENGTH || ')' END) DATA_TYPE_DESC , b.IN_OUT FROM SYS.USER_OBJECTS a, SYS.USER_ARGUMENTS b WHERE a.OBJECT_TYPE = 'PROCEDURE' AND a.OBJECT_ID = b.OBJECT_ID ORDER BY a.OBJECT_NAME ASC, b.SEQUENCE ASC;
(3) 저장프로시저 내용
SELECT TEXT FROM SYS.USER_SOURCE WHERE TYPE = 'PROCEDURE' AND NAME = '[저장프로시저명]'; 5. 사용자정의함수 명세 (1) 사용자정의함수 정보 보기 SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME, TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME FROM SYS.USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION' ORDER BY OBJECT_NAME ASC;
사용자정의함수 명세
(1) 사용자정의함수 파라미터
SELECT a.OBJECT_ID, a.OBJECT_TYPE, a.OBJECT_NAME, b.SEQUENCE, b.ARGUMENT_NAME, b.DATA_TYPE , (CASE b.DATA_TYPE WHEN 'NUMBER' THEN TO_CHAR(b.DATA_LENGTH) WHEN 'DATE' THEN ' ' ELSE TO_CHAR(b.DATA_LENGTH) END ) AS DATA_LENGTH , b.DATA_TYPE || (CASE b.DATA_TYPE WHEN 'NUMBER' THEN '(' || TO_CHAR(b.DATA_LENGTH) || ',' || TO_CHAR(b.DATA_PRECISION) || ')' WHEN 'DATE' THEN ' ' ELSE '(' || b.DATA_LENGTH || ')' END ) DATA_TYPE_DESC , b.IN_OUT FROM SYS.USER_OBJECTS a , SYS.USER_ARGUMENTS b WHERE a.OBJECT_TYPE = 'FUNCTION' AND a.OBJECT_ID = b.OBJECT_ID ORDER BY a.OBJECT_NAME ASC, b.SEQUENCE ASC;
(3) 사용자정의함수 내용
SELECT TEXT FROM SYS.USER_SOURCE WHERE TYPE = 'FUNCTION' AND NAME = '[사용자정의함수명]';
오라클 테이블 복사 프로시져
오라클 테이블 간 데이터 복사 프로시져
sp_dbm_copy
CREATE OR REPLACE PROCEDURE sp_dbm_copy ( p_tname in varchar2 default null , p_dblink in varchar2 default null , p_option in varchar2 default 'I' -- I:insert, R:replace(truncate), A:append , p_exec in number default 0 , p_owner in varchar2 default '' , p_srcowner in varchar2 default null , p_srctname in varchar2 default null ) authid current_user is /* ============================================================================= XXX_DBA 계정에 select any table 권한 필요: 없을 경우 다계정에 대해 NO_COLUMN_LIST 발생 create table XXX_DBA.tb_dbm_copy_log ( logdt date not null, -- 작업일자 owner varchar2 (100) not null, -- 테이블 소유자 tname varchar2 (100) not null, -- 테이블명 dblink varchar2 (100), -- DB LINK sowner varchar2 (100), -- 원본 테이블 소유자 stname varchar2 (100), -- 원본 테이블 coption varchar2 (10), -- 옵션(I:Insert, A:Append, R:Truncate and Insert) result varchar2 (5), -- 결과(S:Sucess, F:Fail) rowcount number, -- 건수 errmsg varchar2 (300), -- 에러메시지 constraint pk_dbm_copy_log primary key (logdt, owner, tname) ); ============================================================================= */ NO_COLUMN_LIST EXCEPTION; NO_PRIMARYKEY EXCEPTION; v_tgt_tname varchar2(50) default null; v_tgt_owner varchar2(30) default null; v_src_owner varchar2(30) default null; v_src_tname varchar2(50) default null; v_option varchar2(2); type typ_rec_cols is record (colid user_tab_columns.column_id%TYPE, colname all_tab_columns.column_name%TYPE); type typ_tab_cols is table of typ_rec_cols; tab_cols typ_tab_cols; v_dblink varchar2(100) default ' '; v_sqltxt long; v_sqlwhere varchar2(1000); v_colist long; v_msg long; v_rowcnt number(10) default 0; V_NL char(1) default chr(10); V_DELIM varchar(10) default ','; begin if p_tname is null then dbms_output.enable(100); dbms_output.put_line('usage: sp_dbm_copy( '); dbms_output.put_line(' p_tname => null '); dbms_output.put_line(' , p_dblink => null '); dbms_output.put_line(' , p_option => ''i'' -- I:insert, R:replace(truncate), A:append'); dbms_output.put_line(' , p_exec => 0 '); dbms_output.put_line(' , p_owner => null '); dbms_output.put_line(' , p_srcowner => null '); dbms_output.put_line(' , p_srctname => null)'); dbms_output.put_line(' p_option=I:Insert, R:Replace(truncate and insert), A:Append'); return; end if; v_tgt_owner := upper(p_owner); v_tgt_tname := upper(p_tname); v_option := upper(p_option); if (p_srcowner is null) then v_src_owner := v_tgt_owner; else v_src_owner := upper(p_srcowner); end if; if (p_srctname is null) then v_src_tname := v_tgt_tname; else v_src_tname := upper(p_srctname); end if; if (p_dblink is not null) then v_dblink := '@'||p_dblink; end if; ------------------------------------------------------------------ v_sqltxt := 'INSERT INTO '||v_tgt_owner||'.'||v_tgt_tname ||'('; if p_exec <> 1 then dbms_output.enable(1000); if v_option = 'R' then dbms_output.put_line('truncate table '||v_tgt_owner||'.'||v_tgt_tname||';'); end if; dbms_output.put_line(v_sqltxt); end if; -- column list execute immediate 'select t.column_id, t.column_name from all_tab_columns t, all_tab_columns'||v_dblink|| ' s '|| ' where t.owner = :1 and t.table_name = :2 and s.owner = :3 and s.table_name = :4 and t.column_name=s.column_name order by t.column_id' bulk collect into tab_cols using v_tgt_owner, v_tgt_tname, v_src_owner, v_src_tname; if SQL%ROWCOUNT<1 then raise NO_COLUMN_LIST; end if; for i in tab_cols.first..tab_cols.last loop if p_exec=1 then if i=tab_cols.first then v_colist := trim(tab_cols(i).colname); else v_colist := v_colist ||V_NL||V_DELIM|| trim(tab_cols(i).colname); end if; else if i=tab_cols.first then dbms_output.enable(1000); dbms_output.put_line(' '||tab_cols(i).colname); else dbms_output.enable(1000); dbms_output.put_line(' ,'||tab_cols(i).colname); end if; end if; end loop; if v_option = 'A' then for icur in (select l.column_name, l.position from dba_cons_columns l, dba_constraints c where l.owner=c.owner and l.constraint_name=c.constraint_name and c.constraint_type='P' and c.owner=v_tgt_owner and c.table_name=v_tgt_tname order by position) loop if icur.position = 1 then -- v_sqlwhere := icur.column_name; v_sqlwhere := 'B.'||icur.column_name||' = A.'||icur.column_name; else --v_sqlwhere := v_sqlwhere||', '||icur.column_name; v_sqlwhere := v_sqlwhere||' AND B.'||icur.column_name||' = A.'||icur.column_name; end if; end loop; if v_sqlwhere is null then raise NO_PRIMARYKEY; end if; -- v_sqlwhere := 'WHERE ('||v_sqlwhere||') NOT IN ( SELECT '||v_sqlwhere||' FROM '||v_tgt_owner||'.'||v_tgt_tname||')'; v_sqlwhere := 'WHERE NOT EXISTS ( SELECT 1 FROM '||v_tgt_owner||'.'||v_tgt_tname||' B WHERE '||v_sqlwhere||')'; end if; if p_exec=1 then if v_option = 'R' then execute immediate 'truncate table '||v_tgt_owner||'.'||v_tgt_tname; end if; v_sqltxt := 'INSERT INTO '||v_tgt_owner||'.'||v_tgt_tname ||'('|| v_colist ||')'||V_NL ||'SELECT '||v_colist||V_NL||'FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' A '; if v_option = 'A' then v_sqltxt := v_sqltxt ||chr(10)||v_sqlwhere; end if; begin execute immediate v_sqltxt; v_rowcnt := to_char(SQL%ROWCOUNT); v_msg := v_tgt_owner||'.'||v_tgt_tname||':(option='||v_option||'):'||lpad(v_rowcnt,10,'.')||' rows from '||v_src_owner||'.'||v_src_tname||v_dblink; commit; --(-------------------------------------------------------------------------------------------------------- execute immediate 'insert into XXX_DBA.tb_dbm_copy_log(logdt, owner, tname, dblink, sowner, stname, coption, result, rowcount)' ||' values( sysdate, :1, :2, :3, :4, :5, :6, :7, :8)' using v_tgt_owner, v_tgt_tname, v_dblink, v_src_owner, v_src_tname, v_option, 'S', to_number(v_rowcnt); commit; --)-------------------------------------------------------------------------------------------------------- exception when others then rollback; --(-------------------------------------------------------------------------------------------------------- execute immediate 'insert into XXX_DBA.tb_dbm_copy_log(logdt, owner, tname, dblink, sowner, stname, coption, result, errmsg)' ||' values( sysdate, :1, :2, :3, :4, :5, :6, :7, :8)' using v_tgt_owner, v_tgt_tname, v_dblink, v_src_owner, v_src_tname, v_option, 'F', substr(SQLERRM,1,250) ; commit; --)-------------------------------------------------------------------------------------------------------- v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); dbms_output.enable(100); dbms_output.put_line(v_msg); end; dbms_output.enable(2000); dbms_output.put_line(v_msg); else dbms_output.enable(1000); dbms_output.put_line(' )'); dbms_output.put_line(' SELECT '); for i in tab_cols.first..tab_cols.last loop if i=tab_cols.first then dbms_output.enable(1000); dbms_output.put_line(' '||tab_cols(i).colname); else dbms_output.enable(1000); dbms_output.put_line(' ,'||tab_cols(i).colname); end if; end loop; dbms_output.put_line(' FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' A '); if v_option = 'A' then dbms_output.put_line(v_sqlwhere); end if; dbms_output.put_line(';'); end if; exception when NO_COLUMN_LIST then v_msg := 'ERROR : [ NO_COLUMN_LIST ] '||v_tgt_owner||'.'||v_tgt_tname; dbms_output.enable(100); dbms_output.put_line(v_msg); when NO_PRIMARYKEY then v_msg := 'ERROR : [ NO_PRIMARYKEY ] '||v_tgt_owner||'.'||v_tgt_tname; dbms_output.enable(100); dbms_output.put_line(v_msg); when others then v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); dbms_output.enable(100); dbms_output.put_line(v_msg); end;
sp_dbw_copy
CREATE OR REPLACE PROCEDURE sp_dbw_copy ( p_tname in varchar2 default null , p_dblink in varchar2 default null , p_option in varchar2 default 'I' -- I,R,DI,M,AI,AX , p_exec in number default 0 , p_owner in varchar2 default null , p_srcowner in varchar2 default null , p_srctname in varchar2 default null ) is NO_COLUMN_LIST EXCEPTION; NO_PRIMARYKEY EXCEPTION; v_tgt_tname varchar2(50) default null; v_tgt_owner varchar2(30) default null; v_src_owner varchar2(30) default null; v_src_tname varchar2(50) default null; v_option varchar2(2); type typ_rec_cols is record (colid user_tab_columns.column_id%TYPE, colname all_tab_cols.column_name%TYPE, pno number); type typ_tab_cols is table of typ_rec_cols; tab_cols typ_tab_cols; v_dblink varchar2(100) default ' '; v_sql_select varchar2(1000) default ' '; v_sql_dataRemove varchar2(1000) default ' '; v_sqlwhere varchar2(4000); v_sql_copyData long; v_colist long; v_keycols varchar2(4000); v_merge_join varchar2(4000); v_merge_update varchar2(4000); v_merge_insert varchar2(4000); v_msg varchar(4000); v_rowcnt number(15) default 0; v_start_dtm date; v_secs varchar2(10); V_NL char(1) default chr(10); /* new line */ V_TAB char(4) default ' '; /* tab */ V_DELIM varchar2(10) default ','; /* delimter */ begin v_tgt_owner := upper(p_owner); v_tgt_tname := upper(p_tname); v_option := upper(p_option); if p_tname is null or v_option not in ('I','R','DI','M','AI','AX') then v_msg := V_NL||'usage: ' ||V_NL||' exec sp_dbw_copy(' ||V_NL||' p_tname => ''table_name'' ' ||V_NL||' , p_dblink => null ' ||V_NL||' , p_option => ''i'' -- I/R/DI/M/AI/AX ' ||V_NL||' , p_exec => 0 ' ||V_NL||' , p_owner => '''' ' ||V_NL||' , p_srcowner => null ' ||V_NL||' , p_srctname => null); ' ||V_NL||' p_option=> I:Insert, R:Replace(truncate and insert), DI:Delete Insert ' ||V_NL||' , M:Merge, AI:Append(not In), AX:Append(not eXists) ' ; dbms_output.enable(500); dbms_output.put_line(v_msg); return; end if; if (p_srcowner is null) then v_src_owner := v_tgt_owner; else v_src_owner := upper(p_srcowner); end if; if (p_srctname is null) then v_src_tname := v_tgt_tname; else v_src_tname := upper(p_srctname); end if; if (p_dblink is not null) then v_dblink := '@'||p_dblink; end if; if v_option = 'R' then v_sql_dataRemove := 'truncate table '||v_tgt_owner||'.'||v_tgt_tname ; elsif v_option = 'DI' then v_sql_dataRemove := 'delete from '||v_tgt_owner||'.'||v_tgt_tname; end if; -- column list v_sql_select := 'select tgt.column_id, tgt.column_name, p.position pno ' ||V_NL||' from all_tab_cols tgt ' ||V_NL||' join all_tab_cols'||v_dblink||' src ' ||V_NL||' on (src.column_name = tgt.column_name) ' ||V_NL||' left join all_constraints c ' ||V_NL||' on (c.owner = tgt.owner and c.table_name = tgt.table_name and c.constraint_type = ''P'') ' ||V_NL||' left join all_cons_columns p ' ||V_NL||' on (p.owner = c.owner and p.table_name = c.table_name and p.constraint_name = c.constraint_name ' ||V_NL||' and p.owner = tgt.owner and p.table_name = tgt.table_name and p.column_name = tgt.column_name) ' ||V_NL||' where tgt.owner = :1 ' ||V_NL||' and tgt.table_name = :2 ' ||V_NL||' and src.owner = :3 ' ||V_NL||' and src.table_name = :4 ' ||V_NL||' and tgt.virtual_column <> ''YES'' ' ||V_NL||' order by tgt.column_id ' ; execute immediate v_sql_select bulk collect into tab_cols using v_tgt_owner, v_tgt_tname, v_src_owner, v_src_tname; if SQL%ROWCOUNT < 1 then dbms_output.enable(10000); dbms_output.put_line(v_sql_select); raise NO_COLUMN_LIST; end if; for i in tab_cols.first..tab_cols.last loop /* insert column list */ if i=tab_cols.first then v_colist := V_TAB||' SRC.'||trim(tab_cols(i).colname); else v_colist := v_colist ||V_NL||V_TAB||V_DELIM||' SRC.'|| trim(tab_cols(i).colname); end if; /* merge on clause, update set clause */ if tab_cols(i).pno is not null then v_merge_join := v_merge_join||' AND SRC.'||tab_cols(i).colname||' = TGT.'||tab_cols(i).colname; v_keycols := v_keycols ||', '||tab_cols(i).colname; else v_merge_update := v_merge_update||', TGT.'||tab_cols(i).colname||' = SRC.'||tab_cols(i).colname||V_NL||V_TAB; end if; end loop; v_merge_join := substr(v_merge_join, 5, 10000); v_keycols := substr(v_keycols, 2,10000); v_merge_update := substr(v_merge_update, 2, 10000); if v_option in ('M','AI', 'AX') then if v_merge_join is null then raise NO_PRIMARYKEY; end if; if v_option = 'AI' then v_sqlwhere := 'WHERE ('||v_keycols||') NOT IN ( SELECT '||v_keycols||' FROM '||v_tgt_owner||'.'||v_tgt_tname||')'; elsif v_option = 'AX' then v_sqlwhere := 'WHERE NOT EXISTS ( SELECT 1 FROM '||v_tgt_owner||'.'||v_tgt_tname||' TGT WHERE '||v_merge_join||')'; end if; end if; if v_option = 'M' then v_sql_copyData := 'MERGE INTO '||v_tgt_owner||'.'||v_tgt_tname||' TGT ' ||V_NL||'USING '||v_src_owner||'.'||v_src_tname||v_dblink||' SRC ' ||V_NL||' ON ('||v_merge_join||' ) ' ||V_NL||'WHEN MATCHED THEN UPDATE ' ||V_NL||' SET ' ||V_NL||' '||v_merge_update ||V_NL||'WHEN NOT MATCHED THEN ' ||V_NL||' INSERT (' ||V_NL|| replace(replace(v_colist, ' SRC.',' '), V_TAB, V_TAB||V_TAB)||') ' ||V_NL||' VALUES (' ||V_NL||replace(v_colist, V_TAB, V_TAB||V_TAB)||')' ; else v_sql_copyData := 'INSERT '|| case when v_option in ('R','AI','AX') then '/*+ APPEND */' end ||V_NL||' INTO '||v_tgt_owner||'.'||v_tgt_tname ||'(' ||V_NL|| replace(v_colist , ' SRC.', ' ') ||V_NL||')' ||V_NL||'SELECT /*+ PARALLEL(SRC 1) */' ||V_NL||v_colist ||V_NL||'FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' SRC '; if v_option IN ('AI', 'AX') then v_sql_copyData := v_sql_copyData ||chr(10)||v_sqlwhere; end if; end if; if p_exec=1 then begin select sysdate into v_start_dtm from dual; if v_option in ('R','DI') then execute immediate v_sql_dataRemove; end if; execute immediate v_sql_copyData; v_rowcnt := SQL%ROWCOUNT; v_msg := v_tgt_owner||'.'||v_tgt_tname||':(option='||v_option||'):'||lpad(to_char(v_rowcnt, 'fm999,999,999,999'),15,'.')||' rows from '||v_src_owner||'.'||v_src_tname||v_dblink; commit; --(-------------------------------------------------------------------------------------------------------- select to_char(trunc(tm /3600), 'fm00') ||':'||to_char(mod(trunc(tm/60),60),'fm00') ||':'||to_char(mod(tm,60), 'fm00') into v_secs from (select (sysdate - v_start_dtm) * 24 * 60 * 60 tm from dual) ; insert into XXX_DBA.tb_dbw_copy_log (logid, owner, table_name, result, rowcount , src_owner, src_table_name, dblink, copyopt, start_time, end_time, errmsg) values(sq_dbw_copy_log.nextval, v_tgt_owner, v_tgt_tname, 'S', to_number(v_rowcnt) , v_src_owner, v_src_tname, v_dblink, v_option, v_start_dtm, sysdate, v_secs); commit; --)-------------------------------------------------------------------------------------------------------- exception when others then v_msg := SQLERRM; rollback; --(-------------------------------------------------------------------------------------------------------- insert into XXX_DBA.tb_dbw_copy_log (logid, owner, table_name, result, errmsg , src_owner, src_table_name, dblink, copyopt, start_time, end_time) values(sq_dbw_copy_log.nextval, v_tgt_owner, v_tgt_tname, 'S', substr(v_msg, 1, 250) , v_src_owner, v_src_tname, v_dblink, v_option, v_start_dtm, sysdate); commit; --)-------------------------------------------------------------------------------------------------------- v_msg := 'ERROR : '|| substr(v_msg,1,500); dbms_output.enable(1000); dbms_output.put_line(v_msg); end; dbms_output.enable(1000); dbms_output.put_line(v_msg); else dbms_output.enable(10000); dbms_output.put_line(''); if v_option in ('R','DI') then dbms_output.put_line(v_sql_dataRemove||';'); end if; dbms_output.put_line(v_sql_copyData||';'); end if; exception when NO_COLUMN_LIST then v_msg := 'ERROR : [ NO_COLUMN_LIST ] '||v_tgt_owner||'.'||v_tgt_tname; dbms_output.enable(100); dbms_output.put_line(v_msg); when NO_PRIMARYKEY then v_msg := 'ERROR : [ NO_PRIMARYKEY ] '||v_tgt_owner||'.'||v_tgt_tname; dbms_output.enable(100); dbms_output.put_line(v_msg); when others then v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); dbms_output.enable(100); dbms_output.put_line(v_msg); end; /
오라클 FK 생성 프로시져
== 사용법 ==
FK 생성
BEGIN SP_DBM_FK_RECOVER(p_owner => 'XXX',p_exec => 1); END;
실패한 FK 생성
BEGIN SP_DBM_FK_RECOVER(p_owner => 'XXX',p_exec => 1, p_status=>'DISABLE'); END;
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' ;
실패한 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' ;
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; /
관련 테이블 생성
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);
관련 트리거
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; /
오라클 프로시져 패러럴 실행
SET SERVEROUTPUT ON DECLARE l_task_name user_parallel_execute_tasks.task_name%TYPE; l_sql_stmt user_parallel_execute_tasks.sql_stmt%TYPE; BEGIN l_task_name := 'px_update'; l_sql_stmt := 'UPDATE t SET col = expr WHERE rowid BETWEEN :start_id AND :end_id'; dbms_parallel_execute.create_task(task_name => l_task_name); dbms_parallel_execute.create_chunks_by_rowid( task_name => l_task_name, table_owner => user, table_name => 'T', by_row => FALSE, chunk_size => 128 ); dbms_parallel_execute.run_task( task_name => l_task_name, sql_stmt => l_sql_stmt, language_flag => dbms_sql.native, parallel_level => 4 ); WHILE (dbms_parallel_execute.task_status(task_name => l_task_name) NOT IN ( dbms_parallel_execute.chunking_failed, dbms_parallel_execute.finished, dbms_parallel_execute.finished_with_error, dbms_parallel_execute.crashed )) LOOP dbms_lock.sleep(1); END LOOP; CASE dbms_parallel_execute.task_status(task_name => l_task_name) WHEN dbms_parallel_execute.chunking_failed THEN dbms_output.put_line('chunking_failed'); WHEN dbms_parallel_execute.finished THEN dbms_output.put_line('finished'); WHEN dbms_parallel_execute.finished_with_error THEN dbms_output.put_line('finished_with_error'); WHEN dbms_parallel_execute.crashed THEN dbms_output.put_line('crashed'); END CASE; dbms_parallel_execute.drop_task(task_name => l_task_name); END; /