带你轻松接触一个可以自动创建过程的脚本

本文中，我们将介绍一个可以能自动创建过程的脚本，大家直接输入源泉表名和目标表名就可以创建以下链接中的全表复制过程。

create or replace procedure hyf_create_sql(p_sour_tab varchar2,p_dst_tab varchar2) asv_dstfile utl_file.file_type;v_outname varchar2(50);v_buffer varchar2(500);type t_cur is ref cursor;v_cur t_cur;v_col_num number;beginselect count(column_name) into v_col_numfrom user_tab_columnswhere table_name = upper(p_dst_tab);v_outname := p_sour_tab || '.sql';v_dstfile := utl_file.fopen('DIR1', v_outname, 'w', 32767);v_buffer := 'create or replace procedure cp_' || p_sour_tab || ' as';utl_file.put_line(v_dstfile, v_buffer);open v_cur for select 'type TYPE_' || column_name || ' is table of ' || table_name || '.' ||column_name || '%type;' as ddfrom user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ; open v_cur for select 'V_' || column_name || ' TYPE_' || column_name ||';' from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ; utl_file.put_line (v_dstfile,'type t_cur is ref cursor;');utl_file.put_line (v_dstfile,'c_table t_cur;');utl_file.put_line (v_dstfile,'v_sql varchar2(500);');utl_file.put_line (v_dstfile,'v_rows number := 5000;');utl_file.put_line(v_dstfile, 'begin'); utl_file.put_line(v_dstfile, 'execute immediate ''truncate table '||p_dst_tab||''';');utl_file.put_line(v_dstfile, 'open c_table for');utl_file.put_line(v_dstfile, ' select * from '||p_sour_tab||';');v_buffer:= 'v_sql := ''insert /*+ APPEND*/ into '||p_dst_tab||' (';utl_file.put_line (v_dstfile,v_buffer);open v_cur for select column_name from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc; for i in 1..v_col_num loopfetch v_cur into v_buffer; if i<> v_col_num then v_buffer:=v_buffer||',';else v_buffer:=v_buffer||')' ;end if;utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ;v_buffer:= 'values (' ;for i in 1..v_col_num loopif i<> v_col_num then v_buffer:=v_buffer||':'||i||',';else v_buffer:=v_buffer||':'||i||')'';';end if;end loop;utl_file.put_line (v_dstfile,v_buffer); utl_file.put_line (v_dstfile,'loop ');utl_file.put_line (v_dstfile,' fetch c_table ');utl_file.put_line (v_dstfile, ' bulk collect into');open v_cur for select 'v_'||column_name from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; if i<> v_col_num then v_buffer:=v_buffer||',';end if;utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ;utl_file.put_line (v_dstfile, ' limit v_rows;');v_buffer:='forall i in 1 .. '||v_buffer||'.count execute immediate v_sql using';utl_file.put_line (v_dstfile,v_buffer);open v_cur for select 'v_'||column_name||'(i)' from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; if i<> v_col_num then v_buffer:=v_buffer||',';else v_buffer:=v_buffer||';';end if;utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ;utl_file.put_line(v_dstfile, ' commit;');utl_file.put_line(v_dstfile, ' exit when c_table%notfound;');utl_file.put_line(v_dstfile, 'end loop;');utl_file.put_line(v_dstfile, ' close c_table;');utl_file.put_line(v_dstfile, 'end;');utl_file.fclose(v_dstfile);exceptionwhen others thenif utl_file.is_open(v_dstfile) thenutl_file.fclose(v_dstfile);end if;raise;end;