SQL: PL/SQL打印用户表脚本文本
PLSQL打印用户表脚本文本
环境： ORACLE 19C
create or replace package CUX_util_pkg2 is
-- Author : SAM
-- Created : 2022/8/21 10:20:39
-- Purpose : LONG_TO_CHAR
-- 更新：2023/4/24,增加识别临时表
-- -- 将ORACLE LONG类型转为字符串类型
FUNCTION LONG_TO_CHAR(
p_rowid rowid,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2
) RETURN VARCHAR2;
--
-- 获取字段默认值
FUNCTION get_data_default(
p_rowid rowid ,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2,
p_data_default varchar2
) RETURN VARCHAR2 ;
-- 例子
-- SELECT CUX_util_pkg2.get_data_default('','CUX','CUX_WIP_TRX_LINES_ALL','LAST_UPDATE_DATE','DATA_DEFAULT') DATA_DEFAULT
-- FROM DUAL;
FUNCTION GET_COMMENT_DDL(object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL
) RETURN CLOB ;
FUNCTION GET_DDL(object_type IN VARCHAR2,
p_table_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT NULL
) RETURN CLOB;
end CUX_util_pkg2;
/
create or replace package body CUX_util_pkg2 is
-- -- 将ORACLE LONG类型转为字符串类型
-- p_rowid: 隐藏的主键
-- p_owner: 用户名（SCHEMA)
-- p_table_name: 表名
-- p_column: clob类型的字段名称，即需要从CLOB类型转为VARCHAR类型的字段名称。
FUNCTION LONG_TO_CHAR(
p_rowid rowid,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2
)
RETURN varchar2 AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--set serveroutput on size 10000000000;--
begin
DBMS_OUTPUT.ENABLE(buffer_size => null);
sql_cur := 'select ' || p_column || ' from ' || p_owner || '.' ||
p_table_name || ' where rowid = ' || chr(39) || p_rowid || chr(39);
-- dbms_output.put_line(sql_cur);
execute immediate sql_cur
into text_c1;
text_c1 := substr(text_c1, 1, 4000);
RETURN TEXT_C1;
END LONG_TO_CHAR;
-- get_data_default(): 获取字段默认值
-- p_rowid: 隐藏的主键
-- p_owner: 用户名（SCHEMA)
-- p_table_name: 表名
-- p_column: clob类型的字段名称，即需要从CLOB类型转为VARCHAR类型的字段名称。
-- p_data_default: 默认值字段
FUNCTION get_data_default(
p_rowid rowid ,
p_owner varchar2,
p_table_name varchar2,
p_column varchar2,
p_data_default varchar2
)
RETURN varchar2 AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--set serveroutput on size 10000000000;--
begin
DBMS_OUTPUT.ENABLE(buffer_size => null);
sql_cur := 'select ' || p_data_default || ' from sys.dba_tab_columns where owner=' || chr(39) || UPPER(p_owner) || chr(39)
|| ' and table_name = ' || chr(39) || UPPER(p_table_name) || chr(39)
|| ' and column_name = ' || chr(39) || UPPER(p_column) || chr(39);
--dbms_output.put_line(sql_cur);
execute immediate sql_cur
into text_c1;
text_c1 := substr(text_c1, 1, 4000);
RETURN text_c1;
END get_data_default;
-- SELECT get_data_default('ROWID',col.owner,col.table_name,col.COLUMN_NAME,'DATA_DEFAULT') FROM DUAL;
FUNCTION GET_COMMENT_DDL(object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL
) RETURN CLOB
AS
V_COMMENTS varchar2(500);
V_TAB_COMMENTS varchar2(500);
L_CLOB CLOB;
CURSOR C_COMMENT IS
SELECT OWNER,TABLE_NAME, COLUMN_NAME, COMMENTS
FROM DBA_COL_COMMENTS
WHERE TABLE_NAME = UPPER(NAME) --'SFY_OE_QUOTATION_HEADERS_ALL'
AND OWNER = NVL(UPPER(SCHEMA),OWNER)
AND COMMENTS IS NOT NULL ;
CURSOR C_TAB_COMMENT IS
SELECT OWNER,TABLE_NAME, TABLE_TYPE, COMMENTS
FROM DBA_TAB_COMMENTS
WHERE TABLE_NAME = UPPER(NAME) --'SFY_OE_QUOTATION_HEADERS_ALL'
AND OWNER = NVL(UPPER(SCHEMA),OWNER)
AND COMMENTS IS NOT NULL ;
BEGIN
L_CLOB := NULL;
FOR R_TB IN C_TAB_COMMENT LOOP
V_TAB_COMMENTS := 'COMMENT ON TABLE '|| R_TB.OWNER||'.'||R_TB.TABLE_NAME||' IS '||chr(39) || R_TB.COMMENTS ||chr(39) ||';' || CHR(10);
L_CLOB := L_CLOB || V_TAB_COMMENTS;
END LOOP;
FOR R_COM IN C_COMMENT LOOP
V_COMMENTS := 'COMMENT ON COLUMN '|| R_COM.OWNER||'.'||R_COM.TABLE_NAME||'.'||R_COM.COLUMN_NAME ||' IS '||chr(39) || R_COM.COMMENTS ||chr(39) ||';' || CHR(10);
L_CLOB := L_CLOB || V_COMMENTS;
END LOOP;
RETURN L_CLOB;
END GET_COMMENT_DDL;
FUNCTION GET_DDL(object_type IN VARCHAR2,
p_table_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT NULL
)
return clob AS
i number;
l_clob clob;
l_clob_comment clob;
v_name varchar2(128);
v_schema varchar2(128);
v_table_header varchar2(500);
v_table_footer varchar2(500);
v_column_desc varchar2(500);
v_col_count number;
-- 表游标
cursor c_table( p_name varchar2, p_schema varchar2) is
select owner, table_name, tablespace_name, dt.temporary,dt.duration
from dba_tables dt
where owner = nvl( p_schema,owner) -- 'SFY'
AND TABLE_NAME =p_name -- 'SFY_WIP_TRX_LINES_ALL'
;
-- 字段 游标
cursor c_column(p_name varchar2, p_schema varchar2) is
SELECT COL.COLUMN_NAME || CHR(32)|| COL.DATA_TYPE ||
CASE WHEN INSTR(COL.DATA_TYPE,'CHAR',1)>0 THEN
'('||COL.DATA_LENGTH||') '
END
||
CASE WHEN COL.NULLABLE = 'N' THEN
CASE WHEN col.default_length>0 THEN
' DEFAULT '|| get_data_default('ROWID',col.owner,col.table_name,col.COLUMN_NAME,'DATA_DEFAULT') || ' NOT NULL '
ELSE
' NOT NULL '
END
ELSE
''
END AS column_desc
FROM sys.DBA_TAB_COLUMNS COL
WHERE TABLE_NAME = p_name -- 'SFY_WIP_TRX_LINES_ALL'
and owner = nvl(p_schema , owner)
-- AND COLUMN_NAME = 'LAST_UPDATE_DATE'
ORDER BY COL.COLUMN_ID ;
-- 统计表有多少个字段
cursor c_column2(p_name varchar2, p_schema varchar2) is
SELECT count(COL.COLUMN_NAME) as col_count
FROM sys.DBA_TAB_COLUMNS COL
WHERE TABLE_NAME = p_name -- 'SFY_WIP_TRX_LINES_ALL'
and owner = nvl(p_schema , owner)
-- AND COLUMN_NAME = 'LAST_UPDATE_DATE'
ORDER BY COL.COLUMN_ID ;
V_CREATE VARCHAR2(100);
begin
-- v_name := 'CUX_WIP_TRX_LINES_ALL';
-- v_schema := 'CUX';
v_name := upper(p_table_name);
v_schema := upper(p_owner);
l_clob := null;
--#region:table
IF UPPER(OBJECT_TYPE) = 'TABLE' THEN
-- 表游标
for r_tab in c_table(v_name, v_schema) loop
if r_tab.temporary= 'Y' THEN
V_CREATE := 'CREATE GLOBAL TEMPORARY TABLE ';
ELSE
V_CREATE :='CREATE TABLE ';
END IF;
v_table_header := V_CREATE || r_tab.owner ||'.'|| r_tab.table_name || ' ( '|| chr(10);
-- v_table_footer := ') TABLESPACE '|| r_tab.tablespace_name ||'; ' ||chr(10)||chr(13);
IF r_tab.duration = 'SYS$TRANSACTION' then
v_table_footer :=' ) ON COMMIT DELETE ROWS ' ||
' RESULT_CACHE (MODE DEFAULT) ' ||
' NOCACHE;'||chr(10)||chr(13);
elsif r_tab.duration = 'SYS$SESSION' then
v_table_footer :=' ) ON COMMIT PRESERVE ROWS ' ||
' RESULT_CACHE (MODE DEFAULT) ' ||
' NOCACHE;'||chr(10)||chr(13);
else
v_table_footer := ') ; ' ||chr(10)||chr(13);
end if;
--#region:column
for r_col2 in c_column2(v_name, v_schema) loop
v_col_count := r_col2.col_count;
end loop;
-- 字段游标
open c_column(v_name, v_schema) ;
loop
fetch c_column into v_column_desc ;
exit when(c_column%notfound);
-- dbms_output.put_line('v_col_count='||v_col_count);
if v_col_count = c_column%rowcount then
l_clob := l_clob || v_column_desc || CHR(10);
else
l_clob := l_clob || v_column_desc || ',' || CHR(10);
end if;
-- dbms_output.put_line('v_col_count-> c_column%rowcount ='||c_column%rowcount);
end loop;
close c_column;
/*
for r_col in c_column(v_name, v_schema) loop
if i = c_column%rowcount - 1 then
l_clob := l_clob || r_col.column_desc || CHR(10);
else
l_clob := l_clob || r_col.column_desc || ',' || CHR(10);
end if;
end loop;
*/
--#endregion:column
l_clob := v_table_header || l_clob || v_table_footer;
end loop;
END IF;
--#endregion:table
--#regeion:commnt
-- 类型为 “注释”COMMENT
IF object_type='COMMENT' THEN
SELECT GET_COMMENT_DDL('COMMENT',V_NAME,V_SCHEMA)
INTO l_clob_comment
FROM DUAL ;
L_CLOB := L_CLOB || l_clob_comment;
END IF;
--#endregeion:commnt
return l_clob;
end GET_DDL;
begin
-- Initialization
-- <Statement>;
NULL;
end CUX_util_pkg2;
/
优质生活从拆开始
