利用PL/SQL读写文件

恩，这是上星期五去面试的时候的面试问题，当时没回答上来，因为根本没用过，都是利用Java进行文件的读写的，我也不知道在PL/SQL中读写文件有什么好处，权当扩大一下知识面吧。

首先，我们需要用到Oracle的DIRECTORY，DIRECTORY简单的说就是一个目录，可以存放绝对路径，也可以存放相对路径，创建目录的语法如下：

create directory exp_dir as 'E:/directory';

当然，创建目录前我们需要把相应的权限授予用户，我们以scott用户为例，虽然scott doesnt know~~~~

grant create any directory to scott;

然后我们再创建一个用作测试的存储过程：

CREATE OR REPLACE PROCEDURE PROC_DIRECTORY_TEST AS 
BEGIN
  declare
      fhandle utl_file.file_type;
    begin
      fhandle := utl_file.fopen('EXP_DIR', 'example.txt', 'w');
      utl_file.put_line(fhandle , 'eygle test write one');
      utl_file.put_line(fhandle , 'eygle test write two');
      utl_file.fclose(fhandle);
    end;

END PROC_DIRECTORY_TEST;

运行这个存储过程，我们发现出现错误：
ORA-29283: 文件操作无效
ORA-06512: 在 "SYS.UTL_FILE", line 475
ORA-29283: 文件操作无效
ORA-06512: 在 "SCOTT.PROC_DIRECTORY_TEST", line 6
ORA-06512: 在 line 2

原因是我们需要手动创建目录'E:/directory'

创建后再执行就成功了,那读文件怎么办呢？很简单，我们一般读文件的需求就是将文件中的内容插入到表中，就不自己写了，直接转载：

文件zte.apsuic位于/home/zxin10/file下,其数据格式：
1|22|cheng
2|33|zhou
3|44|heng
4|55|yaya


表LOADDATA脚本：

-- Create table
create table LOADDATA
(
  ID   VARCHAR2(50),
  AGE  VARCHAR2(50),
  NAME VARCHAR2(50)
)

程序如下：(读取文件时,指定文件名一定要预先存在,否则程序会失败)

create or replace directory BBB as '/home/zxin10/file';
/
--作用法是将特定的文件路径信息想Oracle注册(注册信息存放在sys.dir$表中)

CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_TEST_3
(
   --文件中的信息导入表中
     V_TEMP VARCHAR2,
     v_retvalue   OUT NUMBER --1 成功 ，0失败
AS 
  v_file_handle   utl_file.file_type;  
  v_sql varchar2(1000);
  v_filepath Varchar2(500);
  v_filename Varchar2(500);  
  --文件到表字段的映射
  v_id varchar2(1000);
  v_age varchar2(1000);
  v_name varchar2(1000);
  --缓冲区
  v_str varchar2(1000);
  --列指针
  v_i number;
  --字符串定位解析指针
  v_sposition1 number;
  v_sposition2 number;
  begin
      v_filepath := V_TEMP;
      if v_filepath is null then
         v_filepath := '/home/zxin10/file';
      end if;
      v_filename:='zte.apsuic';
      --v_sql:= 'create or replace directory CCC as '''|| v_filepath || '''';
      --execute immediate v_sql; 

      v_file_handle:=utl_file.fopen('CCC',v_filename,'r');       
      Loop
             --将文件信息读取到缓冲区v_str中,每次读取一行
             utl_file.get_line(v_file_handle,v_str);
             --dbms_output.put_line(v_str); 
             --针对每行的列数
             v_i := 1;
             --针对字符串每次的移动指针
             v_sposition1 := 1;
             --文件中每行信息3列,循环3次
             FOR I IN 1..3 loop               
             --当instr(v_str, '|', 6)其中v_str为1|22|wuzhuocheng ，它返回0
             v_sposition2 := instr(v_str, '|', v_sposition1);
             --字符串解析正常情况
             if v_sposition2 <> 0 then
               if v_i=1     then
                  v_id := substr(v_str, v_sposition1, v_sposition2 - v_sposition1);      --第一列               
               elsif  v_i=2 then
                    v_age := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第二列
               elsif v_i=3  then
                  v_name := substr(v_str, v_sposition1, v_sposition2 - v_sposition1);    --第三列 
               else
                  return;
               end if;                            
             --字符串解析异常情况
             else 
               if v_i=1    then
                v_id := substr(v_str, v_sposition1);      --第一列
               elsif v_i=2 then
                  v_age := substr(v_str, v_sposition1); --第二列
               elsif v_i=3 then
                v_name := substr(v_str, v_sposition1);    --第三列 
               else
                return;
               end if;  
             end if;   
             v_sposition1 := v_sposition2 + 1;
             v_i := v_i+1;
             end loop; 
             --每列循环完后将信息insert into表中
             insert into zxdbm_ismp.loaddata values(v_id,v_age,v_name);                  
      End Loop;
      --关闭句柄
      utl_file.fClose(v_file_handle);
      v_retvalue :=1;
  exception when others then
         v_retvalue :=0; 
  end V3_SUB_FETCH_TEST_3;