Assignment 4 8 Processing and Updating a Group of Rows To help track employee

Assignment 4 8 processing and updating a group of

This preview shows page 6 - 8 out of 8 pages.

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.
Image of page 6
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 ||
Image of page 7
' 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;
Image of page 8

You've reached the end of your free preview.

Want to read all 8 pages?

  • Fall '18
  • Charles Gabor
  • Salary

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture