cursor 可以作为函数的参数吗?
例子:
cursor cur_data is
select a.sort_order,
v.segment,...
from bom a,cux v
where a.aid=v.vid
order by a.sort_order;cursor cur_ss is
select a.sort_order,
v.segment,...
from bom a,cux v
where a.aid=v.vid
and a.sort_order like (ss_order || '%')
order by a.sort_order;cursor cur_xt is
select a.sort_order,
v.segment,...
from bom a,cux v
where a.aid=v.vid
and a.sort_order like (xt_order || '%')
order by a.sort_order;
-----------------------以上为三个不同的cursor------------主程序
if ssname is null thenfor rec_data in cur_data loop
...
end loop;elsif ssname is not null and xtname is nullfor rec_data in cur_ss loop
...
end loop;
elsif xtname is not null and zyname is nullfor rec_data in cur_xt loop
...
end loop;
其实for中的内容都是一样的,只是满足不同条件我调用不同的cursor,能不能将
for rec_data in cur_xt loop
...
end loop;
这一段代码弄成一个函数或者过程,需要一个类似这样的函数:procedure cur_out(cur_temp in cursor)isbegin for rec_data in cur_temp loop
...
end loop;
------------------------------------------------------
每次调用:
cur_out(cur_data);
cur_out(cur_ss);
cur_out(cur_xt);但是这样的函数或者过程怎么构造,语法我查了很多网上资料,也没弄出来,头大啊,各位大虾帮忙!!
例子:
cursor cur_data is
select a.sort_order,
v.segment,...
from bom a,cux v
where a.aid=v.vid
order by a.sort_order;cursor cur_ss is
select a.sort_order,
v.segment,...
from bom a,cux v
where a.aid=v.vid
and a.sort_order like (ss_order || '%')
order by a.sort_order;cursor cur_xt is
select a.sort_order,
v.segment,...
from bom a,cux v
where a.aid=v.vid
and a.sort_order like (xt_order || '%')
order by a.sort_order;
-----------------------以上为三个不同的cursor------------主程序
if ssname is null thenfor rec_data in cur_data loop
...
end loop;elsif ssname is not null and xtname is nullfor rec_data in cur_ss loop
...
end loop;
elsif xtname is not null and zyname is nullfor rec_data in cur_xt loop
...
end loop;
其实for中的内容都是一样的,只是满足不同条件我调用不同的cursor,能不能将
for rec_data in cur_xt loop
...
end loop;
这一段代码弄成一个函数或者过程,需要一个类似这样的函数:procedure cur_out(cur_temp in cursor)isbegin for rec_data in cur_temp loop
...
end loop;
------------------------------------------------------
每次调用:
cur_out(cur_data);
cur_out(cur_ss);
cur_out(cur_xt);但是这样的函数或者过程怎么构造,语法我查了很多网上资料,也没弄出来,头大啊,各位大虾帮忙!!
rs sys_refcursor;
begin open rs for cur_str;
loop
fetch rs into 变量1,变量2...
exit when rs%notfound;
--todo
end loop;
close rs;
end;
调用时候我用cur_out(‘select a.sort_order,...from bom a,cux v where a.aid = v.vid order by a.sort_order;’);
语法有问题吗、?
我编译通过了,但是运行时候到调用时到loop那行就停止了,跳出循环了!
怎么调用啊?
动态执行的sql里不允许有分号,除非你动态执行的是PL-SQL
我已经查出了串‘’里多了一个分号,去掉就行了,用这种方法可以解决。
但是,这样很麻烦,因为每次都要open cursor,close。fetch into 还得定义好多变量,我就是想通过for rs in cur_str loop ,然后通过rs.sort_order来弄,就不用定义变量了,是不是游标名不能做存储过程的参数?
select a.sort_order,
v.segment,...
from bom a,cux v
where a.aid=v.vid and 1=2
order by a.sort_order;到时候直接声明行变量procedure cur_out(cur_str in varchar2)is
rs sys_refcursor;
tp v_type%rowtype;
begin open rs for cur_str;
loop
fetch rs into tp;
exit when rs%notfound;
--todo with tp.字段1 ,tp.字段2...
end loop;
close rs;
end;