Consider the following Employees Table Employees DNO 2001 Rami Ajman 2002 SaharD

Question

Consider the following Employees Table Employees DNO 2001 Rami Ajman 2002 SaharDubai 3000 4000 3500 6000 9000 2000 6500 2004 Manal Sharjah2 2007 ALI AbuDhabi 2005 Ahmed Sharjah 2008 Sami AbuDhabi 3 2006 Salim Sharjah1 1. Write a PL/SQL statements that create a stored procedure named that updates the basicSalary for Employees of a certain department. The procedure receives two parameters, department number and percentage increase. The procedure uses the values of these parameters to update the basic salaries of the employee of that department and display the number of updated rows. Create a PL/SQL block that read two values related to DNO and Percentage increase and call the procedure raisSalary to perform the update. Hint use cursor concept

Explanation / Answer

DECLARE
total number;
CURSOR c is
SELECT * FROM Employees;
dno number;
per number;

PROCEDURE raisSalary(x IN number, p IN number)
IS
BEGIN
for i in c
LOOP
if i.DNO = x then
UPDATE Employees set BasicSalary = i.BasicSalary + (p*(i.BasicSalary)/100) where EmpId = i.EmpId;
total := total + 1;
end if;
END LOOP;
dbms_output.put_line(total);
END;
BEGIN

dno := 1;

per := 2;

raisSalary(dno, per);

END;
/

What we are doing in this code is to create a cursor on the table and then iterate over it. Wherever we find that the the cursor iterator's dno is equal to the given dno, we update the table to set the salary. ALso we increment a counter variable - total.

At the end we print total which denotes the number of updated rows.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.