Oracle存储过程案例

注意几点：

• 时间比较：TO_CHAR(EnableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd')
• 定义表：%rowtype  ，例子：   dormodel  DISABLEOPERATIONRECORD%rowtype;--记录表  
• 第一表字段：%Type  ，例子： s_ipid   INSPECTIONPOINT.ID%Type;--巡查表ID
• 计算天数：TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR('2022-04-19', 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss'))

案例1：

create or replace procedure IP_TASK is
  --声明变量
  cursor c_list is
   select id ipid
   from INSPECTIONPOINT i
    where (EnableTime is not null 
     or DisableTime  is not null )
     and deleted=0
     and (TO_CHAR(EnableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd')
      or TO_CHAR(DisableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd') );
   
  s_ipid   INSPECTIONPOINT.ID%Type;--巡查表ID
  s_msg       VARCHAR(200);--消息
  ipmodel  INSPECTIONPOINT%rowtype;--巡查表
  v_setcount  number(10);--需要执行行数
  dormodel  DISABLEOPERATIONRECORD%rowtype;--记录表
  v_dorcount  number(10);--记录需要执行行数
  s_disableheaven  number(10);--停用天数
      
begin
      --查询巡查定时未执行数据
      --打开游标
      open c_list;
    
      --遍历游标
      Loop
        --赋值给变量
        FETCH c_list
          INTO s_ipid;
        --退出循环条件 (通过%NOTFOUND判断是否有值，有值执行操作，没有则退出循环)
        exit when c_list%NOTFOUND;
        
      select count(1) into v_setcount from INSPECTIONPOINT where DELETED=0 and ID=s_ipid   ;
      
      if v_setcount > 0 then
      --查询今天需要执行的数据
      --看这条数据，启用时间和停用时间是执行那个
        select * into ipmodel from INSPECTIONPOINT where DELETED=0 and ID=s_ipid   ;
      --停用时间不需要计算,1：添加操作记录，2修改巡查停用时间-清空
           if  TO_CHAR(ipmodel.DisableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd') then 
             
           --添加操作记录
             insert into DISABLEOPERATIONRECORD
             (CREATEDUSER,CREATETIME, LINKID, TQTYPE, MODELTYPE, DISABLEHEAVEN, ISTIMING)
             values
             (ipmodel.LastOperateUser,
              SYSDATE,
              ipmodel.id,
              1,
              1,
              0,
              1);
           
           --清空时间
              update INSPECTIONPOINT set DisableTime='',Status=2 where id=ipmodel.id;
            
            end if;
      --启用时间就需要，计算停用天数,1：添加操作记录，2修改巡查启用时间-清空
         if  TO_CHAR(ipmodel.EnableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd') then 
             
         -- 计算天数
         --先看操作记录是否有停用记录 
         
         select count(1) into v_dorcount  from DISABLEOPERATIONRECORD where deleted=0 and  LinkId=ipmodel.id and TqType=1 ;
               
             --没有记录，就拿更新时间，更新时间没有，就拿创建时间
             if  v_dorcount<=0 then   
                   --更新时间
                   if  ipmodel.UpdatedTime is not null then       
                     select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR(ipmodel.UpdatedTime, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')) into s_disableheaven from dual;
                   end if;
                   --创建时间
                   if  ipmodel.UpdatedTime is null then      
                     select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR(ipmodel.CreateTime, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')) into s_disableheaven from dual;
                   end if;
            end if;
            --存在记录
            if  v_dorcount>0 then      
                  select * into dormodel from  ( select * from DISABLEOPERATIONRECORD where deleted=0 and  LinkId=ipmodel.id and TqType=1  order by createtime desc ) where  rownum = 1;
                  
                  select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR(dormodel.CreateTime, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')) into s_disableheaven from dual;              
            end if;
            
           --添加操作记录
             insert into DISABLEOPERATIONRECORD
             (CREATEDUSER,CREATETIME, LINKID, TQTYPE, MODELTYPE, DISABLEHEAVEN, ISTIMING)
             values
             (ipmodel.LastOperateUser,
              SYSDATE,
              ipmodel.id,
              0,
              1,
              s_disableheaven,
              1);
           
           --清空时间
              update INSPECTIONPOINT set EnableTime='',Status=1 where id=ipmodel.id;
            
            end if;
      
 
        end if;
        
      end loop;
    
      --关闭游标
      close c_list;
      COMMIT;
      s_msg := '定时执行日常巡查停启任务';
      DBMS_OUTPUT.PUT_LINE(s_msg || '成功' ||
                           to_char(CURRENT_DATE, 'yyyy-mm-dd'));

  --异常回滚
exception
  when others then
    rollback;
    dbms_output.put_line(s_msg || '失败' ||
                         to_char(CURRENT_DATE, 'yyyy-mm-dd'));
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
end IP_TASK;

案例2：

create or replace procedure EP_Task(e_tasktype in NUMBER) is
  --声明变量
  cursor c_list is
    select e.id EquipId, p.id projectid
  from EQUIP e
  join PROJECT p
    on e.projectid = p.id
 where e.status = 2
   and e.DELETED = 0 ;

  s_equipid   EQUIP.ID%Type;--设备ID
  s_projectid PROJECT.Id%Type;--项目ID
  s_emid      EQUIPMAINTENANCEDETAIL.Emid%Type;--新增维保主表ID
  s_escid     EQUIPSELFCHECKDETAIL.ESID%Type;--新增自检主表ID
  s_msg       VARCHAR(200);--消息
  v_tcount number(10);--维保次数
  v_sentcount number(10);--已发送维保次数
  v_escount   number(10);--自检行数
  v_sentescount  number(10);--已发送自检行数
  v_setcount  number(10);--频率行数
  emmodel  EQUIPFREQUENCYSET%rowtype;--频率表
  s_frequency number(10) ;--执行次数
  s_bl number(10) ;--循环
  
begin

  case e_tasktype
    when 1 then
      --创建维保任务
      --打开游标
      open c_list;
    
      --遍历游标
      Loop
        --赋值给变量
        FETCH c_list
          INTO s_equipid, s_projectid;
        --退出循环条件 (通过%NOTFOUND判断是否有值，有值执行操作，没有则退出循环)
        exit when c_list%NOTFOUND;
        
      select count(1) into v_setcount from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=2 and rownum = 1;
      
      if v_setcount>0 then
          select * into emmodel from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=2 and rownum = 1;
      end if;
    
      --频率任务设置存在
      if v_setcount > 0 and emmodel.endtime>=SYSDATE and emmodel.begintime<=SYSDATE  then
             --查看次数和已发送的次数比较  
             select count(1) into v_sentcount from EQUIPMAINTENANCE where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid and CREATETIME>=emmodel.begintime and CREATETIME<=emmodel.endtime;
              if v_sentcount<emmodel.frequency then
               s_frequency:=emmodel.frequency - v_sentcount;
              end if;
      end if;
      
      if v_setcount <= 0 or emmodel.endtime<SYSDATE or emmodel.begintime>SYSDATE then
       --频率任务没有设置---频率任务设置过期了或者不在范围内-默认1月1次
           select count(1) into v_tcount from EQUIPMAINTENANCE where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid and CREATETIME>=trunc(sysdate, 'mm') ;
           if v_tcount<=0 then
           s_frequency:=1;
            end if;
       end if;
      
        s_bl:=0;
       
        while s_bl < s_frequency loop  s_bl:=s_bl+1;
          
         --新增主表
        insert into EQUIPMAINTENANCE
          (CREATEDUSER,CREATETIME, EQUIPID, PROJECTID, BEGINTIME, ENDTIME, STATUS)
        values
          (1,
          SYSDATE,
           s_equipid,
           s_projectid,
           (trunc(sysdate, 'mm')+1/24),
           (trunc(LAST_DAY(SYSDATE))+23/24),
           1);
        select "SEQ_EQUIPMAINTENANCE".currval into s_emid from dual;
         
      for ep_row in ( select ep.id EPId  from EquipPoint ep  join POINTBASIS pb on ep.PointId=pb.id where ep.equipid=s_equipid and ep.Status=0 and ep.DELETED=0 and  pb.PointNo != 20 and pb.PointNo != 21 )loop 
         
         --新增详情
        insert into EQUIPMAINTENANCEDETAIL
          (CREATEDUSER,CREATETIME, EMID, EPID)
        values
          (1,
          to_timestamp(TO_CHAR(sys_extract_utc(systimestamp),'YYYY-MM-DD') || ' 00:00:00','yyyy-mm-dd hh24:mi:ss.ff'), 
           s_emid,
           ep_row.epid
           );
         
         end loop;
      
    
        end loop;
      end loop;
    
      --关闭游标
      close c_list;
      COMMIT;
      s_msg := '定时创建维保任务';
      DBMS_OUTPUT.PUT_LINE(s_msg || '成功' ||
                           to_char(CURRENT_DATE, 'yyyy-mm-dd'));
    when 2 then
      --创建自检任务
      --打开游标
      open c_list;
    
      --遍历游标
      Loop
        --赋值给变量
        FETCH c_list
          INTO s_equipid, s_projectid;
        --退出循环条件 (通过%NOTFOUND判断是否有值，有值执行操作，没有则退出循环)
        exit when c_list%NOTFOUND;
      
          select count(1) into v_setcount from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=1 and rownum = 1;
      
      if v_setcount>0 then
          select * into emmodel from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=1 and rownum = 1;
      end if;
    
      --频率任务设置存在
      if v_setcount > 0 and emmodel.endtime>=SYSDATE and emmodel.begintime<=SYSDATE  then
             --查看次数和已发送的次数比较  
             select count(1) into v_sentescount from EQUIPSELFCHECK where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid
              and CREATETIME>=emmodel.begintime and CREATETIME<=emmodel.endtime and TYPE=1;
              if v_sentescount<emmodel.frequency then
               s_frequency:=emmodel.frequency - v_sentescount;
              end if;
      end if;
      
      if v_setcount <= 0 or emmodel.endtime<SYSDATE or emmodel.begintime>SYSDATE then
       --频率任务没有设置---频率任务设置过期了或者不在范围内-默认1月1次
           select count(1) into v_escount from EQUIPSELFCHECK where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid and CREATETIME>=trunc(sysdate, 'mm')and TYPE=1 ;
           if v_tcount<=0 then
           s_frequency:=1;
            end if;
       end if;
      
        s_bl:=0;
        
        while s_bl < s_frequency loop  s_bl:=s_bl+1;
                  --新增主表
        insert into EQUIPSELFCHECK
          (CREATEDUSER,
          CREATETIME,
           EQUIPID,
           PROJECTID,
           BEGINTIME,
           ENDTIME,
           TYPE,
           STATUS)
        values
          (1,
           SYSDATE,
           s_equipid,
           s_projectid,
           (trunc(sysdate, 'mm')+1/24),
           (trunc(LAST_DAY(SYSDATE))+23/24),
           1,
           1);
        select "SEQ_EQUIPSELFCHECK".currval into s_escid from dual;
       
        for ep_row in ( select ep.id EPId  from EquipPoint ep  join POINTBASIS pb on ep.PointId=pb.id where ep.equipid=s_equipid and ep.Status=0 and ep.DELETED=0 and pb.ISSELFCHECK=1 )loop 
            
           --新增详情
            insert into EQUIPSELFCHECKDETAIL
                (CREATEDUSER,CREATETIME, ESID, EPID, SEVERITY, ELIGIBILITY)
                values
                (1,
                to_timestamp(TO_CHAR(sys_extract_utc(systimestamp),'YYYY-MM-DD') || ' 00:00:00','yyyy-mm-dd hh24:mi:ss.ff'), 
                s_escid,
                ep_row.epid,
                1,
                1
                );
          
          end loop;
          
        end loop;
      end loop;
    
      --关闭游标
      close c_list;
    
      COMMIT;
      s_msg := '定时创建自检任务';
      DBMS_OUTPUT.PUT_LINE(s_msg || '成功' ||
                           to_char(CURRENT_DATE, 'yyyy-mm-dd'));
    else
      null;
  end case;
  --异常回滚
exception
  when others then
    rollback;
    dbms_output.put_line(s_msg || '失败' ||
                         to_char(CURRENT_DATE, 'yyyy-mm-dd'));
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
end EP_Task;