HomeOracle Page 4 - Database Interaction with PL/SQL, Explicit Cursors
Retrieving More Than One Value With FETCH - Oracle
This article introduces the concept of explicit cursor. We will also examine different approaches to work with explicit cursor. This builds on the concept or cursors, which I looked at in my previous article along with looking at SQL cursor and cursor attributes.
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:
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.