I'll discus shortly how we can use ref cursor easily, with some easy example which will make a very clear concept about ref cursor. After reading this topic you will be very familiar with the ref cursor. So let's start.
First I'll go for an overview of ref cursor like what is ref cursor? How data is fetched using ref cursor? What is the use of ref cursor? and some key information about ref cursor
The name "Ref Cursor" can be confusing because a ref cursor variable is not a cursor. The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database which acts as a pointer or a handle to a result set on the database. We can use ref cursor as OUT parameter of procedure and Function. Remember ref cursor do not hold actual data like variable or array or oracle explicit cursor. It's just a pointer or a handle to the result set of a query. What does it mean? This point will be clear later in this topic.
Oracle introduces REF CURSOR type in oracle 7.3. Oracle 9i introduced the predefined SYS_REFCURSOR type, that's mean we no longer have to define our own REF CURSOR types and I'll use this SYS_REFCURSOR type in my example. Oracle 11g allows the two-way conversion between ref cursors to DBMS_SQL cursors. Oracle 12c allows implicit statements results, similar to that seen in Transact-SQL. I'll discus this two point later here in another post.
N.B. I used oracle database 11g R2, toad 9.5 and SQL*PLUS for the entire example in this post.The following example shows the syntax of ref cursor.
DECLARE
v_ref_cur sys_refcursor;
BEGIN
OPEN v_ref_cur FOR
'select empno, ename,sal
from emp
where deptno =:deptno '
USING p_deptno;
RETURN v_ref_cur;
END;
Code Anatomy: First I declare a variable v_ref_cur which data type is ref cursor. Then opening the variable with 'OPEN v_ref_cur' and in FOR clause I writing the query. Remember query must be enclosed in single quotation mark.
At this point I'll write a simple function that opens a ref cursor
CREATE OR REPLACE FUNCTION get_emps_info (p_deptno IN NUMBER)
RETURN sys_refcursor
IS
v_ref_cur sys_refcursor;
BEGIN
OPEN v_ref_cur FOR
'select empno, ename, sal from emp
where deptno =:deptno'
USING p_deptno;
RETURN v_ref_cur;
END;
Here v_ref_cur is a variable which data type is ref cursor. Now we can look into v_ref_cur with following code in SQL*PLUS.
N.B. SQL*PLUS refcursor is similar to pl/sql sys_refcursor.
Why SQL*PLUS can't print the v_rc_handle variable now? Because of at this point the ref cursor doesn't hold any data; v_rc_handle just a pointer to the query result set. Our first print command fetched all the rows and then it closes the cursor and when we tried to print the ref cursor again, we got an error because SQL*Plus find the cursor as it is open and couldn't print it again.
Now I'll try it a little bit more details, so that it can be clearer. I'll use some PL/SQL code.
Code Clarification :
v_rc_handle is a variable which will act as handle to our ref cursor and variable v_empno, v_ename , v_mgr v_sal will hold our original value. Whit 'v_rc_handle:= get_dept_emps (20);' this line of code we getting the ref cursor handle. Then in loop we are fetching our data. 'EXIT WHEN v_rc%NOTFOUND;' this line will end the loop and Exit the loop when all record of ref cursor will be fetch
Is it possible to use a oracle ref cursor in a SQL query and How?
Now question is, is it possible to use a ref cursor in a SQL query and How? Answer is yes possible. To achieve it first we need to create a type structure. Since SQL cannot read/access PL/SQL table structures so the type must be a database object.
This emp_tabtyp type is a structure to hold a record. To populate this type with data which coming from the ref cursor we will create a function using our table type emp_tabtyp as follows:
Function populate_emps_info calls the get_emps_info function that opens the ref cursor, then looping through and fetching each row and populating our SQL type structure. When all rows are fetched, the ref cursor is closed and the SQL table structure is passed back from the function. So now we have something in an structure that SQL understands, we should be able to query directly from it..
We've successfully get the ref cursor handle and used it to fetch the data back. Remember again A ref cursors is not a cursor or data holder. Once opened, they are simply a pointer to a query that has yet to fetch data.
So, this is all about ref cursor for now. I think it will help you to understand ref cursor. Enjoy it. If you have any query\comment please leave it in comment box. I'll be very pleased to reply you. Thank you.
