plsql 存储过程 批量提交_Oracle 批量插入sql优化一例

概念

大多数程序开发人员在进行大数据量任务的加工处理时，因考虑可能没有足够的undo空间，或者认为频繁地提交大量小事务会比处理和提交一个大事务更快，也更高效；因此总是力图找出一种过程性的方法，通过循环来完成这个任务。这是一种不好的存在误导性的开发习惯，最好的办法是按业务的要求以适当的频度提交，并且相应地设置undo大小。通常频繁的提交并不会更快，一般地在一个sql语句完成工作几乎总是更快一些，应该充分利用sql的批量处理能力。

案例分析

XXX银行数据分析系统的日常数据加载业务中，存在较多大数据量的加载过程，因加载性能低下，加载时长难以接受，希望能进行优化，提高报表的交付时效；他们的实现过程大都类似于下：(以下列举的程序都进行了关键字段替换，均在测试环境中模拟)：

CREATE OR REPLACE PROCEDURE P_PERM_TEST01 /********************* 过程功能: 加工全量储蓄主表 输入参数: 批处理日期I_TRANDATE 输出参数:无 ********************** /

(I_REGCOD VARCHAR2, --日期格式为YYYYMMDD I_TRANDATE VARCHAR2 --日期格式为YYYYMMDD ) IS --定义查询游标 cursor mycur is SELECT A.APPG_DATE, A.REGCOD, A.MEMB_CUST_AC, A.CUSTOMER_NO, A.BRANCH_NO, A.CURRENCY_IND, A.CURR_BAL FROM A_BOCS_INVM1 A WHERE A.REGCOD = I_REGCOD AND NOT EXISTS (SELECT 1 FROM A_BOCS_INVM2 B WHERE A.REGCOD = B.REGCOD AND A.MEMB_CUST_AC = B.MEMB_CUST_AC) UNION ALL SELECT A.APPG_DATE, A.REGCOD, A.MEMB_CUST_AC, A.CUSTOMER_NO, A.BRANCH_NO, A.CURRENCY_IND, A.CURR_BAL FROM A_BOCS_INVM2 A WHERE A.REGCOD = I_REGCOD AND A.APPG_DATE = I_TRANDATE; type ty_PERM_TEST03 is table of TAB_PERM_TEST01%rowtype; TAB_PERM_TEST01 ty_PERM_TEST03; v_batch_comt number := 300000; --每次提交数目BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_PERM_TEST01'; open mycur; loop FETCH mycur BULK COLLECT INTO TAB_PERM_TEST01 LIMIT V_BATCH_COMT; FORALL X IN INDICES OF TAB_PERM_TEST01 INSERT INTO TAB_PERM_TEST01 VALUES TAB_PERM_TEST01 (X); COMMIT; EXIT WHEN mycur%NOTFOUND; END LOOP; CLOSE mycur;EXCEPTION WHEN OTHERS THEN ROLLBACK; END;/

存储过程加工逻辑为简单的查询插入，但出现了在循环中提交的情况；程序中更新目标表时使用了游标迭代，采用在循环中批次插入的方式，这是一种典型的面向过程的编程方式，未能充分利用 sql 批量数据处理能力；并且当游标打开时间过长，一旦数据源被修改，很容易引发 ora-1555快照过旧错误，从而造成执行失败。

我们先检查游标中sql相关的对象的信息：

Elapsed: 00:00:00.03SQL

Tag标签: