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
| DECLARE
v NUMBER;
BEGIN
SELECT 1 INTO v
FROM CALENDRIER
WHERE annee = :ENTETE.ANNEE
AND mois = :ENTETE.MOIS;
INSERT INTO CALENDRIER(....)
SELECT :ENTETE.ANNEE, :ENTETE.MOIS, j1, j2, j3, j4, j5, j6, j7, j8, j9, j10, j11, j12, j13, j14, j15, j16, j17, j18, j19, j20, j21, j22, j23, j24, j25, j26, j27, j28, j29, j30, j31, j32, j33, j34, j35, j36, j37
FROM (
SELECT num,
lead(jj, 0) OVER (ORDER BY num) j1, lead(jj, 1) OVER (ORDER BY num) j2, lead(jj, 2) OVER (ORDER BY num) j3, lead(jj, 3) OVER (ORDER BY num) j4, lead(jj, 4) OVER (ORDER BY num) j5, lead(jj, 5) OVER (ORDER BY num) j6, lead(jj, 6) OVER (ORDER BY num) j7,
lead(jj, 7) OVER (ORDER BY num) j8, lead(jj, 8) OVER (ORDER BY num) j9, lead(jj, 9) OVER (ORDER BY num) j10, lead(jj, 10) OVER (ORDER BY num) j11, lead(jj, 11) OVER (ORDER BY num) j12, lead(jj, 12) OVER (ORDER BY num) j13, lead(jj, 13) OVER (ORDER BY num) j14,
lead(jj, 14) OVER (ORDER BY num) j15, lead(jj, 15) OVER (ORDER BY num) j16, lead(jj, 16) OVER (ORDER BY num) j17, lead(jj, 17) OVER (ORDER BY num) j18, lead(jj, 18) OVER (ORDER BY num) j19, lead(jj, 19) OVER (ORDER BY num) j20, lead(jj, 20) OVER (ORDER BY num) j21,
lead(jj, 21) OVER (ORDER BY num) j22, lead(jj, 22) OVER (ORDER BY num) j23, lead(jj, 23) OVER (ORDER BY num) j24, lead(jj, 24) OVER (ORDER BY num) j25, lead(jj, 25) OVER (ORDER BY num) j26, lead(jj, 26) OVER (ORDER BY num) j27, lead(jj, 27) OVER (ORDER BY num) j28,
lead(jj, 28) OVER (ORDER BY num) j29, lead(jj, 29) OVER (ORDER BY num) j30, lead(jj, 30) OVER (ORDER BY num) j31, lead(jj, 31) OVER (ORDER BY num) j32, lead(jj, 32) OVER (ORDER BY num) j33, lead(jj, 33) OVER (ORDER BY num) j34, lead(jj, 34) OVER (ORDER BY num) j35, lead(jj, 35) OVER (ORDER BY num) j36,
lead(jj, 36) OVER (ORDER BY num) j37
FROM (
SELECT LEVEL num,
CASE WHEN TO_CHAR(TRUNC(TO_DATE('01.'|| :ENTETE.MOIS ||'.'|| :ENTETE.ANNEE, 'DD.MM.RRRR'), 'IW') + LEVEL - 1, 'MM.RRRR') = :ENTETE.MOIS ||'.'|| :ENTETE.ANNEE
THEN EXTRACT( DAY FROM TRUNC(TO_DATE('01.'|| :ENTETE.MOIS ||'.'|| :ENTETE.ANNEE, 'DD.MM.RRRR'), 'IW') + LEVEL - 1) ELSE NULL END jj
FROM dual
CONNECT BY LEVEL <= 37
))
WHERE num = 1;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END; |
Partager