带参数的游标
游标和函数,存储过程一样可以有参数,并使用到查询中
参数在游标定义时使用,打开时传递参数值
1:定义带参数游标语法
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
定义参数的语法如下:
Parameter_name [IN] data_type[{:=|DEFAULT} value]
2:打开传参语法
OPEN cursor_name[value[,value]....];
或
for rowtype in cursor_name(parameter)
3:第一种写法
declare
cursor cur1(c_id number) is
select tid,tname from t1 where tid = c_id;
v_t1 t1%rowtype;
begin
for rec_id in 1..3 loop
open cur1(rec_id);
loop
fetch cur1 into v_t1;
exit when cur1%notfound;
dbms_output.put_line(v_t1.tid || ' -- ' ||v_t1.tname );
end loop;
close cur1;
end loop;
end;
4:第二种写法
declare
cursor cur1(c_id number) is
select tid,tname from t1 where tid = c_id;
v_t1 t1%rowtype;
begin
for rec_id in 1..3 loop
for v_t1 in cur1(rec_id) loop
dbms_output.put_line(v_t1.tid || ' -- ' ||v_t1.tname );
end loop;
end loop;
end;