如何将Oracle表里的数据导出成insert into脚本?

转载，请保留原作者的名字。


create or replace procedure print_insert(v_tname varchar2,v_cbatch number default 0)
/*
v_tname 要输出sql的表名 作者

[e-mail]
v_cbatch 输出commit间隔 博客:http://blog.itpub.net/xzh2000
*/
as
/* 声明动态游标变量 */
type cur_alldata is ref cursor;
l_alldata cur_alldata;
/* 将单行数据写入v_row*/
v_sql varchar2(3999);
v_row varchar2(3999);
/* 函数的前向声明 */
function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2;
/* 格式化数据输出 */
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;
/* 格式化不同的数据类型 */
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;
/* 针对不同的数据类型进行处理 */
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;