BFILENAME加载图片示例

小鱼儿 2022-06-11 02:29 319阅读 0赞

\--创建测试表

CREATE TABLE my\_blobs (   
　　dname VARCHAR2(30), --目录名称  
　　sname VARCHAR2(30), --目录描述   
　　fname VARCHAR2(30), -- 文件名称  
　　fblob BLOB); --图片  
  
  
\--授权创建目录  
SQL> conn / AS SYSDBA  
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0   
Connected as SYS  
SQL> GRANT CREATE ANY DIRECTORY TO SPATIAL ;

Grant succeeded

\--创建目录  
SQL> conn spatial/spatial  
Connected.  
SQL> CREATE OR REPLACE DIRECTORY spatial\_dir AS '/home/oracle/spatial';  
  
  
\--创建加载图片存储  
CREATE OR REPLACE PROCEDURE LOAD\_BLOB(PDNAME IN VARCHAR2,  
                                      PSNAME IN VARCHAR2,  
                                      PFNAME IN VARCHAR2) IS  
  SRC\_FILE BFILE;  
  DST\_FILE BLOB;  
  LGH\_FILE BINARY\_INTEGER;  
BEGIN  
  SRC\_FILE := BFILENAME('SPATIAL\_DIR', PFNAME);  
  -- 插入一条空BLOB数据记录,返回BLOB字段  
  INSERT INTO MY\_BLOBS  
    (DNAME, SNAME, FNAME, FBLOB)  
  VALUES  
    (PDNAME, PSNAME, PFNAME, EMPTY\_BLOB())  
  RETURNING FBLOB INTO DST\_FILE;  
  -- 锁住该记录  
  SELECT FBLOB  
    INTO DST\_FILE  
    FROM MY\_BLOBS  
   WHERE DNAME = PDNAME  
     AND SNAME = PSNAME  
     AND FNAME = PFNAME  
     FOR UPDATE;  
  -- 打开图片文件  
  DBMS\_LOB.FILEOPEN(SRC\_FILE, DBMS\_LOB.FILE\_READONLY);  
  -- 获取图片长度  
  LGH\_FILE := DBMS\_LOB.GETLENGTH(SRC\_FILE);  
  -- 读取图片到目标文件  
  DBMS\_LOB.LOADFROMFILE(DST\_FILE, SRC\_FILE, LGH\_FILE);  
  -- 更新BLOB字段  
  UPDATE MY\_BLOBS  
     SET FBLOB = DST\_FILE  
   WHERE DNAME = PDNAME  
     AND SNAME = PSNAME  
     AND FNAME = PFNAME;  
  -- 关闭文件读取  
  DBMS\_LOB.FILECLOSE(SRC\_FILE);  
END LOAD\_BLOB;  
  
  
  
  
\--测试加载图片  
SQL> EXEC LOAD\_BLOB('SPATIAL\_DIR','ORACLE TIMESTEN','qq.png');   
PL/SQL procedure successfully completed.  
  
  
  
  
\--从OS上检查BLOB大小  
SQL> !  
\[oracle@xml-ora1 ~\]$ ll /home/oracle/spatial/qq.png   
\-rw-r--r-- 1 oracle oinstall 119700 Jun  1 09:31 /home/oracle/spatial/qq.png  
  
  
\--从Oracle检查BLOB大小  
  
  
SQL> set serveroutput on  
SQL> DECLARE  
  2    A BLOB;  
  3  BEGIN  
  4    SELECT FBLOB INTO A FROM MY\_BLOBS;  
  5    DBMS\_OUTPUT.PUT\_LINE(DBMS\_LOB.GETLENGTH(A));  
  6  END;  
  7  /  
119700  
  
  
PL/SQL procedure successfully completed.