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

Last update on November 23 2023 09:28:48 (UTC/GMT +8 hours)

PL/SQL Cursor: Exercise-41 with Solution

Write a PL/SQL block to display the last name, first name and overpaid amount by adding formal parameters and specify a default values for the added 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, dt_of_hire DATE DEFAULT '31-DEC-99' ) IS
    SELECT last_name, first_name, (salary - job_max_sal) overpayment
    FROM employees
    WHERE job_id = emp_job_nm
    AND salary > job_max_sal
    AND hire_date > dt_of_hire	
    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 hired after 2005:');
  DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
  OPEN emp_cur('ST_MAN', 5000,'31-DEC-05');
  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)

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to find out the start date for current job of a specific employee.
Next: Write a block in PL/SQL to display the first name, job title and start date of employees.