orc 文件读取数据 java oracle读取文件方法

转载

lanhy 2023-11-08 12:01:15

文章标签 orc 文件读取数据 java 数据句柄 SQL 文章分类 Java 后端开发

摘要:

用户提出一个需求,即ORACLE中的一个表存储了照片信息,字段类型为BLOB,要求能导出成文件形式. 本想写个C#程序来做,后来想起ORACLE有很多包,功能很好很强大,于是网上参考了些文章完成了. 主要是用了ORACLE的两个包:UTL_FILE和DBMS_LOB.

实现过程:

第一步：以管理员用户登陆设置可操作目录

--CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.
create or replace directory BLOBDIR as 'D:\PIC';
grant read,write on directory BLOBDIR to sharedb;
GRANT EXECUTE ON utl_file TO sharedb;
select * from ALL_DIRECTORIES;

第二步：普通用户登陆,编写存储过程

Code
CREATE OR REPLACE PROCEDURE GET_PIC_BLOB (i_xh VARCHAR2) IS

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

BEGIN
SELECT PIC INTO L_BLOB FROM TB_ZP WHERE PSNNO = i_xh;

 l_blob_len := DBMS_LOB.GETLENGTH(l_blob);

 l_file := UTL_FILE.FOPEN('BLOBDIR',i_xh || '.jpg','WB',32767);

 WHILE l_pos < l_blob_len LOOP

    DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);

    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);

    l_pos := l_pos + l_amount;

 END LOOP;

 UTL_FILE.FCLOSE(l_file);


EXCEPTION

 

 --WHEN NO_DATA_FOUND THEN
  --DBMS_OUTPUT.put_line('no data : ' || i_xh);
 WHEN OTHERS THEN

  IF UTL_FILE.IS_OPEN(l_file) THEN

   UTL_FILE.FCLOSE(l_file);

  RAISE;

  END IF;
END GET_PIC_BLOB;

第三步：编写PL/SQL 块,循环执行该存储过程

declare 

    cursor cur_01 is 

        select xh from xs_xsjbk where rownum <= 5000 ;

begin

    for rec_01 in cur_01 loop        

        GET_PIC_BLOB(rec_01.xh);

    end loop;   
end;

测试结果.取了5000条数据,其中有照片信息的为3407条.用时1分12秒,感觉还可以.

总结:

1.由管理员创建可访问目录和授权给普通用户比较重要,一开始没有注意,总是报非法路径错误,搞了较长时间在这上面.

2.存储过程中的NO_DATA_FOUND异常本来是屏显输出无照片的学号信息,但是实际运行时出错,原因是DBMS_OUTPUT.put_line打印条数过多,于是注释掉了.

3.Oracle本身提供了大量使用的包,如UTL_HTTP,DBMS_OUTPUT等,分别封装了不同的功能,进行大量的应用程序开发的可能,从而拓展了Oracle的功能.

网上参考:

Code
CREATE OR REPLACE PROCEDURE P_WRITE_EMP AS

V_FILE UTL_FILE.FILE_TYPE;

V_BUFFER VARCHAR2(32767);
BEGIN

V_FILE := UTL_FILE.FOPEN('D_OUTPUT', 'EMP' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.csv', 'w', 32767);

V_BUFFER := 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO';

UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);
FOR I IN 

(

 SELECT '"' || EMPNO || '","' || 

 ENAME || '","' || 

 JOB || '","' || 

 MGR || '","' || 

 HIREDATE || '","' || 

 SAL || '","' || 

 COMM || '","' || 

 DEPTNO || '"' RESULT

 FROM EMP

 ) LOOP

 UTL_FILE.PUT_LINE(V_FILE, I.RESULT);

 END LOOP;

 UTL_FILE.FCLOSE(V_FILE);

 END;

PL/SQL 3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下：
declare
file_handle UTL_FILE.FILE_TYPE;
begin
file_handle := UTL_FILE.FOPEN('TMP', '文件名', 'w',[1-32767]);
--四个参数：目录，文件名，打开方式，最大行数(默认为2000)
UTL_FILE.PUTF(file_handle, '写入的信息\n');
UTL_FILE.FCLOSE(file_handle);
exception
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
end;
--PutF()过程用来以指定格式把文本写入一个文件
--Put_Line()过程把一个指定的字符串写入文件并在文件中开始新的一行

Code
CREATE OR REPLACE PROCEDURE pReadFileTest  

   (FPATH IN STRING,FNAME IN STRING,MAX_NUM IN NUMBER) 
IS 

   FILE_HANDLE UTL_FILE.FILE_TYPE;

   TEXT_BUFFER STRING(1000);

   LINE_NUM NUMBER;
