oracle bolb类型的 使用

--存入
--SYS用户创建目录
create directory images as 'c:\images';
--授权给用户
grant read on directory images to scott;
--建BLOB类型字段表
create table my_img
(img_no number,img_name varchar2(200),img blob);
--将文件中的图片存到数据库中
declare
l_bfile bfile;
l_blob blob;
begin
  insert into my_img (img) values (empty_blob()) return img into l_blob;
  l_bfile := bfilename('IMAGES','\one.jpg');
  dbms_lob.open(l_bfile,dbms_lob.file_readonly);
  dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
  dbms_lob.close(l_bfile);
  commit;
end;


--读取
CREATE OR REPLACE PROCEDURE souchang_dump_blob (ptable varchar2,pkey varchar2,pblob 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_sql varchar2(2000);
      l_blob_len  INTEGER;
      l_iname varchar2(100);
      l_oname varchar2(100);
      l_cursor varchar2(2000);
      type c_blob is ref cursor;
      my_blob c_blob;
      l_errcode number;
      l_errmsg varchar2(200);
    BEGIN
   /* if pkey is null then
       null;
    else
    */
        l_cursor:='select '||pkey||' from '||upper(ptable)||' where img_no=1';
        open  my_blob for l_cursor;
        loop
           l_iname:='';
           l_pos:=1;
           l_buffer:=Null;
           fetch my_blob into l_iname;
                 exit when my_blob%notfound;
                 l_blob_sql:='select '||pblob||' from '||upper(ptable)||' where '||pkey||'=:b';
                 execute immediate l_blob_sql into l_blob using l_iname;
     l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
     l_oname:='ong.jpg';
     --dbms_output.put_line(l_oname);
     --dbms_output.put_line(l_blob_len);
     l_file := UTL_FILE.FOPEN('IMAGES',l_oname,'w', 32767);
            --l_amount := 4000;
     WHILE l_pos < l_blob_len Loop
           /*If (l_amount>(l_blob_len-l_pos)) Then
              l_amount:=l_blob_len-l_pos+1;
           End If;   */
       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;
       dbms_output.put_line(l_amount);
     END LOOP;
     UTL_FILE.FCLOSE(l_file);
     end loop;
     close my_blob;
     /*end if;*/
   EXCEPTION
     WHEN OTHERS THEN
       IF UTL_FILE.IS_OPEN(l_file) THEN
         UTL_FILE.FCLOSE(l_file);
       END IF;
       l_errcode:=sqlcode;
       l_errmsg:=sqlerrm;
       dbms_output.put_line(l_errcode||':'||l_errmsg);
       RAISE;
       --null;
   END souchang_dump_blob;