[EXAMQUESTION] Firepeople

CREATE OR REPLACE
PROCEDURE FIREPEOPLE(
--Parameters : percentage of the total salary cost that need to be saved
percentage IN NUMBER
) IS
 salary_before NUMBER := 0;
 salary_after NUMBER := 0;
 invalid_percentage_exception EXCEPTION;
 CURSOR dept_cursor IS SELECT * FROM departments;
 CURSOR emp_cursor(dept NUMBER) IS
    SELECT * FROM employees e
    --join necessary tables
    inner join jobs j ON j.job_id = e.job_id
    WHERE e.department_id = dept
    --order on jobid
    ORDER BY j.min_salary ASC, j.max_salary ASC, e.hire_date DESC
    FOR UPDATE NOWAIT;
 emp_row emp_cursor%ROWTYPE;
BEGIN
--First check input parameter
IF percentage < 0 OR percentage >1 THEN
 RAISE invalid_percentage_exception;
END IF;
--Loop over all departments
FOR v_dept_rec IN dept_cursor LOOP
  SELECT SUM(salary) INTO salary_before FROM employees WHERE department_id = v_dept_rec.department_id;
  salary_after := salary_before;
  DBMS_OUTPUT.put_line('Department: ' || v_dept_rec.department_name);
  DBMS_OUTPUT.put_line('Total before saving: '|| salary_before);
  DBMS_OUTPUT.put_line('We want to reduce it to: '|| (salary_before-(percentage*salary_before)));
  OPEN emp_cursor(v_dept_rec.department_id);
  WHILE salary_after > (salary_before*(1-percentage)) LOOP
    FETCH emp_cursor INTO emp_row;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.put_line('-----Delete ' || emp_row.first_name || ' ' || emp_row.last_name);
    DBMS_OUTPUT.put_line('-----His/her salary was ' || emp_row.salary);
    DELETE FROM employees WHERE CURRENT OF emp_cursor;
    salary_after := salary_after-emp_row.salary;
  END LOOP;
  CLOSE emp_cursor;
  DBMS_OUTPUT.put_line('Total after saving: '|| salary_after);
  DBMS_OUTPUT.put_line('Percentage saved: '|| ROUND((1-salary_after/salary_before)*100,2));
  DBMS_OUTPUT.put_line('***********************************************************************');
END LOOP;
EXCEPTION
  WHEN invalid_percentage_exception THEN
    DBMS_OUTPUT.put_line('Please enter an amount between 0 and 1');
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Unknown exception');
END FIREPEOPLE;