[PL/SQL]运用存储过程实现导入指定文件的数据到数据库(针对本博客的EXP_DATA存储)

[PL/SQL]使用存储过程实现导入指定文件的数据到数据库(针对本博客的EXP_DATA存储)
create or replace procedure IMP_DATA
(
  file_name in varchar2 --要导入的文件名,包含路径(如:d:\test\exp_0003.txt)
 ,p_user    in varchar2 default SYS_CONTEXT('USERENV', 'CURRENT_USER') --要导入的用户,缺省为当前用户
 ,p_sep     in varchar2 default ',' --字段分隔符,默认为逗号(需要打开要导入的文件确认导入的字段分隔符)
) AS
  /*
  描述:根据EXP_DATA过程导出的数据进行导入
  created by cryking 2013.03.07
  注意:1.本存储建议由SYS账户或具有SYSDBA权限的账户执行
       2.不要在其他事务中运行本存储过程
       3. 默认导入的数据(p_user为空,或未指定)全部在当前用户下
  */
  v_file UTL_FILE.file_type;
  TYPE t_filed IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;
  v_fileds t_filed;
  TYPE t_data IS TABLE OF varchar2(4000) INDEX BY BINARY_INTEGER;
  v_data     t_data;
  v_datatype t_data;
  v_sql      varchar2(30000);
  V_esql     varchar2(30000);
  v_filed    VARCHAR2(100) := '';
  v_filedstr VARCHAR2(4000) := '';
  V_TABLE    VARCHAR2(1000);
  v_user     varchar2(20);
  v_path     varchar2(500);
  v_filename varchar2(50);
  v_sep      varchar2(10);
  v_text     varchar2(32600);
  v_textTmp  varchar2(32600);
  i_flag     integer:=0;
  I_TABLE    INTEGER;
  exp_sep exception;
  ex_table exception;
