w3resource

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:

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

Improve this sample solution and post your code through Disqus

Previous: 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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.