PL/SQL Cursor Exercises: Show the uses of a virtual column in an explicit cursor query

PL/SQL Cursor: Exercise-34 with Solution

Write a PL/SQL block to show the uses of a virtual column in an explicit cursor query.

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  IS
    SELECT employee_id, first_name,
           (salary * .05) sal_hike
    FROM employees
    WHERE job_id LIKE '%_PROG'
    ORDER BY employee_id;
  emp_sal_rec emp_cur%ROWTYPE;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO emp_sal_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (
      'Salary increased for ' || emp_sal_rec.first_name ||
      ' is: ' || emp_sal_rec.sal_hike
    ); 
  END LOOP;
  CLOSE emp_cur;
END;
/

Sample Output:

output

SQL> /
Salary increased for Alexander is: 450
Salary increased for Bruce is: 300
Salary increased for David is: 240
Salary increased for Valli is: 240
Salary increased for Diana is: 210

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to show the uses of a variable in explicit cursor query, and the result set is affected with the value of the variable is incremented after every fetch.
Next: Write a PL/SQL block to display the employee ID, first name, job title and the start date of present job.