下面一个存储过程,实现功能很简单。参数:订单号、客户编号
第一步根据订单号取订单明细
第二步根据判断订单明细中的商品标识,如果为合法标识,则直接根据订单明细提供的商品标识和客户编号查询该客户对应此商品的最后一次发货折扣,取出折扣更新到订单明细历史折扣字段。如果订单明细中商品标识不合法,则根据提供的条形码先匹配商品基本信息,取得商品标识,再根据取得的商品标识和客户编号查询该客户对应此商品的最后一次发货折扣,取出折扣更新到订单明细历史折扣字段。
问题,执行此存储过程太慢。如何才能提供执行效率。在后台执行红色标识的SQL,速度是有点慢,如何来提供此存储过程的执行效率?
create or replace procedure refreshShipDiscount(papercode in varchar2,clientid in varchar2) is
--
orderCusor SYS_REFCURSOR;
bookCusor SYS_REFCURSOR;
deliveryCusor SYS_REFCURSOR;
--
itemid varchar2(50);
itemcode varchar2(50);
discount number(10,4);
id number;
bookid number;
BEGIN
insert into tt(papercode,roomid) values('1111','22222222');
commit;
insert into tt(papercode,roomid) values(papercode,clientid);
commit;
open orderCusor for select t.id,t.provideritemid as itemid,t.itemcode from exchange_order t where t.papercode=papercode;
loop
begin
fetch orderCusor into id,itemid,itemcode;
insert into tt(papercode,roomid) values(papercode,id);
if isnumeric(itemid)=1 then
open deliveryCusor for select a.discount from fx_deliverydiscount a where a.clientid=clientid and a.bookid=itemid;
loop
begin
fetch deliveryCusor into discount;
exit;
end;
end loop;
close deliveryCusor;
else
open bookCusor for select t.bookid from fx_bookinfo t where t.partid=itemcode;
loop
begin
fetch bookCusor into bookid;
exit;
end;
end loop;
close bookCusor;
open deliveryCusor for select a.discount from fx_deliverydiscount a where a.clientid=clientid and a.bookid=bookid;
loop
begin
fetch deliveryCusor into discount;
exit;
end;
end loop;
close deliveryCusor;
end if;
if discount is null then
begin
discount:=65;
end;
end if;
update exchange_order t set t.historydiscount=discount where t.id=id;
exit when orderCusor%notfound;
end;
end loop;
commit;
close orderCusor;
END refreshShipDiscount;
第一步根据订单号取订单明细
第二步根据判断订单明细中的商品标识,如果为合法标识,则直接根据订单明细提供的商品标识和客户编号查询该客户对应此商品的最后一次发货折扣,取出折扣更新到订单明细历史折扣字段。如果订单明细中商品标识不合法,则根据提供的条形码先匹配商品基本信息,取得商品标识,再根据取得的商品标识和客户编号查询该客户对应此商品的最后一次发货折扣,取出折扣更新到订单明细历史折扣字段。
问题,执行此存储过程太慢。如何才能提供执行效率。在后台执行红色标识的SQL,速度是有点慢,如何来提供此存储过程的执行效率?
create or replace procedure refreshShipDiscount(papercode in varchar2,clientid in varchar2) is
--
orderCusor SYS_REFCURSOR;
bookCusor SYS_REFCURSOR;
deliveryCusor SYS_REFCURSOR;
--
itemid varchar2(50);
itemcode varchar2(50);
discount number(10,4);
id number;
bookid number;
BEGIN
insert into tt(papercode,roomid) values('1111','22222222');
commit;
insert into tt(papercode,roomid) values(papercode,clientid);
commit;
open orderCusor for select t.id,t.provideritemid as itemid,t.itemcode from exchange_order t where t.papercode=papercode;
loop
begin
fetch orderCusor into id,itemid,itemcode;
insert into tt(papercode,roomid) values(papercode,id);
if isnumeric(itemid)=1 then
open deliveryCusor for select a.discount from fx_deliverydiscount a where a.clientid=clientid and a.bookid=itemid;
loop
begin
fetch deliveryCusor into discount;
exit;
end;
end loop;
close deliveryCusor;
else
open bookCusor for select t.bookid from fx_bookinfo t where t.partid=itemcode;
loop
begin
fetch bookCusor into bookid;
exit;
end;
end loop;
close bookCusor;
open deliveryCusor for select a.discount from fx_deliverydiscount a where a.clientid=clientid and a.bookid=bookid;
loop
begin
fetch deliveryCusor into discount;
exit;
end;
end loop;
close deliveryCusor;
end if;
if discount is null then
begin
discount:=65;
end;
end if;
update exchange_order t set t.historydiscount=discount where t.id=id;
exit when orderCusor%notfound;
end;
end loop;
commit;
close orderCusor;
END refreshShipDiscount;
-- 你不能动不动就用游标啊................
-- 游标是来做特殊事情的,只有在SQL语句无法代替,或者很难代替的时候才想到用游标!
-- 还嵌套游标,能不慢吗?