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;
/