比如我定义一个游标CURSOR cur(p_str VARCHAR2) IS select * from table a
where a.id in (p_str);
我需要传入参数 '1','2','3'但是编译器会把它当成3个参数,而不是一个如何解决?
请教
where a.id in (p_str);
我需要传入参数 '1','2','3'但是编译器会把它当成3个参数,而不是一个如何解决?
请教
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
create or replace procedure cursor_test(p_in in varchar2) is
type t_cursor is ref cursor;
v_str varchar(100);
v_cur t_cursor;
r_cur dept%rowtype;
begin
v_str := 'select * from dept where deptno in ('||p_in||')';
open v_cur for v_str;
loop
Fetch v_cur into r_cur;
exit when v_cur%notfound;
dbms_output.put_line('the result is: '||r_cur.deptno||' '||r_cur.dname);
end loop;
close v_cur;
end cursor_test;
--创建function
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;然后用
CURSOR cur(p_str VARCHAR2) IS select * from table a
where a.id in (split(p_str,',');p_str用'''1'',''2'',''3'''传入
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter); WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i); IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str; IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP; RETURN str_split;
END fn_split;
条件修改为 IN (SELECT . FROM TEM_A).
剩下要做的事情就是在客户端把那些东西存储到临时表中而已。
其中string的枚举值不能超过1000,否则出错,
建议采用楼上临时表的方案