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

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

Mysql存储过程优化——使用临时表代替游标。

Mysql游标在操作小数据量时比较方便，效率可观，但操作大数据量，速度比较慢，甚至直接产生系统错误。

 一般说来，当操作的数据超过1万条时，就避免用游标吧。

 为了测试游标性能，写了下面一个游标对IDC_Gather_Info表中数据进行遍历

下面是当表中数据分别为15万、5万、1万时游标的表现：

 

1.数据量15万，存储过程执行失败，提示错误：Incorrect key file for table '/tmp/#sql_3044_0.MYI';try to repair it

2.数据量5万，执行成功，耗时31.051s

3.数据量1万，执行成功，耗时1.371s

 

下面使用临时表替换游标：

1.数据量15万，执行成功，耗时8.928s

2.数据量5万，执行成功，耗时2.994s

3.数据量1万，执行成功，耗时0.634s

 

可以看到Mysql的游标在处理大一点的数据量时还是比较乏力的，仅适合用于操作几百上千的小数据量。

Answer 2

在sql server 里可以用 临时表,表变量等 .
可以将复合条件的记录都插入到临时表,然后再用 update 等语句对临时表进行计算.
最后将临时表的数据插入到正式表.

其实你这个需求 ,是可以不用触发器的.
多写几个 insert ,update 就是了.

Answer 3

可以用集合 批量绑定来 替换游标的

Answer 4

没有吧，除非你自己开发一种新存储过程来替代游标的功能

Answer 5

没有吧，除非你自己开发一种新存储过程来替代游标的功能