Lab 6 – Stored Procedures/Iterative Statements

DBS311 – Advanced Data Systems 
1 | P a g e
Lab 6 – Stored Procedures/Iterative Statements
Submission
Your submission will consist of two files:
(a) A single text-based SQL file with appropriate header and commenting – and
(b) An output file demonstrating that your stored procedures work
Please ensure your SQL file runs when the entire file is executed.
Your file names should be L06-lastname-firstname
For example: L06-King-Les.sql and L06-King-Les.output or L06-King-Les.pdf, etc.
Your submission needs to be commented. At a minimum, your comments before each SQL
statement should include the complete question below you are answering.
Style Guide
For all the stored procedures make sure you handle all exceptions such as
• TOO_MANY_ROWS
• NO_DATA_FOUND
• OTHERS
• . . .
Besides checking all required exceptions, have the OTHER exception checked just in case any
error occurs that has not been anticipated at the time you write the code.
Stored procedures are programs and all programs should be properly commented to explain
your logic.
Tasks
1. Write a store procedure that gets an integer number n and calculates and displays its factorial.
Example:
0! = 1
2! = fact(2) = 2 * 1 = 1
3! = fact(3) = 3 * 2 * 1 = 6
DBS311 – Advanced Data Systems Summer 2020
2 | P a g e
. . .
n! = fact(n) = n * (n-1) * (n-2) * . . . * 1
2. The company wants to calculate the employees’ annual salary:
The first year of employment, the amount of salary is the base salary which is $10,000.
Every year after that, the salary increases by 5%.
Write a stored procedure named calculate_salary which gets an employee ID and for that
employee calculates the salary based on the number of years the employee has been working
in the company. (Use a loop construct to calculate the salary).
The procedure calculates and prints the salary.
Sample output:
First Name: first_name
Last Name: last_name
Salary: $9999,99
If the employee does not exists, the procedure displays a proper message.
3. Write a stored procedure named warehouses_report to print the warehouse ID, warehouse
name, and the city where the warehouse is located in the following format for all warehouses:
Warehouse ID:
Warehouse name:
City:
State:
If the value of state does not exist (null), display “no state”.
The value of warehouse ID ranges from 1 to 9.
You can use a loop to find and display the information of each warehouse inside the loop.
(Use a loop construct to answer this question. Do not use cursors.)
Example Submission
-- ***********************
-- Name: Your Name
-- ID: #########
-- Date: The current date
-- Purpose: Lab 6 DBS311
-- ***********************
-- Question 1 – Copy of question from above
-- Q1 SOLUTION –
CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS
BEGIN
 ....
DBS311 – Advanced Data Systems Summer 2020
3 | P a g e
EXCEPTION
WHEN OTHERS
 THEN
 DBMS_OUTPUT.PUT_LINE (Error!');
END procedure_name;
-- Question 2 – Copy of question from above
-- Q2 Solution –
…