PL/SQL Cursor Exercises: Create an explicit cursor with for loop

PL/SQL Cursor: Exercise-13 with Solution

Write a program in PL/SQL to create an explicit cursor with for loop.

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

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
    CURSOR emp_cur_detail IS
      SELECT department_name,
             d.department_id,
             first_name,
             last_name,
             job_id,
             salary
      FROM   departments d
             join employees e
               ON e.department_id = d.department_id
      WHERE  job_id = 'ST_CLERK'
             AND salary > 3200;
BEGIN
    FOR emp_rec IN emp_cur_detail LOOP
        dbms_output.Put_line('Name: '
                             ||emp_rec.first_name
                             ||' '
                             ||emp_rec.last_name
                             ||Chr(9)
                             ||'Department Name: '
                             ||emp_rec.department_name
                             ||Chr(9)
                             ||'Department ID: '
                             ||emp_rec.department_id
                             ||Chr(9)
                             ||'Job ID: '
                             ||emp_rec.job_id
                             ||Chr(9)
                             ||'Salary: '
                             ||emp_rec.salary);
    END LOOP;
END; 
/

Sample Output:

SQL> /
Name: Laura Bissot      Department Name: Shipping       Department ID: 50       Job ID: ST_CLERK     Salary: 3300
Name: Trenna Rajs       Department Name: Shipping       Department ID: 50       Job ID: ST_CLERK     Salary: 3500
Name: Renske Ladwig     Department Name: Shipping       Department ID: 50       Job ID: ST_CLERK     Salary: 3600
Name: Jason Mallin      Department Name: Shipping       Department ID: 50       Job ID: ST_CLERK     Salary: 3300

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to create an implicit cursor with for loop.
Next: Create a PL/SQL block to increase salary of employees in the department 50 using WHERE CURRENT OF clause.