DECLARE
del_sql varchar2(500) := '';
create_sql varchar2(500) := '';
v_count number(10);
suffix varchar2(500) := '_TEST';
table_name_bak varchar2(500) := '非资产表';
column_name_bak varchar2(500) := '备用字段';
notes_sql varchar2(500) := '';
default_value varchar2(500) := '';
-------------------------------手动修改区域--------------------------------------------------------
--输入需要创建测试表的表名称
Prm_Sourcestr varchar2(500) := 'fact_buildnode1';
-------------------------------------------------------------------------------------------
BEGIN
--删除原有的测试表
select count(*)
into v_count
from user_objects
where object_name = upper(Prm_Sourcestr)||suffix;
if v_count > 0 then
execute immediate 'drop table ' || Prm_Sourcestr||suffix ||' purge';
end if;
--创建表
create_sql:='create table '||upper(Prm_Sourcestr)||suffix||' as '||'select * from '||upper(Prm_Sourcestr)||' where 1<>1';
execute immediate create_sql;
--创建表注释
notes_sql:='comment on table '||Prm_Sourcestr||suffix||' is '''||table_name_bak||'''';
execute immediate notes_sql;
for wf in (SELECT column_name,data_type FROM user_tab_columns where table_name = upper(Prm_Sourcestr) )
loop
--创建字段注释
notes_sql:='comment on column '||Prm_Sourcestr||suffix||'.'||wf.column_name ||' is '''||column_name_bak||'''';
execute immediate notes_sql;
end loop ;
--
--SELECT COLUMN_NAME,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME ='FACT_BUILDNODE1'
for wf in ( SELECT COLUMN_NAME,DATA_DEFAULT FROM USER_TAB_COLUMNS where TABLE_NAME = upper(Prm_Sourcestr) )
loop
--添加表默认值
if wf.DATA_DEFAULT IS NOT NULL THEN
default_value:='alter TABLE '|| Prm_Sourcestr||suffix || ' modify '|| wf.COLUMN_NAME || ' default '|| wf.DATA_DEFAULT;
dbms_output.put_line(default_value);
execute immediate default_value;
end if;
end loop ;
END;
oracle函数自动创建测试表和字段注释
最新推荐文章于 2024-05-27 01:18:31 发布