导出数据、生成SQL INSERT的方法。

在工作中常常碰到这种情况：我们需要将一个应用系统的基础数据导出来，以便基础数据下次导入到另外一个场景。

事实上，这种情况我们可以用pl/sql的功能Export Tables来实现。Export Table有三种导出方式：Oracle Export，SQL Insert，PL/SQL Developer。通常我们会使用Oracle Export，SQL Insert的功能。(第三种俺没用过，呵呵，所以这么说。)

Oracle Export是将数据导出成dmp格式的文件，这样有个麻烦，不能看到里面的内容。虽然它的速度很快，但是缺点太明显，所以至少我一般不用它。除非是导出含有clob,blob的表，才会用到这个功能。

SQL Insert是最实用的功能。当然它也有缺点，比如不能使用clob,blob。但是优点明显，就是能看到sql语句的详细内容。所以一般我用它，除非clob,blob才用export tables的功能。

实际中，一个系统可能有几百个表，但是基础数据表可能才100多个，这样使用PL/SQL的SQL Insert功能就会出现一种情况：每次我导出基础数据的时候，就要从用户的几百个表中选择那100多个表。这样很考验人的耐心的，反正我是被这个搞得头晕眼花。

正是因为我没耐心，所以就写了这片短文，希望在今后的工作中，能用更快的速度来完成导出导入功能，所以就自己做了一个方法，来完成导入导出功能，最终的效果应该是和PL/SQL里面的SQL Insert功能一样的，且个人觉得更适合本人使用（不知道适合不适合别人使用，呵呵）。

************************************************************************************************************

闲话少说，开始正题吧。

主要思路是这样的：

1，将基础数据的表名插入到临时表t_proc_init_tab中，不论顺序，表结构如下：

create table T_PROC_INIT_TAB
(
  TABLE_NAME VARCHAR2(100)
)

。

2，分析临时表t_proc_init_tab中所有表的依赖关系，最后算出所有表的依赖等级，插入到临时表T_PROC_CONSTRAINT_GRADE中。等级越高，标识该表依赖程度越高。后面插入数据的时候就会在后面执行。T_PROC_CONSTRAINT_GRADE的表结构如下：

create table T_PROC_CONSTRAINT_GRADE
(
  SRC_TABLE       VARCHAR2(100) not null,
  GRADE           NUMBER not null
)

算出表依赖等级，使用后面存储过程PKG_CREATE_INS_SQL.SP_DoConstraint。

3，依据依赖等级高低，从低到高，一个一个的导出表的数据，生成SQL Insert到临时表t_proc_exp_sqlinsert中，t_proc_exp_sqlinsert的表结构如下：

create table T_PROC_EXP_SQLINSERT
(
  ID         NUMBER not null,
  SQLINFO    VARCHAR2(2000),
  TABNAME    VARCHAR2(100),
  CREATEDATE DATE
)

。

执行这个步骤，使用后面存储过程PKG_CREATE_INS_SQL.SP_MAIN。

4，使用spool，生成基础数据的txt文件。

set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool d:/boss_init_data.sql
select pes.sqlinfo||';' from t_proc_exp_sqlinsert pes order by id;
spool off

 

5，最后在d：盘下，找到boss_init_data.sql文件，这个就是基础数据的导入文件，下次用的使用在PL/SQL的COMMAND WINDOW下直接使用@@d:/boss_init_data.sql; 这个命令，就可以将数据导入到表中去了。

 

这样，以后不用每次都去从几百个表中查找那100多个表了，直接执行存储过程就行了。利索多了，呵呵。

 

************************************************************************************************************

附录存储过程包：

create or replace package PKG_CREATE_INS_SQL is
  /***************************************************************
  *函 数 名：  生成sql insert语句
  *函数功能:
  *作者：      ham
  *编写日期：  2010-04-28
  *输入：
  *输出：
  *返 回 值：
  ***************************************************************/
  -- 单表生成sql insert语句
  Procedure SP_CREATE(i_tablename in varchar2);

  --分析所有表的依赖关系。以等级排列。
  procedure SP_DoConstraint(o_returncode out varchar2, --
                            o_returnmsg  out varchar2);

  --查询单表的依赖等级。
  function GETTABLEGRADE(i_table in varchar2 --表名
                         ) return number;
  --主程序
  procedure SP_MAIN(o_returncode out varchar2, --
                    o_returnmsg  out varchar2);

