在存储过程中
procedure test_m(pClassType VarChar,pDs Out rcDataset)
as
vClass varchar(20);
begin
select '''早班'',''中班''' into vClass from dual;
Open pDs For select count(*) aaa from SurfaceQuality_961 where ClassType In (vClass);
end;
返回是0
但是 直接查询Open pDs For select count(*) aaa from SurfaceQuality_961 where ClassType In ('早班','中班');结果是840,请教是什么原因?传参数的结果跟用变量的一样.
谢谢!
procedure test_m(pClassType VarChar,pDs Out rcDataset)
as
vClass varchar(20);
begin
select '''早班'',''中班''' into vClass from dual;
Open pDs For select count(*) aaa from SurfaceQuality_961 where ClassType In (vClass);
end;
返回是0
但是 直接查询Open pDs For select count(*) aaa from SurfaceQuality_961 where ClassType In ('早班','中班');结果是840,请教是什么原因?传参数的结果跟用变量的一样.
谢谢!
Open pDs For select count(*) aaa from SurfaceQuality_961 where instr(ClassType,''''||vClass||'''')>0;
...
所以你做的替换时没有意义的。
好像可以不建临时表, execute immediate () using () into (); into 就是最后的结果去的地方了,可以给到一个变量里。——但是结果集如果不是一条,菜鸟我就不知道怎么处理了。
create or replace package pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end pkg_test;create or replace package body pkg_test
as
--输入ID 返回记录集的函数
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql using p_id;
end if;
return rc;
end get;
end pkg_test;
调用方式
pkg_test.get(yournum)