Retrieving More Than One Value With FETCH - Oracle

In all of the above examples, we are trying to fetch only two columns of values ('empno' and 'ename'). We can retrieve more than one value (or entire row) using FETCH statement. Let us consider the following example to fetch an entire row from FETCH into a single variable.

Declare
  CURSOR c_emp IS
    select * from emp;
  r_emp emp%rowtype;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.empno || ', ' || r_emp.ename);
  End loop;
  CLOSE c_emp;
End;

In the above example we are using 'SELECT *' statement to retrieve all columns of information. 'r_emp' is declared as of type 'emp%rowtype'. That means it can store an entire row with the structure available in the table 'emp'. We directly used only a single variable 'r_emp' with FETCH statement (as it can hold an entire row). We displayed the necessary values using dot notation (as demonstrated in the DBMS_OUTPUT statement).

We can also retrieve only specified columns (without declaring too many variables) using TYPE and RECORD declarations as explained in part-2 of the series. Let us examine that with a simple example:

Declare
  TYPE t_emprec IS RECORD
  (
    ename emp.ename%type,
    sal   emp.sal%type,
    job   emp.job%type
  );
  r_emp   t_emprec;
  CURSOR c_emp IS
    select ename,sal,job from emp;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.ename || ', ' || r_emp.sal || ', ' || r_emp.job);
  End loop;
  CLOSE c_emp;
End;

Another wonder is that we can also declare a variable which is directly based on the structure of an existing cursor as shown in the following example:

Declare
  CURSOR c_emp IS
    select ename,sal,job from emp;
  r_emp c_emp%rowtype;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.ename || ', ' || r_emp.sal || ', ' || r_emp.job);
  End loop;
  CLOSE c_emp;
End;

In the above program the most important declaration is as follows:

r_emp c_emp%rowtype;

The above declaration says that a variable 'r_emp' should have the same structure as of cursor 'c_emp' to hold the values. It can hold an entire row from 'c_emp'. This type of syntax is quite widely used by PL/SQL developers. As there exists several approaches of using explicit cursor, I leave it to the readers to choose the best approach which suits them.