我编写了一个过程，其中数据从源表同时更新/插入到目标表。对于较少的记录，该过程工作正常，但当我尝试执行更多记录时，执行该操作需要更多的时间。
在逻辑保持不变的情况下，我们能用大容量collect转换merge子句吗？我找不到任何有用的资源。
我已附上合并程序。
create or replace PROCEDURE TEST1 (
p_array_size IN NUMBER
) IS
CURSOR dtls IS SELECT DISTINCT
account_num
FROM
table1
WHERE
rprtd_till_dt = (
SELECT
dt - 1
FROM
dates
WHERE
id = 'odc'
);
TYPE data_tbl IS TABLE OF dtls%rowtype;
data data_tbl;
BEGIN
DECLARE
v_noofDays NUMBER:=0;
currentDt DATE;
BEGIN
SELECT dt INTO currentDt FROM dates WHERE id = 'odc';
BEGIN
OPEN dtls;
LOOP
FETCH dtls BULK COLLECT INTO data LIMIT p_array_size;
EXIT WHEN data.COUNT = 0;
FOR i IN 1..data.COUNT
LOOP
IF(TRUNC(data(i).creation_dt,'MM') = TRUNC(currentDt,'MM')) THEN
v_noofDays := currentDt - 1 - data(i).creation_dt;
ELSE
v_noofDays := currentDt - TRUNC(currentDt,'MM');
END IF;
MERGE INTO table1 updtbl USING ( SELECT
d.*
FROM
table2 d,
(
SELECT
b.prdct_id,
FROM
table3 a,
table2 b
WHERE
a.ir_id = b.ir_id
AND a.price_component_id = b.price_component_id
AND a.financial_institution_id = b.financial_institution_id
GROUP BY
b.prdct_id,
) e
WHERE
d.prdct_id = e.prdct_id
AND d.bndng_typ = data(i).bndng_typ
AND d.bndng_val = data(i).bndng_val
AND d.financial_institution_id = data(i).financial_institution_id
AND d.prdct_id = data(i).prdct_id
AND d.prdct_sub_id = data(i).prdct_sub_id
AND d.instrmnt_id = data(i).instrmnt_id
)
inp ON (
updtbl.POS_NUM = data(i).POS_NUM
AND updtbl.POS_TYPE = data(i).POS_TYPE
AND updtbl.PRICE_COMPONENT_ID = inp.PRICE_COMPONENT_ID
AND updtbl.RPRTD_TILL_DT = data(i).RPRTD_TILL_DT
)
WHEN NOT MATCHED THEN
INSERT VALUES (
data(i).loan_account_num,
inp.ir_id,
inp.price_component_id,
)
WHEN MATCHED THEN
update SET SEQ_NUM=1,
NET_INTRST_AMT=round(data(i).curr_loan_bal*inp.price_component_value*v_noofDays/36000,2),
DM_BTID=200
WHERE SEQ_NUM=2;
COMMIT;
END LOOP;
END LOOP;
CLOSE dtls;
END;
END;
END TEST1;
/如果有人能帮我指导语法，对如何实现上述程序使用批量收集会很有帮助。
2条答案
按热度按时间w41d8nur1#
我希望这能给你一些想法。避免复制和粘贴，检查语法。
w9apscun2#
我知道有点晚了，但是如果你还没有解决它的话，请使用下面的方法