Oracle存储过程并发读取Blob字段并生成文件

最新推荐文章于 2025-03-19 16:57:08 发布

foxship305

最新推荐文章于 2025-03-19 16:57:08 发布

阅读量1.6k

点赞数 1

分类专栏： Oracle 文章标签： oracle 并发 blob

本文链接：https://blog.youkuaiyun.com/foxship305/article/details/122065367

版权

Oracle 专栏收录该内容

3 篇文章

订阅专栏

这几天上线的项目涉及从数据库中读Blob字段，落成文件，但是发现写文件太慢了，几百个文件要落好几个小时。很心疼运维兄弟加班初始化历史数据，自己就在网上搜索了下，对于读取Blob字段写入文件本身的代码，并未发现太有用的帮助文档。但是之前看过一篇并发执行存过的帖子，借此改造了下，实现并发读取Blob字段，并生成文件。写到博客中，以备以后用到，同时为程序员朋友们共享下思路（我也简单写了段 java代码测试，java的多线程也能实现相关功能）。将代码抽象后示例如下：

先建两个表，一个存储Blob字段，一个用作日志和监控并发使用

-- 存储blob字段的表
create table T_STORE_BLOG
(
  sid       VARCHAR2(32) not null,
  blogdata  BLOB,
  create_tm DATE,
  file_name VARCHAR2(200)
);
-- 日志和控制并发的表
create table T_DOWNBLOG_LOG
(
  batchid   VARCHAR2(32) not null,
  c_result  VARCHAR2(20),
  create_tm DATE,
  end_tm    DATE,
  n_count   NUMBER,
  err_msg   VARCHAR2(200)
);

存过中涉及到了directory 和创建job，因此,执行存过的用户要有directory的读、写、执行权限和创建job的权限，对于程序中注释掉的“dbms_lock.sleep”，如果希望使用，则还需要有执行dbms_lock的权限。相关脚本参考如下：

--DBA创建directory
create or replace directory DOWN_BLOB_DIR as 'G:\test';
--授权给 username
grant all on directory DOWN_BLOB_DIR to username;
--查看下是否授权
select * from user_tab_privs t
where t.table_name = 'DOWN_BLOB_DIR' and t.grantee = 'username'; 

--DBA赋予user执行  create job 的权限
grant create job to caojyster;
--DBA赋予user执行 dbms_lock 的权限
grant execute on dbms_lock to username;

主存过如下，每个并发处理的文件个数是通过修改变量 V_STEPLEN 的值控制的，可以修改此值调整并发数量,也可以修改为入参。我这里写死了，每个并发执行50个文件，如果数据库里有500个文件需要处理，主控程序会自动发起10个并发。

CREATE OR REPLACE PROCEDURE P_BATCHWRITEBLOB_MAIN(
    IDATE IN VARCHAR2, --数据日期：YYYYMMDD格式
    R_MSG OUT VARCHAR2  --返回信息
)
IS
    /*
    多线程下载 BLOB文件主控程序
    */
    V_STEPLEN  NUMBER := 50 ;  --每个并发执行的记录数,可以修改此值调整并发数量,也可以修改为入参
    V_RECCOUNT NUMBER :=0;  --满足条件的记录条数
    V_BATCHNUM NUMBER :=0;  --并发次数
    V_ORDER    NUMBER :=0;  --并发的序号
    V_BATCHSEQ VARCHAR2(30);--主控标识串
    V_ERR_MSG VARCHAR2(200);
    
    V_START_TM DATE;  --执行开始时间
    V_END_TM DATE;    --执行结束时间或到了超时的时间
    
    V_SUCC_COUNT NUMBER:=0; --成功执行完的子过程数
    V_MONI_TIME NUMBER:=0;  --持续时间,避免超时
    
