嵌套查询oracle缺点,存储过程小疑点,关于嵌套查询,

当前位置:我的异常网» Oracle开发 » 存储过程小疑点,关于嵌套查询,

存储过程小疑点,关于嵌套查询,

www.myexceptions.net  网友分享于：2013-03-09  浏览：35次

存储过程小问题,关于嵌套查询,急!!!!在线等...

create   or   replace   procedure   MIS_UpdateOutPrice(ibilldate   in   date,iGoodsid   in   varchar2)   is

aprices         Number;

begin

--1操作领料单,更改当前单据之后的单据中的货品平均价及总金额

declare

cursor   mycursor   is

Select   billcode,billdate,itemno,Row_id   from   MIS_RPT_OUTSTORELL_VIEW

where   goodsid= ' '||iGoodsid|| ' '   and   billdate> =ibilldate;

cursorrecord   mycursor%rowtype;

begin

open   mycursor;

if   mycursor%isopen   then

loop

fetch   mycursor   into   cursorrecord;

--统计平均价//下面的sql   语句有问题,提示:嵌套分组函数没有group   by

select   DECODE(sum(count),0,0,round(sum(total)/sum(count),4))   as   price   into   aprices

from   (select   sum(MIS_GetUnitRate(GOODSID,UNITID,initcount)+MIS_GetUnitRate(GOODSID,UNITID,INCOUNT)-MIS_GetUnitRate(GOODSID,UNITID,outcount))   as   count

,sum(inittotalmoney+INTOTALMONEY-outTOTALMONEY)   as   total

from   MIS_rpt_outin_VIEW   where   GOODSID=iGoodsid   and   (billdate

union

select   sum(MIS_GetUnitRate(GOODSID,UNITID,initcount)+MIS_GetUnitRate(GOODSID,UNITID,INCOUNT)-MIS_GetUnitRate(GOODSID,UNITID,outcount))   as   count

,sum(inittotalmoney+INTOTALMONEY-outTOTALMONEY)   as   total

from   MIS_rpt_outin_VIEW   where   GOODSID=iGoodsid

and   (billdate=cursorrecord.billdate

and   billcode < ' '||cursorrecord.billcode|| ' '));

--end

if   aprices <> 0   then

--更新明细表平均价

update   MIS_OUTSTORE_LLDETAIL   set   UNITPRICE=aprices   where   ITEMNO=cursorrecord.itemno;

文章评论