轉(zhuǎn)載，請保留原作者的名字。


create or replace procedure print_insert(v_tname varchar2,v_cbatch number default 0)
/*
v_tname 要輸出sql的表名 作者zh2000@hotmail.com
v_cbatch 輸出commit間隔 博客:http://blog./xzh2000
*/
as
/* 聲明動態(tài)游標變量 */
type cur_alldata is ref cursor;
l_alldata cur_alldata;
/* 將單行數(shù)據(jù)寫入v_row*/
v_sql varchar2(3999);
v_row varchar2(3999);
/* 函數(shù)的前向聲明 */
function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2;
/* 格式化數(shù)據(jù)輸出 */
function formatdata(v_tname varchar2,v_row varchar2) return varchar2
as
v_ldata varchar2(32765);
v_rdata varchar2(32765);
v_cname varchar2(3999);
v_instr number(;
v_count number(6);
begin
v_instr := instr(v_row,'(');
v_ldata := substr(v_row,1,v_instr);
v_rdata := substr(v_row,v_instr+1);
v_instr := instr(v_rdata,')');
v_rdata := substr(v_rdata,1,v_instr-1);

v_count := 0;
loop
v_instr := instr(v_rdata,',');
exit when v_instr = 0;

v_cname := substr(v_rdata,1,v_instr-1);
v_rdata := substr(v_rdata,v_instr+1);
v_count := v_count + 1;
/* 格式化不同的數(shù)據(jù)類型 */
v_cname := formatfield(v_tname,v_cname,v_count);

/* 將處理后的字段值加入v_ldata */
if v_count = 1 then
v_ldata := v_ldata||v_cname;
else
v_ldata := v_ldata||','||v_cname;
end if;

end loop;

/* 添加最后一個字段的值 */
if v_count = 1 then
v_ldata := v_ldata||formatfield(v_tname,v_rdata,v_count+1)||');';
else
v_ldata := v_ldata||','||formatfield(v_tname,v_rdata,v_count+1)||');';
end if;
return v_ldata;
end;
/* 針對不同的數(shù)據(jù)類型進行處理 */
function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2
as
v_name varchar2(3999);
v_type varchar2(99);
begin
select coltype into v_type from col where tname = upper(v_tname) and colno = v_colno;
if v_type = 'DATE' then
v_name := 'to_date('||''''||v_cname||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';
elsif v_type = 'VARCHAR2' then
v_name := ''''||v_cname||'''';
else
v_name := v_cname;
end if;
return v_name;
end;
/* 求輸入表的字段列表 */
function getfields(v_tname varchar2) return varchar2
as
v_fields varchar2(3999);
begin
for cur_fname in (select cname,coltype from col where tname = upper(v_tname) order by colno) loop
if v_fields is null then
v_fields := 'nvl('||cur_fname.cname||','||''''||'0'||''''||')';
else
v_fields := v_fields||'||'',''||'||'nvl('||cur_fname.cname||','||''''||'0'||''''||')';
end if;
end loop;
v_fields := 'select '||''''||'insert into '||v_tname||' values('||''''||'||'||v_fields||'||'||''''||')'||''''||' from '||v_tname;
return v_fields;
end;
begin
execute immediate 'alter session set nls_date_format='||''''||'yyyy-mm-dd hh24:mi:ss'||'''';
dbms_output.put_line(' *** xzxh2000向你問好！ *** ');

v_sql := getfields(v_tname);
--dbms_output.put_line(v_sql);
open l_alldata for v_sql;
loop
fetch l_alldata into v_row;
exit when l_alldata%notfound;
--dbms_output.put_line(v_row);

dbms_output.put_line(formatdata(v_tname,v_row));
if mod(l_alldata%rowcount,v_cbatch) = 0 then
dbms_output.put_line('commit;');
end if;
end loop;

close l_alldata;
end;