end PKG_CREATE_INS_SQL;
/
create or replace package body PKG_CREATE_INS_SQL is
  /***************************************************************
  *函 数 名：  生成sql insert语句
  *函数功能:
  *作者：      ham
  *编写日期：  2010-04-28
  *输入：
  *输出：
  *返 回 值：
  ***************************************************************/
  --
  Procedure SP_CREATE(i_tablename in varchar2) is
    l_head    varchar2(2000);
    l_tabhead varchar2(2000);
    l_tabval  varchar2(2000);
    l_idx     number;
    --可变数组
    type type_array is table of varchar2(20) index by binary_integer;
    coltype_array type_array;
    colname_array type_array;
 
    --字段类型
    cursor c_col is
      select utc.COLUMN_NAME, utc.DATA_TYPE
        from user_tab_columns utc
       where utc.TABLE_NAME = i_tablename;
 
    type t_cur is ref cursor;
    c_cur_tab  t_cur;
    strSQL     VARCHAR2(2000);
    l_tabvalue VARCHAR2(2000);
 
    TYPE table_forall IS TABLE OF T_PROC_TMP_COLVAL%ROWTYPE;
    v_table table_forall;
 
    l_num    number;
    l_tmpidx number;
  begin
    l_idx    := 0;
    l_head   := '';
    l_tabval := '';
 
    select wm_concat(utc.COLUMN_NAME)
      into l_tabhead
      from user_tab_columns utc
     where utc.TABLE_NAME = i_tablename;
 
    l_tabhead := 'insert into ' || i_tablename || '(' || l_tabhead || ') ';
 
    for c_cl in c_col loop
      l_idx := l_idx + 1;
      coltype_array(l_idx) := c_cl.data_type;
      colname_array(l_idx) := c_cl.column_name;
   
      if coltype_array(l_idx) = 'DATE' then
        l_head := l_head || 'to_char(' || colname_array(l_idx) || ',' || '''' ||
                  'yyyy-mm-dd hh24:mi:ss' || '''' || ')||' || '''' || '@@' || '''' || '||';
      else
        l_head := l_head || colname_array(l_idx) || '||' || '''' || '@@' || '''' || '||';
      end if;
    end loop;
 
    insert /*+ append */
    into t_proc_exp_sqlinsert nologging
      (id, sqlinfo, tabname, createdate)
    values
      (seq_inssqlid.nextval,
       'delete from ' || i_tablename,
       i_tablename,
       sysdate);
 
    insert /*+ append */
    into t_proc_exp_sqlinsert nologging
      (id, sqlinfo, tabname, createdate)
    values
      (seq_inssqlid.nextval, 'commit', i_tablename, sysdate);
 
    l_head := substr(l_head, 0, length(l_head) - 8);
    strSQL := 'select ' || l_head || ' from ' || i_tablename;
    open c_cur_tab for strSQL;
    loop
      fetch c_cur_tab
        into l_tabvalue;
      exit when c_cur_tab %notfound;
   
      select column_value BULK COLLECT
        into v_table
        from table(fn_split(l_tabvalue, '@@'));
   
      l_tabval := '';
      l_num    := 0;
      FOR i IN 1 .. v_table.count LOOP
        l_num := l_num + 1;
     
        if coltype_array(l_num) = 'CHAR' or
           coltype_array(l_num) = 'VARCHAR2' or
           coltype_array(l_num) = 'NVARCHAR2' then
          l_tabval := l_tabval || ',' || '''' || v_table(i).colval || '''';
        elsif coltype_array(l_num) = 'NUMBER' or
              coltype_array(l_num) = 'LONG' then
          l_tabval := l_tabval || ',' || v_table(i).colval;
        elsif coltype_array(l_num) = 'DATE' then
          l_tmpidx := instr(v_table(i).colval, '00:00:00');
       
          if l_tmpidx = 12 then
            l_tabval := l_tabval || ',' || 'to_date(' || '''' ||
                        substr(v_table(i).colval, 1, 10) || '''' || ',' || '''' ||
                        'yyyy-mm-dd' || '''' || ')';
          else
            l_tabval := l_tabval || ',' || 'to_date(' || '''' || v_table(i)
                       .colval || '''' || ',' || '''' ||
                        'yyyy-mm-dd hh24:mi:ss' || '''' || ')';
          end if;
        else
          l_tabval := l_tabval || ',' || '''' || '''';
        end if;
      END LOOP;
   
      l_tabval := substr(l_tabval, 2);
      l_tabval := 'values(' || l_tabval || ')';
      l_tabval := l_tabhead || l_tabval;
   
      insert /*+ append */
      into t_proc_exp_sqlinsert nologging
        (id, sqlinfo, tabname, createdate)
      values
        (seq_inssqlid.nextval, l_tabval, i_tablename, sysdate);
   
    end loop;
    close c_cur_tab;
 
    insert /*+ append */
    into t_proc_exp_sqlinsert nologging
      (id, sqlinfo, tabname, createdate)
    values
      (seq_inssqlid.nextval, 'commit', i_tablename, sysdate);
 
    commit;
  exception
    when others then
      rollback;
  end;

  --分析所有表的依赖关系。以等级排列。
  procedure SP_DoConstraint(o_returncode out varchar2, --
                            o_returnmsg  out varchar2) is
    cursor c_tab is
      select distinct ut.table_name from user_tables ut;
    l_grade number;
  begin
    delete from t_proc_constraint_grade;
 
    for c_ta in c_tab loop
      l_grade := gettablegrade(c_ta.table_name);
    end loop;
    commit;
    o_returncode := '1';
    o_returnmsg  := 'good';
  exception
    when others then
      o_returncode := '0';
      o_returnmsg  := 'bad' || SQLERRM;
  end;

  --查询单表的依赖等级。
  function GETTABLEGRADE(i_table in varchar2 --表名
                         ) return number is
    l_grade    number;
    l_num      number;
    l_pretable varchar2(100);
    l_pregrade number;
 
    --根据表名查外键
    cursor c_constraint is
      select a.constraint_name constraint_name,
             a.table_name      origial_table,
             b.table_name      refer_table
        from user_constraints a, user_constraints b
       where a.constraint_type = 'R'
         and a.r_constraint_name = b.constraint_name
         and a.table_name = upper(i_table);
  begin
    l_grade := 1;
 
    --查询临时表中的表的等级
    select count(1)
      into l_num
      from t_proc_constraint_grade pcg
     where pcg.src_table = i_table;
 
    --存在表的等级，则取出等级
    if l_num > 0 then
      select pcg.grade
        into l_grade
        from t_proc_constraint_grade pcg
       where pcg.src_table = i_table;
   
      return l_grade;
    end if;
 
    --查询表上的外键数量
    select count(1)
      into l_num
      from user_constraints a, user_constraints b
     where a.constraint_type = 'R'
       and a.r_constraint_name = b.constraint_name
       and a.table_name = upper(i_table);
 
    if l_num = 0 then
      --该表无外键，插入临时表，等级为1。     
      insert into t_proc_constraint_grade
        (src_table, grade)
      values
        (i_table, 1);
   
      l_grade := 1;
    else
      --该表有多个外键。比较涉及表的等级，取最大的等级。
      for c_ct in c_constraint loop
        l_pretable := c_ct.refer_table;
        l_pregrade := gettablegrade(l_pretable);
     
        if l_pregrade + 1 >= l_grade then
          l_grade := l_pregrade + 1;
        end if;
      end loop;
      --插入等级表   
      insert into t_proc_constraint_grade
        (src_table, grade)
      values
        (i_table, l_grade);
    end if;
 
    return l_grade;
  end;

  procedure SP_MAIN(o_returncode out varchar2, --
                    o_returnmsg  out varchar2) is
    cursor c_initab is
      select pcg.src_table
        from t_proc_init_tab pit, t_proc_constraint_grade pcg
       where pit.table_name = pcg.src_table
       order by pcg.grade;
  begin
 
    for c_it in c_initab loop
      sp_create(c_it.src_table);
    end loop;
 
    o_returncode := '1';
    o_returnmsg  := 'good';
  exception
    when others then
      o_returncode := '0';
      o_returnmsg  := 'bad' || SQLERRM;
  end;

end PKG_CREATE_INS_SQL;
/

 

************************************************************************************************************

后序

这个存储过程其实写的还是有些问题的，但基本能用。以后如果有时间再看看能不能升级下。如果哪位看官有好的建议，俺也可以试试你的建议。呵呵。