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

PL/SQL Cursor: Exercise-12 with Solution

Write a program in PL/SQL to create an implicit 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:

BEGIN
    FOR emprec IN(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 > 3000) LOOP
        dbms_output.Put_line('Name: '
		                     ||emprec.first_name
                             ||' '
							 ||emprec.last_name||chr(9)
							 ||' Department: '
                             ||emprec.department_name||chr(9)
                             ||' Department ID: '
                             ||emprec.department_id||chr(9)
                             ||' Job ID: '
                             ||emprec.job_id||chr(9)
                             ||' Salary: '
                             ||emprec.salary);
    END LOOP;
END; 
/

Sample Output:

SQL> /
Name: Julia Nayer        Department: Shipping    Department ID: 50       Job ID: ST_CLERK        Salary: 3200
Name: Laura Bissot       Department: Shipping    Department ID: 50       Job ID: ST_CLERK        Salary: 3300
Name: Jason Mallin       Department: Shipping    Department ID: 50       Job ID: ST_CLERK        Salary: 3300
Name: Curtis Davies      Department: Shipping    Department ID: 50       Job ID: ST_CLERK        Salary: 3100
Name: Stephen Stiles     Department: Shipping    Department ID: 50       Job ID: ST_CLERK        Salary: 3200
Name: Trenna Rajs        Department: Shipping    Department ID: 50       Job ID: ST_CLERK        Salary: 3500
Name: Renske Ladwig      Department: Shipping    Department ID: 50       Job ID: ST_CLERK        Salary: 3600

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to declare a record datatype with same datatype of tables using %TYPE attribute.
Next: Write a program in PL/SQL to create an explicit cursor with for loop.