oracle导出表insert语句存储过程

 

 

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

 

 

 

 

评论

2 楼 wenjinglian 2013-04-12  

sangshuye 写道

哥们，我用的怎么不好使啊？

可能是版本问题，我当时是oracle10g

1 楼 sangshuye 2013-04-07  

哥们，我用的怎么不好使啊？