Snippet Name: Wage and Commission Calculation
Description: This block calculates the total wages (salary plus commission)
paid to employees in the SERVICE department. It also determines how
many of the employees have salaries higher than $1000, and how
many have commissions larger than their salaries.
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 27th, 2009
|
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows *
* returned by emp_cursor. */
FOR emp_record IN emp_cursor(SERVICE) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 1000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
'Total Wages: ' || TO_CHAR(total_wages));
COMMIT;
END; |
