Description
In this lab, we will use AlphaCoEmp and Emp_Work tables that you have created in the earlier labs.
The following is the plan of what you will do today.
• Load randomly generated values for salary and title for the tuples in AlphaCoEmp using a procedure.
• Write a function called calcSalaryRaise() to calculate the amount of raise for an employee.
• Create a table called EmpStats.
• Create a procedure called saveCountsByTitle
• Create a function called countByTitle() with an exception
Run the queries and capture the results in lab5_output.lst, using spool.
Question 1 (10 pts)
In this exercise, we will try to assign job titles to employees randomly selected from a hardcoded set of titles, in AlphaCoEmp table, using a PLSQL procedure. Since we want to assign salaries based on titles, we will store titles and salaries in two PLSQL VArrays and randomly select an index into the arrays and use the title and its associated salary from the same index number. For example, the salary for the title stored at index 0 in the titles array will be at index 0 in the salaries array.
Note: In PLSQL VArrays, subscripts start at 1, not 0.
Examine the code in the procedure below, to assign job titles and salaries.
Create or Replace Procedure assignJobTitlesAndSalaries
As
type titlesList IS VARRAY(5) OF AlphaCoEmp.title%type;
type salaryList IS VARRAY(5) of AlphaCoEmp.salary%type;
v_titles titlesList;
v_salaries salaryList;
Cursor Emp_cur IS
Select * from AlphaCoEmp;
l_emprec Emp_cur%rowtype;
l_title AlphaCoEmp.title%type;
l_salary AlphaCoEmp.salary%type;
l_randomnumber INTEGER := 1;
BEGIN
/* Titles are stored in the v_titles array */
/* Salaries for each title are stored in the v_salaries array.
The salary of v_titles[0] title is at v_salaries[0].
*/
v_titles := titlesList(‘advisor’, ‘director’, ‘assistant’, ‘manager’, ‘supervisor’);
v_salaries := salaryList(130000, 100000, 600000, 500000, 800000);
/* use a for loop to iterate through the set
for l_emprec IN Emp_cur
LOOP
/* We get a random number between 1-5 both inclusive */
l_randomnumber := dbms_random.value(1,5);
/* Get the title using the random value as the index into the
v_tiles array */
l_title := v_titles(l_randomnumber);
/* Get the salary using the same random value as the index into the v_salaries array */
l_salary := v_salaries(l_randomnumber);
/* Update the employee title and salary using the l_title
and l_salary */
update AlphaCoEmp
set title = l_title
where name = l_emprec.name;
update AlphaCoEmp
set salary = l_salary
where name = l_emprec.name;
END LOOP;
commit;
END;
/
Show errors;
Run the code and if it compiles without errors, run the command,
a) exec assignJobTitlesAndSalaries at SQL prompt.
b) Run a Select * on AlphaCoEmp table and check if titles and salaries are assigned.
c) Now, modify the above procedure and include one more job title and a salary for that title in the code.
I added an occupation named ‘trash collector’ with a salary of 1000000.
d) Run the procedure.
e) Execute the procedure and make sure it is working ok.
To ensure that it is working correctly, I made sure to adjust the varray size to 6 and increased the upper-bound of l_randomnumber from 5 to 6.
Question 2 (15 pts)
You will write a PLSQL function that calculates the salary raise based on the current salary and the percent raise. PLSQL functions return a value.
The function calcSalaryRaise() calculates raise amount as follows:
• Takes the name and percent salary (an integer) as parameters.
• Fetches the employee’s salary from AlphaCoEmp Table and calculates the amount of raise.
Create or Replace Function calcSalaryRaise( p_name in AlphaCoEmp.name%type, percentRaise IN Integer)
RETURN NUMBER
IS
l_salary AlphaCoEmp.salary%type;
l_raise AlphaCoEmp.salary%type;
l_cnt Integer;
BEGIN
— Find the current salary of p_name from AlphaCoEMP table.
Select salary into l_salary from AlphaCoEmp
where name = p_name;
— Calculate the raise amount
l_raise := l_salary * (percentRaise/100);
/* return a value from the function */
return l_raise;
END;
/
Show Errors;
Run the function (copy and paste it at SQL prompt or run it from a script file). Once it compiles without errors, you can execute it by calling it, as shown below.
a) If you want to test the function and see if it is working ok, call it as follows:
Select calcSalaryRaise(‘Stone’,2) from Dual;
Note: You can give any name that is in the AlphaCoEmp table.
b) Call the function as part of a more useful SQL query
Select name, title, salary CURRENTSALARY, trunc(calcSalaryRaise(name,2)) NEWSALARY
from AlphaCoEmp where upper(name) = upper(‘Stone’);
What is the output?
NAME TITLE CURRENTSALARY NEWSALARY
————————- ——————– ———————— —————-
Stone trash collector 1000000 20000
c) If you examine the code of the function, we are comparing (string compare) the name with the parameter, p_name without checking the case. Modify the code so that both strings are compared with each other, both in upper or lowercase.
Test and make sure your function work correctly after modifications.
The function still works properly after setting both strings that we are comparing to uppercase.
d) What happens if you call the function with a name that NOT in the AlphaEoEmp table?
Select calcSalaryRaise(‘BoogieMonster’,2) from Dual;
CALCSALARYRAISE(‘BOOGIEMONSTER’,2)
———————————————————
Now let us rewrite the function with an exception.
Add the given code below the “return l_raise” statement.
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘No employee with given name found!’);
–return 0;
WHEN others THEN
dbms_output.put_line(‘Error!’);
Test your function again with a name that is not in the table.
CALCSALARYRAISE(‘BOOGIEMONSTER’,2)
———————————————————-
0
No employee with given name found!
It now returns a 0 for the value, rather than leaving the value blank and prints out “No employee with given name found!”
Question 3 (15 pts)
a) Let us create a table called EmpStats as follows:
Create table EmpStats (title VARCHAR(20) Primary KEY,empcount INTEGER, lastModified DATE);
b) The function (incomplete) below, counts the number of employees from AlphaCoEmp table by title, where title is passed as a parameter and returns the count.
Complete the function and run it.
Create or Replace Function countByTitle(p_title in AlphaCoEmp.title%type)
RETURN NUMBER IS
l_cnt Integer;
BEGIN
/* Complete the query below */
Select into l_cnt from AlphaCoEmp
Group by
Having
return l_cnt;
END;
/
c) Run the SQL commands below and show the output.
select countByTitle(‘director’) from Dual;
select countByTitle(‘advisor’) from Dual;
Output:
COUNTBYTITLE(‘DIRECTOR’)
—————————————
84
COUNTBYTITLE(‘ADVISOR’)
————————————-
44
Question 4 (15 pts)
If the function, countByTitle() in Q3 is working ok, we will write a procedure to store the number of employees by title in the table, EmpStats. We will call the function countByTitle() in this procedure.
CREATE or REPLACE procedure saveCountByTitle
AS
l_advisor_cnt integer := 0;
BEGIN
l_advisor_cnt := countByTitle(‘advisor’);
delete from EmpStats; — Any previously loaded data is deleted
/* inserting count of employees with title, ‘advisor’.*/
insert into EmpStats values (‘advisor’,l_advisor_cnt,SYSDATE);
END;
/
Show errors;
The above procedure stores the count of employees with the title, ‘advisor’.
a) Complete the procedure to store the count of employees for every title you have in AlphaCoEmp table.
b) Execute the procedure.
c) Show the data in the EmpStats table (do a Select * ).
Output:
TITLE EMPCOUNT LASTMODIF
——————– ————— —————
advisor 41 28-OCT-19
director 74 28-OCT-19
assistant 88 28-OCT-19
manager 88 28-OCT-19
supervisor 94 28-OCT-19
trash collector 34 28-OCT-19
Question 5 (20 pts)
a) Write a PLSQL function called countBySalaryRange() that takes two parameters, called lowEnd and highEnd, both numbers. The function counts the no.of employees in AlphaCoEmp table, whose salaries fall with in the range between lowEnd and highEnd (both numbers inclusive) and returns the count.
b) Check if your function is created without errors.
c) Now, call the function, countBySalaryRange() with two values of your choice for the parameters and display the value returned.
Select countBySalaryRange(20000,900000) From Dual;
COUNTBYSALARYRANGE(20000,900000)
—————————————————–
378
d) Show how you call countBySalaryRange() with two values of your choice for the parameters and display the value returned with an appropriate column heading.
Select countBySalaryRange(20000,900000) EmployeesInSalaryRange From Dual;
EMPLOYEESINSALARYRANGE
——————————————-
387
