[PL/SQL]使用存储过程实现导出指定数据到文件(仿EXP)

最新推荐文章于 2023-08-10 13:28:04 发布

Cryking

最新推荐文章于 2023-08-10 13:28:04 发布

阅读量2.9k

点赞数 2

分类专栏： oracle

本文链接：https://blog.csdn.net/edcvf3/article/details/8633247

版权

oracle 专栏收录该内容

92 篇文章 3 订阅

订阅专栏

由于EXP不能直接导出表的指定字段(其实也可通过CREATE TABLE tablename AS SELECT...来间接实现),故自己写了个存储来实现这个功能.

create or replace procedure exp_data
(
  file_name in varchar2 --要导出的文件名,(包含路径,如:d:\test\exp_0003.txt)
 --,path_name in varchar2 --要导出文件的路径
 ,p_user    in varchar2 default SYS_CONTEXT('USERENV', 'CURRENT_USER') --要导出的用户,缺省为当前用户
 ,p_table   in varchar2 default '' --要导出的表,缺省为所有的表
 ,p_filed   in varchar2 default '*' --要导出的字段,逗号分隔,默认为所有字段
 ,p_sep     in varchar2 default ',' --字段分隔符,默认为逗号
 ,p_where   in varchar2 default ' ' --条件,注意要写'WHERE'
) AS
  /*
  描述:按指定条件导出指定表的指定列的数据到指定文件(增强了EXP工具,exp不能直接导出指定字段的数据)
  created by cryking 2013.03.03
  注意:1.本存储建议由SYS账户或具有SYSDBA权限的账户执行
       2.不要在其他事务中运行本存储过程
       3.null值导出后为'null'
      4.指定字段分隔符不能在导出的字段数据中有,否则会报错
  更新日志: 2013.03.04 by cryking
          1.可以不指定用户,缺省为当前用户,但需要当前用户拥有CREATE DIRECTORY的权限
          2.支持导出用户的所有表数据(外部表除外),支持导出所有用户的数据(指定P_USER为'ALL'时)
           2013.03.05 by cryking
          3.合并文件名和路径输入参数
           4.增加了导出文件中用户、表名、字段前[USER:][TABLE:][FILED:]标签,方便以后的导入

  */
  v_file UTL_FILE.file_type;
  TYPE t_filed IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;
  v_fileds t_filed;
  v_sql    varchar2(30000);
  v_user   varchar2(20);
  v_path     varchar2(500);
  v_filename varchar2(50);
  i_cursor integer;
  v_col    varchar2(4000);
  i        integer;
  i_rows   integer := 1;
  exp_sep exception;

BEGIN
  --没有输入用户的情况
  if trim(p_user) is null then
    v_user := SYS_CONTEXT('USERENV', 'CURRENT_USER');
  else
    v_user := upper(p_user);
  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;

  rollback; --防止在其他事务中运行本存储,先回滚之前的事务
  execute immediate 'create or replace directory EXPDIR as ''' || path_name ||
                    ''' '; --创建目录
  
  EXECUTE IMMEDIATE 'ALTER session SET nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
  v_file := UTL_FILE.fopen('EXPDIR', file_name, 'w'); --创建文件

  IF TRIM(p_table) IS NULL THEN
    --导出当前用户所有表数据
    for x in (select OWNER, object_name
                from all_objects
               where object_type = 'TABLE'
                 AND OWNER = DECODE(v_user, 'ALL', OWNER, V_USER) --p_user为ALL的时候,导出所有用户数据
                 AND NOT EXISTS --排除外部表
               (SELECT 1
                        FROM user_external_tables
                       WHERE all_objects.object_name =
                             user_external_tables.TABLE_NAME)
               ORDER BY OWNER)
    LOOP
      execute immediate 'select column_name from all_tab_cols where owner = DECODE(''' ||
                        v_user || ''',''ALL'',OWNER,''' || v_user ||
                        ''') AND TABLE_NAME=''' || x.object_name || ''' ' BULK
                        COLLECT
        INTO v_fileds; --获得当前表的所有字段
    
      v_sql := 'select * from ' || v_user || '.' || x.object_name;
      dbms_output.put_LINE(v_sql);
      i_cursor := DBMS_SQL.OPEN_CURSOR; --获得游标
      DBMS_SQL.PARSE(i_cursor, v_sql, DBMS_SQL.NATIVE); --解析sql
      for j in 1 .. v_fileds.count
      loop
        --定义列
        DBMS_SQL.DEFINE_COLUMN(i_cursor, j, v_col, 4000);
        --dbms_output.put_LINE(v_col);
      end loop;
      i      := DBMS_SQL.EXECUTE(i_cursor);
      i_rows := 1;
      utl_file.put(v_file, '[USER:]' || x.OWNER); --写用户名到文件
      utl_file.put(v_file, '[TABLE:]' || x.object_name); --写表名到文件
       utl_file.new_line(v_file);
      utl_file.put(v_file, '[filed:]'); --写字段到文件
       for i in 1 .. v_fileds.count
          loop
            utl_file.put(v_file, v_fileds(i)); --写表头到文件
            utl_file.put(v_file, p_sep); --输出字段分隔符
          end loop;
          utl_file.new_line(v_file);
      
      while (DBMS_SQL.FETCH_ROWS(i_cursor)) > 0
      loop
        for k in 1 .. v_fileds.count
        loop
          DBMS_SQL.COLUMN_VALUE(i_cursor, k, v_col);
          if instr(v_col, p_sep) > 0 then
            raise exp_sep;
          end if;
          utl_file.putF(v_file,
                        case when v_col is null then 'null' else v_col end);
          utl_file.put(v_file, p_sep); --输出字段分隔符
        END LOOP;
        utl_file.new_line(v_file);
      END LOOP;
    
      DBMS_SQL.close_cursor(i_cursor);
    END LOOP;
  
  ELSE
    IF V_USER = 'ALL' THEN
      raise_application_error(-20002, '导出所有用户数据时,指定了表名');
    END IF;
    --导出指定表数据
    if p_filed = '*' OR TRIM(p_filed) IS NULL then
      select column_name bulk collect
        into v_fileds
        from all_tab_cols
       where owner = upper(v_user)
         and table_name = upper(p_table);
    else
      select * bulk collect
        into v_fileds
        from table(splitstr(p_filed, p_sep));
    end if;
  
    v_sql := 'select ' || p_filed || ' from ' || v_user || '.' || p_table || ' ' ||
             p_where;
  
    i_cursor := DBMS_SQL.OPEN_CURSOR; --获得游标
    DBMS_SQL.PARSE(i_cursor, v_sql, DBMS_SQL.NATIVE); --解析sql
    for j in 1 .. v_fileds.count
    loop
      --定义列
      DBMS_SQL.DEFINE_COLUMN(i_cursor, j, v_col, 4000);
    end loop;
    i := DBMS_SQL.EXECUTE(i_cursor);
    
    utl_file.put(v_file, '[filed:]'); --写字段到文件
    for i in 1 .. v_fileds.count
        loop
          utl_file.put(v_file, v_fileds(i)); --写表头到文件
          utl_file.put(v_file, p_sep); --输出字段分隔符
        end loop;
         utl_file.new_line(v_file);
    
    while (DBMS_SQL.FETCH_ROWS(i_cursor)) > 0
    loop
        
      for k in 1 .. v_fileds.count
      loop
        DBMS_SQL.COLUMN_VALUE(i_cursor, k, v_col);
        if instr(v_col, p_sep) > 0 then
          raise exp_sep;
        end if;
        utl_file.putF(v_file,
                      case when v_col is null then 'null' else v_col end);
        utl_file.put(v_file, p_sep); --输出字段分隔符
      END LOOP;
      utl_file.new_line(v_file);
    END LOOP;
    DBMS_SQL.close_cursor(i_cursor);
  END IF;
  UTL_FILE.fclose(v_file);
  DBMS_OUTPUT.put_line('导出数据成功完成!');

EXCEPTION
  when exp_sep then
    raise_application_error(-20001,
                            '导出的数据中包含了指定的字段分隔符:' || p_sep || ',请更换字段分隔符!');
    UTL_FILE.fclose(v_file);
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('导出数据' || file_name || '失败');
    UTL_FILE.fclose(v_file);
    raise;
END exp_data;

其中SPLITSTR分隔函数的实现见本博客的:

http://blog.csdn.net/edcvf3/article/details/8050978 贴中的内容二

测试:

SYS用户登入:

执行

begin
exp_data('exp_0303.txt', 'd:\test', 'scott', 'emp', 'ename,empno');
end;

来实现导出SCOTT下的表EMP的字段ENAME和EMPNO,看到“导出数据成功完成!”后,打开文件exp_0303.txt,看到内容:

ename,empno,
smith,7369,
allen,7499,
ward,7521,
jones,7566,
martin,7654,
blake,7698,
clark,7782,
scott,7788,
king,7839,
turner,7844,
adams,7876,
james,7900,
ford,7902,
miller,7934,

...

执行
begin

exp_data('exp_0303.txt', 'd:\test', 'scott', 'emp', 'ename,empno',',','where empno=7788');

end;

来实现按条件导出指定数据,完成后打开文件,内容如下:

ename,empno,
scott,7788,

执行
begin

exp_data('exp_0303.txt', 'd:\test', 'scott', 'emp');

end;

来导表EMP的所有数据,完成后打开文件内容如下:

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,
7369,smith,CLERK,7902,17-12月-80,1200,null,20,
7499,allen,SALESMAN,7698,20-2月 -81,2400,300,30,
7521,ward,SALESMAN,7698,22-2月 -81,1875,500,30,
7566,jones,MANAGER,7839,02-4月 -81,4462.5,null,20,
7654,martin,SALESMAN,7698,28-9月 -81,1875,1400,30,
7698,blake,MANAGER,7839,01-5月 -81,4275,null,30,
7782,clark,MANAGER,7839,09-6月 -81,3675,null,10,
7788,scott,ANALYST,7566,19-4月 -87,184.5,null,20,
7839,king,PRESIDENT,null,17-11月-81,7500,null,10,
7844,turner,SALESMAN,7698,08-9月 -81,2250,0,30,
7876,adams,CLERK,7788,23-5月 -87,1650,null,20,
7900,james,CLERK,7698,03-12月-81,1425,null,30,
7902,ford,ANALYST,7566,03-12月-81,4500,null,20,
7934,miller,CLERK,7782,23-1月 -82,1950,null,10,

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

2013.03.04更新后测试:

begin
exp_data('exp_0304.txt', 'd:\test');
end;

结果:

[USER:]SCOTT[TABLE:]TEST3
ID,NAME,
6,6,
7,7,
8,8,
[USER:]SCOTT[TABLE:]T4
A,B,
12,Big number,
10,number ten,
[USER:]SCOTT[TABLE:]T5
C,D,
number ten,10,

[USER:]SCOTT[TABLE:]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,

...

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

2013.03.05 更新后,调用方式为:

begin

exp_data('d:\test\exp_030401.txt', 'SCOTT', 'emp', 'ename,empno',',','where empno=7788');

end;

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

导出的数据可以用SQLLDR工具很方便的导入数据库,如有BUG,欢迎指出.