对Oracle来说是个新手,需要有关此过程的帮助,使用Oracle 11g
示例表数据(TAB1(:
ID | 金额 | 货币 |
---|---|---|
10 | 300 | >td style="text align:right;">GBP
看起来您想要使用嵌套的FOR
循环。
我建议您使用游标FOR循环-它们更容易维护,因为您不必声明游标变量(顺便说一句,在您的情况下,它无论如何都不起作用,因为您希望将ID
和CURRENCY
存储到标量R_1
变量中(,打开游标,注意退出循环和关闭游标。在游标FOR循环中,Oracle为您完成所有这些操作。
这里有一个例子:
样品表:
SQL> select * from tab1;
ID AMOUNT CUR
---------- ---------- ---
10 300 GBP
15 500 GBP
20 100 GBP
程序:
SQL> create or replace procedure myproc as
2 begin
3 for cur_id in (select id from tab1) loop
4 dbms_output.put_line('ID = ' || cur_id.id);
5 for cur_other in (select amount, currency
6 from tab1
7 where id = cur_id.id --> use ID fetched in outer loop
8 )
9 loop
10 dbms_output.put_line(cur_other.amount ||' - '|| cur_other.currency);
11 end loop;
12 end loop;
13 end;
14 /
Procedure created.
测试:
SQL> set serveroutput on
SQL> exec myproc;
ID = 10
300 - GBP
ID = 15
500 - GBP
ID = 20
100 - GBP
PL/SQL procedure successfully completed.
SQL>
如何返回refcursor?
SQL> create or replace procedure myproc (par_id in tab1.id%type,
2 par_rc out sys_refcursor) as
3 begin
4 for cur_id in (select id
5 from tab1
6 where id = par_id
7 ) loop
8
9 open par_rc for select amount, currency
10 from tab1
11 where id = cur_id.id;
12 end loop;
13 end;
14 /
Procedure created.
SQL> var l_rc refcursor
SQL>
SQL> exec myproc(10, :l_rc);
PL/SQL procedure successfully completed.
SQL> print l_rc
AMOUNT CUR
---------- ---
300 GBP
SQL>