# TP2 PL/SQL


SET serveroutput ON

# Exercice 1

CREATE OR REPLACE PROCEDURE DEP_SANS_CHEF(ville IN VARCHAR)
IS
    CURSOR departments
        IS SELECT department_name, department_id FROM hr.departments d, hr.locations l
                    WHERE d.location_id=l.location_id
                    AND d.manager_id IS NULL
                    AND LOWER(l.city)=LOWER(ville);
    nb NUMBER;
    dep departments%ROWTYPE;
    villeInexistante EXCEPTION;
    pasDeDep EXCEPTION;
BEGIN
    SELECT COUNT(*) INTO nb FROM hr.locations WHERE LOWER(city)=LOWER(ville);
    IF nb=0 THEN
        raise villeInexistante;
    END IF;
    SELECT COUNT(*) INTO nb FROM hr.departments d, hr.locations l
                    WHERE d.location_id=l.location_id
                    AND d.manager_id IS NULL
                    AND LOWER(l.city)=LOWER(ville);
    IF nb=0 THEN
        raise pasDeDep;
    END IF;

    dbms_output.put_line('Liste des departements sans manager dans la ville de ' || ville || ' :');
    FOR dep IN departments loop
        dbms_output.put_line('    - ' || dep.department_name);
    END loop;
exception
    WHEN villeInexistante THEN
        dbms_output.put_line('Pas de ville ' || ville);
    WHEN pasDeDep THEN
        dbms_output.put_line('Pas de departement sans chef dans la ville de ' || ville);
END;
/

EXEC DEP_SANS_CHEF('seattle')
EXEC DEP_SANS_CHEF('southlake')
EXEC DEP_SANS_CHEF('tokyo')
EXEC DEP_SANS_CHEF('roma')

Liste des departements sans manager dans la ville de seattle :
- Treasury
- Corporate Tax
- Control AND Credit
- Shareholder Services
- Benefits
- Manufacturing
- Construction
- Contracting
- Operations
- IT Support
- NOC
- IT Helpdesk
- Government Sales
- Retail Sales
- Recruiting
- Payroll

Pas de departement sans chef dans la ville de southlake
Pas de departement sans chef dans la ville de tokyo
Pas de departement sans chef dans la ville de roma


# Exercice 2

CREATE OR REPLACE PROCEDURE EFFECTIF_DEP(ville IN VARCHAR)
IS
    CURSOR liste IS SELECT d.department_name, COUNT(*) AS nb_employees FROM hr.departments d, hr.locations l, hr.employees e
            WHERE d.location_id=l.location_id
            AND d.department_id=e.department_id
            AND LOWER(l.city)=LOWER(ville)
            GROUP BY d.department_id, d.department_name;
    dep liste%ROWTYPE;
    villeInexistante EXCEPTION;
    pasDeDep EXCEPTION;
    nb NUMBER;
BEGIN
    SELECT COUNT(*) INTO nb FROM hr.locations WHERE LOWER(city)=LOWER(ville);
    IF nb=0 THEN
        raise villeInexistante;
    END IF;
    SELECT COUNT(*) INTO nb FROM hr.locations l, hr.departments d WHERE l.location_id=d.location_id;
    IF nb=0 THEN
        raise pasDeDep;
    END IF;
    dbms_output.put_line('Effectifs de ' || ville || ' :');
    FOR dep IN liste LOOP
        dbms_output.put_line('    - ' || dep.nb_employees || ' employes dans le departement ' || dep.department_name);
    END LOOP;

exception
    WHEN villeInexistante THEN
        dbms_output.put_line('Pas de ville ' || ville);
    WHEN pasDeDep THEN
        dbms_output.put_line('Pas de departement dans la ville de ' || ville);
END;
/

EXEC EFFECTIF_DEP('seattle')
EXEC EFFECTIF_DEP('southlake')
EXEC EFFECTIF_DEP('tokyo')
EXEC EFFECTIF_DEP('roma')

# Exercice 3

DECLARE
    CURSOR employes IS SELECT * FROM hr.employees ORDER BY salary DESC;
    emp employes%ROWTYPE;
BEGIN
    FOR emp IN employes LOOP
        IF employes%ROWCOUNT>=6 THEN
            EXIT;
        ELSE
            dbms_output.put_line(emp.first_name || ' ' || emp.last_name || ' gagne ' || emp.salary || '$');
        END IF;
    END LOOP;
