DBS3110 Lab 6 – Stored Procedures/Iterative Statements Solution

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
. . .
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: #########
-- Purpose: Lab 6 DBS311
-- ***********************

-- Question 1 – Copy of question from above
-- Q1 SOLUTION –
CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS
BEGIN
....
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (Error!');
END procedure_name;



-- Question 2 – Copy of question from above
-- Q2 Solution –
…