关于游标没有获得参数的问题

[cainiao2029]

procedure addQuestionnaire(v_title in varchar2, dept_flag in varchar2, v_deptId in varchar2, v_validDate in varchar2,
v_description in varchar2,v_openflag in varchar2,v_publisher in varchar2)
is
CURSOR receiver IS select id from epare_employee t where t.deptid not in('调离退人员','合作单位','互换','退休人员');
CURSOR receiverOne(acceptDept VARCHAR2) IS select id from epare_employee t where t.deptid in (acceptDept);
v_id int;
v_userid int;
begin
dbms_output.put_line('输入的值为'||v_deptId);
select questionnaireseq.nextval into v_id from dual;
insert into epare_questionnaire(id, title, openflag, enddate, DESCRIPTION,publisher)
values(v_id, v_title, v_openflag, to_date(v_validDate, 'yyyy-MM-dd'), v_description,v_publisher);

if(v_openflag = '1') then
if(dept_flag='0')then
begin
open receiver;
LOOP
fetch receiver into v_userid;
exit when receiver%NOTFOUND;
insert into epare_questionnaire_user(USERID, TESTPAPERID, FLAG)values(v_userid, v_id, '0');
end loop;
close receiver;
end;
else
begin
open receiverOne(v_deptId);
LOOP
fetch receiverOne into v_userid;
exit when receiverOne%NOTFOUND;
insert into epare_questionnaire_user(USERID, TESTPAPERID, FLAG)values(v_userid, v_id, '0');
end loop;
close receiverOne;
end;
end if;
end if;

end;
运行时输入的参数 为v_openflag = '1'并且dept_flag='1'，根据调试跟踪，代码运行到了open receiverOne(v_deptId);其中v_deptId传入的值为:'一大队','二大队'，但是发现这个游标没有返回任何值，可是我单独执行游标对应的语句
select id from epare_employee t where t.deptid in ('一大队','二大队');时是有数据的，这个值如何才能传入游标啊？在网上查了动态SQL，没看明白~

[robin_ares]

--定义
l_cur sys_refcursor;
type tmp_record is record (
v_id epare_employee.id%type
)
;
l_tmp_record tmp_record;

--语句
sql_area:='select id from epare_employee t where t.deptid in ('||v_deptId||')'；

--打开游标 循环
open l_cur for sql_area;
loop
fetch l_cur into l_tmp_record;
exit when l_cur%notfound;
insert into epare_questionnaire_user(USERID, TESTPAPERID, FLAG)values(v_userid, l_tmp_record.v_id, '0');

end loop;

[cainiao2029]

Robin_Ares,谢谢你的帮助，经过google，我也查到了一个方法也能解决我的问题，我贴出来，希望对大家有所帮助


procedure addQuestionnaire(v_title in varchar2, dept_flag in varchar2, v_deptId in varchar2, v_validDate in varchar2,
v_description in varchar2,v_openflag in varchar2,v_publisher in varchar2)
is
CURSOR receiver IS select id from epare_employee t where t.deptid not in('天津分公司','调离退人员','机务','合作单位','地勤','互换','退休人员');
--CURSOR receiverOne(acceptDept VARCHAR2) IS select id from epare_employee t where t.deptid in (acceptDept);
type cur is ref cursor;
receiverOne cur;
v_id int;
v_userid int;
tempSql varchar2(2000) := '';
tempWhere varchar2(2000) := '';
begin
select questionnaireseq.nextval into v_id from dual;
insert into epare_questionnaire(id, title, openflag, enddate, DESCRIPTION,publisher)
values(v_id, v_title, v_openflag, to_date(v_validDate, 'yyyy-MM-dd'), v_description,v_publisher);

if(v_openflag = '1') then
if(dept_flag='0')then
begin
open receiver;
LOOP
fetch receiver into v_userid;
exit when receiver%NOTFOUND;
insert into epare_questionnaire_user(USERID, TESTPAPERID, FLAG)values(v_userid, v_id, '0');
end loop;
close receiver;
end;
else
begin
tempWhere := tempWhere || ' where t.deptid in ( '||v_deptId||') ';
tempSql := ' select id from epare_employee t ' || tempWhere ;
open receiverOne for tempSql;
LOOP
fetch receiverOne into v_userid;
exit when receiverOne%NOTFOUND;
insert into epare_questionnaire_user(USERID, TESTPAPERID, FLAG)values(v_userid, v_id, '0');
end loop;
close receiverOne;
end;
end if;
end if;

end;

这样的话，传入了诸如 '一大队',' 二大队'就可以解决问题了。

[zl3450341]

学习了