|
|
51CTO旗下网站
|
|
移动端

Oracle存储过程读写文件

有时候我们需要在文件与数据库表之间利用程序来实现两者的交互,下文介绍的就是Oracle存储过程读写文件的实现方法,供您参考。

作者:佚名来源:互联网|2010-11-19 11:32

Oracle存储过程读写文件是实现文件与数据库表之间交互的重要手段,下面就为您详细介绍Oracle存储过程读写文件方面的知识,希望对您能有所帮助。

有时候我们需要在文件与数据库表之间利用程序来实现两者的交互,这里可以利用UTL_FILE包实现对文件的I/O操作.下面就分别介绍文件写表以及表数据写文件.

[1]表信息导出到文件

在SUSE上建议一个文件夹/home/zxin10/file,然后对其chmod g+w file进行授权(否则无法导出到文件),再对您指定的路径(/home/zxin10/file)向Oracle的系统表sys.dir$进行注册(否则也是无法成功将信息导出到文件),操作完后可以查询sys.dir$可以看到表中的OS_PATH中有您指定的路径位置.

注册方式:执行SQL语句create or replace directory BBB as '/home/zxin10/file'; 即可

存储过程如下:(写文件时,文件名可以不用先创建,程序中会自动创建指定文件)

  1. CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_TEST_2  
  2. (  
  3.    V_TEMP VARCHAR2,  
  4.    --1为成功,0为失败  
  5.    v_retvalue   OUT NUMBER   
  6.  )  
  7. AS  
  8.   --游标定义  
  9.   type ref_cursor_type is REF CURSOR;  
  10.   cursor_select   ref_cursor_type;  
  11.   select_cname    varchar2(1000);  
  12.     
  13.   v_file_handle   utl_file.file_type;  
  14.     
  15.   v_sql varchar2(1000);  
  16.   v_filepath Varchar2(500);  
  17.   v_filename Varchar2(500);  
  18.   --缓冲区  
  19.   v_results Varchar2(500);  
  20.     
  21.   v_pid varchar2(1000);  
  22.   v_cpcnshortname Varchar2(500);  
  23.    
  24.   begin  
  25.       v_filepath :V_TEMP;  
  26.       if v_filepath is null then  
  27.          v_filepath :'/home/zxin10/file3';  
  28.       end if;  
  29.       v_filename:='free_'|| substr(to_char(sysdate,'YYYYMMDDHH24MI'),1,10) ||'.all' ;  
  30.       --游标开始  
  31.       select_cname:='select cpid,cpcnshortname from zxdbm_ismp.scp_basic';   
  32.       --打开一个文件句柄 ,同时fopen的***个参数必须是大写     
  33.       v_file_handle:=utl_file.fopen('BBB',v_filename,'A');  
  34.       Open cursor_select For select_cname;     
  35.       Fetch  cursor_select into v_pid,v_cpcnshortname;  
  36.       While  cursor_select%Found     
  37.       Loop  
  38.       v_results :v_pid||'|'||v_cpcnshortname;  
  39.       --将v_results写入文件  
  40.       utl_file.put_line(v_file_handle,v_results);     
  41.       Fetch  cursor_select into v_pid,v_cpcnshortname;        
  42.       End Loop;  
  43.         
  44.       Close cursor_select;--关闭游标  
  45.       utl_file.fClose(v_file_handle);--关闭句柄  
  46.       v_retvalue :=1;  
  47.   exception when others then  
  48.          v_retvalue :=0;   
  49.   end V3_SUB_FETCH_TEST_2; 

[2]将文件信息导入到表中

和上面一样,先对指定文件路径进行chmod,然后想Oracle的sys.dir$进行路径注册.

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

