ref cursor examples

PL/SQL Ref Cursors examples

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.

A ref cursor is technically same as cursor and can be processed in the same fashion at the most basic level.

A ref cursor is defined at runtime and acts as a pointer to the result set of the select query with it is opened dynamically.

Ref Cursor can be passed/returned to another PL/SQL routine (function or procedure) or even can be returned to client from the Oracle Database Server.

Ref Cursors can be further categorized in 2 parts
1) Strongly Typed Ref Cursor
2) Weakly Typed Ref Cursor

When a return type is included while defining Ref Cursor it called Strongly Typed Ref Cursor. Structure of Strongly Typed Ref Cursor is known at the compile time and can only be associated with queries which return result-set of same structure.

Example of Strongly Typed Ref Cursor

create or replace function f_emp_Dept (p_deptno in number)
return sys_refcursor
is
type rec_row is record
(
employee_id employees.employee_id%type,
first_name employees.first_name%type,
manager_if employees.manager_id%type,
salary employees.salary%type,
department_name departments.department_name%type
);

type t_str_cursor is ref cursor return rec_row;

v_cur t_str_cursor;
begin
open v_cur for
select e.employee_id,e.firsT_name, e.manager_id,e.salary,d.department_name
from employees e, departments d
where d.department_id =e.department_id and d.department_id = p_deptno;

return v_cur;
end;
/

select f_emp_dept(90) from dual;

Weakly Typed Ref Cursor do not have return type. Weakly Typed ref cursor gives us great flexibility and can be associated with any query.

They can be directly created with predefined SYS_REFCURSOR type.

Example of Weakly Typed Ref Cursor

create or replace function f_emp_dept(p_deptno in number) return sys_refcursor is
v_cur sys_refcursor;
begin
open v_cur for
select e.employee_id, e.first_name, e.manager_id, e.salary, d.department_name
from employees e, departments d
where d.department_id = e.department_id
and d.department_id = p_deptno;

return v_cur;
end;
/

select f_get_emp_by_dept(90) from dual;