select * from table where id in ( select regexp_substr(str,'[^,]+',1,level) from dual connect by level <= length(str)-length(replace(str,','))+1);
-- 给个例子给你:
eygle@SZTYORA> select empno, ename from emp;
EMPNO ENAME ---------- -------------------- 7499 ALLEN 7521 WARD 7654 MARTIN 7844 TURNER 7900 JAMES 7369 SMITH 7876 ADAMS
已选择7行。
eygle@SZTYORA> create or replace procedure emp_proc(v_empnos varchar2, o_cur out sys_refcursor) 2 is 3 begin 4 open o_cur for 'select t.ename from emp t where t.empno in ('||v_empnos||')'; 5 end; 6 /
过程已创建。
eygle@SZTYORA> eygle@SZTYORA> set serveroutput on; eygle@SZTYORA> var c_cur refcursor; eygle@SZTYORA> exec emp_proc('7369,7876',:c_cur);
select * from table where id in (
select regexp_substr(str,'[^,]+',1,level)
from dual
connect by level <= length(str)-length(replace(str,','))+1);
eygle@SZTYORA> select empno, ename from emp;
EMPNO ENAME
---------- --------------------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7900 JAMES
7369 SMITH
7876 ADAMS
已选择7行。
eygle@SZTYORA> create or replace procedure emp_proc(v_empnos varchar2, o_cur out sys_refcursor)
2 is
3 begin
4 open o_cur for 'select t.ename from emp t where t.empno in ('||v_empnos||')';
5 end;
6 /
过程已创建。
eygle@SZTYORA>
eygle@SZTYORA> set serveroutput on;
eygle@SZTYORA> var c_cur refcursor;
eygle@SZTYORA> exec emp_proc('7369,7876',:c_cur);
PL/SQL 过程已成功完成。
eygle@SZTYORA> print c_cur;
ENAME
--------------------
SMITH
ADAMS