Assignment 4 8 processing and updating a group of

Assignment 4-8: Processing and Updating a Group of Rows
To help track employee information, a new EMPLOYEE table was added to the Brewbean's database.
Review the data in this table. A PL/SQL block is needed to calculate annual raises and update employee
salary amounts in the table. Create a block that addresses all the requirements in the following list. All
salaries in the EMPLOYEE table are recorded as monthly amounts. Tip: Display the calculated salaries for
verification before including the update action.
• Calculate 6% annual raises for all employees except the president. If a 6% raise totals more than
$2,000, cap the raise at $2,000.
• Update the salary for each employee in the table.
• For each employee number, display the current annual salary, raise, and proposed new annual
salary.
• Finally, following the details for each employee, show the total cost of all employees' salary
increases for Brewbean's.

DECLARE
CURSOR cur_emp IS
SELECT empno, ename, sal
FROM employee
WHERE job <> 'PRESIDENT'
FOR UPDATE OF sal NOWAIT;
lv_incre_num NUMBER(8,2);
lv_annual_num NUMBER(6);
lv_newsal_num NUMBER(8,2);
lv_newmo_num NUMBER(8,2);
lv_totalincre_num NUMBER(8,2):=0;
BEGIN
FOR rec_emp IN cur_emp
LOOP
lv_annual_num := rec_emp.sal * 12;
lv_incre_num := lv_annual_num * .06;
IF lv_incre_num > 2000 THEN
lv_incre_num := 2000;
END IF;
lv_newsal_num := lv_annual_num + lv_incre_num;
lv_newmo_num := ROUND(lv_newsal_num/12,2);
lv_totalincre_num := lv_totalincre_num + lv_incre_num;
UPDATE employee
SET sal = lv_newmo_num
WHERE CURRENT OF cur_emp;
DBMS_OUTPUT.PUT_LINE('Emp no: ' || rec_emp.empno || ' Emp name: ' || rec_emp.ename ||

' Current Annual Salary: ' || lv_annual_num || ' Salary Raise: ' ||lv_incre_num||
' Proposed New Annual Salary: ' ||lv_newsal_num);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Total annual increment: ' || lv_totalincre_num);
END;
SELECT * FROM employee;