create or replace package dyn_demo
as
type array is table of varchar2(200);
procedure do_query(p_enames in array,
p_operators in array,
p_values in array);
end;
/
create or replace package body dyn_demo
as
procedure do_query(p_enames in array,
p_operators in array,
p_values in array) is
type re is ref cursor;
l_query long;
l_sep varchar2(20) default 'where';
l_cursor re;
l_ename emp.ename%type;
l_empno emp.empno%type;
l_job emp.job%type;
begin
l_query:='select ename,empno,job from emp';
for i in 1..p_enames.count loop
l_query:=l_query||' '||l_sep||' '||p_enames(i)||
' '||p_operators(i)||' '||p_values(i);
l_sep:='and';
end loop;
open l_cursor for l_query;
loop
fetch l_cursor into l_ename,l_empno,l_job;
exit when l_cursor%notfound;
dbms_output.put_line(l_ename||','||l_empno||','||l_job);
end loop;
close l_cursor;
end;
end;
-------------------------------------------
SQL> exec dyn_demo.do_query(dyn_demo.array('ename','job'),dyn_demo.array('like','='),dyn_demo.array('%A%','CLERK'))
BEGIN dyn_demo.do_query(dyn_demo.array('ename','job'),dyn_demo.array('like','='),dyn_demo.array('%A%','CLERK')); END; *
第 1 行出现错误:
ORA-00911: 无效字符
ORA-06512: 在 "SCOTT.DYN_DEMO", line 20
ORA-06512: 在 line 1
那样写行吗?行的话为什么调用会报错
---------------------------------------------------------------
as
type array is table of varchar2(200);
procedure do_query(p_enames in array,
p_operators in array,
p_values in array);
end;
/
create or replace package body dyn_demo
as
procedure do_query(p_enames in array,
p_operators in array,
p_values in array) is
type re is ref cursor;
l_query long;
l_sep varchar2(20) default 'where';
l_cursor re;
l_ename emp.ename%type;
l_empno emp.empno%type;
l_job emp.job%type;
begin
l_query:='select ename,empno,job from emp';
for i in 1..p_enames.count loop
l_query:=l_query||' '||l_sep||' '||p_enames(i)||
' '||p_operators(i)||' '||p_values(i);
l_sep:='and';
end loop;
open l_cursor for l_query;
loop
fetch l_cursor into l_ename,l_empno,l_job;
exit when l_cursor%notfound;
dbms_output.put_line(l_ename||','||l_empno||','||l_job);
end loop;
close l_cursor;
end;
end;
-------------------------------------------
SQL> exec dyn_demo.do_query(dyn_demo.array('ename','job'),dyn_demo.array('like','='),dyn_demo.array('%A%','CLERK'))
BEGIN dyn_demo.do_query(dyn_demo.array('ename','job'),dyn_demo.array('like','='),dyn_demo.array('%A%','CLERK')); END; *
第 1 行出现错误:
ORA-00911: 无效字符
ORA-06512: 在 "SCOTT.DYN_DEMO", line 20
ORA-06512: 在 line 1
那样写行吗?行的话为什么调用会报错
---------------------------------------------------------------
begin
dyn_demo.do_query(dyn_demo.array('ename','job'),dyn_demo.array('like','='),dyn_demo.array('%A%','CLERK'));
end;
试试