[go: up one dir, main page]
Scribd
Upload
9 views2 pages

Lab 2 PLSQL Unstored Stored Functions Procedures

The document outlines a series of tasks related to PL/SQL programming, including the creation of stored procedures and functions for managing employee and department data. Key tasks involve calculating department totals, employee salaries, and displaying employee-manager relationships. It also includes requirements for both stored and unstored functions to enhance database interaction and reporting capabilities.

Uploaded by

nadia
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
9 views2 pages

Lab 2 PLSQL Unstored Stored Functions Procedures

The document outlines a series of tasks related to PL/SQL programming, including the creation of stored procedures and functions for managing employee and department data. Key tasks involve calculating department totals, employee salaries, and displaying employee-manager relationships. It also includes requirements for both stored and unstored functions to enhance database interaction and reporting capabilities.

Uploaded by

nadia
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
You are on page 1/ 2

Forther_2_PLSQL

PART 1: STORED PROCEDURE AND FUNCTION :

1. Write a stored function "FN_NBR_DEPARTMENT" that returns the total number of


departments. Use this function in an SQL query.
2. Write a stored function "FN_NOMDEPT" that takes the employee's ID as a parameter and returns
the name of the department to which he/she works.

3. Write a stored procedure "PROC_DETAILS_EMP" to display the first and last name of

each employee and the first and last name of his manager.

4. Write a stored procedure "PROC_SALMOY" to display the average salary of each

department. This procedure also returns the number of departments. Use this stored

procedure in an anonymous block.

5. Write a stored function "FN_NBR_EMP" that takes a department number as a parameter

and returns the number of its employees. Translate this function into an equivalent

stored procedure "PROC_NBR_EMP".

6. Write a stored procedure "PROC_TEST_NBR_EMP" that displays the names of

departments with more than 40 employees. Use the stored function FN_NBR_EMP.

7. Write a stored procedure "PROC_SAL_SUP" that displays a list of employees with a salary
higher than the one who’s ID is given as a parameter. Use this stored procedure in an
anonymous block to display those who have a higher salary than employee 121.

8. Write a stored function "FN_MOY_SALARY" that returns the average salary of the
department of the employee whose ID is passed as a parameter.

1/2
Forther_2_PLSQL

9. Write a stored procedure "PROC_LISTE_EMP" to display for each manager the list of

employees he/she manages. Display their first and last names and their salaries. Use a

parameterized cursor.

10. E Write a stored function " FN_TRIMESTER " that returns the number of the trimester of

a date passed as a parameter. Use this function to display those who were hired in the

second semester of the year 1998.

PART 2: NON-STORED PROCEDURE AND FUNCTION :

1. Write an unstored function 'FN_EMP' that returns the number of employees managed

by a manager passed in parameter. Use this function with the name of the manager

'Payam'.

2. Write a stored procedure "PROC_DEPTS" to display the list of departments with more

than 20 employees. Display their names and the average salary.

2/2

You might also like