Oracle SQL中如何将Blob字段内容提取插入至目标表

原表结构 #

create table file_ingestion_table 
(File_id    NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 14486912 CACHE 1000 NOORDER  NOCYCLE  NOKEEP  NOSCALE ,
FileName    varchar2(1000),
FileType    varchar2(16),
FileContent Blob,
tag varchar2(64),
Who varchar2(100),
Status varchar2(64))
;

处理CSV格式Blob数据 #

通过将Blob转成字符型数据，拆分行与字段后插入目标表，示例代码如下（以插入employee表为例）：

DECLARE
    v_blob BLOB;
    v_clob CLOB;
    v_line VARCHAR2(32767);
    v_index NUMBER := 1;
BEGIN
    -- 获取指定File_id的Blob数据
    SELECT FileContent INTO v_blob FROM file_ingestion_table WHERE File_id = 14486912;
    
    -- 将Blob转换为Clob
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
    DBMS_LOB.CONVERTTOCLOB(v_clob, v_blob, DBMS_LOB.LOBMAXSIZE, 1, 32767, NLS_CHARSET_ID('AL32UTF8'));
    
    -- 逐行解析CSV，跳过表头后插入目标表
    v_line := REGEXP_SUBSTR(v_clob, '[^'||CHR(10)||']+', 1, v_index);
    WHILE v_line IS NOT NULL LOOP
        IF v_index > 1 THEN
            INSERT INTO employee (Name, Department, Salary)
            VALUES (
                REGEXP_SUBSTR(v_line, '[^,]+', 1, 1),
                REGEXP_SUBSTR(v_line, '[^,]+', 1, 2),
                TO_NUMBER(REGEXP_SUBSTR(v_line, '[^,]+', 1, 3))
            );
        END IF;
        v_index := v_index + 1;
        v_line := REGEXP_SUBSTR(v_clob, '[^'||CHR(10)||']+', 1, v_index);
    END LOOP;
    
    COMMIT;
    DBMS_LOB.FREETEMPORARY(v_clob);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_LOB.FREETEMPORARY(v_clob);
        RAISE;
END;
/

处理JSON格式Blob数据 #

利用Oracle的JSON_TABLE函数直接解析JSON数组，插入目标表，示例代码如下：

DECLARE
    v_blob BLOB;
    v_clob CLOB;
BEGIN
    -- 获取指定File_id的Blob数据
    SELECT FileContent INTO v_blob FROM file_ingestion_table WHERE File_id = 14486912;
    
    -- 将Blob转换为Clob
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
    DBMS_LOB.CONVERTTOCLOB(v_clob, v_blob, DBMS_LOB.LOBMAXSIZE, 1, 32767, NLS_CHARSET_ID('AL32UTF8'));
    
    -- 解析JSON数组并批量插入目标表
    INSERT INTO employee (Name, Department, Salary)
    SELECT j.Name, j.Department, j.Salary
    FROM JSON_TABLE(
        v_clob,
        '$[*]' COLUMNS (
            Name VARCHAR2(100) PATH '$.Name',
            Department VARCHAR2(100) PATH '$.Department',
            Salary NUMBER PATH '$.Salary'
        )
    ) j;
    
    COMMIT;
    DBMS_LOB.FREETEMPORARY(v_clob);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_LOB.FREETEMPORARY(v_clob);
        RAISE;
END;
/

通用优化点 #

• 批量插入：大数据量场景下，改用FORALL批量插入减少IO开销
• 格式校验：增加对CSV分隔符、JSON格式合法性的校验逻辑
• 状态标记：处理完成后更新file_ingestion_table的Status字段，标记为已处理

内容的提问来源于stack exchange，提问作者Surbhi Jain