Scribd
Upload
2 views11 pages

TP 2 PL SQL

The document outlines various stored procedures and functions in SQL related to employee and department management. It includes functions to count departments and employees, retrieve department names based on employee IDs, and procedures to display employee details and average salaries by department. Additionally, it features a procedure to check departments with more than 40 employees and outputs relevant information.

Uploaded by

mehdi dhib
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views11 pages

TP 2 PL SQL

The document outlines various stored procedures and functions in SQL related to employee and department management. It includes functions to count departments and employees, retrieve department names based on employee IDs, and procedures to display employee details and average salaries by department. Additionally, it features a procedure to check departments with more than 40 employees and outputs relevant information.

Uploaded by

mehdi dhib
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

PARTIE 1 : PROCEDURE ET FONCTION STOCKEES :

1-

CREATE OR REPLACE FUNCTION FN_NBR_DEPARTEMENT

RETURN NUMBER IS

nb NUMBER;

BEGIN

SELECT COUNT(*) INTO nb FROM departments;

RETURN nb;

END;

SELECT FN_NBR_DEPARTEMENT FROM dual;

2-

CREATE OR REPLACE FUNCTION FN_NOMDEPT (

p_emp_id IN employees.employee_id%TYPE

RETURN departments.department_name%TYPE

IS

v_dept_name departments.department_name%TYPE;

BEGIN

SELECT department_name

INTO v_dept_name

FROM departments

WHERE department_id = (

SELECT department_id

FROM employees
WHERE employee_id = p_emp_id

);

RETURN v_dept_name;

END;

SELECT FN_NOMDEPT(100) AS nom_dept FROM dual;

3-

CREATE OR REPLACE PROCEDURE PROC_DETAILS_EMP IS

BEGIN

FOR emp IN (

SELECT

e.first_name,

e.last_name,

m.first_name AS manager_first_name,

m.last_name AS manager_last_name

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.employee_id

LOOP

dbms_output.put_line(

'Employee Name: ' || emp.first_name || ' ' || emp.last_name ||

' | Manager Name: ' || emp.manager_first_name || ' ' || emp.manager_last_name

);

END LOOP;

END;
4.1-

CREATE OR REPLACE PROCEDURE PROC_SALMOY (p_nbre OUT NUMBER) IS

BEGIN

SELECT COUNT(DISTINCT department_id) INTO p_nbre FROM employees;

FOR rec IN (

SELECT department_name, AVG(salary) as sal_moy

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY department_name

LOOP

DBMS_OUTPUT.PUT_LINE('Department: ' || rec.department_name ||

' - Average Salary: ' || rec.sal_moy);

END LOOP;

END;

4.2-

DECLARE

v_nbre_dept NUMBER;

BEGIN

PROC_SALMOY(v_nbre_dept);

DBMS_OUTPUT.PUT_LINE('Number of Departments: ' || v_nbre_dept);

END;
5.1-

CREATE OR REPLACE FUNCTION FN_NBR_SALARIE(dept_id IN NUMBER) RETURN


NUMBER IS

nb_salarie NUMBER;

BEGIN

SELECT COUNT(*) INTO nb_salarie

FROM employees

WHERE department_id = dept_id;

RETURN nb_salarie;

END;

SELECT FN_NBR_SALARIE(10) FROM DUAL;

5.2-

CREATE OR REPLACE PROCEDURE PROC_NBR_SALARIE(dept_id IN NUMBER, nb_salaried


OUT NUMBER) IS

BEGIN

SELECT COUNT(*) INTO nb_salaried

FROM employees

WHERE department_id = dept_id;

END;

/
6-

CREATE OR REPLACE PROCEDURE PROC_TEST_NBR_SALARIE IS

v_dept_name VARCHAR2(30);

BEGIN

-- Boucle sur tous les départements

FOR dept_rec IN (SELECT department_name, department_id FROM departments)

LOOP

-- Utilisation de la fonction pour vérifier le nombre de salariés

IF FN_NBR_SALARIE(dept_rec.department_id) > 40 THEN

v_dept_name := dept_rec.department_name;

DBMS_OUTPUT.PUT_LINE(v_dept_name);

END IF;

END LOOP;

END;

EXECUTE PROC_TEST_NBR_SALARIE;
PARTIE 2 : PROCEDURE ET FONCTION NON STOCKEES :

You might also like