ORACLE批量导入图片到BLOB字段代码示例

-- Create directory

create or replace directory 图片目录

  as 'E:\照片' ;

CREATE OR REPLACE PROCEDURE PRO_插入图片(V_表名 IN VARCHAR2) IS

  P_FILENAME VARCHAR2(50); --照片名，动态拼接得到

  P_证件号码 VARCHAR2(50);

  P_姓名   VARCHAR2(50); --这个照片名是通过姓名+证件号拼接得到的，因为基础测试数据没有提供真实的证件号码，就选择用手机号来代替

  P_查询SQL VARCHAR2(500);

  P_更新SQL VARCHAR2(5000);

  P_LOB   BLOB;

  P_FILE   BFILE;

  TYPE P_REF_CURSOR IS REF CURSOR ; --定义动态游标变量类型

  P_CURSOR P_REF_CURSOR; --定义动态游标变量，因为一次要插入全表的照片，所以选择用游标来处理

  TYPE P_ROW_RECORD IS RECORD(

   证件号码 VARCHAR2(50),

   姓名   VARCHAR2(50));

  C_ROW P_ROW_RECORD;

  V_ERR VARCHAR2(300);

BEGIN

  P_更新SQL := 'update ' || V_表名 || ' set 证件号码=手机号码 WHERE 证件号码 IS NULL' ;

--用手机号来代替证件号码为空的数据

  EXECUTE IMMEDIATE P_更新SQL;

  COMMIT ;

  P_查询SQL := 'SELECT 证件号码,姓名 FROM ' || V_表名 ||

        ' WHERE 证件号码 IS NOT NULL and 照片 IS NULL order by 证件号码' ;

  OPEN P_CURSOR FOR P_查询SQL;

  LOOP

   begin

    FETCH P_CURSOR

     INTO C_ROW;

    EXIT WHEN P_CURSOR%NOTFOUND;

    P_证件号码 := C_ROW.证件号码;

    P_姓名   := C_ROW.姓名;

    SELECT REPLACE (P_证件号码, ' ' , '' ) INTO P_证件号码 FROM DUAL;

    SELECT substr(P_证件号码, 1, 11) INTO P_证件号码 FROM DUAL;

    SELECT REPLACE (P_姓名, ' ' , '' ) INTO P_姓名 FROM DUAL;

    P_FILENAME := P_证件号码 || P_姓名 || '.jpg' ;

    SELECT REPLACE (P_FILENAME, ' ' , '' ) INTO P_FILENAME FROM DUAL;

--以下便是插入图片的核心代码

    INSERT INTO TA_照片总表_TEMP

     (证件号码, 姓名, 照片)

    VALUES

     (P_证件号码, P_姓名, EMPTY_BLOB()) RETURN 照片 INTO P_LOB;

    --获取指定目录下的文件

    P_FILE := BFILENAME( '图片目录' , P_FILENAME);

    --以只读的方式打开文件

    DBMS_LOB.FILEOPEN(P_FILE, DBMS_LOB.FILE_READONLY);

    --传递对象

    DBMS_LOB.LOADFROMFILE(P_LOB, P_FILE, DBMS_LOB.GETLENGTH(P_FILE));

    --关闭原始文件

    DBMS_LOB.FILECLOSE(P_FILE);

    COMMIT ;

P_更新SQL := 'UPDATE ' || V_表名 ||

        ' A SET a.照片=(SELECT 照片 FROM TA_照片总表_TEMP b

        WHERE A.证件号码 = B.证件号码 and a.姓名=b.姓名 AND ROWNUM=1)

  WHERE EXISTS (SELECT 1 FROM TA_照片总表_TEMP B WHERE A.证件号码 = B.证件号码 and a.姓名=b.姓名)' ;

  EXECUTE IMMEDIATE P_更新SQL;

  COMMIT ;

EXCEPTION

WHEN OTHERS THEN

  rollback ;

  V_ERR := SUBSTR(SQLERRM, 1, 150) || '照片名:' || P_FILENAME;

     INSERT INTO TA_程序运行异常记录

     VALUES

      (SQ_异常序列.NEXTVAL, 'PRO_插入图片' , V_ERR, SYSDATE);

     COMMIT ;

   end ;

  END LOOP;

  CLOSE P_CURSOR;

  COMMIT ;

  DELETE TA_照片总表_TEMP;

  COMMIT ;

END PRO_插入图片;