311-Lab06W8 stored procedures 2
Lab 6 – Week 8 (Stored Procedures/Iterative Statements)
Submission
ON Blackboard
Copyright By PowCoder代写 加微信 powcoder
Create a new Worksheet in SQL Developer. Save the file as L06_ID#_LASTNAME.txt need a txt file
Your submission needs to be commented and include the question and 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
WHEN OTHERS
DBMS_OUTPUT.PUT_LINE (‘Error!’);
END procedure_name;
For all the stored procedures make sure you handle all exceptions such as
· TOO_MANY_ROWS
· NO_DATA_FOUND
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 The company wants to calculate what the employees’ annual salary would be:
Do NOT change any salaries in the table.
Assume that the starting salary or sometimes called base salary was $10,000.
Every year of employment after that, the salary increases by 5%.
Write a stored procedure named calculate_salaryXX (where XX is your Oracle ID) which gets an employee ID from the user 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 the calculation of 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 exist, the procedure displays a proper message.
2 Do these 3 tries without an exception handler.
For a given CITY name, you need to find out Department_id and Department_name that exists in that city. There are 3 scenarios here;
In a given CITY, there is a SINGLE department
In a given CITY, there is a MORE THAN ONE department
In a given CITY, there is NO department
Do the problem using SOUTHLAKE as the city first and see the result.
Then try TORONTO
Then try SEATTLE
Submit the code and the results.
3 Write a stored procedure named employee_works_hereXX to print the employee_id, employee Last name and department name.
This is sample output
Employee # Last Name Department Name
9999 Able Manufacturing
9998 Notsoable Shipping
If the value of the department name is null or does not exist, display “no department name”.
The value of employee ID ranges from your Oracle id’s last 2 digits (ex: dbs311_203g37 would use 37)
to employee 105.
(NOTE: Check manually and not in the procedure, to see if your number is in the employee table. If not pick the first employee number higher that does exist)
Since you are looping there will be missing employee numbers. At that stage you can get out of the loop that displays the data about each employee.
DO NOT USE CURSORS
Go to next page to see sample submission
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Date: The current date
— 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
WHEN OTHERS
DBMS_OUTPUT.PUT_LINE (Error!’);
END procedure_name;
— Question 2 –
— Q2 Solution –
DBS311_Lab06_W8 stored procedures 2-RT by rt — 3 November 2021 1 of 1
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com