END;
/

Steven King gagne 24000$
Neena Kochhar gagne 17000$
Lex De Haan gagne 17000$
John Russell gagne 14000$
Karen Partners gagne 13500$

CREATE OR REPLACE PROCEDURE TOP_N(n IN NUMBER)
IS
    CURSOR employes IS SELECT * FROM hr.employees ORDER BY salary DESC;
    emp employes%ROWTYPE;
    nIncorrect EXCEPTION;
BEGIN
    IF n<=0 THEN
        RAISE nIncorrect;
    END IF;

    FOR emp IN employes LOOP
        IF employes%ROWCOUNT>n THEN
            EXIT;
        ELSE
            dbms_output.put_line(emp.first_name || ' ' || emp.last_name || ' gagne ' || emp.salary || '$');
        END IF;
    END LOOP;
EXCEPTION
    WHEN nIncorrect THEN
        dbms_output.put_line('n negatif ou nul');
END;
/

EXEC TOP_N(5)

Steven King gagne 24000$
Neena Kochhar gagne 17000$
Lex De Haan gagne 17000$
John Russell gagne 14000$
Karen Partners gagne 13500$

EXEC TOP_N(-10)

n negatif ou nul

EXEC TOP_N(11)

Steven King gagne 24000$
Neena Kochhar gagne 17000$
Lex De Haan gagne 17000$
John Russell gagne 14000$
Karen Partners gagne 13500$
Michael Hartstein gagne 13000$
Shelley Higgins gagne 12000$
Nancy Greenberg gagne 12000$
Alberto Errazuriz gagne 12000$
Lisa Ozer gagne 11500$
Gerald Cambrault gagne 11000$

CREATE OR REPLACE PROCEDURE TOP_N_BIS(n IN NUMBER)
IS
    CURSOR employes IS SELECT * FROM hr.employees ORDER BY salary DESC;
    emp employes%ROWTYPE;
    i NUMBER := 0;
    dernierSalaire hr.employees.salary%TYPE := -1;
    nIncorrect EXCEPTION;
BEGIN
    IF n<=0 THEN
        RAISE nIncorrect;
    END IF;

    FOR emp IN employes LOOP
        IF i>=n THEN
            EXIT;
        ELSE
            IF dernierSalaire != emp.salary THEN
                i := i + 1;
                dernierSalaire := emp.salary;
            END IF;
            dbms_output.put_line(i ||') '||emp.first_name || ' ' || emp.last_name || ' gagne ' || emp.salary || '$');
        END IF;
    END LOOP;
EXCEPTION
    WHEN nIncorrect THEN
        dbms_output.put_line('n negatif ou nul');
END;
/

EXEC TOP_N_BIS(5)

1) Steven King gagne 24000$
2) Neena Kochhar gagne 17000$
2) Lex De Haan gagne 17000$
3) John Russell gagne 14000$
4) Karen Partners gagne 13500$
5) Michael Hartstein gagne 13000$

EXEC TOP_N_BIS(7)
1) Steven King gagne 24000$
2) Neena Kochhar gagne 17000$
2) Lex De Haan gagne 17000$
3) John Russell gagne 14000$
4) Karen Partners gagne 13500$
5) Michael Hartstein gagne 13000$
6) Shelley Higgins gagne 12000$
6) Nancy Greenberg gagne 12000$
6) Alberto Errazuriz gagne 12000$
7) Lisa Ozer gagne 11500$


EXEC TOP_N_BIS(11)

1) Steven King gagne 24000$
2) Neena Kochhar gagne 17000$
2) Lex De Haan gagne 17000$
3) John Russell gagne 14000$
4) Karen Partners gagne 13500$
5) Michael Hartstein gagne 13000$
6) Shelley Higgins gagne 12000$
6) Nancy Greenberg gagne 12000$
6) Alberto Errazuriz gagne 12000$
7) Lisa Ozer gagne 11500$
8) Gerald Cambrault gagne 11000$
8) Ellen Abel gagne 11000$
8) Den Raphaely gagne 11000$
9) Clara Vishney gagne 10500$
9) Eleni Zlotkey gagne 10500$
10) Harrison Bloom gagne 10000$
10) Janette King gagne 10000$
10) Peter Tucker gagne 10000$
10) Hermann Baer gagne 10000$
11) Tayler Fox gagne 9600$


