一个简短的存储过程

写了一个简短的循环插入数据的存储过程

CREATE OR REPLACE PROCEDURE MSGTORETIREDER

(IN_AAA027 IN VARCHAR2,IN_AAE002 IN NUMBER,IN_AAA079 in VARCHAR2) IS

pcxlh NUMBER(20);--批量序列号

n_aac001 NUMBER(20);--人员ID

n_num NUMBER(20);

Retmsg VARCHAR(100);

BEGIN

BEGIN

select t.aaa027 into Retmsg from DX.Sm_Key t where t.aaa027 = IN_AAA027;

Exception

when NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20010, '当前统筹区不支持短信发放');

RETURN ;

END;

n_num := 0;

select count(*) into n_num from SMSLOG t where t.aaa079 = IN_AAA079 and t.aaa027 = IN_AAA027 and t.aae002 = IN_AAE002;

IF n_num>0 THEN

RAISE_APPLICATION_ERROR(-20010,IN_AAE002||'月短信已经发送，不能重复发送！');

RETURN ;

ELSE

begin

insert into SMSLOG(smsid,aaa079,aaa027,aae002)

values(

DX.Sm_Send_Xh.NEXTVAL,IN_AAA079,IN_AAA027,IN_AAE002

);

--循环找出当月发放所有的人

for cr IN (SELECT acd8.*

FROM acd8

WHERE acd8.aae140 = '120'

and acd8.aaa079 = '1'

and acd8.bac046 > 0

and (acd8.bae041 is null or acd8.bae041 = '0')

and acd8.aaa027 = IN_AAA027

and acd8.aae002 = IN_AAE002

and acd8.aaa079 = IN_AAA079) LOOP

n_aac001:=cr.aac001;

insert into DX.SM_SEND(xh,lx, nr, sfhm, scsj, zt, fssj,dsfs,bz,tjfssj,czy,aab001,pcxlh,aac001,cfcs,errcodes,aaa027 )

select DX.Sm_Send_Xh.NEXTVAL,'2',x.nr,x.aac067,sysdate,'0','','','','','',x.aab001,DX.Sm_Send_Pcxlh.NEXTVAL,x.aac001,'0','',IN_AAA027

from (

SELECT acd8.baz070,  ac01.aac003 ,ac01.aac001,  ac01.aac002 ,

SUM(decode(ac82.bae033,'0',ac82.bac046,0)) zcff,

SUM(decode(ac82.bae033,'0',0,ac82.bac046)) bkff,

sum(ac82.bac046) ffje, acd8.aae010,acd8.aae002,ac63.aac067,acd8.aab001,

'您'||acd8.aae002||'月应发养老金'||sum(ac82.bac046)||'元，'||(

case

when SUM(decode(ac82.bae033, '0', 0,ac82.bac046))> 0 then

'其中补发金额'||SUM(decode(ac82.bae033, '0', 0,ac82.bac046))||'元，'

when SUM(decode(ac82.bae033, '0', 0,ac82.bac046)) < 0 then

'其中补扣金额'||abs(SUM(decode(ac82.bae033, '0', 0,ac82.bac046)))||'元，'

else ''

end

)||'预计3天内发放至尾号'||substr(acd8.aae010,-4)||'的银行卡,'||'请注意查收。详情请咨询12333或单位。' nr

FROM acd8, ac82, ac01, ab01,ac63

WHERE acd8.baz070 = ac82.baz070

and acd8.aab001 = ab01.aab001

AND acd8.aac001 = ac01.aac001

and acd8.aae140 = '120'

and acd8.aac001 = ac63.aac001

and acd8.aaa079 = IN_AAA079 and acd8.bac046 > 0

and (acd8.bae041 is null or acd8.bae041='0')

and acd8.aaa027 = IN_AAA027

and acd8.aae002 = IN_AAE002

and acd8.aac001 = n_aac001

group BY ac01.aac003, ac01.aac001, ac01.aac002, acd8.baz070, acd8.aae010,acd8.aae002,ac63.aac067,acd8.aab001

order by ac01.aac003

) x;

END LOOP;

end;

END IF;

END ;