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;
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