PL/SQL Day 5
PACKAGES
Practice 1
Create a package named MANAGE_EMP_PACK that has two public procedures,
two package level variables and a private function. The public procedure
HIRE_EMP adds an employee record in EMPLOYEES table and the public
procedure FIRE_EMP deletes an employee record from the EMPLOYEES table.
The two variables v_insert_cnt and v_delete_cnt are used in the package, for
keeping record of the numbers of times insert / delete has been executed.
Create a private function VALIDATE_EMP in the package to validate employee
number. This function can be called from HIRE_EMP and FIRE_EMP.
The function VALIDATE_EMP accepts an employee number in a parameter
p_eno and returns TRUE if the value of employee number exists in the
EMPLOYEES table else it returns FALSE.
The procedure HIRE_EMP takes all the column values of the EMPLOYEES table
as parameters. It gives a call to VALIDATE_EMP by passing on the value of
employee number and if the function returns TRUE then it displays message
‘Employee number already in use’. If the function returns FALSE then it
inserts a new record in the EMPLOYEES table and displays a message ‘One
employee added’. It also increments the value of v_insert_cnt by 1.
The procedure FIRE_EMP accepts an employee number as a parameter and
gives a call to VALIDATE_EMP by passing on the value of employee number.
If the function returns TRUE then it deletes the corresponding record from the
EMPLOYEES table, displays message ‘One employee deleted’ and increments
the value of v_delete_cnt by 1. If the function returns FALSE then it displays
message ‘Wrong employee number’.
Check working of the methods in MANAGE_EMP_PACK by giving calls to the
public procedures and by displaying value of the appropriate package
variable, through an anonymous block
Practice 2
Create a package named MY_EMP_PACK having two overloaded functions
named GET_AVG_SAL. The first function accepts first name as a parameter
while the second function accepts employee_id as a parameter and both
return the average salary paid in the department to which the employee
belongs. Both the functions should handle the exception for non-existing
employee by displaying an appropriate error message.
Give a call to GET_AVG_SAL through an anonymous PL/SQL block by passing
on employee number 204. Do the same again by passing on employee name
‘Valli’.
Practice 3
Create a package containing a private function Find_Plan and a public
procedure Display_Plans as described below:
a) The function Find_Plan will find out and return the plan id which is used
by maximum customer.
b) The procedure Display_Plan will call Find_Plan function and will display
the Plan name and the corresponding service name.
c) Call the above procedure from anonymous PL/SQL block.
1/3
PL/SQL Day 5
The sample report is shown below :
Plan ID :_______________ Plan_Name:_____________
Service_name:_________
Note: refer Operator_schema schema
Practice 4
Create a Package pkgDisplay_details which has a public function and a
public procedure as given below :
1) Public function fnDisplayStatus which accepts paper id as parameter
and returns the status of paper. If an invalid paper is passed then it
returns invalid
2) Public procedure prDispCount which accepts author id and status
as parameter. The procedure displays the no of papers by the author
with the given status.
Hint : prDispCount('A001','Published') will display the count as 2.
Invoke the above function and procedure from the anonymous block and display the
values returned by the function.
Note: refer scholar_domain_publication schema
Practice 5
Create a Package named Movie_Pack which contains procedures and functions
mentioned in A and B.
A. Write a private function named fnGenerateMovieID which generates an unique
movie id in continuation to the existing one and return the same.
B. Write a procedure named prNewMovie which inserts a new movie details into
the movie table:
The procedure accepts following parameters:
Movie name
Language
Genre
Duration
Date of release
The procedure should do the following
Invoke the function fnGenerateMovieID which return the movie Id.
Catch all the possible exceptions
Insert the record
After successful insertion display the message “1 row inserted in
Movie_details table”.
Note: refer cinema_schema schema
Practice 6
2/3
PL/SQL Day 5
Create a Package named PKG_TRANSPORTS which contains Public members as
mentioned below:
- Public Exception: ROUTE_NOT_FOUND
- Public Procedure: SHOW_DETAILS
a) Public Exception: ROUTE_NOT_FOUND
Associate ROUTE_NOT_FOUND exception with ERROR CODE: -20000.
b) Public Procedure: SHOW_DETAILS
Procedure accepts ROUTE_NO as parameter.
If route number is invalid, procedure raise exception with ERROR CODE:
-20000 and ERROR MESSAGE: Invalid route number.
Display list of schedule_ids, if route number is valid.
d) Invoke the above procedure SHOW_DETAILS from an anonymous block
and display output. Anonymous block must handle exception raised in
procedure with help of ROUTE_NOT_FOUND exception.
e) should handle all the probable errors
Note: refer Bus Shedule schema
3/3
