oracle函数自动创建测试表和字段注释

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;