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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
|
DROP TABLE visa;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';
CREATE TABLE visa(
dateEntree VARCHAR2(20),
dateSortie VARCHAR2(20)
);
INSERT INTO visa VALUES('27/02/2005', '27/03/2006');
COMMIT;
CREATE OR REPLACE PACKAGE p
AS
TYPE periodes IS TABLE OF NUMBER;
FUNCTION periodeVisitees (curseur IN SYS_REFCURSOR) RETURN periodes PIPELINED;
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY p
IS
FUNCTION periodeVisitees (curseur IN SYS_REFCURSOR) RETURN periodes PIPELINED IS
moisDepart NUMBER;
moisRetour NUMBER;
Rvisa visa%rowtype;
BEGIN
LOOP
FETCH curseur INTO Rvisa;
EXIT WHEN curseur%NOTFOUND;
moisDepart :=to_char(to_date(RVisa.dateEntree,'DD/MM/YYYY'),'MM');
moisRetour :=to_char(to_date(RVisa.dateSortie,'DD/MM/YYYY'),'MM');
IF (moisDepart > moisRetour) THEN
FOR x in moisDepart..12 LOOP
PIPE ROW(x);
END LOOP;
FOR x in 1..moisRetour LOOP
PIPE ROW(x);
END LOOP;
ELSE
FOR x in moisDepart..moisRetour LOOP
PIPE ROW(x);
END LOOP;
END IF;
END LOOP;
RETURN ;
END;
END;
/
show errors
SELECT * FROM TABLE(p.periodeVisitees(CURSOR(select * from Visa)));
SQL> @tpl
Table dropped.
Session altered.
Table created.
1 row created.
Commit complete.
Package created.
No errors.
Package body created.
No errors.
COLUMN_VALUE
------------
2
3 |
Partager