BEGIN
  /*----------输入参数检查部分----------*/
  --没有输入用户的情况
  if trim(p_user) is null then
    v_user := SYS_CONTEXT('USERENV', 'CURRENT_USER');
  else
    v_user := upper(p_user);
  end if;

  if trim(p_sep) is null then
    v_sep := ',';
  else
    v_sep := p_sep;
  end if;

  --获取路径
  select replace(file_name, regexp_REPLACE(file_name, '\\*[^\\*]*\\'), '')
    into v_path
    from dual;
  --获取文件名
  select regexp_REPLACE(file_name, '\\*[^\\*]*\\')
    into v_filename
    from dual;

  /*------------------------------------*/
  --设置日期格式
  EXECUTE IMMEDIATE 'ALTER session SET nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
  rollback; --防止在其他事务中运行本存储,先回滚之前的事务
  execute immediate 'create or replace directory IMPDIR as ''' || v_path ||
                    ''' '; --创建目录

  v_file := UTL_FILE.fopen('IMPDIR', v_filename, 'r'); --读取文件

  --导入所有数据
  loop
    UTL_FILE.get_line(v_file, v_text);
    if substr(v_text, -1, 1) <> ',' and INSTR(v_text, '[TABLE:]') = 0 then
      v_textTmp := v_text || chr(10);
      continue;
    else
      v_textTmp := v_textTmp || v_text;
    end if;
    --获取表名
    IF INSTR(v_text, '[TABLE:]') > 0 THEN
      v_textTmp := '';
      V_TABLE   := UPPER(SUBSTR(v_text, INSTR(v_text, ']', 1, 2) + 1));
      SELECT COUNT(*)
        INTO I_TABLE
        FROM all_TABLES
       WHERE all_TABLES.TABLE_NAME = V_TABLE
         AND OWNER = v_user;
      IF I_TABLE = 0 THEN
        v_sql := 'create table ' || v_user || '.' || V_TABLE || '(';
      ELSE
        v_sql := 'insert into ' || v_user || '.' || V_TABLE || '(';
      END IF;
    END IF;
    --获取字段列表
    IF INSTR(v_text, '[filed:]') > 0 THEN
      v_textTmp := '';
      select * bulk collect
        into v_fileds
        from table(splitstr(replace(v_text, '[filed:]'), v_sep));
      IF INSTR(v_sql, 'create ') > 0 then
        FOR I IN 1 .. v_fileds.COUNT
        LOOP
          V_sql := v_sql || v_fileds(i) || ',';
        END LOOP;
        V_sql := v_sql || ') ';
        execute immediate v_sql; --先创建表
        v_sql := 'INSERT INTO ' || v_user || '.' || V_TABLE || ' VALUES(';
      else
        v_filedstr := '';
        FOR I IN 1 .. v_fileds.COUNT
        LOOP
          SELECT COUNT(*)
            INTO I_TABLE
            FROM ALL_tab_columns
           WHERE TABLE_NAME = V_TABLE
             AND OWNER = v_user
             and COLUMN_NAME = UPPER(v_fileds(i));
          if I_TABLE = 0 then
            raise ex_table;
          else
            v_sql := v_sql || v_fileds(i) || ',';
            select data_type
              into v_filed
              from ALL_tab_columns
             where TABLE_NAME = V_TABLE
               AND OWNER = v_user
               and COLUMN_NAME = UPPER(v_fileds(i));
            v_filedstr := v_filedstr || v_filed || ',';
          end if;
        end loop;
        V_sql := substr(v_sql, 1, length(V_sql) - 1) || ') values('; --去掉最后的逗号
      
      end if;
    
    END IF;
  
    if INSTR(v_text, ',') > 0 and INSTR(v_text, '[filed:]') = 0 then
      select * bulk collect
        into v_data
        from table(splitstr(v_textTmp, v_sep));
    
      select * bulk collect
        into v_datatype
        from table(splitstr(v_filedstr, ','));
      V_esql := v_sql;
      FOR I IN 1 .. v_data.COUNT
      LOOP
        CASE
          WHEN INSTR(v_datatype(i), 'CHAR') > 0 THEN
            if v_data(i) = 'null' then
              V_esql := v_esql || 'NULL,';
            else
              V_esql := v_esql || ' ''' || v_data(i) || ''','; --处理数据类型
            end if;
          WHEN INSTR(v_datatype(i), 'NUMBER') > 0 THEN
            if v_data(i) = 'null' then
              V_esql := v_esql || 'NULL,';
            else
              V_esql := v_esql || ' ' || v_data(i) || ','; --处理数据类型
            end if;
          WHEN INSTR(v_datatype(i), 'DATE') > 0 THEN
            if v_data(i) = 'null' then
              V_esql := v_esql || 'NULL,';
            else
              IF length(v_data(i)) - length(replace(v_data(i), ':', '')) = 0 then
                --日期数据格式确认
                V_esql := v_esql || 'TO_DATE(''' || v_data(i) ||
                          ''',''YYYY-MM-DD''),'; --处理数据类型
              elsif length(v_data(i)) - length(replace(v_data(i), ':', '')) = 1 then
                V_esql := v_esql || 'TO_DATE(''' || v_data(i) ||
                          ''',''YYYY-MM-DD HH24:MI''),'; --处理数据类型
              else
                V_esql := v_esql || 'TO_DATE(''' || v_data(i) ||
                          ''',''YYYY-MM-DD HH24:MI:SS''),'; --处理数据类型
              end if;
            end if;
          
        END CASE;
      end loop;
      V_esql := substr(v_esql, 1, length(V_esql) - 1) || ')';
      --dbms_output.put_line('v_sql:' || v_esql);
      begin
        execute immediate v_esql;
        i_flag:=i_flag+1; --控制多少条记录提交一次
         if i_flag=5000 then 
           commit;
           i_flag:=0;
        end if;
      EXCEPTION
        when others then
          dbms_output.put_line('插入数据失败:[' || SQLCODE || '] ' || SQLERRM);
          dbms_output.put_line('失败语句:' || v_esql);
      end;
      v_textTmp := '';
    END IF;
  
  end loop;

  UTL_FILE.fclose(v_file);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('导出数据成功完成!');
    commit;
    UTL_FILE.fclose(v_file);
  when ex_table then
     ROLLBACK;
    raise_application_error(-20001,
                            '导入的表:' || V_TABLE || '已存在,且字段不一致!');
    UTL_FILE.fclose(v_file);
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.put_line('导出数据' || file_name || '失败');
    UTL_FILE.fclose(v_file);
    raise;
END IMP_DATA;


 

简约地写了个IMP,实现了导入文件中所有数据到指定用户.(固定5000记录提交一次)

注:暂时还未实现没有表存在时,自动创建表并导入数据

现在我们来综合执行测试一下:

先执行导出:

begin

EXP_data('d:\test\exp_0304.txt');

end;

默认导出当前用户所有表的数据,内容为(部分内容):

[USER:]SCOTT[TABLE:]EMP
[filed:]EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,
1214,null,null,null,null,1212,null,null,
1212,g001,null,null,null,4500,null,null,
1213,gwmk,null,null,null,null,null,null,
1235,w002,null,null,null,null,null,null,
1234,w001,null,null,null,null,null,null,
2012,g002  ,null,null,null,null,null,null,
2013,1002  ,null,null,null,null,null,null,
2014,1002,null,null,null,null,null,null,
7369,smith,CLERK,7902,1980-12-17 00:00:00,1200,null,20,
7499,allen,SALESMAN,7698,1981-02-20 00:00:00,2400,300,30,
7521,ward,SALESMAN,7698,1981-02-22 00:00:00,1875,500,30,
7566,jones,MANAGER,7839,1981-04-02 00:00:00,4462.5,null,20,
7654,martin,SALESMAN,7698,1981-09-28 00:00:00,1875,1400,30,
7698,blake,MANAGER,7839,1981-05-01 00:00:00,4275,null,30,
7782,clark,MANAGER,7839,1981-06-09 00:00:00,3675,null,10,

...

...

再执行导入:

为了方便测试,我先备份EMP表:

16:31:18 SCOTT@orcl> CREATE TABLE EMP_BAK2013 AS SELECT * FROM EMP;

表已创建。

已用时间:  00: 00: 00.32


然后DROP:

16:49:30 SCOTT@orcl> truncate table emp;

表被截断。

已用时间:  00: 00: 01.92
16:50:13 SCOTT@orcl> select * from emp;

未选定行。

已用时间:  00: 00: 00.01


 

先执行导出:

begin

EXP_data('d:\test\exp_0304.txt');

end;

 看到输出为:

导入数据成功完成后

进行查询:

16:51:56 SCOTT@orcl> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      1214 <null>     <null>    <null>     <null>                    1212 <null>     <null>
      1212 g001       <null>    <null>     <null>                    4500 <null>     <null>
      1213 gwmk       <null>    <null>     <null>              <null>     <null>     <null>
      1235 w002       <null>    <null>     <null>              <null>     <null>     <null>
      1234 w001       <null>    <null>     <null>              <null>     <null>     <null>
      2012 g002       <null>    <null>     <null>              <null>     <null>     <null>
      2013 1002       <null>    <null>     <null>              <null>     <null>     <null>
      2014 1002       <null>    <null>     <null>              <null>     <null>     <null>
      7369 smith      CLERK           7902 1980-12-17 00:00:00       1200 <null>             20
      7499 allen      SALESMAN        7698 1981-02-20 00:00:00       2400        300         30
      7521 ward       SALESMAN        7698 1981-02-22 00:00:00       1875        500         30
      7566 jones      MANAGER         7839 1981-04-02 00:00:00     4462.5 <null>             20
      7654 martin     SALESMAN        7698 1981-09-28 00:00:00       1875       1400         30
      7698 blake      MANAGER         7839 1981-05-01 00:00:00       4275 <null>             30
      7782 clark      MANAGER         7839 1981-06-09 00:00:00       3675 <null>             10
      7788 scott      ANALYST         7566 1987-04-19 00:00:00      184.5 <null>             20
      7839 king       PRESIDENT <null>     1981-11-17 00:00:00       7500 <null>             10
      7844 turner     SALESMAN        7698 1981-09-08 00:00:00       2250          0         30
      7876 adams      CLERK           7788 1987-05-23 00:00:00       1650 <null>             20
      7900 james      CLERK           7698 1981-12-03 00:00:00       1425 <null>             30
      7902 ford       ANALYST         7566 1981-12-03 00:00:00       4500 <null>             20
 ....


发现数据成功导入.如果存在主键、唯一键约束的,在导入数据的时候,会自动跳过此行,并进行提示,如:

插入数据失败:[-1] ORA-00001: 违反唯一约束条件 (SCOTT.PK_DEPT)
失败语句:insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values( 40, 'OPERATIONS', 'BOSTON')

-------------------------------------------------

如有BUG,欢迎各位指出。