oracle导出表insert语句存储过程

平惠9668

　　
　　

create or replace procedure print_insert(v_tname  varchar2,
v_cbatch number default 0)
/*
v_tname 要输出sql的表名
v_cbatch 输出commit间隔
*/
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(10);
v_count number(6);
begin
v_instr := instr(v_row, '(', 1, 2); --INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)
v_ldata := substr(v_row, 1, v_instr);
v_rdata := substr(v_row, v_instr + 1);
v_instr := instr(v_rdata, ')', -1, 1);
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;
dbms_output.put_line(v_ldata);
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 || '''';
elsif v_type = 'CHAR' 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);
v_fieldName 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;
if v_fieldName is null then
v_fieldName := cur_fname.cname;
else
v_fieldName := v_fieldName || ',' || cur_fname.cname;
end if;
end loop;
v_fields := 'select ' || '''' || 'insert into ' || v_tname || ' (' ||
v_fieldName || ') values(' || '''' || '||' || v_fields || '||' || '''' || ')' || '''' ||
' from ' || v_tname;
return v_fields;
end;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null); --解决PLSQL Developer 出现ORU-10027: buffer overflow, limit of 10000 bytes
execute immediate 'alter session set nls_date_format=' || '''' ||
'yyyy-mm-dd hh24:mi:ss' || '''';
dbms_output.put_line(' *** 表SQL输出 *** ');
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;
　　
　　原博客:http://blog.itpub.net/xzh2000
　　对原作者的代码进行了相应的修改。
　　
　　在命令行中执行：
　　

set serveroutput on; --设置服务器输出
exec print_insert('表名',100);
　　
　　
　　--获取表的相关字段属性(相当于java反射获取对象属性)
　　select * from col where tname = upper('表名');