BONUS_CALCULATION.SQL

-- CALCULATE BONUS BASED ON JOB HISTORY AND COMMISSION
-- 40% IF JOB HISTORY IS PRESENT, 25 % IF COMMISSION IS PRESENT OTHERWISE 35%

SET SERVEROUTPUT ON

DECLARE
  CURSOR EMPCUR IS
     SELECT EMPLOYEE_ID, SALARY, COMMISSION_PCT, COUNT(START_DATE) JOBCOUNT
     FROM EMPLOYEES LEFT OUTER JOIN JOB_HISTORY JH
     USING(EMPLOYEE_ID)
     GROUP BY EMPLOYEE_ID,SALARY,COMMISSION_PCT
     ORDER BY 1;
     
   V_BONUS NUMBER(5);
BEGIN
  FOR  EMPREC IN EMPCUR
  LOOP
     IF EMPREC.JOBCOUNT > 0 THEN
        V_BONUS := EMPREC.SALARY * 0.40;
     ELSIF EMPREC.COMMISSION_PCT IS NOT NULL THEN
        V_BONUS := EMPREC.SALARY * 0.25;
     ELSE
        V_BONUS := EMPREC.SALARY * 0.35;
     END IF;
     
     DBMS_OUTPUT.PUT_LINE(EMPREC.EMPLOYEE_ID || ' - ' || V_BONUS);
  END LOOP;     
END;