BEGIN 

   DBMS_OUTPUT.PUT_LINE('INPUT PATH='FPATH); 

   DBMS_OUTPUT.PUT_LINE('INPUT FILENAME='FNAME);

   LINE_NUM :=0;

   BEGIN 

     FILE_HANDLE := UTL_FILE.FOPEN(FPATH,FNAME,'R',MAX_NUM);

     LOOP 

       LINE_NUM:= LINE_NUM + 1;

       UTL_FILE.GET_LINE(FILE_HANDLE,TEXT_BUFFER);

       DBMS_OUTPUT.PUT_LINE('LINE'LINE_NUM' : 'TEXT_BUFFER);

     END LOOP;

   EXCEPTION

     WHEN NO_DATA_FOUND THEN

       RETURN;

     WHEN UTL_FILE.INVALID_PATH THEN

       DBMS_OUTPUT.PUT_LINE('INVALID PATH');

     WHEN UTL_FILE.INVALID_MODE THEN 

       DBMS_OUTPUT.PUT_LINE('INVALID MODE');

     WHEN UTL_FILE.INVALID_FILEHANDLE THEN

       DBMS_OUTPUT.PUT_LINE('INVALID FILEHANDLE');

     WHEN UTL_FILE.INVALID_OPERATION THEN

       DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');

     WHEN UTL_FILE.READ_ERROR THEN

       DBMS_OUTPUT.PUT_LINE('READ ERROR');

     WHEN UTL_FILE.WRITE_ERROR THEN

       DBMS_OUTPUT.PUT_LINE('WRITE ERROR');

     WHEN UTL_FILE.INTERNAL_ERROR THEN

       DBMS_OUTPUT.PUT_LINE('INTERNAL ERROR');

     WHEN OTHERS THEN 

       DBMS_OUTPUT.PUT_LINE(SQLERRM);

   END;

EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('OTHER ERROR='SQLERRM);
END pReadFileTest;

文件I/O对于数据库的开发来说显得很重要，比如如果数据库中的一部分数据来自于磁盘文件，那么就需要使用I/O接口把数据导入到数据库中来。在PL/SQL中没有直接的I/O接口，一般在调试程序时可以使用Oracle自带的DBMS_OUTPUT包的put_line函数（即向屏幕进行I/O操作）即可，但是对于磁盘文件的I/O操作它就无能为力了。其实Oracle同样也提供了可以进行文件I/O的实用包-----UTL_FILE包，利用这个实用包提供的函数来实现对磁盘的I/O操作。

UTL_FILE包提供了很多实用的函数来进行I/O操作，主要有以下几个函数：

fopen 打开指定的目录路径的文件。

get_line 获取指定文件的一行的文本。

put_line 向指定的文件写入一行文本。

fclose 关闭指定的文件。

下面利用这些函数，实现从文件取数据，然后将数据写入到相应的数据库中。

Code
  create or replace procedure loadfiledata(p_path varchar2,p_filename varchar2) as 


 


  v_filehandle utl_file.file_type; --定义一个文件句柄


  v_text varchar2(100); --存放文本


  v_name test_loadfile.name%type;


  v_addr_jd test_loadfile.addr_jd%type;


  v_region test_loadfile.region%type;


  v_firstlocation number;


  v_secondlocation number;


  v_totalinserted number;


  begin


  if (p_path is null or p_filename is null) then


  goto to_end;


  end if;


  v_totalinserted:=0;


  /*open specified file*/


  v_filehandle:=utl_file.fopen(p_path,p_filename,'r');


  loop


  begin


  utl_file.get_line(v_filehandle,v_text);


  exception


  when no_data_found then


  exit;


  end ;


  v_firstlocation:=instr(v_text,',',1,1);


  v_secondlocation:=instr(v_text,',',1,2);


  v_name:=substr(v_text,1,v_firstlocation-1);


  v_addr_jd:=substr(v_text,v_firstlocation+1,v_secondlocation-v_firstlocation-1);


  v_region:=substr(v_text,v_secondlocation+1);


  /*插入数据库操作*/


  insert into test_loadfile


  values (v_name,v_addr_jd,v_region);


  commit;


  end loop;


  <<to_end>>


  null;


  end loadfiledata; 
 
Codecreate or replace procedure test_error
(
str out varchar2,
str2 out varchar2
)
as
begin
  declare 
    isto_file utl_file.file_type;
    err_num number;
    i number;
    k number;
    m number;
    err_msg varchar2(100);
    fp_buffer varchar2(4000);
  begin 
    isto_file := utl_file.fopen('IST0_DIR', 'kj021320.txt', 'W');
    i:=0;
    while (i<2)
    loop
    utl_file.put_line(isto_file, 'My');
    i:=i+1;
    end loop;
    utl_file.fflush(isto_file);
    utl_file.fclose(isto_file);
    
    
    isto_file := utl_file.fopen('IST0_DIR', 'kj021320.txt', 'a');
    m:=0;
    while (m<2)
    loop
    utl_file.put_line(isto_file, 'My');
    m:=m+1;
    end loop;
    utl_file.fflush(isto_file);
    utl_file.fclose(isto_file);
    
    isto_file := utl_file.fopen('IST0_DIR', 'kj021320.txt', 'R');
    str2:=''; 
    loop
    utl_file.get_line (isto_file , fp_buffer );
    str2:=str2 || fp_buffer;
    end loop;
    utl_file.fclose(isto_file);       

    for j in 1..10  /**//* for */
    loop
      k:=11;
    end loop; 
  EXCEPTION
  WHEN OTHERS THEN
    err_num:=sqlcode; /**//* 異常num */
    err_msg:=substr(sqlerrm,1,100); /**//* 異常msg */
    str:=substr(sqlerrm,1,100);
  end; 
end test_error;
 
 
Code/**//*