关闭

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

81人阅读 评论(0) 收藏 举报
分类:
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;
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:3712次
    • 积分:87
    • 等级:
    • 排名:千里之外
    • 原创:4篇
    • 转载:9篇
    • 译文:0篇
    • 评论:0条
    文章分类