PL/SQL Cursor Exercises: FETCH more than one record and single column from a table

PL/SQL Cursor: Exercise-16 with Solution

Write a program in PL/SQL to FETCH more than one record and single column from a table.

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
  v_emp_name VARCHAR2(20);
  CURSOR cur_emp_name IS
    SELECT first_name
    FROM   employees;

BEGIN
  OPEN cur_emp_name;
  LOOP
    FETCH cur_emp_name
    INTO  v_emp_name;
    EXIT
  WHEN cur_emp_name%NOTFOUND;
    dbms_output.put_line('Name of employee: ’
    || v_emp_name);
  END LOOP;
  CLOSE cur_emp_name;
END;
/

Sample Output:

SQL> /
Name of employee: Ellen
Name of employee: Sundar
Name of employee: Mozhe
Name of employee: David
Name of employee: Hermann
Name of employee: Shelli
Name of employee: Amit
Name of employee: Elizabeth

Flowchart:

Go to:

• PL/SQL Cursor Exercises Home. ↩
• PL/SQL Exercises Home ↩

PREV : Write a program in PL/SQL to FETCH single record and single column from a table.
NEXT : Write a program in PL/SQL to FETCH multiple records and more than one columns from the same table.

Improve this sample solution and post your code through Disqus