PL/SQL Cursor Exercises: Display the last name, first name and overpaid amount by adding formal parameters
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.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics