1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| CREATE OR REPLACE
FUNCTION control(p_collab absence.cd_collab%TYPE)
RETURN number
IS
nbr_total NUMBER := 0;
temp_fin absence.dat_deb%TYPE := NULL;
temp_deb absence.dat_fin%TYPE := NULL;
CURSOR cur_absence IS
SELECT a.dat_deb
, a.dat_fin
FROM absence a
INNER JOIN
demande_absence d
ON d.cd_dem_abs = a.cd_dem_abs
WHERE d.cd_etat = 4
AND d.dat_demande >= TO_DATE('01/02/2012', 'DD/MM/YYYY')
AND a.cd_motif IN (2,3)
AND a.cd_collab = p_collab
ORDER BY a.dat_fin
;
var_absence cur_absence%ROWTYPE;
BEGIN
OPEN cur_absence;
LOOP
FETCH cur_absence INTO var_absence;
EXIT WHEN cur_absence%NOTFOUND;
IF temp_fin IS NOT NULL AND temp_deb IS NOT NULL AND var_absence.dat_deb = temp_fin + 1
THEN
nbr_total := nbr_total + (var_absence.dat_fin - temp_deb);
ELSE
nbr_total := nbr_total + (var_absence.dat_fin - var_absence.dat_deb);
END IF ;
temp_fin := var_absence.dat_fin;
temp_deb := var_absence.dat_deb;
END LOOP;
RETURN nbr_total;
END;
/ |
Partager