Working with REF CURSOR in PL/SQL - Working with RECORD and REF CURSOR
(Page 2 of 4 )
Until now, we have been working either with %TYPE or %ROWTYPE. This means we are working with either one value or one complete record. How do we create our own data type, with our own specified number of values to hold? This is where TYPE and RECORD come in. Let us consider the following example:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp for select ename,sal from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end;
The most confusing aspect from the above program is the following:
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
The above defines a new data type named "rec_emp" (just like %ROWTYPE with limited specified fields) which can hold two fields, namely "name" and "sal."
er rec_emp;
The above statement declares a variable "er" based on the datatype "rec_emp." This means that "er" internally contains the fields "name" and "job."
fetch c_emp into er;
The above statement pulls out a row of information (in this case "ename" and "sal") and places the same into the fields "name" and "sal" of the variable "er." Finally, I display both of those values using the following statement:
dbms_output.put_line(er.name || ' - ' || er.sal);
Next: Working with more than one query with the same REF CURSOR >>
More Oracle Articles
More By Jagadish Chatarji
