Search This Blog

Sunday, April 5, 2015

Example of Loops

1. Loop with if example
2. Loop in for.

function CF_GET_YEARFormula return Char is
V_DATE DATE := TO_DATE(:YEARSPLUS,'YYYY/MM/DD');
begin
  IF TO_DATE(:YEARSPLUS,'YYYY/MM/DD') < SYSDATE
      THEN
          LOOP
          V_DATE := ADD_MONTHS(V_DATE,36);
          IF V_DATE > SYSDATE
         THEN EXIT;
         END IF;
          END LOOP;
         
  END IF;        
  RETURN NVL(TO_CHAR(V_DATE,'YYYY/MM/DD'),'');
  EXCEPTION
      WHEN OTHERS THEN RETURN NULL;
end;


2. Loop in for.  
BEGIN
    FOR i IN (SELECT employee_number, change_date, SUBSTR (change_date, 1, 2)
               FROM xx_increasing_sal_v
              WHERE SUBSTR (change_date, 1, 2) <> '01'
              --and MESSAGE = 'Change Date Must Be At The First Of The Month'
              )
     LOOP
      UPDATE xx_increasing_sal_v
         SET MESSAGE = 'Change Date Must Be At The First Of The Month'
       WHERE employee_number = i.employee_number
       and SUBSTR (change_date, 1, 2)<> '01'
       ;
         commit;
      END LOOP;
    END;

No comments:

Post a Comment