- 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('表名');