oracle 游标使用根据工龄更新年假

create or replace procedure UPDATE_CMCC_WORK_AGE is
  --ID
  workageID   CMCC_WORK_AGE.Work_Age_Id%type;
  --工龄
  workageYear CMCC_WORK_AGE.Work_Age%type;
  --是否劳模
  isModelWorker Cmcc_Work_Age.Is_Model_Worker%type;
  --定义游标(简单的说就是一个可以遍历的结果集)
  cursor v_workage is
    select WORK_AGE_ID, WORK_AGE,Is_Model_Worker
      from CMCC_WORK_AGE;
    -- where CMCC_WORK_AGE.WORK_AGE != '0';
begin
  --遍历游标处理后更新到表。遍历游标有几种方法，用loop语句是其中比较直观的一种。
  --每年年初凌晨执行，工龄加1
  update CMCC_WORK_AGE  set work_age = work_age + 1;
  commit;
  open v_workage;
  LOOP
    fetch v_workage
      into workageID, workageYear,isModelWorker;
    exit when v_workage%notfound;
    --Dbms_Output.put_line('工龄:' || workageID);


    --工龄少于5年非劳模
    if (workageYear < 5 and isModelWorker=0) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '5'
       where Work_Age_Id = workageID;
       commit;
     --工龄少于5年劳模
    elsif (workageYear < 5 and isModelWorker=1) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '10'
       where Work_Age_Id = workageID;
       commit;
    elsif (workageYear >= 5 and workageYear < 10 and isModelWorker=0) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '10'
       where Work_Age_Id = workageID;
       commit;
    elsif (workageYear >= 5 and workageYear < 10 and isModelWorker=1) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '15'
       where Work_Age_Id = workageID;
       commit;
    elsif (workageYear >= 10 and workageYear < 15 and isModelWorker=0) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '12'
       where Work_Age_Id = workageID;
       commit;
        elsif (workageYear >= 10 and workageYear < 15 and isModelWorker=1) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '17'
       where Work_Age_Id = workageID;
       commit;
    elsif (workageYear >= 15 and workageYear < 20 and isModelWorker=0) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '15'
       where Work_Age_Id = workageID;
       commit;
     elsif (workageYear >= 15 and workageYear < 20 and isModelWorker=1) then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '20'
       where Work_Age_Id = workageID;
       commit;
    elsif (workageYear >= 20  and isModelWorker=0)  then
      update CMCC_WORK_AGE
         set HOLIDAY_NUM = '20'
       where Work_Age_Id = workageID;
       commit;
     else
       update CMCC_WORK_AGE
         set HOLIDAY_NUM = '25'
       where Work_Age_Id = workageID;
       commit;
    end if;


  end loop;
  close v_workage;
END;