oracle游标存储过程循环插数据,oracle 存储过程 含游标和循环

create or replace procedure getSystemNotice(pHumanID in INTEGER)

is

iCount INTEGER ;

fromTable sys_refcursor;

msgcur sys_refcursor;

var_MAXID INTEGER ;

var_EVENTSQL CLOB ;

var_EVENTID INTEGER ;

var_HASGREENCHANNEL INTEGER;

var_EXPOFLAG INTEGER;

var_CIVIFLAG INTEGER;

var_READFLAG INTEGER;

var_SQL varchar2(10000);

var_CONTENT VARCHAR2(500) ;

var_DISPLAYTYPE INTEGER ;

var_ISREAD INTEGER ;

Begin

/*待办案卷*/

Select count(*) into iCount from dlsys.tchumanfolderlist where TaskItemID=2 and humanID=pHumanID;

If iCount>0 then

/*获取开启待办案卷箱子的查询语句*/

Select HUMANLISTSQL into var_EVENTSQL from dlsys.tchumanfolderlist where TaskItemID=2 and humanID=pHumanID;

/*获取案卷eventID*/

var_SQL := 'Select eventID from ('||var_EVENTSQL||')';

open fromTable for var_SQL;

loop

fetch fromTable into var_EVENTID;

exit when fromTable%notfound;

if var_EVENTID is not null then

/*获取案卷相关信息*/

Select eventDESC,HASGREENCHANNEL,EXPOFLAG,CIVIFLAG,READFLAG into

var_CONTENT ,

var_HASGREENCHANNEL,

var_EXPOFLAG,

var_CIVIFLAG,

var_READFLAG

from umdata.toEvent where eventID=var_EVENTID;

/*根据案卷信息设定网站数据*/

var_DISPLAYTYPE:=0;

If var_HASGREENCHANNEL=1 then

var_DISPLAYTYPE:=3;

end if;

if var_EXPOFLAG=1 then

var_DISPLAYTYPE:=2;

end if;

if var_CIVIFLAG=1 then

var_DISPLAYTYPE:=1;

End if;

If var_READFLAG=1 then

var_ISREAD:=1;

else

var_ISREAD:=0;

End if;

/*判定是新增还是变更*/

Select nvl(count(*),0) into iCount from umdata.tcinstantmsg where BIZID=var_EVENTID;

If iCount=0 and var_READFLAG =0 then

Select MAX(ID) into var_MAXID from umdata.tcinstantmsg;

Insert into umdata.tcinstantmsg (ID,BIZID,SUBSYSTEMID,MSGTYPEID,MSGTYPENAME,MSGTITLE,CONTENT,HUMANID,DISPLAYTYPE,ISREAD)

values (var_MAXID+1,var_EVENTID,10000,1101,'待办案卷','待办案卷 ',var_CONTENT,pHumanID,var_DISPLAYTYPE,var_ISREAD);

else

if iCount<>0 then

Update umdata.tcinstantmsg set ISREAD=var_ISREAD where ISREAD<>var_ISREAD

and HumanID=pHumanID

and BIZID=var_eventID;

end if;

End if;

commit;

end if;

End loop;

End if;

close fromTable;

/*我的消息，每次刷新前先删除网站中我的消息信息*/

Delete from umdata.tcinstantmsg where MSGTYPEID=1105 and HumanID=pHumanID;

commit;

/*获取我的消息相关信息*/

Select MAX(ID) into var_MAXID from umdata.tcinstantmsg;

open msgcur for

Select MESSAGETEXT,receiveflag from dlmis.trlogmessage

where receiveHumanID=pHumanID

and (receiveflag<>1 or receiveflag is null )

and (deleteflag<>1 or deleteflag is null) and sysdate-senddate<1 and sysdate-senddate>=0;

loop

fetch msgcur into var_CONTENT ,var_READFLAG;

exit when msgcur%notfound;

/*插入我的消息*/

Insert into umdata.tcinstantmsg (ID,SUBSYSTEMID,MSGTYPEID,MSGTYPENAME,MSGTITLE,CONTENT,HUMANID)

values (var_MAXID+1,10000,1105,'我的消息','我的消息',var_CONTENT,pHumanID);

commit;

end loop;

close msgcur;

Exception

When others then

Rollback;

End getSystemNotice;

/