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
|
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL> CREATE OR REPLACE
2 PACKAGE AFFICHAGE AS
3
4 TYPE livretuple IS RECORD(
5 nom employees.first_name%TYPE);
6
7 CURSOR les_livres RETURN livretuple;
8
9 procedure nb_a_rendre (adherent NUMBER );
10
11 procedure lst_livres_dispo;
12
13 END AFFICHAGE;
14 /
Package created
SQL>
SQL> CREATE OR REPLACE
2 PACKAGE BODY AFFICHAGE AS
3
4 CURSOR les_livres RETURN livretuple IS SELECT first_name FROM employees WHERE employee_id = 100;
5
6 procedure nb_a_rendre (adherent NUMBER) IS
7 query_str VARCHAR2(1000);
8 num NUMBER;
9 BEGIN
10 query_str := 'SELECT COUNT(employee_id) FROM employees WHERE department_id = '|| TO_CHAR(adherent) ||' AND manager_id is null';
11 EXECUTE IMMEDIATE query_str
12 INTO num;
13 dbms_output.put_line ('Nombre de livre à rendre :' || num);
14 END;
15
16
17 PROCEDURE lst_livres_dispo IS
18 s livretuple;
19 BEGIN
20 FOR s IN les_livres LOOP
21 DBMS_OUTPUT.PUT_LINE(s.nom);
22 END LOOP;
23 END;
24
25 END AFFICHAGE;
26 /
Package body created
SQL> set serveroutput on
SQL> exec affichage.lst_livres_dispo
Steven
PL/SQL procedure successfully completed
SQL> exec affichage.nb_a_rendre(10)
Nombre de livre à rendre :0
PL/SQL procedure successfully completed
SQL> |
Partager