Ref Cursor: Strongly Typed VS Weakly Typed

A Ref Cursor is a User Defined Type allows you to declare the cursor variable which is independent of select statement. 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 with it is opened dynamically. It does not contain result of the query. 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

SQL> create or replace function f_get_emp_by_dept (p_deptno in number)
  2     return sys_refcursor
  3  is
  4     type my_row is record
  5     (
  6        empno   emp.empno%type,
  7        ename   emp.ename%type,
  8        mgr     emp.mgr%type,
  9        sal     emp.sal%type,
 10        dname   dept.dname%type
 11     );
 12
 13     type t_stronge_cursor is ref cursor return my_row;
 14
 15     v_cur t_stronge_cursor;
 16  begin
 17     open v_cur for
 18        select empno, ename, mgr, sal, dname
 19          from emp, dept
 20         where dept.deptno = emp.deptno and dept.deptno = p_deptno;
 21
 22     return v_cur;
 23  end;
 24  /

Function created.

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 directky created with predefined SYS_REFCURSOR type.

Example of Weakly Typed Ref Cursor

SQL> create or replace function f_get_emp_by_dept(p_deptno in number) return sys_refcursor is
  2    v_cur sys_refcursor;
  3  begin
  4    open v_cur for
  5     select empno, ename, mgr, sal, dname
  6     from    emp, dept
  7     where  dept.deptno = emp.deptno
  8     and    dept.deptno = p_deptno;
  9
 10    return v_cur;
 11  end;
 12  /

Function created.

Let's Develop in Oracle

Ref Cursor: Strongly Typed VS Weakly Typed

1 comment: