DBS311 – Lab 6 – Week 8 Solved

$ 24.99


(Stored Procedures/Iterative Statements)

Your submission will be a single text-based SQL file with appropriate header and commenting. Please ensure your file runs when the entire file is executed in SQL Developer.
Create a new Worksheet in SQL Developer. Save the file as L06_ID#_LASTNAME.sql
Your submission needs to be commented and include the question, the solutions.
In this Lab, you create PL/SQL stored procedures to perform the following tasks. As you know, a stored procedure does not return any value. To send values back to the caller, you can use OUT parameters.
A parameter can be
• IN parameter
• OUT parameter
• IN OUT parameter
See the following template:
CREATE OR REPLACE procedure_name(arg1 IN/OUT/IN OUT data_type, …) AS
END procedure_name;

For all the stored procedures make sure you handle all exceptions such as
• . . .
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.
1. Write a store procedure that gets an integer number n and calculates and displays its factorial.
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:

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 – write a brief note about what the question is asking — Q1 SOLUTION –
CREATE OR REPLACE procedure_name(arg1 data_type, …) AS
END procedure_name;

— Question 2 –
— Q2 Solution –


There are no reviews yet.

Be the first to review “DBS311 – Lab 6 – Week 8 Solved”

Your email address will not be published. Required fields are marked *