我写了一个程序,试图将值从源表插入到目标表,我使用批量收集来执行大量数据的数据。
create or replace PROCEDURE TEST2 (
p_array_size IN NUMBER
) IS
CURSOR cur1 IS SELECT DISTINCT
*
FROM
test;
CURSOR cur3 IS SELECT * FROM test;
CURSOR cur2( BND_TYPE d.bndng_typ%TYPE, BND_VAL d.bndng_val%TYPE, FINANCIAL_INST_ID d.financial_institution_id%TYPE, PRDCT_ID d.prdct_id%TYPE,
PRDCT_SUB_ID d.prdct_sub_id%TYPE, INSTRUMENT_ID d.instrmnt_id%TYPE) IS SELECT
d.*
FROM
test1 d,
(
SELECT
b.prdct_id,
FROM
test2 a,
test1 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 = BND_TYPE
AND d.bndng_val = BND_VAL
AND d.financial_institution_id = FINANCIAL_INST_ID
AND d.prdct_id = PRDCT_ID
AND d.prdct_sub_id = PRDCT_SUB_ID
AND d.instrmnt_id = INSTRUMENT_ID ;
TYPE loan_data_tbl IS TABLE OF cur1%rowtype INDEX BY PLS_INTEGER;
loan_data loan_data_tbl;
TYPE loanrate_tbl IS TABLE OF cur2%rowtype INDEX BY BINARY_INTEGER;
loan_rate loanrate_tbl;
BEGIN
DECLARE
v_noofDays NUMBER:=0;
currentDt DATE;
BEGIN
SELECT * INTO currentDt FROM dt;
BEGIN
IF cur1%Isopen Then
Close cur1;
End IF;
IF cur2%Isopen Then
Close cur2;
End IF;
OPEN cur1;
LOOP
FETCH cur1 BULK COLLECT INTO loan_data LIMIT p_array_size;
EXIT WHEN loan_data.COUNT = 0;
FOR i IN 1..loan_data.COUNT
LOOP
OPEN cur3;
OPEN cur2(loan_data(i).bndng_typ, loan_data(i).bndng_val,loan_data(i).financial_institution_id,
loan_data(i).prdct_id, loan_data(i).prdct_sub_id, loan_data(i).instrmnt_id);
loop
FETCH cur2 BULK COLLECT INTO loan_rate LIMIT p_array_size;
EXIT WHEN loan_rate.COUNT = 0;
FOR j IN 1..loan_rate.COUNT
LOOP
IF(cur3.POS_NUM = loan_data(i).POS_NUM AND cur3.POS_TYPE = loan_data(i).POS_TYPE
AND cur3.PRICE_COMPONENT_ID = loan_rate(j).PRICE_COMPONENT_ID
AND cur3.RPRTD_TILL_DT = loan_data(i).RPRTD_TILL_DT) THEN
update test SET SEQ_NUM=1,
WHERE SEQ_NUM=2;
ELSE
INSERT INTO test VALUES (
....
....
);
END IF;
COMMIT;
END LOOP;
END LOOP;
CLOSE cur2;
CLOSE cur1;
END LOOP;
END LOOP;
CLOSE cur1;
END;
END;
End ;
/
在上面的过程中,为了安全起见,我删除了一些列名。
我有两个错误一个是
pls-00201:必须声明标识符“d.bndng_typ”,pls-00225:子程序或游标“cur3”引用超出范围
如果有人能帮忙解决这个问题。
2条答案
按热度按时间0s7z1bwu1#
在cursor中,不能从cursor查询中提供引用别名的类型。您只需要提供表名
test1
而不是别名d
.zqdjd7g92#
这是未经测试的,因为我没有您的表和数据,但作为第一次重构,我将从以下类型的结构开始:
我也会考虑从
test
至test1
作为一个单独的游标,而不是显式地使用两个单独的游标,因为优化人员可能会找到一个更有效的方法,例如哈希连接。可能是
update
/insert
组合可以写成单个merge
. 一旦你有了merge
工作时,您可能会发现可以一次将其应用于整个表,而不需要任何光标循环。如果逐行更新的次数导致性能问题(比如说,如果超过几千次),那么批量收集到一个数组中可能很有用。如果是这样的话,您需要对其进行结构化,以便可以使用
forall
构造,而不是更多的循环。