DECLARE
    CURSOR topEmp IS SELECT * FROM hr.employees ORDER BY salary DESC;
    CURSOR jobHist(empId hr.employees.employee_id%TYPE) IS SELECT *
                                FROM hr.jobs NATURAL JOIN hr.job_history
                                WHERE employee_id=empId
                                ORDER BY start_date DESC;
    emp topEmp%ROWTYPE;
    job jobHist%ROWTYPE;

BEGIN
    FOR emp IN topEmp LOOP
        IF topEmp%ROWCOUNT>=6 THEN
            EXIT;
        ELSE
            dbms_output.put_line('Historique de ' || emp.first_name || ' ' || emp.last_name || ' :');
            FOR job IN jobHist(emp.employee_id) LOOP
                IF jobHist%ROWCOUNT>=4 THEN
                    EXIT;
                ELSE
                    dbms_output.put_line(   '    - '                                ||
                                            to_char(job.start_date, 'dd/mm/yyyy')   ||
                                            ' -> '                                  ||
                                            to_char(job.end_date, 'dd/mm/yyyy')     ||
                                            ' : '                                   ||
                                            job.job_title
                                        );
                END IF;
            END LOOP;
        END IF;
    END LOOP;
END;
/

Historique de Steven King :
Historique de Neena Kochhar :
- 28/10/1993 -> 15/03/1997 : Accounting Manager
- 21/09/1989 -> 27/10/1993 : Public Accountant
Historique de Lex De Haan :
- 13/01/1993 -> 24/07/1998 : Programmer
Historique de John Russell :
Historique de Karen Partners :



SELECT COUNT(*) AS nb_subalternes FROM hr.employees
                WHERE manager_id=100;

CREATE OR REPLACE FUNCTION NB_SUBALTERNES_DIRECTS(id hr.employees.employee_id%TYPE)
RETURN NUMBER
IS
    nb NUMBER;
    idIncorrecte EXCEPTION;
BEGIN
    SELECT COUNT(*) INTO nb FROM hr.employees WHERE employee_id=id;
    IF nb=0 OR id IS NULL THEN
        RAISE idIncorrecte;
    END IF;
    SELECT COUNT(*) INTO nb FROM hr.employees WHERE manager_id=id;
    RETURN nb;

EXCEPTION
    WHEN idIncorrecte THEN
        dbms_output.put_line('ID incorrecte ou non indiquee');
END;
/

NB_SUBALTERNES_DIRECTS(100)
---------------------------
             14


SELECT employee_id, NB_SUBALTERNES_DIRECTS(employee_id) FROM hr.employees;

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    100                  14
    101                   5
    102                   1
    103                   4
    104                   0
    105                   0
    106                   0
    107                   0
    108                   5
    109                   0
    110                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    111                   0
    112                   0
    113                   0
    114                   5
    115                   0
    116                   0
    117                   0
    118                   0
    119                   0
    120                   8
    121                   8

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    122                   8
    123                   8
    124                   8
    125                   0
    126                   0
    127                   0
    128                   0
    129                   0
    130                   0
    131                   0
    132                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    133                   0
    134                   0
    135                   0
    136                   0
    137                   0
    138                   0
    139                   0
    140                   0
    141                   0
    142                   0
    143                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    144                   0
    145                   6
    146                   6
    147                   6
    148                   6
    149                   6
    150                   0
    151                   0
    152                   0
    153                   0
    154                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    155                   0
    156                   0
    157                   0
    158                   0
    159                   0
    160                   0
    161                   0
    162                   0
    163                   0
    164                   0
    165                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    166                   0
    167                   0
    168                   0
    169                   0
    170                   0
    171                   0
    172                   0
    173                   0
    174                   0
    175                   0
    176                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    177                   0
    178                   0
    179                   0
    180                   0
    181                   0
    182                   0
    183                   0
    184                   0
    185                   0
    186                   0
    187                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    188                   0
    189                   0
    190                   0
    191                   0
    192                   0
    193                   0
    194                   0
    195                   0
    196                   0
    197                   0
    198                   0

EMPLOYEE_ID NB_SUBALTERNES_DIRECTS(EMPLOYEE_ID)
----------- -----------------------------------
    199                   0
    200                   0
    201                   1
    202                   0
    203                   0
    204                   0
    205                   1
    206                   0