BEGIN
    SELECT SYSDATE INTO V_START_TM
    FROM DUAL; 

    --查询记录条数
    SELECT COUNT(*) INTO V_RECCOUNT
    FROM T_STORE_BLOG T
    WHERE T.CREATE_TM = TO_DATE(IDATE,'YYYYMMDD');
    
    --生成一个标识串
    V_BATCHSEQ := IDATE||'-'||DBMS_RANDOM.STRING('U',10);
    DBMS_OUTPUT.put_line('V_BATCHSEQ is:'||V_BATCHSEQ);
    
    --清空此标识对应的日志
    DELETE FROM T_DOWNBLOG_LOG T
    WHERE T.BATCHID LIKE V_BATCHSEQ||'%';
    COMMIT;

    --判断需要多少并发数,如果为0则不会进入后续的循环处理
    V_BATCHNUM := CEIL(V_RECCOUNT/V_STEPLEN);
    DBMS_OUTPUT.put_line('V_BATCHNUM is:'||V_BATCHNUM);

    --循环调用子存过发起并发
    FOR V_ORDER IN 1..V_BATCHNUM LOOP
        dbms_scheduler.create_job(job_name   => dbms_scheduler.generate_job_name,
                            job_type   => 'PLSQL_BLOCK',
                            job_action => 'begin P_BATCHWRITEBLOB_SON('''||V_BATCHSEQ||''','||V_ORDER||','||V_STEPLEN||','''||IDATE||'''); end;',
                            start_date => sysdate,
                            enabled    => true,
                            comments   => 'temporary job,will be delete soon after complete!',
                            auto_drop  => true
                            );
        --DBMS_OUTPUT.put_line('action is:'||'begin P_BATCHWRITEBLOB_SON('''||V_BATCHSEQ||''','||V_ORDER||','||V_STEPLEN||','''||IDATE||'''); end;');
    END LOOP;
    
    --监控并发执行状态，超过下面设定的时间不完成，则主控程序判定超时退出，但是注意，子存过其实还在执行，所以要关注下，不一定要重跑
    WHILE( V_SUCC_COUNT < V_BATCHNUM AND V_MONI_TIME < 10 ) LOOP
        --成功的个数
        SELECT COUNT(*) INTO V_SUCC_COUNT
        FROM T_DOWNBLOG_LOG T
        WHERE T.BATCHID LIKE V_BATCHSEQ||'%'
          AND T.C_RESULT = '0';
        --持续时间
        SELECT (SYSDATE - V_START_TM)*100  --如果不设定超时时间就取消对于超时时间的限制
        INTO V_MONI_TIME
        FROM DUAL; 

        --dbms_lock.sleep(10); --sleep 10秒，需要DBA赋予本用户执行 dbms_lock 的权限
        
    END LOOP;
    
    IF V_MONI_TIME >= 10 THEN  --这个时间变量值的判断和上面的要保持一致
        SELECT SYSDATE INTO V_END_TM
        FROM DUAL; 
    
        INSERT INTO T_DOWNBLOG_LOG( BATCHID,  C_RESULT, CREATE_TM, END_TM, ERR_MSG)
        VALUES(V_BATCHSEQ, '9',V_START_TM, V_END_TM, '超时强制退出,但是子存过依旧执行中,需要关注子存过执行状态');
        COMMIT;
        RETURN; --退出
    END IF;

    SELECT SYSDATE INTO V_END_TM
    FROM DUAL; 
    INSERT INTO T_DOWNBLOG_LOG( BATCHID,  C_RESULT, CREATE_TM, END_TM, N_COUNT)VALUES(V_BATCHSEQ, '0',V_START_TM, V_END_TM, V_RECCOUNT);
    COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    V_ERR_MSG := SUBSTR(SQLERRM,1,200);
    SELECT SYSDATE INTO V_END_TM
    FROM DUAL;
    INSERT INTO T_DOWNBLOG_LOG( BATCHID,  C_RESULT, CREATE_TM, END_TM, ERR_MSG)VALUES(V_BATCHSEQ, '9',V_START_TM, V_END_TM, V_ERR_MSG);
    COMMIT;
END;

子存过如下：

CREATE OR REPLACE PROCEDURE P_BATCHWRITEBLOB_SON(
    V_BATCHSEQ IN VARCHAR2, --并发标识
    V_ORDER    IN NUMBER,   --并发序号
    V_STEPLEN  IN NUMBER,   --每个批次处理的文件数量
    IDATE IN VARCHAR2      --数据日期：YYYYMMDD格式
                                     
)
IS
    /*
    多线程下载 BLOB文件子程序，实现文件下载
    */
    V_ROWNUM_BEGIN  NUMBER :=0;  --开始的rownum
    V_ROWNUM_END NUMBER :=0;     --结束的rownum
    V_BATCH_SONID  VARCHAR2(30); --子流程标识号
    V_START_TM DATE;
    V_END_TM DATE;
    
    V_LOCALDIR VARCHAR2(150);  --DIRECTORY 名称，间接映射本地路径,需要DBA创建，赋权限给当前用户
    V_FILENAME VARCHAR2(200);  --文件名
    V_UFILE UTL_FILE.FILE_TYPE;  --文件标识
    V_FBLOB  BLOB;  --BLOB 变量，承载游标中的每一个blob
    V_FILE_LEN NUMBER; --文件长度
    V_POS NUMBER;  --每次的起读位移
    V_AMOUNT BINARY_INTEGER := 32767;
    V_BUFFER RAW(32767); --缓冲区
    V_REC_COUNT NUMBER := 0; --处理的记录条数
    V_ERR_MSG VARCHAR2(200);
    
BEGIN
    SELECT SYSDATE INTO V_START_TM
    FROM DUAL; 
    
    V_LOCALDIR := 'DOWN_BLOB_DIR';    --DIRECTORY 名称，间接映射本地路径,需要DBA创建，赋权限给当前用户

    V_BATCH_SONID := V_BATCHSEQ||'-'||V_ORDER; --子流程标识号

    V_ROWNUM_BEGIN := (V_ORDER - 1 )*V_STEPLEN + 1;  --开始记录的rownum
    V_ROWNUM_END := V_ORDER * V_STEPLEN;  --结束记录的rownum
    
    --清空此标识对应的日志
    DELETE FROM T_DOWNBLOG_LOG T
    WHERE T.BATCHID = V_BATCH_SONID 
        AND T.C_RESULT = '0';
    COMMIT;
    
    --循环处理文件
    FOR REC IN (
        SELECT * FROM (
            SELECT ROWNUM AS ROWN, T.*
            FROM T_STORE_BLOG T
            WHERE T.CREATE_TM = TO_DATE(IDATE,'YYYY-MM-DD')
            ORDER BY T.SID ASC)
        WHERE ROWN >= V_ROWNUM_BEGIN AND ROWN <=V_ROWNUM_END
    )
    LOOP
        V_POS:= 1; --每次循环从第一位开始
        V_REC_COUNT := V_REC_COUNT +1;
        V_FILENAME := REC.FILE_NAME; --文件名
        V_FBLOB := REC.BLOGDATA;  --BLOG 字段
        V_FILE_LEN := DBMS_LOB.getlength(V_FBLOB);  --长度
        
        --创建文件
        V_UFILE := UTL_FILE.fopen(V_LOCALDIR, V_FILENAME,'WB',32767);
        --写文件
        WHILE V_POS < V_FILE_LEN LOOP
            DBMS_LOB.READ(V_FBLOB, V_AMOUNT, V_POS, V_BUFFER );
            UTL_FILE.PUT_RAW(V_UFILE, V_BUFFER, TRUE);
            V_POS := V_POS + V_AMOUNT;
        END LOOP;
        
        UTL_FILE.FCLOSE(V_UFILE); --关闭文件
    END LOOP;
    
    SELECT SYSDATE INTO V_END_TM
    FROM DUAL;
    INSERT INTO T_DOWNBLOG_LOG( BATCHID,  C_RESULT, CREATE_TM, END_TM, N_COUNT)VALUES(V_BATCH_SONID, '0',V_START_TM, V_END_TM, V_REC_COUNT);
    COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    V_ERR_MSG := SUBSTR(SQLERRM,1,198);
    SELECT SYSDATE INTO V_END_TM
    FROM DUAL;
    IF UTL_FILE.is_open(V_UFILE) THEN
        UTL_FILE.FCLOSE(V_UFILE); --关闭文件
    END IF;
    
    INSERT INTO T_DOWNBLOG_LOG( BATCHID,  C_RESULT, CREATE_TM, END_TM,  ERR_MSG)VALUES(V_BATCH_SONID, '9', V_START_TM, V_END_TM, V_ERR_MSG);
    COMMIT;
END;

并发执行存储过程参考了如下的文档，非常感谢：

(26条消息) Oracle11gR2 并行执行存储过程_SeanData的博客-优快云博客_oracle并行执行存储过程