读取远程表并逆向生成建表语句

来源:转载

create or replace procedure tab_sync_create(v_tab_name varchar2,v_data_link varchar2 default null)as type cur_type is ref cursor; cur_policy cur_type; rec_tab_columns user_tab_columns%rowtype; rec_tab_comments user_tab_comments%rowtype; rec_col_comments user_col_comments%rowtype; rec_uc user_constraints%rowtype; rec_ucc user_cons_columns%rowtype; rec_ui user_indexes%rowtype; rec_uic user_ind_columns%rowtype; n_count number default 0; v_at varchar2(50) default null; v_type varchar2(500) default null;--字段类型声明 v_p_cons_name varchar2(500) default null;--主键约束名称 v_p_cons_list varchar2(500) default null;--主键约束列表 v_crt_tab_sql varchar2(4000) default null;--建表语句 v_crt_con_sql varchar2(1000) default null;--建约束语句 v_crt_cmt_sql varchar2(1000) default null;--建备注语句begin --生成数据链 if v_data_link is not null then v_at := '@'||v_data_link; end if; /* -- 生成建表语句 -- */ /* 备注: chr(9) 制表符,chr(10) 回车符,chr(13) 换行符,chr(32) 空格符 */ open cur_policy for 'select table_name, column_name, data_type, char_length, data_length, data_precision, data_scale, nullable from user_tab_columns'||v_at||' where table_name=upper('''||v_tab_name||''') order by column_name'; --拼接前半部语句 v_crt_tab_sql := 'create table '||v_tab_name||' ('||chr(13); --重置相关变量 v_type := null; n_count := 0; loop fetch cur_policy into rec_tab_columns.table_name, rec_tab_columns.column_name, rec_tab_columns.data_type, rec_tab_columns.char_length, rec_tab_columns.data_length, rec_tab_columns.data_precision, rec_tab_columns.data_scale, rec_tab_columns.nullable; exit when cur_policy%notfound; --拼接上一条记录逗号 if n_count!=0 then v_crt_tab_sql := v_crt_tab_sql||','||chr(13); end if; --根据字段类型生成相应字段声明语句 if rec_tab_columns.data_type='NVARCHAR2' then v_type := 'NVARCHAR2('||rec_tab_columns.char_length||')'; elsif rec_tab_columns.data_type='VARCHAR2' then/* 注意是 elsif 不是 elseif */ v_type := 'VARCHAR2('||rec_tab_columns.data_length||')'; elsif rec_tab_columns.data_type='CHAR' then v_type := 'CHAR('||rec_tab_columns.data_length||')'; elsif rec_tab_columns.data_type='NUMBER' then if rec_tab_columns.data_precision is null then --先判断 data_precision 为空 v_type := 'NUMBER'; elsif rec_tab_columns.data_scale=0 then --再判断 data_scale 为0 v_type := 'NUMBER('||rec_tab_columns.data_precision||')'; else --最后处理一般情况 v_type := 'NUMBER('||rec_tab_columns.data_precision||','||rec_tab_columns.data_scale||')'; end if; elsif rec_tab_columns.data_type='FLOAT' then v_type := 'FLOAT'; elsif rec_tab_columns.data_type='DATE' then v_type := 'DATE'; end if; --拼接字段声明 v_crt_tab_sql := v_crt_tab_sql||chr(9)||rec_tab_columns.column_name||chr(32)||v_type||case when rec_tab_columns.nullable='Y' then null else chr(32)||'not null' end; --记录执行成功数 n_count := n_count+1; end loop; --拼接后半部语句 v_crt_tab_sql := lower(v_crt_tab_sql)||chr(13)||')'; close cur_policy; dbms_output.put_line(v_crt_tab_sql);--输出建表语句 /* -- 生成非外键约束 -- */ open cur_policy for 'select uc.constraint_name, uc.constraint_type, uc.search_condition, ucc.column_name from user_constraints'||v_at||' uc join user_cons_columns'||v_at||' ucc on uc.constraint_name=ucc.constraint_name where uc.table_name=upper('''||v_tab_name||''') and uc.constraint_name not like ''%SYS_%'' order by uc.constraint_name'; --重置相关变量 v_p_cons_list := null; n_count := 0; loop fetch cur_policy into rec_uc.constraint_name, rec_uc.constraint_type, rec_uc.search_condition, rec_ucc.column_name; exit when cur_policy%notfound; --根据约束类型生成相应语句 if rec_uc.constraint_type='C' then --条件约束 v_crt_con_sql := 'alter table '||v_tab_name||' add constraint '||rec_uc.constraint_name||' check ('||rec_uc.search_condition||')'; dbms_output.put_line(v_crt_con_sql);--输出条件约束语句 elsif rec_uc.constraint_type='P' then --主键约束 --存储主键约束名 v_p_cons_name := rec_uc.constraint_name; --拼接上一条记录逗号,仅在有下一条记录时拼接 if n_count!=0 then v_p_cons_list := v_p_cons_list||', '; end if; --拼接主键约束列列名 v_p_cons_list := v_p_cons_list||rec_ucc.column_name; --记录执行成功数 n_count := n_count+1; elsif rec_uc.constraint_type='U' then --唯一约束 v_crt_con_sql := 'alter table '||v_tab_name||' add constraint '||rec_uc.constraint_name||' unique ('||rec_ucc.column_name||')'; dbms_output.put_line(v_crt_con_sql);--输出唯一约束语句 end if; end loop; --拼接主键约束语句 if v_p_cons_list is not null then v_crt_con_sql := 'alter table '||v_tab_name||' add constraint '||v_p_cons_name||' primary key ('||v_p_cons_list||')'; dbms_output.put_line(v_crt_con_sql);--输出主键约束语句 end if; close cur_policy; /* -- 生成外键约束 -- */ open cur_policy for 'select uc.constraint_name, ucc.column_name, ui.table_name, uic.column_name from user_constraints'||v_at||' uc join user_cons_columns'||v_at||' ucc on uc.constraint_name=ucc.constraint_name join user_indexes'||v_at||' ui on uc.r_constraint_name=ui.index_name join user_ind_columns'||v_at||' uic on ui.index_name = uic.index_name where uc.table_name=upper('''||v_tab_name||''') and uc.constraint_type=''R'' order by uc.constraint_name'; loop fetch cur_policy into rec_uc.constraint_name, rec_ucc.column_name, rec_ui.table_name, rec_uic.column_name; exit when cur_policy%notfound; --外键约束,每条外键约束都要单独拼接 v_crt_con_sql := 'alter table '||v_tab_name||' add constraint '||rec_uc.constraint_name||' foreign key ('||rec_ucc.column_name||') references '||rec_ui.table_name||' ('||rec_uic.column_name||')'; dbms_output.put_line(v_crt_con_sql);--输出外键约束语句 end loop; close cur_policy; /* -- 生成表备注 -- */ open cur_policy for 'select * from user_tab_comments'||v_at||' where table_name=upper('''||v_tab_name||''')'; loop fetch cur_policy into rec_tab_comments.table_name,rec_tab_comments.table_type,rec_tab_comments.comments; exit when cur_policy%notfound; if rec_tab_comments.comments is not null then v_crt_cmt_sql := 'comment on table '||rec_tab_comments.table_name||' is '''||rec_tab_comments.comments||''''; dbms_output.put_line(v_crt_cmt_sql);--输出表备注 end if; end loop; close cur_policy; /* -- 生成列备注 -- */ open cur_policy for 'select * from user_col_comments'||v_at||' where table_name=upper('''||v_tab_name||''') order by column_name'; loop fetch cur_policy into rec_col_comments.table_name,rec_col_comments.column_name,rec_col_comments.comments; exit when cur_policy%notfound; if rec_col_comments.comments is not null then v_crt_cmt_sql := 'comment on column '||rec_col_comments.table_name||'.'||rec_col_comments.column_name||' is '''||rec_col_comments.comments||''''; dbms_output.put_line(v_crt_cmt_sql);--输出列备注 end if; end loop; close cur_policy; exception when others then --log_msg('tab_sync_create['||v_tab_name||']错误:'||sqlerrm,'失败'); dbms_output.put_line('tab_sync_create['||v_tab_name||']错误:'||sqlerrm);end tab_sync_create;



分享给朋友:
您可能感兴趣的文章:
随机阅读: