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(deptID VARCHAR2) IS select id from epare_employee t where t.deptid in (deptID);
v_id int;
v_userid int;
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
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);此时在数据表epare_employee中共有记录2200条,我传入的v_deptId='一中队','二中队',这两个中队一共有300人,但是执行后的结果还是2200条,是不是我的CURSOR哪里写的有问题啊?
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(deptID VARCHAR2) IS select id from epare_employee t where t.deptid in (deptID);
v_id int;
v_userid int;
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
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);此时在数据表epare_employee中共有记录2200条,我传入的v_deptId='一中队','二中队',这两个中队一共有300人,但是执行后的结果还是2200条,是不是我的CURSOR哪里写的有问题啊?
select id from epare_employee t where t.deptid in (deptID);名字呀
改成V_deptID什么的
select id from epare_employee t where t.deptid = t.deptID;
就是这个原因,这个问题好多网友都提出过的,参数的名称不要和字段名一样,一样会有问题的,对于你这里t.deptid in (deptID) 一定是返回true的,这里的deptid被当作字段了,所以这个返回肯定是true也就是 无论你传入的值是多少都是选所有结果集的,所以这里出现了2200条记录,就是这个原因这里还有一个问题就是in (deptid) 这样的把deptid变量表示成'value1','value2' 然后想达到in ('value1','value2')也是做不到的的。只能通过动态sql来做了。
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.id, '0'); end loop;