oracle中游标及变量的使用

create or replace procedure proc_SmsSendQueue as

      
       tmpMobilePhone varchar2(50);
       tmpSendcontent NVARCHAR2(100);
       tmpPhoneType   CHAR(2);
       tmpSmsType    VARCHAR2(16);
       tmpPriority    Number(1);
       tmpEnterTime Date;

       tmpSendFlag  Char(1);

       tmpSendCount         Number(1);
       tmpCreatedby         Number(8);
       tmpCreatedon         Date;
       tmpLastupdatedby     Number(8);
       tmpLastupdatedon     Date;

       tmpSendTasksn        number(16);      
       tmpSMSSENDSN         NUMBER(16);
       tmpsmssenddsn       number(16);
       s_processsn        number(16);
       s_flag             char(1);
       s_count            number(8);
     
      starttime           date;
      remark              varchar2(100);

cursor cur_SmsSendQueue is
         select sendtasksn,sendtaskdtlsn,mobilephone,Sendcontent,phonetype,'促销',tmpPriority,sysdate,'0',tmpSendCount,17,sysdate,17,sysdate from vcrm_sms_sendcustomer2 where sendtasksn=tmpSendTasksn and length(mobilephone)=11;
 begin
       starttime:=sysdate;
       select flag into s_flag from plus.TProcess where Procedure = 'proc_SmsSendQueue';
       select processsn into s_processsn from plus.TProcess where Procedure = 'proc_SmsSendQueue';
       if s_flag='0' then
            update plus.TProcess set flag='1',lastupdatedon=sysdate where processsn=s_processsn;
            commit;

           --Find how many records from crm_sms_sendtask where flag='2'
           select count(*) into s_count from crm_sms_sendtask where flag='2' and senddate<sysdate and senddate>=sysdate-7;
           if s_count>0 then

                     -- Search sendtasksn from crm_sms_sendtask
                     select sendtasksn into tmpSendTasksn from (select sendtasksn   from crm_sms_sendtask where flag='2' and senddate<sysdate order by senddate) where rownum<2;

                     --Find how many records from crm_sms_sendcustomer where sendtasksn=tmpsendtasksn
                     select count(*) into s_count from crm_sms_sendcustomer where sendtasksn=tmpSendTasksn;

                     if s_count>0 then

                           --Search Priority from crm_sms_TypePriority
                           select priority into tmpPriority from crm_sms_TypePriority where SmsType='促销';

                           --Search SendCount from crm_sms_TypePriority
                           select SendCount into tmpSendCount from crm_sms_TypePriority where SmsType='促销';

                               --open cursor for insert crm_sms_SendQueue
                                open  cur_SmsSendQueue;
                                       loop
                                           fetch cur_SmsSendQueue into tmpSMSSENDSN,tmpsmssenddsn,tmpMobilePhone,tmpSendcontent,tmpPhoneType,tmpSmsType,tmpPriority,tmpEnterTime,tmpSendFlag,tmpSendCount,tmpCreatedby,tmpCreatedon,tmpLastupdatedby,tmpLastupdatedon;
                                           exit when cur_SmsSendQueue%notfound;

                                           remark :='短信发送任务导入到短信发送队列表'||tmpSendTasksn;

                                                   begin
                                                    insert into crm_sms_SendQueue   (SmsSendSn,SmsSenddSn,MobilePhone,sendcontent,PhoneType,SmsType,priority,EnterTime,SendFlag,SendCount,Createdby,Createdon,Lastupdatedby,lastupdatedon)
                                                    values (tmpSMSSENDSN,tmpsmssenddsn,tmpMobilePhone,tmpSendcontent,tmpPhoneType,tmpSmsType,tmpPriority,tmpEnterTime,tmpSendFlag,tmpSendCount,tmpCreatedby,tmpCreatedon,tmpLastupdatedby,tmpLastupdatedon);
                                                   commit;

                                                    exception
                                                            when others then
                                                           update crm_sms_sendtask set flag='7',lastupdatedon=sysdate,lastupdatedby=17 where sendtasksn=tmpSendTasksn;
                                                           commit;

                                                    end;

                                       end loop;

                                close cur_smsSendQueue;
                               
                                update crm_sms_sendtask set flag='4',lastupdatedon=sysdate,lastupdatedby=17 where sendtasksn=tmpSendTasksn;
                                commit;
                           else
                          
                                update crm_sms_sendtask set flag='7',lastupdatedon=sysdate,lastupdatedby=17 where sendtasksn=tmpSendTasksn;
                                commit;
                               remark:=remark||'短信发送任务中没有明细';
                           end if;
             else
                   remark:=remark||',短信发送任务中没有通过的任务';
             end if;

      else
           remark:=remark||',进程初始化失败';
     end if;

        insert into plus.tprocess_log (ProcessSN,Startupby,Startupon,Stopedby,Stopedon,Remark,Lastupdatedby,Lastupdatedon,Flag)
                          values (s_processsn,
                                  17,
                                  starttime,
                                  17,
                                  sysdate,
                                  remark,
                                  17,
                                  sysdate,
                                  s_flag
                                 );
                      commit;

       update plus.TProcess set flag='0',lastupdatedon=sysdate where processsn=s_processsn;
       commit;

 end;

转载于:https://www.cnblogs.com/jameshappy/archive/2012/09/06/2673436.html