Oracle批量导入图片到blob字段

要插入图片的表不是固定的,而且是批量插入很多张,还要考虑到因为图片的文件名错误,修改后要再次插入,此时应避免已经插入的重复执行操作, 浪费时间。

所以就选择先用一张临时表来暂时保存从文件系统读取的照片,用DBMS_LOB的方法来处理。

第一步:创建一个数据库可以访问的目录(注意:这个目录是数据库服务器上的目录,不是客户机上的)

-- 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;

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/e80f28ee85f1eff2034e392a4b927697.html