오라클 프로시져
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 ORACLE 프로시져[편집]
1.1 프로시져[편집]
1.1.1 프로시져 재 컴파일[편집]
ALTER PROCEDURE [PROCEDURE NAME] COMPILE;SELECT 'ALTER PROCEDURE '||OBJECT_NAME||' COMPILE;' DSQL
FROM USER_PROCEDURES
WHERE OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME
;
1.1.2 프로시져 소스 조회[편집]
SELECT *
FROM DBA_SOURCE
WHERE TYPE='PROCEDURE'
AND TEXT LIKE '%PROD_ID%';1.2 패키지에서 주석처리 되지 않은 항목 조회[편집]
오라클 사용자가 사용하는 패키지의 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%');2 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';3 프로시져_샘플[편집]
3.1 기본 프로시져[편집]
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;3.2 프로시져 실행 옵션[편집]
AUTHID DEFINER 실행 시 컴파일 할 때의 유저 사용(DEFAULT)
AUTHID CURRENT_USER 실행 시 현재 접속하고 있는 유저 사용.- PL/SQL내에서 EXECUTE IMEDIATE 실행 시 권한없음 에러가 발생하면 AUTHID CURRENT_USER 추가
3.3 커서 활용 샘플[편집]
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;
/3.4 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;
/4 오라클 프로시져 함수 목록[편집]
4.1 저장프로시저 명세[편집]
4.1.1 (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;4.1.2 (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;4.1.3 (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;
4.2 사용자정의함수 명세[편집]
4.2.1 (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;4.2.2 (3) 사용자정의함수 내용[편집]
SELECT TEXT
FROM SYS.USER_SOURCE
WHERE TYPE = 'FUNCTION'
AND NAME = '[사용자정의함수명]';5 오라클 테이블 복사 프로시져[편집]
5.1 오라클 테이블 간 데이터 복사 프로시져[편집]
5.1.1 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;5.1.2 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;
/6 오라클 FK 생성 프로시져[편집]
== 사용법 ==
6.1 FK 생성[편집]
BEGIN
SP_DBM_FK_RECOVER(p_owner => 'XXX',p_exec => 1);
END;6.2 실패한 FK 생성[편집]
BEGIN
SP_DBM_FK_RECOVER(p_owner => 'XXX',p_exec => 1, p_status=>'DISABLE');
END;6.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'
;6.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'
;6.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;
/6.6 관련 테이블 생성[편집]
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);6.7 관련 트리거[편집]
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;
/7 오라클 프로시져 패러럴 실행[편집]
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;
/