Oracle—过程中的两个循环

Question

我需要一些帮助来编写Oracle PL/SQL过程，该过程应执行以下操作：

1. 在用输入参数B-block或D-activate(这已经完成(更新一个表中的字段后，从触发器调用该过程

2. 该过程应该首先打开一个光标，该光标将捕捉客户端的帐号，并打开一个循环，该循环将逐个处理账户

3. 这一个帐户应该转发到另一个循环，该循环将捕获该帐户的客户端的卡号(第二个光标(，当进入该循环时，卡号应该用作被调用来阻止/解锁该卡的存储过程的输入参数-该存储过程已经存在，我只需要将其称为

4. 该过程不需要返回任何参数，其想法只是用已经为编写的存储过程来阻止/激活客户端的卡号

我应该为此写一个包还是只写一个过程？我怎样才能把一个循环写进另一个循环呢？

Answer 1

我刚刚意识到，我可以在过程中不使用游标来完成这项工作。例如：

create or replace procedure blokiraj_proc (core_cust_id varchar2,  kyc_blocked varchar2) as
type NumberArray is Array(100) of test_racuni.foracid%type;
type StringArray is Array (1000) of test_kartice.card_num%type;
accnt NumberArray;
card_number StringArray;
begin  
select foracid bulk collect into accnt from test_racuni where cif_id = core_cust_id;
for i in accnt.first..accnt.last 
loop
select card_num  bulk collect into card_number from test_kartice where rbs_acct_num = accnt(i);
dbms_output.enable (100000);
dbms_output.put_line (accnt(i));
for j in 1..card_number.count 
loop
dbms_output.put_line (card_number(j));
blokiraj_karticu (card_number(j));
end loop;
end loop;
end;

这是比卷发器更好的方法吗？为什么当我触发该过程时，dbms_output不打印任何内容？

Answer 2

正如@EdStevens所指出的，您无法避免处理游标。但您可以避免游标在游标内的循环结构。以及内部的隐式打开和关闭光标。查询组合成一个简单的JOIN，然后大容量收集到一个集合中
为此，我创建了一个包含账号和卡号的记录；然后是该记录的集合。然后将光标大容量收集到集合中。您的初始代码允许处理多达100000张卡，虽然我喜欢批量收集(在需要时(，但我不喜欢填充内存，因此我限制了每次提取的批量收集收集行数。不幸的是，这引入了循环中的循环结构，但代价远不如游标中的游标结构大。结果如下。

create or replace procedure blokiraj_proc (core_cust_id varchar2) as
type acct_card_r 
is record(
acct_num test_kartice.rbs_acct_num%type
, card_num test_kartice.card_num%type
);
type acct_card_array is table of acct_card_r; 
acct_card_list acct_card_array; 
k_acct_card_buffer_limit constant integer := 997;
cursor c_acct_card(c_cust_id varchar2) is
select r.foracid 
, k.card_num
from test_racuni r
left join test_kartice k 
on (k.rbs_acct_num = r.foracid)
where r.cif_id = c_cust_id
order by r.foracid 
, k.card_num;
begin 
dbms_output.enable (buffer_size => null); -- enable dbms_output with size unlimited
open c_acct_card(core_cust_id); 
loop 
fetch c_acct_card
bulk collect 
into acct_card_list
limit k_acct_card_buffer_limit;
for i in 1 .. acct_card_list.count
loop
dbms_output.put (acct_card_list(i).acct_num || ' ==> ');
if acct_card_list(i).card_num is not null 
then
dbms_output.put_line (acct_card_list(i).card_num);
blokiraj_karticu (acct_card_list(i).card_num);
else
dbms_output.put_line ('No card for this account');
end if; 
end loop; 
-- exit buffer fetch when current buffeer is not full. As that means all rows 
-- from cursor have been fetched/processed.
exit when acct_card_list.count < k_acct_card_buffer_limit;   
end loop;
close c_acct_card;
end blokiraj_proc;

这只是另一种方法。如果对你更好，那就太好了。我还想重复并扩展Ed Stevens关于从触发器运行此操作的警告。如果这里的任何一个表是触发器触发的表，那么仍然会得到一个正在变化的表异常——你不能把它隐藏在过程后面。即使没有，触发器也有很多循环。