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
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
Related Posts:
- Cursor with Parameters in Oracle
- Difference Between Cursor And Ref Cursor
- View results of refcursor out parameter
- SQL Interview Question Answers
Get Results from REFCURSOR with unknown number of columns in PLSQL
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.
Related Posts:
- Cursor with Parameters in Oracle
- Difference Between Cursor And Ref Cursor
- View results of refcursor out parameter
- SQL Interview Question Answers
Get Results from REFCURSOR with unknown number of columns in PLSQL
Very interesting coll
ReplyDelete