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:
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?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework