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


		[PL/SQL]使用存储过程实现导出指定数据到文件(仿EXP)
 
由于EXP不能导出表的指定字段(其实也可通过CREATE TABLE  tablename AS SELECT...来间接实现),故自己写了个存储来实现这个功能.  www.zzzyk.com  
[sql] 
/*  
描述:按指定条件导出指定表的指定列的数据到指定文件(增强了EXP工具,exp不能直接导出指定字段的数据)  
created by cryking 2013.03.03  
注意:1.本存储建议由SYS账户或具有SYSDBA权限的账户执行  
     2.不要在其他事务中运行本存储过程  
     3.null值导出后为'null'    www.zzzyk.com  
    4.指定字段分隔符不能在导出的字段数据中有,否则会报错  
     5.注意系统会自动创建或覆盖目录名为EXPDIR的目录,所以在执行存储之前检查一下是否有目录名为EXPDIR的目录  
*/  
create or replace procedure exp_data  
(  
  file_name in varchar2 --要导出的文件名  
 ,path_name in varchar2 --路径  
 ,p_user    in varchar2 --要导出的用户  
 ,p_table   in varchar2 --要导出的表  
 ,p_filed   in varchar2 default '*' --要导出的字段,逗号分隔,默认为所有字段  
 ,p_sep     in varchar2 default ',' --字段分隔符,默认为逗号  
 ,p_where   in varchar2 default ' ' --条件,注意要写'WHERE'  
) AS  
  v_file UTL_FILE.file_type;  
  TYPE t_filed IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;  
  v_filed  t_filed;  
  v_sql    varchar2(30000);  
  v_cursor integer;  
  v_col    varchar2(4000);  
  i_exe    integer;  
  v_sid    integer;  
  v_SERIAL integer;  
  
  exp_sep exception;  
  
BEGIN  
  rollback; --防止在其他事务中运行本存储,先回滚之前的事务  
  execute immediate 'create or replace directory EXPDIR as ''' || path_name ||  
                    ''' '; --创建目录  
  
  v_file := UTL_FILE.fopen('EXPDIR', file_name, 'w');  
  select * bulk collect into v_filed from table(splitstr(p_filed, ','));  
  for i in 1 .. v_filed.count  
  loop  
    utl_file.putF(v_file, v_filed(i)); --写表字段到文件  
    utl_file.put(v_file, p_sep); --输出字段分隔符  
  end loop;  
  utl_file.new_line(v_file);  
  
  v_sql    := 'select ' || p_filed || ' from ' || p_user || '.' || p_table ||  
              p_where;  
  v_cursor := DBMS_SQL.OPEN_CURSOR; --获得游标  
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE); --解析sql  
  
  for j in 1 .. v_filed.count  
  loop  
    --定义列  
    DBMS_SQL.DEFINE_COLUMN(v_cursor, j, v_col, 4000);  
    --dbms_output.put_LINE(v_col);  
  end loop;  
  
  i_exe := DBMS_SQL.EXECUTE(v_cursor);  
  
  while (DBMS_SQL.FETCH_ROWS(v_cursor))>0 loop  
    for k in 1 .. v_filed.count  
    loop  
      DBMS_SQL.COLUMN_VALUE(v_cursor, k, v_col);  
      --dbms_output.put_LINE(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;  
  UTL_FILE.fclose(v_file);  
  DBMS_OUTPUT.put_line('导出数据成功完成!');  
  
EXCEPTION  
  when exp_sep then  
    raise_application_error(-20001,  
                            '导出的数据中包含了指定的字段分隔符:' || p_sep || ',请更换字段分隔符!');  
  WHEN OTHERS THEN  
    DBMS_OUTPUT.put_line('导出数据失败');  
    UTL_FILE.fclose(v_file);  
    raise;  
END exp_data;  
 
 测试:
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,
 
---------------------------------------