大批量复制Oracle数据表，连带复制主键约束，字段说明以及字段默认值(量产)

DECLARE
      CURSOR tab_name_cur
      IS
          SELECT table_name
          FROM user_tables
          WHERE table_name LIKE 'GZD_GZDXX_%_2017';   
      tab_name_rec         tab_name_cur%ROWTYPE ;
      SQL_Str_Create       VARCHAR2(2500);
      SQL_Str_Comments     VARCHAR2(2500);
      SQL_Str_Pk           VARCHAR2(2500);
      Pk_column_name    user_cons_columns.column_name%TYPE;
      SQL_Str_default      VARCHAR2(2500);
    
BEGIN
      OPEN tab_name_cur;
      LOOP
       FETCH tab_name_cur INTO tab_name_rec;
       EXIT
            WHEN tab_name_cur%NOTFOUND;
               --dbms_output.put_line(tab_name_rec.table_name);
           --复制表
           SQL_Str_Create := ' CREATE TABLE ' || SUBSTR(tab_name_rec.table_name,1,LENGTH(tab_name_rec.table_name)-5)
                           || '_2018' || ' AS SELECT * FROM ' || tab_name_rec.table_name || ' WHERE 1=2' || ';' ;
           dbms_output.put_line(SQL_Str_Create);
           --execute IMMEDIATE SQL_Str_Create;
           --查询主键
        SELECT string_agg(cu.column_name) as  column_name INTO Pk_column_name
                    FROM user_cons_columns cu, user_constraints au
                    WHERE cu.constraint_name = au.constraint_name
                    AND au.constraint_type = 'P'
                    AND au.table_name = tab_name_rec.table_name
                    GROUP BY cu.constraint_name;
            
         --dbms_output.put_line('pk_column_name :' || tab_pk_rec.column_name || ' and constraint_name :' ||                 tab_pk_rec.constraint_name);
                   
         dbms_output.put_line('Pk_column_name--> '||Pk_column_name);   
                 
               
            --增加主键
            SQL_Str_Pk := 'alter table ' || tab_name_rec.table_name || ' add constraint '  
                                  ||' PK_'||tab_name_rec.table_name || ' primary key('
                                  ||  Pk_column_name
                                  ||') using index tablespace USERS pctfree 10 initrans 2 '
                                  || ' maxtrans 255  storage ('
                                  || 'initial 64K next 1M minextents 1 maxextents unlimited );';
             dbms_output.put_line(SQL_Str_Pk);     
           --查询备注
           FOR tab_commonts_rec IN
               (SELECT column_name,comments
               FROM user_col_comments c
               WHERE c.table_name = tab_name_rec.table_name)
               LOOP
                   --dbms_output.put_line('column_name :' || tab_commonts_rec.column_name || ' and                        comments :' ||tab_commonts_rec.comments);
                --增加备注
                   SQL_Str_Comments := 'comment on column ' || tab_name_rec.table_name
                                       || '.' || tab_commonts_rec.column_name               
                                       || ' IS '  || chr(39)||tab_commonts_rec.comments ||chr(39)||  ';' ;
                dbms_output.put_line(SQL_Str_Comments);
            END LOOP;
           
           --查询默认值
           FOR tab_commonts_default IN
               (SELECT t.column_name as column_name,t.data_type as data_type
            FROM USER_TAB_COLS t
            WHERE TABLE_NAME =tab_name_rec.table_name)
            LOOP
                   CASE tab_commonts_default.data_type
                       WHEN 'NUMBER' THEN
                           --dbms_output.put_line(tab_commonts_default.column_name || ' is NUMBER');
                           --增加默认值
                           SQL_Str_default := 'alter table '||tab_name_rec.table_name||' modify '
                                               || tab_commonts_default.column_name || ' default 0;';
                          dbms_output.put_line(SQL_Str_default);
                       WHEN 'VARCHAR2' THEN
                           --dbms_output.put_line(tab_commonts_default.column_name || ' is VARCHAR2');
                           SQL_Str_default := 'alter table '||tab_name_rec.table_name||' modify '
                                               || tab_commonts_default.column_name || ' default NULL;';
                          --dbms_output.put_line(SQL_Str_default);
                       ELSE
                         dbms_output.put_line(tab_commonts_default.column_name || ' is Unknown');
                   END CASE;
            END LOOP;            
      END LOOP;
      CLOSE  tab_name_cur;          
END;

亲测截图：

 

执行结果如下图：