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

数据库  /  管理员 发布于 6年前   119

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

所以就选择先用一张临时表来暂时保存从文件系统读取的照片，用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;

--定义一张异常信息记录表，是一个非常好的习惯

    INSERT INTO TA_程序运行异常记录    VALUES     (SQ_异常序列.NEXTVAL, 'PRO_插入图片', V_ERR, SYSDATE);    COMMIT;  end; END LOOP; CLOSE P_CURSOR; COMMIT; DELETE TA_照片总表_TEMP; COMMIT;END PRO_插入图片;

总结

然后测试、运行，基本都没问题，不过图片的大小，很影响实际插入的时间，这个时间的优化目前还没有好的对策。

以上就是本文关于ORACLE批量导入图片到BLOB字段代码示例的全部内容，希望对大家有所帮助。感兴趣的朋友可以参阅：oracle 数据库启动阶段分析 、ORACLE SQL语句优化技术要点解析 、oracle中length、lengthb、substr、substrb函数用法介绍 等，有什么问题可以随时留言，小编会及时回复大家的。也希望朋友们对站多多支持！