oracle游标杂耍(二)
ger);
end t_goods_cv;
create package body t_goods_cv
as
begin
procedure my_prc(v_ref1 in out ref_cur_strong,v_ref2 in out ref_cur_weak v_id in integer)
is
begin
if v_id=1 then
open v_ref1 for select * from test_user.t_goods;
fetch v_ref1 into row_foreach
loop
dbms_output.put_line(row_foreach.name||row_foreach.address);
exit when v_ref1%notfound;
end loop;
close v_ref1;
else v_id<0 then
open v_ref2 for select * from test_user.t_test1;
fetch v_ref2 into row_foreach2
while row_foreach2.name is not null
loop
......;
end loop;
end if;
end;
end t_goods_cv;
---使用 BULK COLLECT子句，从游标变量中提取多行，放入一个或多个集合中。
DECLARE
TYPE c_cur is ref cursor return t_goods%rowtype
type t_name is table of t_goods.name%type;
type t_code is table of t_goods.code%type;
v_ref_cur c_cur;
v_name t_name;
v_code t_code;
begin
open v_ref_cur for select code,name from test_user.t_goods;
fetch v_ref_cur bulk collect into v_code,v_name;
...........
close v_ref_cur;
end;
---主变量或游标变量 格式是：变量或游标变量前加：这是在pl/sql中用法
即 :v_i---代表主变量
:ref_cur----代表主游标变量
-----游标表达式
一个游标表达式返回一个嵌套游标，结果集中的每行都包含值加上子查询生成的游标。
游标表达式的查询，是作为游标声明的一部分，可以在动态SQL查询中使用游标表达式。语法：
CURSOR (subquery);
案例
create or replace procedure my_test(id in integer)
as
type refcursor is ref cursor;
cursor c1 is
select a.code,
cursor(select b.name,
cursor(select c.name from tc c where c.id=b.ref_id)as cname
from tb b where b.ref_ta_id=a.id)as bname
from ta a where a.id=id;
v_1 refcursor;
v_2 refcursor;
v_3 refcursor;
v_1name ta.code%type;
v_2name tb.name%type;
v_3name tc.name%type;
begin
open c1;
loop
fetch c1 into v_1name,v_1;
....;
exit when c1%notfound;
loop
fetch v_1 into v_2name,v2;
...;
exit when v_1%notfound;
loop
fetch v2 into v_3name;
...;
exit when v2%notfound;
....;
end loop;
end loop;
end loop;
close c1;
end;
end my_test;