PL/SQL Cursor Exercises: Display the last name, first name and overpaid amount by using parameters

PL/SQL Cursor: Exercise-37 with Solution

Write a PL/SQL block to display the last name, first name and overpaid amount by using parameters.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
  CURSOR emp_cur (emp_job_nm VARCHAR2, job_max_sal NUMBER) IS
    SELECT last_name, first_name, (salary - job_max_sal) overpayment
    FROM employees
    WHERE job_id = emp_job_nm
    AND salary > job_max_sal
    ORDER BY salary;
 
  PROCEDURE emp_excesspaid  IS
    last_name_   employees.last_name%TYPE;
    first_name_  employees.first_name%TYPE;
    paid_excess  employees.salary%TYPE;
  BEGIN
    LOOP
      FETCH emp_cur INTO last_name_, first_name_, paid_excess;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
        ' (by ' || paid_excess || ')');
    END LOOP;
  END emp_excesspaid;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE('---------------------------------');
  DBMS_OUTPUT.PUT_LINE('Extra Salary paid to Programmers:');
  DBMS_OUTPUT.PUT_LINE('---------------------------------');
  OPEN emp_cur('IT_PROG', 6000);
  emp_excesspaid; 
  CLOSE emp_cur;
 
  DBMS_OUTPUT.PUT_LINE('-----------------------------------');
  DBMS_OUTPUT.PUT_LINE('Extra Salary paid to Stock Manager:');
  DBMS_OUTPUT.PUT_LINE('-----------------------------------');
  OPEN emp_cur('ST_MAN', 5000);
  emp_excesspaid; 
  CLOSE emp_cur;
END;
/

Sample Output:

SQL> /
---------------------------------
Extra Salary paid to Programmers:
---------------------------------
Hunold, Alexander (by 3000)
-----------------------------------
Extra Salary paid to Stock Manager:
-----------------------------------
Mourgos, Kevin (by 800)
Vollman, Shanta (by 1500)
Kaufling, Payam (by 2900)
Weiss, Matthew (by 3000)
Fripp, Adam (by 3200)

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the name of department the their costliest employee.
Next: Write a PL/SQL block to display the number of employees by month.