Question

存储过程中用什么可以替代游标

存储过程代码如下：

CREATE OR REPLACE PROCEDURE pd_P_visitPlan_answer (sKeyid varchar2,
sVpid varchar2,
sVpcmid varchar2,
sVpctid varchar2,
sVpidid varchar2,
sAnswerType varchar2,
sReturnMsg out varchar2) is

type Type_Cur is ref cursor;
VisitPlanCur Type_Cur;
sSQL varchar2(500);
nNum number(4) default 1;--自动增长序号
sSequenceNum varchar2(20) default 0;--序号
sCondition varchar2(1000) default '';--用于拼接sql语句条件

sCnid varchar2(20);
sContent varchar2(200);
sIsupstandard varchar2(1);
iShoworder number(4,2);

rownum number(4);--记录行数量，用于变量赋值前，判断是否存在数据
sDefaultAnswer varchar2(2) default '0';
begin
--查询指标基本信息
sSQL := 'select cnid, content, isupstandard, showorder from cm_k_checkanswer t where t.keyid = ' || sKeyid;

open VisitPlanCur for sSQL;
loop
fetch VisitPlanCur
into sCnid,sContent,sIsupstandard,iShoworder;
exit when VisitPlanCur%notfound;
--生成序号
select to_char(sysdate,'yyMMddhh24miss') || replace(lpad(seq_visitplan.nextval,5),' ','0') || replace(lpad(nNum,3),' ','0') into sSequenceNum from dual;
nNum := nNum + 1;

--根据指标答案类型进行判断，当为文本时默认为空
if sAnswerType = '4' then
sDefaultAnswer := '';
end if;

--数据写入cm_p_visitplan_answer
insert into cm_p_visitplan_answer(vpanid, vpidid, cnid, content, isupstandard, showorder, answer, vpctid, vpcmid, vpid)
values(sSequenceNum,sVpidid,sCnid,sContent,sIsupstandard,iShoworder,sDefaultAnswer,sVpctid,sVpcmid,sVpid);

end loop;
close VisitPlanCur;

exception
when others then
if sReturnMsg ='' or sReturnMsg is null then
rollback;
sReturnMsg := ' 存储过程： pd_P_visitPlan_answer 错误 ' || sqlerrm;
end if;
end;

Answer 1

可以用for循环的 for v_test in （select * from） loop

end loop；

Answer 2

组合sql能代替一部分游标

Answer 3

用CTE查询改一下呢

Answer 4

为什么要替代?