我编写了一个过程，我正在尝试将值从源表插入到目标表，我使用批量收集来执行大量数据的数据。
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"引用超出范围
如果有人可以帮助解决这个问题。
在游标中，不能提供引用游标查询中的别名的类型。您只需提供表名test1而不是别名d。
CURSOR cur2( BND_TYPE test1.bndng_typ%TYPE,
BND_VAL test1.bndng_val%TYPE,
FINANCIAL_INST_ID test1.financial_institution_id%TYPE,
PRDCT_ID test1.prdct_id%TYPE,
PRDCT_SUB_ID test1.prdct_sub_id%TYPE,
INSTRUMENT_ID test1.instrmnt_id%TYPE)
IS SELECT
.....
这是未经测试的，因为我没有你的表和数据，但作为第一次重构，我会从这种类型的结构开始：
create or replace procedure test2
as
cursor loan_data_cur
( bnd_type cr_loan_prima_rate_orig.bndng_typ%type
, bnd_val cr_loan_prima_rate_orig.bndng_val%type
, financial_inst_id cr_loan_prima_rate_orig.financial_institution_id%type
, prdct_id cr_loan_prima_rate_orig.prdct_id%type
, prdct_sub_id cr_loan_prima_rate_orig.prdct_sub_id%type
, instrument_id cr_loan_prima_rate_orig.instrmnt_id%type )
is
select d.*
from test1 d
join ( select b.prdct_id
from test2 a
join test1 b
on b.ir_id = a.ir_id
and b.price_component_id = a.price_component_id
and b.financial_institution_id = a.financial_institution_id
group by b.prdct_id ) e
on d.prdct_id = e.prdct_id
where d.bndng_typ = loan_data_cur.bnd_type
and d.bndng_val = loan_data_cur.bnd_val
and d.financial_institution_id = loan_data_cur.financial_inst_id
and d.prdct_id = loan_data_cur.prdct_id
and d.prdct_sub_id = loan_data_cur.prdct_sub_id
and d.instrmnt_id = loan_data_cur.instrument_id;
begin
for loan_data in (
select distinct *
from test
)
loop
for loan_rate in loan_data_cur
( loan_data.bndng_typ
, loan_data.bndng_val
, loan_data.financial_institution_id
, loan_data.prdct_id
, loan_data.prdct_sub_id
, loan_data.instrmnt_id )
loop
update test t set t.seq_num = 1
where t.seq_num = 2
and t.pos_num = loan_data.pos_num
and t.pos_type = loan_data.pos_type
and t.price_component_id = loan_rate.price_component_id
and t.rprtd_till_dt = loan_data.rprtd_till_dt;
if sql%rowcount = 0 then
insert into test values (x, y, z);
end if;
end loop;
end loop;
commit;
end test2;
我还会考虑将从test到test1的连接作为单个游标，而不是显式使用两个单独的游标，因为优化器可能会找到更有效的方法，例如哈希连接。
可能update/insert组合可以写成一个merge.一旦merge工作，您可能会发现您可以一次将其应用于整个表，而根本不需要任何光标循环。
如果逐行更新的数量导致性能问题(例如，如果超过几千个(，则批量收集到数组中可能很有用。如果是这样，您可能希望对其进行结构化，以便可以使用forall构造而不是更多循环来应用更新/插入。