select * from tb1 where id in ids;想实现上面的操作,即想传入一个ids实现类似下面的操作:
select * from tb1 where id in ('1','2','3')
请有解决过类似问题的朋友给予帮助
select * from tb1 where id in ('1','2','3')
请有解决过类似问题的朋友给予帮助
调试欢乐多
(
str in varchar2
)
AS
sqlstr varchar2(100);
BEGIN
sqlstr := 'select * from tb1 where id in ('||str||')';
execute immediate sqlstr;
END;execute testp('''1'',''x'',''3''')
execute testp('1,2,3')
type rc is ref cursor;
c_cu rc;
aa varchar(2000);
begin
aa:='select ''1'',''2'' from dual';
for rec in c_cu loop
dbms_output.put_line(rec);
end loop;
close c_cu;
end;
CREATE OR REPLACE PACKAGE course_pkg AS
TYPE t_RefCursor is ref cursor;
PROCEDURE testp(str in varchar2);
END course_pkg; 创建一个package bodyCREATE OR REPLACE PACKAGE BODY course_pkg AS
PROCEDURE testp(str in varchar2)
AS
v_Cur t_Refcursor;
v_SQLStatement varchar2(100);
v_Comm tb1%rowtype;
BEGIN
v_SQLStatement := 'select * from tb1 where id in ('||str||')';
open v_Cur for v_SQLStatement;
LOOP
FETCH v_Cur INTO v_Comm;
exit when v_Cur%notfound;
DBMS_OUTPUT.PUT_LINE(v_Comm.id||
' '||v_Comm.val);
END LOOP;
END testp;END course_pkg;
还有一种方法是定义type,cank
http://hi.baidu.com/jdsnhan/blog/item/7c21ebaf4ee400cd7cd92a36.html
但是放到存储过程中就提示如下错误:错误:PLS-00642: 在 SQL 语句中不允许使用本地收集类型
行:344
文本:NO IN (SELECT * FROM TABLE(cast(str2table(selectNos) as myArray )) t) and