Assignment 4-8: Processing and Updating a Group of RowsTo 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 forverification 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 ISSELECT empno, ename, salFROM 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 employeeSET sal = lv_newmo_numWHERE 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;