This week we will expand our knowledge of working with data with Procedural SQL. Very often you will need to package together various SQL commands into one unit of code. Or you need to define certain functions to be performed on the data which requires creating loops and conditional statements. This can be done using using Procedural SQL, called PL/SQL in Oracle Database. There are different versions of Procedural SQL in most multi-modal database engines.
This week we will look at:
- Basics of PL/SQL
- Creating PL/SQL program units (anonymous blocks)
- Creating PL/SQL Procedures and Functions
Click here to download the PL/SQL, Procedures and Functions notes.
Lab Exercise 1 – PL/SQL
The following lab exercises will take you from creating basic PL/SQL units, right through to creating functions and procedures with PL/SQL.
Check out the link to the chapter Fundamentals of PL/SQL Language.
[Take examples from notes/slides and run them in SQL Developer before attempting the following Tasks]
[Tasks 1-3 will take approx 15-25 minutes to complete]
[Tasks 4-9 will take approx 45-60 minutes to complete.]
Task 1 – Hello World
Write a PL/SQL anonymous block for the following:
- Print out the message ‘Hello World’.
- Print the current date, in three different formats.
Remember to SET SERVEROUTPUT ON, or turn on DBMS OUTPUT in SQL Developer
Write a PL/SQL anonymous block to counts the number of records in the employee table and prints the results.
Write a PL/SQL anonymous block to perform a calculation and print the results. This will accept one input parameter : Date of Birth. (in format DD/MM/YYYY)
Create a variables to store:
- Retirement Age
- Person Date of Birth
Calculate their current age and how long they have until retirement age.
Print these two calculated numbers in a formatted string, containing text to explain what these values mean.
Write a PL/SQL anonymous block that selects some of the attributes from the EMP and DEPT tables, join the data to form one record.
Only select data from 1 record. Use a SELECT … INTO… statement
Display the results on the screen.
Extend your code from Task 4 and write a PL/SQL anonymous block to select all employees and will print out some of their details to the screen.
You will need to use a Cursor FOR Loop.
Write a procedure to calculate the new salary, for an employee, based on defined percentage pay rise. This value can be entered as a parameter (in a procedure) or defined in a variable in an anonymous block.
The pay rise should only be applied to employees who earn less than 1601 and does not receive a commission payment.
You will need to define a Cursor with a select statement to select the appropriate values. Then update the records (update statement) to have the new values.
The procedure should only display the employee details, their current salary and their new salary, for those that were updated.
Update the procedure create in Task 6 to include the appropriate exception handling.
Write a procedure that will update an Employee salary based on a percentage pay rise.
The procedure should accept two parameters, employee number and percentage pay rise.
The procedure should update the employee salary to the new salary.
Write a function that calculate the tax payable on a salary. No data should be updated in this process.
The function should take as input a salary about and use the following rules to calculate the tax amount.
if salary is less than 25000 then tax is 10%
if salary is between 25001 and 40000 then tax is 20%
if salary is between 40001 and 60000 then tax is 30%
if salary is greater than 60000 then tax is 40%
Create a new version of the EMP_SALARY procedure to include a call to this tax function and include the tax amount in the output along with an additional value containing the salary and the tax amount.
Task 10 (Optional)
For the DEPT table, create stored procedures to
– Insert new rows into a table using the supplied parameter values
– Update data in a table for rows that match the supplied parameter values
– Delete rows from a table that match the supplied parameter values
– Query a table and retrieve data based on supplied parameter values
These procedures are typically used by REST services.
Lab Exercise 2 – Assignment & Commence Work on Assignment
Assignment Handout – I will walk through the assignment for this part of the module. The assignment hand-out can be found on BrightSpace.
Use any remaining time to work on your assignment.