正文
oracle 存储过程 where in参数传入问题
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
问题：
举个简单例子说明
create or replace procedure procStr(inString in varchar2）
as
begin
select * from book where id in (inString);
end procStr;
我在inString中输入‘21,22’，21,22，‘‘21’，‘22’’
都不行，请教各位大侠如何通过参数，给存储过程的in语句进行赋值
其中inString中id的个数是不定的，多少都有可能
解决办法：
1.
create or replace procedure procStr(inString in varchar2）
as
begin
execute immediate 'select * from book where id in ('||inString||')';
end procStr;
2.
-- 给个例子给你：
CREATE
OR
REPLACE
PACKAGE pkg_emp
AS
TYPE myrctype
IS
REF
CURSOR
;
PROCEDURE
Get_empinfo(v_empno
IN
VARCHAR2, p_rc
OUT
myrctype);
END
pkg_emp;
/
CREATE
OR
REPLACE
PACKAGE BODY pkg_emp
AS
PROCEDURE
Get_empinfo(v_empno
IN
VARCHAR2, p_rc
OUT
myrctype)
IS
sqlstr VARCHAR2(4000);
BEGIN
sqlstr :=
'SELECT * FROM emp WHERE empno IN ('
||v_empno||
')'
;
OPEN
p_rc
FOR
sqlstr;
END
Get_empinfo;
END
pkg_emp;
/
SQL> var v_rc refcursor;
SQL>
exec
pkg_emp.Get_empinfo(
'7902,7788'
,:v_rc);
SQL> print v_rc;
3.create or replace procedure procStr(inString in varchar2）
as
sys_refcursor resultcur ;
begin
open resultcur for 'select * from book where id in (' || inString || ')';
end procStr;
as
sys_refcursor resultcur ;
begin
open resultcur for 'select * from book where id in (' || inString || ')';
end procStr;
