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;
亲测截图：
执行结果如下图：
