create or replace procedure p_record(
resultItem out sys_refcursor
)
3 as
4 begin
5 open resultItem for select empname from emp where eid in('01','02','03');
6 end;
7 /想个这个in 动态的传个值 in('01','02','03') 变成 in(v_id)
这种,好像 单引号要转换,望各位大侠帮帮小弟
resultItem out sys_refcursor
)
3 as
4 begin
5 open resultItem for select empname from emp where eid in('01','02','03');
6 end;
7 /想个这个in 动态的传个值 in('01','02','03') 变成 in(v_id)
这种,好像 单引号要转换,望各位大侠帮帮小弟
--动态SQL去拼接
--v_id参数
--你调试下,打印sql_str出来看下是否正确,dbms_output.put_line(sql_str)create or replace procedure p_record(
v_id varchar2,
resultItem out sys_refcursor
)
as
sql_str varchar2(4000);
begin
sql_str :='select empname from emp where eid in('||v_id||')' ;
open resultItem for sql_str;
end;
v_id 输入'''01'''||','||'''02'''||','||'''03'''
with a as
(
select '0102030405' id from dual
)
select ''''||substr(id,2*(rownum - 1)+1, 2)||'''' newid
from a
connect by rownum <=round(length(id)/2)NEWID
------
'01'
'02'
'03'
'04'
'05'
--动态SQL去拼接
--v_id参数
--你调试下,打印sql_str出来看下是否正确,dbms_output.put_line(sql_str)
create or replace procedure p_record(v_id varchar2, resultItem out sys_refcursor )
as
sql_str varchar2(4000);
begin
sql_str :='select empname from emp where eid in('||v_id||')' ;
dbms_output.put_line(sql_str) ;
open resultItem for sql_str;
end;
--测试:
DECLARE
TYPE refcur IS REF CURSOR;
rs refcur;
v_id VARCHAR2(100):='''01'''||','||'''02'''||','||'''03'''; --v_id这样输入
BEGIN
p_record(v_id,rs);
END;--过程中SQL打印结果:
select empname from emp where eid in('01','02','03')
eid varchar2(10),
empname varchar2(20)
);INSERT INTO emp(eid,empname) values('01','蒋介石');
INSERT INTO emp(eid,empname) values('02','毛泽东');
INSERT INTO emp(eid,empname) values('03','林彪');
INSERT INTO emp(eid,empname) values('04','蒋经国');
INSERT INTO emp(eid,empname) values('05','张三');
CREATE OR REPLACE PROCEDURE p_record(v_eid in VARCHAR2, resultItem out sys_refcursor)
AS
v_sql VARCHAR2(2000);
v_eid2 VARCHAR2(1000);
BEGIN
v_eid2 := ''''||replace(v_eid,',',''',''')||'''';
dbms_output.put_line(v_eid2);
v_sql := 'SELECT empname FROM emp WHERE eid in('||v_eid2||')';
dbms_output.put_line(v_sql);
OPEN resultItem FOR v_sql;
exception when others then
dbms_output.put_line(v_eid2||chr(10)||v_sql);
END;
/set serveroutput on;
var c_cur refcursor;
exec p_record('01,02',:c_cur);
print c_cur;
eid varchar2(10),
empname varchar2(20)
);INSERT INTO emp(eid,empname) values('01','蒋介石');
INSERT INTO emp(eid,empname) values('02','毛泽东');
INSERT INTO emp(eid,empname) values('03','林彪');
INSERT INTO emp(eid,empname) values('04','蒋经国');
INSERT INTO emp(eid,empname) values('05','张三');
CREATE OR REPLACE PROCEDURE p_record(v_eid in VARCHAR2, resultItem out sys_refcursor)
AS
BEGIN
OPEN resultItem FOR 'SELECT empname FROM emp WHERE eid in('''||replace(v_eid,',',''',''')||''')';
END;
/set serveroutput on;
var c_cur refcursor;
exec p_record('01,02',:c_cur);
print c_cur;
2 /'''01'',''02''
--------------
'01','02','03'