Database Interaction with PL/SQL, Explicit Cursors - Retrieving More Than One Value With FETCH
(Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 36 
