oracle数据库中BLOB格式照片转存本地jpg格式的方式

oracle数据库中BLOB格式照片转存本地jpg格式的方式

目前需求是将windows系统上的oracle库里的照片导入应用系统的人脸库；oracle中存储照片的格式是blob，需要先将blob格式转化为jpg照片，oracle中表的存储有两个字段：证件号、照片，存储一人多张照片，最终照片名字的命名需要区分有辨识度，所以在照片名字的命名考虑不同人同名的，一人多张照片的情况，照片命名规则,同一个人只有一张照片：姓名_证件号.jpg;同一个人有多张照片：姓名_证件号_系号.jpg 。
这里使用oracle本身的存储过程，以为一张表的数据量有400万+的，所以后面考虑分表并行调用存储过程进行处理。

如果你的oracle有多个表空间，需要注意的是每一个表空间可能有一个用户，如果用system用户或者其他用户没有权限时，需要使用该表空间的用户进行登录才能操作；本次本人通过navicat工具进行连接的。

第一步、处理一人多张的图片问题:解决同人多张图片,使用"证件号_序号.jpg"格式命名

1、将旧表数据插入到新表中,新表新增一个xh字段,该字段值为证件号_序号

CREATE OR REPLACE PROCEDURE create_and_populate_table IS
BEGIN
    -- 使用动态SQL创建表
    EXECUTE IMMEDIATE 'CREATE TABLE JOBS_copy AS SELECT e.*,CAST(NULL AS VARCHAR2(50)) as xh FROM BAK_OJDT_PERSON_PIC_C e WHERE 1=0';
    -- 将BAK_OJDT_PERSON_PIC_C表中的数据插入到 JOBS_copy 表中
    EXECUTE IMMEDIATE 'INSERT INTO JOBS_copy SELECT t.OJ_IDNO AS OJ_IDNO,t.OJ_PIC AS OJ_PIC,CASE WHEN t.rn =1 THEN t.OJ_IDNO ELSE CONCAT(t.OJ_IDNO,CONCAT(''_'',t.rn)) END AS XH FROM (select OJ_IDNO,OJ_PIC,ROW_NUMBER() OVER(PARTITION BY OJ_IDNO ORDER BY NULL) as rn FROM BAK_OJDT_PERSON_PIC_C WHERE ROWNUM <= 10) t';
    -- 提交事务
    COMMIT;
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('Table JOBS_copy created and data inserted successfully.');
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获错误并输出错误信息
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

调用存储过程

BEGIN
    create_and_populate_table;
END;

2、通用模板,需要传入原表名和目标表名，

CREATE OR REPLACE PROCEDURE create_and_populate_table_dynamic(
    source_table_name IN VARCHAR2,
    target_table_name IN VARCHAR2
) IS
BEGIN
    -- 动态创建表
    EXECUTE IMMEDIATE 'CREATE TABLE ' || target_table_name || ' AS SELECT e.*,CAST(NULL AS VARCHAR2(50)) as xh FROM ' || source_table_name || ' e WHERE 1=0';

    -- 动态插入数据
    EXECUTE IMMEDIATE 'INSERT INTO ' || target_table_name || ' SELECT t.OJ_IDNO AS OJ_IDNO,t.OJ_PIC AS OJ_PIC,CASE WHEN t.rn =1 THEN t.OJ_IDNO ELSE CONCAT(t.OJ_IDNO,CONCAT(''_'',t.rn)) END AS XH FROM (select OJ_IDNO,OJ_PIC,ROW_NUMBER() OVER(PARTITION BY OJ_IDNO ORDER BY NULL) as rn FROM ' || source_table_name || ') t';
    
    -- 提交事务
    COMMIT;
    
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('Table ' || target_table_name || ' created and data inserted from ' || source_table_name || ' successfully.');
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获错误并输出错误信息
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

调用通用模板

BEGIN
    create_and_populate_table_dynamic('BAK_OJDT_PERSON_PIC_C', 'BAK_OJDT_PERSON_PIC_C_COPY');
END;

第二步、拆分表处理,将大表拆分为多个小表

将JOBS_copy表进行拆分为15张表进行处理，每张表大概30万+条数据，依照下面sql进行一一拆分

BEGIN
	EXECUTE IMMEDIATE 'insert into BAK_OJDT_PERSON_PIC_COPY_PART1  SELECT XH,OJ_PIC FROM (SELECT XH,OJ_PIC,ROWNUM AS rnum from BAK_OJDT_PERSON_PIC_COPY) WHERE rnum <=300000';
	COMMIT;
END;

1、在拆分表过程中,可能会出现下面错误:

ORA-01691: Lob 段