它的工作原理如下:
create or replace procedure back_end_proc(p_rc OUT SYS_REFCURSOR) is
v_script VARCHAR2(4000); begin
v_script := 'select sysdate data from dual where ''a'' = :a union all select sysdate-1 data from dual where ''b'' = :b';
open p_rc for v_script using 'a','b'; end;调用该过程时:
DECLARE
rc SYS_REFCURSOR;
BEGIN
victom.back_end_proc2(:rc );
END;将有一个输出行:
DATA
27.06.2017 11:25:02
26.06.2017 11:25:02问题是:
如何将using 'a','b'参数修改为一个数组或一些集合数据,将'a','b'作为一个字符串包含,或者将smth作为一个单值包含?
发布于 2017-06-27 17:56:03
如果您的目标查询看起来像这样...
select * from whatever
where id in ( .... )传递一个集合是非常简单的。
create or replace type tt1 as table of varchar2(1);
/
create or replace procedure back_end_proc(
p_ids in tt1
, p_rc OUT SYS_REFCURSOR) is
begin
open p_rc for
'select * from whatever
where id member of :1'
using p_ids;
end;但是,您发布的案例稍微复杂一些,因为您希望在每个子查询中分配集合中的特定值。这意味着您需要使用VARRAY,因为它是唯一能保证排序的集合类型。所以:
create or replace type va1 as varray(10) of varchar2(1);
/
create or replace procedure back_end_proc(
p_ids in va1
, p_rc OUT SYS_REFCURSOR) is
begin
open p_rc for
'select sysdate from dual where ''a'' = :1
union all
select sysdate-1 from dual where ''b'' = :2'
using p_ids(1), p_ids(2);
end;https://stackoverflow.com/questions/44775659
复制相似问题