pl/sql 将bolb存成文件

最新推荐文章于 2022-03-04 08:58:53 发布

levin_li

最新推荐文章于 2022-03-04 08:58:53 发布

阅读量128

点赞数

分类专栏： Database 文章标签： oracle

本文链接：https://blog.csdn.net/levin_li/article/details/7078432

版权

Database 专栏收录该内容

65 篇文章 0 订阅

订阅专栏

1.确认现有对象

SQL> col fdesc for a30
SQL> select fid,fname,fdesc from eygle_blob;

       FID FNAME                                              FDESC
---------- -------------------------------------------------- ------------------------------
         1 ShaoLin.jpg                                        少林寺-康熙手书
         2 DaoYing.jpg                                        倒映

2.创建存储Directory

SQL> connect / as sysdba
Connected.
SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';

Directory created.

SQL>
SQL> grant read,write on directory BLOBDIR to eygle;

Grant succeeded.

SQL>

3.创建存储过程

SQL> connect eygle/eygle
Connected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varchar2,poname varchar2) IS
  2    l_file      UTL_FILE.FILE_TYPE;
  3    l_buffer    RAW(32767);
  4    l_amount    BINARY_INTEGER := 32767;
  5    l_pos       INTEGER := 1;
  6    l_blob      BLOB;
  7    l_blob_len  INTEGER;
  8  BEGIN
  9    SELECT FPIC
 10    INTO      l_blob
 11    FROM      eygle_blob
 12    WHERE  FNAME = piname;
 13
 14    l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
 15    l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767);
 16
 17    WHILE l_pos < l_blob_len LOOP
 18      DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
 19      UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
 20      l_pos := l_pos + l_amount;
 21    END LOOP;
 22
 23    UTL_FILE.FCLOSE(l_file);
 24
 25  EXCEPTION
 26    WHEN OTHERS THEN
 27      IF UTL_FILE.IS_OPEN(l_file) THEN
 28        UTL_FILE.FCLOSE(l_file);
 29      END IF;
 30      RAISE;
 31  END;
 32  /

Procedure created.

4.取出数据

SQL> host ls -l d:\oradata\Pic
total 7618
-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg

SQL> exec eygle_dump_blob('ShaoLin.jpg','01.jpg')

PL/SQL procedure successfully completed.

SQL> host ls -l d:\oradata\Pic
total 11072
-rwxrwxrwa   1 Administrators  SYSTEM          1768198 Apr 26 07:16 01.jpg
-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg

SQL>
SQL> exec eygle_dump_blob('DaoYing.jpg','02.jpg')

PL/SQL procedure successfully completed.

SQL> host ls -l d:\oradata\Pic
total 15236
-rwxrwxrwa   1 Administrators  SYSTEM          1768198 Apr 26 07:16 01.jpg
-rwxrwxrwa   1 Administrators  SYSTEM          2131553 Apr 26 07:19 02.jpg
-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg