select a.id,b.no from a,b where a.id=b.id and a.id='1' and b.no='2'
union all
select a.id,b.no from a,b where a.id=b.id and a.id='a' and b.no='b'
union all
select a.id,b.no from a,b where a.id=b.id and a.id='y' and b.no='z'
union all...
目的是写一个procedure,根据输入的参数个数(也就是a.id,b.no),给出查询结果.
不能用in,因为a.id和b.no是成对出现.请高手指点
union all
select a.id,b.no from a,b where a.id=b.id and a.id='a' and b.no='b'
union all
select a.id,b.no from a,b where a.id=b.id and a.id='y' and b.no='z'
union all...
目的是写一个procedure,根据输入的参数个数(也就是a.id,b.no),给出查询结果.
不能用in,因为a.id和b.no是成对出现.请高手指点
用循环分离参数再拼接sql语句,最后用execute immediate 执行
有那么麻烦吗?直接用in好,效率也不会太低用union all扫描表的次数多
procedure 中call一张表.
里面拼sql,然后execute immediate...
waterfirer的回答我会试试.
TYPE id_array IS TABLE OF varchar2(20);所以这样还麻烦了,不如在procedure里面直接查表用cursor
is
v_input varchar2(2000); := null;
begin
for i in (select id||','||no rec from calltable)
loop
v_input := v_input + ' '''||i.rec||' '',';
end loop;
v_input := substr(v_input,0,length(v_input)-1);
execute immediate 'select a.id,b.no from a,b where a.id=b.id and a.id,b.no in ('||v_input||')';
exception
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace);
end proc;
然后用以下语句可以实现
SELECT * FROM a,b WHERE a.id=b.id AND (A.ID ,B.NO) = (SELECT X,Y FROM TT)
1) for i in (select id||','||no rec from calltable)
for循环不允许那样依次处理表的记录。
2)查询的结果是一个table,procedure怎样返回?
execute immediate 'select a.id,b.no from a,b where a.id=b.id and a.id,b.no in ('||v_input||')';
TYPE FEE_POLICY_MAP IS TABLE OF FEE_POLICY%ROWTYPE INDEX BY BINARY_INTEGER;这样的
select a.id,b.no from a,b,tmp c where a.id=b.id and a.id=c.id and b.no=c.no。
楼主可以尝试此方案
(a.id,b.no) in ('y','z')
(a.id,b.no) in (('y','z'),('y','w'))