表LOADDATA脚本:

  1. -- Create table  
  2. create table LOADDATA  
  3. (  
  4.   ID   VARCHAR2(50),  
  5.   AGE  VARCHAR2(50),  
  6.   NAME VARCHAR2(50)  
  7. )  
  8.     / 

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

  1. create or replace directory BBB as '/home/zxin10/file';  
  2. /  
  3. --作用法是将特定的文件路径信息想Oracle注册(注册信息存放在sys.dir$表中)  
  4.  
  5. CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_TEST_3  
  6. (  
  7.    --文件中的信息导入表中  
  8.      V_TEMP VARCHAR2,  
  9.      v_retvalue   OUT NUMBER --1 成功 ,0失败  
  10. AS   
  11.   v_file_handle   utl_file.file_type;    
  12.   v_sql varchar2(1000);  
  13.   v_filepath Varchar2(500);  
  14.   v_filename Varchar2(500);    
  15.   --文件到表字段的映射  
  16.   v_id varchar2(1000);  
  17.   v_age varchar2(1000);  
  18.   v_name varchar2(1000);  
  19.   --缓冲区  
  20.   v_str varchar2(1000);  
  21.   --列指针  
  22.   v_i number;  
  23.   --字符串定位解析指针  
  24.   v_sposition1 number;  
  25.   v_sposition2 number;  
  26.   begin  
  27.       v_filepath :V_TEMP;  
  28.       if v_filepath is null then  
  29.          v_filepath :'/home/zxin10/file';  
  30.       end if;  
  31.       v_filename:='zte.apsuic';  
  32.       --v_sql:= 'create or replace directory CCC as '''|| v_filepath || '''';  
  33.       --execute immediate v_sql;   
  34.         
  35.       v_file_handle:=utl_file.fopen('CCC',v_filename,'r');         
  36.       Loop  
  37.              --将文件信息读取到缓冲区v_str中,每次读取一行  
  38.              utl_file.get_line(v_file_handle,v_str);  
  39.              --dbms_output.put_line(v_str);   
  40.              --针对每行的列数  
  41.              v_i :1;  
  42.              --针对字符串每次的移动指针  
  43.              v_sposition1 :1;  
  44.              --文件中每行信息3列,循环3次  
  45.              FOR I IN 1..3 loop                 
  46.              --当instr(v_str, '|', 6)其中v_str为1|22|wuzhuocheng ,它返回0  
  47.              v_sposition2 :instr(v_str, '|', v_sposition1);  
  48.              --字符串解析正常情况  
  49.              if v_sposition2 <> 0 then  
  50.                if v_i=1     then  
  51.                   v_id :substr(v_str, v_sposition1, v_sposition2 - v_sposition1);      --***列                 
  52.                elsif  v_i=2 then  
  53.                     v_age :substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第二列  
  54.                elsif v_i=3  then  
  55.                   v_name :substr(v_str, v_sposition1, v_sposition2 - v_sposition1);    --第三列   
  56.                else  
  57.                   return;  
  58.                end if;                              
  59.              --字符串解析异常情况  
  60.              else   
  61.                if v_i=1    then  
  62.                 v_id :substr(v_str, v_sposition1);      --***列  
  63.                elsif v_i=2 then  
  64.                   v_age :substr(v_str, v_sposition1); --第二列  
  65.                elsif v_i=3 then  
  66.                 v_name :substr(v_str, v_sposition1);    --第三列   
  67.                else  
  68.                 return;  
  69.                end if;    
  70.              end if;     
  71.              v_sposition1 :v_sposition2 + 1;  
  72.              v_i :v_i+1;  
  73.              end loop;   
  74.              --每列循环完后将信息insert into表中  
  75.              insert into zxdbm_ismp.loaddata values(v_id,v_age,v_name);                    
  76.       End Loop;  
  77.       --关闭句柄  
  78.       utl_file.fClose(v_file_handle);  
  79.       v_retvalue :=1;  
  80.   exception when others then  
  81.          v_retvalue :=0;   
  82.   end V3_SUB_FETCH_TEST_3;  

【编辑推荐】

Oracle读写文件bfilename的实例

Oracle XE自带数据库创建的过程

Oracle物化视图创建全过程

ORACLE创建实例的过程

oracle时间加减的语句写法

【责任编辑:段燃 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

276人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

643人订阅学习

WOT2019全球人工智能技术峰会

WOT2019全球人工智能技术峰会

通用技术、应用领域、企业赋能三大章节,13大技术专场,60+国内外一线人工智能精英大咖站台,分享人工智能的平台工具、算法模型、语音视觉等技术主题,助力人工智能落地。
共50章 | WOT峰会

0人订阅学习

读 书 +更多

Eclipse从入门到精通(第2版)

本书为《Eclipse从入门到精通》一书的全新改版。本书以最新的Eclipse 3.2作为写作版本。全书分为5篇:起步篇介绍了Eclipse及相关插件的安装...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO播客