REF CURSOR

Oracle PL-SQL : REF CURSOR

oracle_pl-sql_:_ref_cursor

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 can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units. Ref Cursors are opened with an OPEN FOR statement. In most other ways they behave similar to normal cursors.

This feature was introduced with PL/SQL v2.3 (Oracle 7.3).

Create a function that opens a cursor and returns a reference to it:

CREATE OR REPLACE FUNCTION func_refcur RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR select * from dual;
  RETURN c;
END;
/

Call the above function and fetch all rows from the cursor it returns:

set serveroutput on
DECLARE
 c SYS_REFCURSOR;
 v VARCHAR2(1);
BEGIN
 c := func_refcur();   -- Get ref cursor from function
 LOOP 
   FETCH c into v;
   EXIT WHEN c%NOTFOUND;
   dbms_output.put_line('Value from cursor: '||v);
 END LOOP;
END;
/

Ref Cursor is of two types

  1. Strong ref cursor (also called as static structure type) :

    When return type included then it is called strong or static structure type.

    Static ref cursor supports different type of select statement but all of same structure, but not necessary that the table should be same.

  2. Weak ref cursor (also called as dynamic structure type) :

    This ref cursor allows us to any type of select statement irrespective of data structure .i.e any table.

Syntax :

type  is ref cursor [ return  ];

Syntax for open statement :

open  for select statement . . . .;


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.