PL/SQL Cursor Exercises: Retriev the records from the employees table and display them using cursors

PL/SQL Cursor: Exercise-10 with Solution

Write a program in PL/SQL to retriev the records from the employees table and display them using cursors.

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
  z_empid employees.employee_id%TYPE;
  z_empname employees.first_name%TYPE;
  z_salary employees.salary%TYPE;
  CURSOR employee_cursor IS  -- declaring a cursor
    SELECT employee_id,
           first_name,
           salary
    FROM   employees;

BEGIN
  OPEN employee_cursor;    -- opening the cursor
  LOOP
    FETCH employee_cursor  -- fetching records from the cursor
    INTO  z_empid,
          z_empname,
          z_salary;
    EXIT
  WHEN employee_cursor%NOTFOUND;
    IF (z_salary > 8000) THEN
      dbms_output.Put_line(z_empid
      || '   '
      || z_empname
      || '   '
      || z_salary);
    ELSE
      dbms_output.Put_line(z_empname
      || ' salary is less then 8000');
    END IF;
  END LOOP;
  CLOSE employee_cursor;  --closing the cursor
END;
/

Sample Output:

SQL> /
100   Steven   24000
101   Neena   17000
102   Lex   17000
103   Alexander   9000
Bruce salary is less then 8000
David salary is less then 8000
Valli salary is less then 8000
Diana salary is less then 8000
108   Nancy   12008
109   Daniel   9000
110   John   8200
Ismael salary is less then 8000
Jose Manuel salary is less then 8000
Luis salary is less then 8000
114   Den   11000
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to display a cursor based detail information of employees from employees table.
Next: Write a program in PL/SQL to declare a record datatype with same datatype of tables using %TYPE attribute.