-- ANSI 파일
-- tbwrap iname=PKG_MGM.sql
-- tbsql USER_ID/PASSWORD@SERVICE_ID @PKG_MGM.tbw
CREATE OR REPLACE PACKAGE PKG_MGM
IS
-- 일자 : 2013.05.08
-- 작성 : 박장원
TYPE TYPE_CURSOR IS REF CURSOR;
TYPE TYPE_RECORD IS RECORD ( R_COL01 VARCHAR(4000)
, R_COL02 VARCHAR(4000)
, R_COL03 VARCHAR(4000)
, R_COL04 VARCHAR(4000)
, R_COL05 VARCHAR(4000)
, R_COL06 VARCHAR(4000)
, R_COL07 VARCHAR(4000)
, R_COL08 VARCHAR(4000)
, R_COL09 VARCHAR(4000)
, R_COL10 VARCHAR(4000));
TYPE TYPE_TABLE IS TABLE OF TYPE_RECORD;
TYPE TYPE_ARRAY IS TABLE OF VARCHAR(4000) INDEX BY BINARY_INTEGER;
-- TEMP TABLE 생성 스크립트 추출
FUNCTION FNC_DDL_TBL ( P_TABLE_NAME IN VARCHAR
, P_ALIAS IN VARCHAR := '') RETURN TYPE_TABLE PIPELINED;
-- MERGE INTO 스크립트 추출
FUNCTION FNC_DML_MRG (P_TABLE_NAME IN VARCHAR) RETURN TYPE_TABLE PIPELINED;
-- Primary Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_PK (P_TABLE_NAME IN VARCHAR := '%') RETURN TYPE_TABLE PIPELINED;
-- Foreign Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_FK (P_TABLE_NAME IN VARCHAR := '%') RETURN TYPE_TABLE PIPELINED;
-- Insert 구문 추출
FUNCTION FNC_DML_INS (P_TABLE_NAME IN VARCHAR) RETURN TYPE_TABLE PIPELINED;
-- Table 정의서
FUNCTION FNC_TBL_SCHEMA ( P_TABLE_NAME IN VARCHAR := '%'
, P_SYSTEM_NAME IN VARCHAR := 'SYSTEM'
, P_WRITER_NAME IN VARCHAR := 'SYSTEM') RETURN TYPE_TABLE PIPELINED;
-- Index 스크립트 추출
FUNCTION FNC_DDL_IDX (P_TABLE_NAME IN VARCHAR := '%') RETURN TYPE_TABLE PIPELINED;
-- COLUMN의 DATA_DEFAULT 가져오기
FUNCTION FNC_DATA_DEFAULT ( TABLE_NAME IN VARCHAR
, COLUMN_NAME IN VARCHAR) RETURN VARCHAR;
-- COLUMN의 COLUMN_EXPRESSION 가져오기
FUNCTION FNC_COLUMN_EXPRESSION ( TABLE_NAME IN VARCHAR
, INDEX_NAME IN VARCHAR
, COLUMN_POSITION IN NUMBER) RETURN VARCHAR;
END PKG_MGM;
/
CREATE OR REPLACE PACKAGE BODY PKG_MGM
IS
-- TEMP TABLE 생성 스크립트 추출
FUNCTION FNC_DDL_TBL ( P_TABLE_NAME IN VARCHAR
, P_ALIAS IN VARCHAR := '') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (SELECT COL1
, COL2
, COL3
, COL4
FROM (SELECT RPAD(DECODE(COL.COLUMN_ID, 0, 'SELECT', ','), 8, ' ') AS COL1
, RPAD(NVL2(P_ALIAS, P_ALIAS || '.', P_ALIAS) || COL.COLUMN_NAME, 40, ' ') AS COL2
, RPAD('AS ' || COL.COLUMN_NAME, 40, ' ') AS COL3
, '-- ' || CST.PK || REPLACE(CMT.COMMENTS, CHR(10), ' ') || ' ' || TO_CHAR(TRIM(CST.AK || ' ' || CST.FK)) AS COL4
, COL.TABLE_NAME
, COL.COLUMN_ID
FROM USER_TAB_COLUMNS COL
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || (CTC.POSITION + 1) || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || (CTC.POSITION + 1) || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || (CTC.POSITION + 1) || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS FK
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.TABLE_NAME = CTC.TABLE_NAME
WHERE CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
UNION ALL
SELECT DECODE(NO, 1, 'CREATE TABLE ', 2, 'AS', 3, 'FROM ') AS COL1
, DECODE(NO, 1, T.TABLE_NAME || '_TEMP', 3, T.TABLE_NAME || NVL2(P_ALIAS, ' ' || P_ALIAS, P_ALIAS) || ';') AS COL2
, NULL AS COL3
, DECODE(NO, 3, '-- ' || REPLACE(T.COMMENTS, CHR(10), ' ')) AS COL4
, T.TABLE_NAME
, DECODE(NO, 1, -2, 2, -1) AS COLUMN_ID
FROM USER_TAB_COMMENTS T
, (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 3) D
ORDER BY COLUMN_ID ASC)
WHERE TABLE_NAME = UPPER(P_TABLE_NAME))
LOOP
V_ROW.R_COL01 := CSR_TBL.COL1;
V_ROW.R_COL02 := CSR_TBL.COL2;
V_ROW.R_COL03 := CSR_TBL.COL3;
V_ROW.R_COL04 := CSR_TBL.COL4;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- MERGE INTO 스크립트 추출
FUNCTION FNC_DML_MRG (P_TABLE_NAME IN VARCHAR) RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (WITH TBL_COL
AS
( SELECT COL.TABLE_NAME
, D.NO
, COL.COLUMN_ID
, COL.COLUMN_NAME
, '-- ' || CST.PK || REPLACE(CMT.COMMENTS, CHR(10), ' ') || ' ' || TRIM(CST.AK || ' ' || CST.FK ) AS COMMENTS
, CST.ISPK
, COL.NULLABLE
, PKG_MGM.FNC_DATA_DEFAULT(COL.TABLE_NAME, COL.COLUMN_NAME) AS DATA_DEFAULT
, DECODE(COL.COLUMN_NAME, 'SYS_ID', '#{g.tenant}', '#{p.' || LOWER(COL.COLUMN_NAME) || '}') AS PARAM_COLUMN
, DECODE(COL.COLUMN_ID, FIRST_VALUE(COL.COLUMN_ID) OVER (PARTITION BY D.NO, COL.TABLE_NAME ORDER BY D.NO, COL.COLUMN_ID), 'Y', 'N') AS ISFIRSTCOL
, DECODE(COL.COLUMN_ID, LAST_VALUE(COL.COLUMN_ID) OVER (PARTITION BY D.NO, COL.TABLE_NAME ORDER BY D.NO, COL.COLUMN_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 'Y', 'N') AS ISLASTCOL
, FLOOR(MAX(LENGTH(COL.COLUMN_NAME)) OVER (PARTITION BY D.NO, COL.TABLE_NAME) / 4) * 4 + 4 AS COLLEN
, 4 AS TAB
FROM USER_TAB_COLUMNS COL
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || (CTC.POSITION + 1) || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || (CTC.POSITION + 1) || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || (CTC.POSITION + 1) || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS FK
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.TABLE_NAME = CTC.TABLE_NAME
WHERE CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 11) D ON D.NO IN (1, DECODE(CST.ISPK, 'Y', 3), DECODE(CST.ISPK, NULL, 5), 10, 11)
)
, TBL_DML
AS
( SELECT C.TABLE_NAME
, C.NO
, C.COLUMN_ID
, LPAD(' ', C.TAB * 2, CHR(32)) AS COL1
, DECODE(C.NO , 1, DECODE(C.ISFIRSTCOL, 'Y', RPAD('USING' , C.TAB * 2, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)))
, LPAD(' ', C.TAB * 2, CHR(32))) AS COL2
, DECODE(C.NO , 1, DECODE(C.ISFIRSTCOL, 'Y', RPAD('(SELECT' , C.TAB * 2, CHR(32)) , RPAD(',', C.TAB * 2, CHR(32)))
, 3, DECODE(C.ISFIRSTCOL, 'Y', RPAD('ON (' , C.TAB * 3, CHR(32)) , RPAD(' AND', C.TAB * 3, CHR(32)))
, 5, DECODE(C.ISFIRSTCOL, 'Y', RPAD('UPDATE SET' , C.TAB * 3, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)) || RPAD(',', C.TAB * 1, CHR(32)))
, 10, DECODE(C.ISFIRSTCOL, 'Y', RPAD('INSERT (' , C.TAB * 3, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)) || RPAD(',', C.TAB * 1, CHR(32)))
, 11, DECODE(C.ISFIRSTCOL, 'Y', RPAD('VALUES (' , C.TAB * 3, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)) || RPAD(',', C.TAB * 1, CHR(32)))) AS COL3
, DECODE(C.NO , 1, RPAD(DECODE(C.NULLABLE, 'N', 'NVL('|| C.PARAM_COLUMN || ', ' || C.DATA_DEFAULT || ')', C.PARAM_COLUMN) , C.TAB * 15, CHR(32))
, 3, RPAD('D.' || C.COLUMN_NAME , C.TAB * 10, CHR(32))
, 5, RPAD(C.COLUMN_NAME , C.TAB * 10, CHR(32))
, 10, RPAD(C.COLUMN_NAME || DECODE(C.ISLASTCOL, 'Y', ')') , C.TAB * 10, CHR(32))
, 11, RPAD('D.' || C.COLUMN_NAME || DECODE(C.ISLASTCOL, 'Y', ')') , C.TAB * 10, CHR(32))) AS COL4
, DECODE(C.NO , 1, RPAD('AS ' || C.COLUMN_NAME , C.TAB * 10, CHR(32))
, 3, '= T.' || C.COLUMN_NAME || DECODE(C.ISLASTCOL, 'Y', ')')
, 5, RPAD('= D.' || C.COLUMN_NAME , C.TAB * 10, CHR(32))) AS COL5
, DECODE(C.NO , 3, NULL, C.COMMENTS) AS COL6
FROM TBL_COL C
UNION ALL
SELECT TBL.TABLE_NAME
, D.NO
, -1 AS COLUMN_ID
, DECODE(D.NO , 0, '-- ' || REPLACE(TBL.COMMENTS, CHR(10), ' ')
, 1, 'MERGE INTO '
, LPAD(' ', 8, CHR(32))) AS COL1
, DECODE(D.NO , 1, TBL.TABLE_NAME || ' T'
, 3, LPAD(' ', 8, CHR(32))
, 5, 'WHEN '
, 10, 'WHEN '
, LPAD(' ', 8, CHR(32))) AS COL2
, DECODE(D.NO , 2, RPAD(',', 8, CHR(32))
, 3, RPAD('FROM DUAL)', 12, CHR(32))
, 5, 'MATCHED THEN'
, 6, RPAD('WHERE', 8, CHR(32))
, 8, RPAD('DELETE WHERE', 16, CHR(32))
, 10, 'NOT MATCHED THEN'
, 12, RPAD('WHERE', 8, CHR(32))) AS COL3
, DECODE(D.NO , 2, RPAD('#{p.del_yn}', 60, CHR(32))
, 3, 'D'
, 6, '(1 = 1)'
, 8, '(D.DEL_YN = ' || CHR(39) || 'Y' || CHR(39) || ')'
, 12, '(1 = 1);') AS COL4
, DECODE(D.NO , 2, RPAD('AS DEL_YN', 40, CHR(32))) AS COL5
, DECODE(D.NO , 2, '-- 삭제여부 [Y/N]') AS COL6
FROM USER_TAB_COMMENTS TBL
JOIN (SELECT LEVEL - 1 AS NO
FROM DUAL
CONNECT BY LEVEL <= 13) D ON D.NO <> 11
)
SELECT T.*
FROM TBL_DML T
WHERE T.TABLE_NAME = UPPER(P_TABLE_NAME)
ORDER BY T.NO, T.COLUMN_ID)
LOOP
V_ROW.R_COL01 := CSR_TBL.COL1;
V_ROW.R_COL02 := CSR_TBL.COL2;
V_ROW.R_COL03 := CSR_TBL.COL3;
V_ROW.R_COL04 := CSR_TBL.COL4;
V_ROW.R_COL05 := CSR_TBL.COL5;
V_ROW.R_COL06 := CSR_TBL.COL6;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Primary Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_PK (P_TABLE_NAME IN VARCHAR := '%') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (WITH CST_TBL
AS
( SELECT CST.*
, COL.COLUMN_NAME
, COL.POSITION
FROM ( SELECT S.OWNER
, S.CONSTRAINT_NAME
, S.TABLE_NAME
, S.STATUS
, S.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS S
WHERE S.CONSTRAINT_TYPE IN ('P', 'U')) CST
JOIN USER_CONS_COLUMNS COL ON COL.OWNER = CST.OWNER
AND COL.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
AND COL.TABLE_NAME = CST.TABLE_NAME
)
SELECT 'ALTER TABLE ' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';' AS SCRIPT_DROP
, 'ALTER TABLE ' || TABLE_NAME || ' ADD CONSTRAINT ' || CONSTRAINT_NAME ||
DECODE(CONSTRAINT_TYPE, 'P', ' PRIMARY KEY ', 'U', ' UNIQUE ') || '(' || CST_COLUMNS || ');' AS SCRIPT_ADD
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' ENABLE;' AS SCRIPT_ENABLE
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' DISABLE;' AS SCRIPT_DISABLE
FROM (SELECT TABLE_NAME AS TABLE_NAME
, CONSTRAINT_NAME AS CONSTRAINT_NAME
, CONSTRAINT_TYPE AS CONSTRAINT_TYPE
, LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY POSITION) AS CST_COLUMNS
FROM CST_TBL
GROUP BY TABLE_NAME
, CONSTRAINT_NAME
, CONSTRAINT_TYPE)
WHERE TABLE_NAME LIKE UPPER(P_TABLE_NAME))
LOOP
V_ROW.R_COL01 := CSR_TBL.SCRIPT_DROP;
V_ROW.R_COL02 := CSR_TBL.SCRIPT_ADD;
V_ROW.R_COL03 := CSR_TBL.SCRIPT_ENABLE;
V_ROW.R_COL04 := CSR_TBL.SCRIPT_DISABLE;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Foreign Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_FK (P_TABLE_NAME IN VARCHAR := '%') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (WITH CST_TBL
AS
( SELECT CST.*
, COL.COLUMN_NAME
, COL.POSITION
FROM ( SELECT S.OWNER
, S.CONSTRAINT_NAME
, S.TABLE_NAME
, S.R_OWNER
, S.R_CONSTRAINT_NAME
, S.DELETE_RULE
, S.STATUS
FROM USER_CONSTRAINTS S
START WITH S.CONSTRAINT_TYPE IN ('P', 'U')
--AND S.TABLE_NAME = UPPER(P_TABLE_NAME)
CONNECT BY PRIOR S.OWNER = S.R_OWNER
AND PRIOR S.CONSTRAINT_NAME = S.R_CONSTRAINT_NAME) CST
JOIN USER_CONS_COLUMNS COL ON COL.OWNER = CST.OWNER
AND COL.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
AND COL.TABLE_NAME = CST.TABLE_NAME
)
SELECT 'ALTER TABLE ' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';' AS SCRIPT_DROP
, 'ALTER TABLE ' || TABLE_NAME || ' ADD CONSTRAINT ' || CONSTRAINT_NAME || ' FOREIGN KEY (' || FK_COLUMNS || ') REFERENCES '
|| R_TABLE_NAME || ' (' || PK_COLUMNS || ')' || DECODE(DELETE_RULE, 'SET NULL', ' ON DELETE ' || DELETE_RULE) || ';' AS SCRIPT_ADD
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' ENABLE;' AS SCRIPT_ENABLE
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' DISABLE;' AS SCRIPT_DISABLE
FROM (SELECT T02.TABLE_NAME AS TABLE_NAME
, T02.CONSTRAINT_NAME AS CONSTRAINT_NAME
, LISTAGG(T02.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY T02.POSITION) AS FK_COLUMNS
, T01.TABLE_NAME AS R_TABLE_NAME
, LISTAGG(T01.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY T01.POSITION) AS PK_COLUMNS
, T02.DELETE_RULE AS DELETE_RULE
FROM CST_TBL T01
JOIN CST_TBL T02 ON T02.R_OWNER = T01.OWNER
AND T02.R_CONSTRAINT_NAME = T01.CONSTRAINT_NAME
AND T02.POSITION = T01.POSITION
GROUP BY T02.TABLE_NAME
, T02.CONSTRAINT_NAME
, T01.TABLE_NAME
, T02.DELETE_RULE)
WHERE R_TABLE_NAME LIKE UPPER(P_TABLE_NAME))
LOOP
V_ROW.R_COL01 := CSR_TBL.SCRIPT_DROP;
V_ROW.R_COL02 := CSR_TBL.SCRIPT_ADD;
V_ROW.R_COL03 := CSR_TBL.SCRIPT_ENABLE;
V_ROW.R_COL04 := CSR_TBL.SCRIPT_DISABLE;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Insert 구문 추출
FUNCTION FNC_DML_INS (P_TABLE_NAME IN VARCHAR) RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (SELECT COL1
, COL2
, COL3
, COL4
, COL5
, COL6
FROM (SELECT TAB.TABLE_NAME
, DECODE(D.NO, 1, 0
, 2, 99998
, 3, 99999) AS NO
, DECODE(D.NO, 1, 'SELECT ' || CHR(39) || 'INSERT INTO ' || TAB.TABLE_NAME || ' VALUES (' || CHR(39)
, 2, '|| ' || CHR(39) || ');' || CHR(39) || ' AS INS_SCRIPT'
, 3, 'FROM ' || TAB.TABLE_NAME || ';') AS COL1
, NULL AS COL2
, NULL AS COL3
, NULL AS COL4
, NULL AS COL5
, NULL AS COL6
FROM USER_TAB_COMMENTS TAB
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 3) D ON 1 = 1
UNION ALL
SELECT COL.TABLE_NAME
, COL.COLUMN_ID AS NO
, DECODE(COL.COLUMN_ID, 1, ' ', '|| CHR(44) ') AS COL1
, CASE
WHEN COL.DATA_TYPE IN ('NVARCHAR', 'VARCHAR', 'CHAR', 'LONG', 'NUMBER') THEN '|| CHR(39) '
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') THEN '|| ' || CHR(39) || 'TO_DATE(' || CHR(39) || '|| CHR(39) '
ELSE ' '
END AS COL2
, '|| ' || RPAD(
CASE
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') THEN 'TO_CHAR('
END
|| COL.COLUMN_NAME ||
CASE
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') THEN ', ' || CHR(39) || 'YYYYMMDDHH24MISS' || CHR(39) || ')'
END
, 70, ' ') || ' ' AS COL3
, CASE
WHEN COL.DATA_TYPE IN ('NVARCHAR', 'VARCHAR', 'CHAR', 'LONG', 'NUMBER') THEN '|| CHR(39) '
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') THEN '|| CHR(39) || CHR(44) || CHR(39) || ' || CHR(39) || 'YYYYMMDDHH24MISS' || CHR(39) || ' || CHR(39) || ' || CHR(39) || ')' || CHR(39)
ELSE ' '
END AS COL4
, '--' AS COL5
, 'REPLACE(' || COL.COLUMN_NAME || ', CHR(39), CHR(39) || ' || CHR(39) || ' || CHR(39) || ' || CHR(39) || ' || CHR(39))' AS COL6
FROM USER_TAB_COLUMNS COL
) T
WHERE T.TABLE_NAME = UPPER(P_TABLE_NAME)
ORDER BY T.NO ASC)
LOOP
V_ROW.R_COL01 := CSR_TBL.COL1;
V_ROW.R_COL02 := CSR_TBL.COL2;
V_ROW.R_COL03 := CSR_TBL.COL3;
V_ROW.R_COL04 := CSR_TBL.COL4;
V_ROW.R_COL05 := CSR_TBL.COL5;
V_ROW.R_COL06 := CSR_TBL.COL6;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Table 정의서
FUNCTION FNC_TBL_SCHEMA ( P_TABLE_NAME IN VARCHAR := '%'
, P_SYSTEM_NAME IN VARCHAR := 'SYSTEM'
, P_WRITER_NAME IN VARCHAR := 'SYSTEM') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (SELECT COL01
, COL02
, COL03
, COL04
, COL05
, COL06
, COL07
, COL08
, COL09
, COL10
FROM (SELECT TABLE_NAME AS TABLE_NAME
, NO AS GROUP_NO
, 1 AS ORDER_NO
, DECODE(NO, 2, '시스템명', 3, '테이블ID', 4, '테이블명', 5, 'NO') AS COL01
, DECODE(NO, 2, P_SYSTEM_NAME, 3, TABLE_NAME, 4, REPLACE(COMMENTS, CHR(10), ' '), 5, '컬럼ID') AS COL02
, DECODE(NO, 5, '컬럼명') AS COL03
, DECODE(NO, 5, 'KEY') AS COL04
, DECODE(NO, 2, '작성일', 3, '작성자', 4, NULL, 5, 'TYPE') AS COL05
, DECODE(NO, 2, TO_CHAR(SYSDATE, 'YYYY.MM.DD'), 3, P_WRITER_NAME, 4, NULL, 5, '길이') AS COL06
, DECODE(NO, 5, 'BYTE') AS COL07
, DECODE(NO, 5, 'NULLABLE') AS COL08
, DECODE(NO, 5, 'DEFAULT') AS COL09
, DECODE(NO, 5, '특성') AS COL10
FROM (SELECT T.TABLE_NAME
, T.COMMENTS
, D.NO
FROM USER_TAB_COMMENTS T
, (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 5) D
WHERE T.TABLE_TYPE = 'TABLE')
UNION ALL
SELECT COL.TABLE_NAME AS TABLE_NAME
, 6 AS GROUP_NO
, COL.COLUMN_ID AS ORDER_NO
, TO_CHAR(COL.COLUMN_ID + 1) AS COL01
, COL.COLUMN_NAME AS COL02
, REPLACE(CMT.COMMENTS, CHR(10), ' ') AS COL03
, DECODE(CST.ISPK, 'Y', 'PK', ' ') AS COL04
, COL.DATA_TYPE AS COL05
, CASE
WHEN COL.DATA_TYPE = 'NUMBER' AND COL.DATA_SCALE IS NOT NULL THEN '(' || NVL(COL.DATA_PRECISION, 38) || ', ' || COL.DATA_SCALE || ')'
WHEN COL.DATA_TYPE IN ('CHAR', 'VARCHAR') THEN '(' || COL.CHAR_LENGTH || DECODE(COL.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR') || ')'
ELSE ' '
END AS COL06
, TO_CHAR(DECODE(COL.CHAR_USED, 'C', COL.DATA_LENGTH * LENGTHB('가'), COL.DATA_LENGTH)) AS COL07
, DECODE(COL.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') AS COL08
, TRIM(PKG_MGM.FNC_DATA_DEFAULT(COL.TABLE_NAME, COL.COLUMN_NAME)) AS COL09
, TRIM(DECODE(CST.ISFK, 'Y', 'FK', ' ') || ' ' || DECODE(CST.ISAK, 'Y', 'AK', ' ')) AS COL10
FROM USER_TAB_COLUMNS COL
JOIN USER_TAB_COMMENTS TAB ON TAB.TABLE_NAME = COL.TABLE_NAME
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || CTC.POSITION + 1 || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION + 1 || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION + 1 || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS FK
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.TABLE_NAME = CTC.TABLE_NAME
WHERE CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
WHERE TAB.TABLE_TYPE = 'TABLE')
WHERE TABLE_NAME LIKE UPPER(P_TABLE_NAME)
ORDER BY TABLE_NAME
, GROUP_NO
, ORDER_NO)
LOOP
V_ROW.R_COL01 := CSR_TBL.COL01;
V_ROW.R_COL02 := CSR_TBL.COL02;
V_ROW.R_COL03 := CSR_TBL.COL03;
V_ROW.R_COL04 := CSR_TBL.COL04;
V_ROW.R_COL05 := CSR_TBL.COL05;
V_ROW.R_COL06 := CSR_TBL.COL06;
V_ROW.R_COL07 := CSR_TBL.COL07;
V_ROW.R_COL08 := CSR_TBL.COL08;
V_ROW.R_COL09 := CSR_TBL.COL09;
V_ROW.R_COL10 := CSR_TBL.COL10;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Index 스크립트 추출
FUNCTION FNC_DDL_IDX (P_TABLE_NAME IN VARCHAR := '%') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_ROW IN (SELECT IDX.TABLE_NAME
, IDX.INDEX_NAME
, 'CREATE ' || DECODE(IDX.UNIQUENESS, 'UNIQUE', 'UNIQUE ', '') || 'INDEX ' || IDX.INDEX_NAME || ' ON ' || IDX.TABLE_OWNER || '.' || IDX.TABLE_NAME
|| ' (' ||
(SELECT LISTAGG(NVL(PKG_MGM.FNC_COLUMN_EXPRESSION(COL.TABLE_NAME, COL.INDEX_NAME, COL.COLUMN_POSITION), COL.COLUMN_NAME) || ' ' || COL.DESCEND, ', ') WITHIN GROUP (ORDER BY COL.COLUMN_POSITION)
FROM USER_IND_COLUMNS COL
WHERE COL.TABLE_NAME = IDX.TABLE_NAME
AND COL.INDEX_NAME = IDX.INDEX_NAME)
|| ') '
|| 'TABLESPACE ' || IDX.TABLESPACE_NAME || ';' AS CREATE_SCRIPT
, 'ALTER INDEX ' || IDX.INDEX_NAME || ' REBUILD TABLESPACE ' || IDX.TABLESPACE_NAME || ';' AS REBULT_SCRIPT
, 'DROP INDEX ' || IDX.INDEX_NAME || ';' AS DROP_SCRIPT
, TO_CHAR(OBJ.LAST_DDL_TIME, 'YYYY/MM/DD HH24:MI:SS') AS MODIFY_DTIME
FROM USER_INDEXES IDX
JOIN USER_OBJECTS OBJ ON OBJ.OBJECT_NAME = IDX.INDEX_NAME
WHERE IDX.INDEX_TYPE NOT IN ('LOB')
AND IDX.TABLE_NAME LIKE UPPER(P_TABLE_NAME)
ORDER BY IDX.INDEX_NAME)
LOOP
V_ROW.R_COL01 := CSR_ROW.TABLE_NAME;
V_ROW.R_COL02 := CSR_ROW.INDEX_NAME;
V_ROW.R_COL03 := CSR_ROW.DROP_SCRIPT;
V_ROW.R_COL04 := CSR_ROW.CREATE_SCRIPT;
V_ROW.R_COL05 := CSR_ROW.REBULT_SCRIPT;
V_ROW.R_COL06 := CSR_ROW.MODIFY_DTIME;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- COLUMN의 DATA_DEFAULT 가져오기
FUNCTION FNC_DATA_DEFAULT ( TABLE_NAME IN VARCHAR
, COLUMN_NAME IN VARCHAR) RETURN VARCHAR
IS
V_RTN VARCHAR(32767);
V_SQL VARCHAR(2000);
BEGIN
V_SQL := 'SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ' || CHR(39) || TABLE_NAME || CHR(39) || ' AND COLUMN_NAME = ' || CHR(39) || COLUMN_NAME || CHR(39);
EXECUTE IMMEDIATE V_SQL INTO V_RTN;
V_RTN := TRIM(SUBSTR(REPLACE(V_RTN, CHR(10), ''), 1, 4000));
RETURN V_RTN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- COLUMN의 COLUMN_EXPRESSION 가져오기
FUNCTION FNC_COLUMN_EXPRESSION ( TABLE_NAME IN VARCHAR
, INDEX_NAME IN VARCHAR
, COLUMN_POSITION IN NUMBER) RETURN VARCHAR
IS
V_RTN VARCHAR(32767);
V_SQL VARCHAR(2000);
BEGIN
V_SQL := 'SELECT COLUMN_EXPRESSION FROM USER_IND_EXPRESSIONS WHERE TABLE_NAME = ' || CHR(39) || TABLE_NAME || CHR(39) || ' AND INDEX_NAME = ' || CHR(39) || INDEX_NAME || CHR(39) || ' AND COLUMN_POSITION = ' || COLUMN_POSITION;
EXECUTE IMMEDIATE V_SQL INTO V_RTN;
V_RTN := TRIM(SUBSTR (V_RTN, 1, 4000));
RETURN V_RTN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
RETURN NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END PKG_MGM;
/
Tibero 관리 패키지 [for emro 9.1]
|
2019.11.04 10:01:20
|
2020.03.18 16:16:52
|
464
|
Aiden
Total of Attached file
0.00 Bytes of 0 files
2024.01.08
2023.05.16
2019.11.04
2018.09.10
2017.11.05
2017.10.29
2016.09.20
2016.01.21