The Primary purpose of this post is to provide fair idea on the advanced concepts in PL/SQL like REF Cursor. We have given a try and hope to be useful for my audiences.
REF CURSOR
• A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result.
• To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, and then declare cursor variables of that type.
Syntax of the REF Cursor
Define a REF Cursor TYPE:
TYPE ref_type_name IS REF CURSOR
[RETURN {
cursor_name%ROWTYPE
|ref_cursor_name%ROWTYPE
|record_name%TYPE
|record_type_name
|db_table_name%ROWTYPE
}
];
RETURNspecifies the data type of a cursor variable return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row from a cursor or strongly typed cursor variable. You can use the %TYPE attribute to provide the datatype of a previously declared record.
Ø cursor_name
An explicit cursor previously declared within the current scope.
An explicit cursor previously declared within the current scope.
Ø ref_cursor_name
An ref cursor previously declared within the current scope.
An ref cursor previously declared within the current scope.
Ø record_name
A user-defined record previously declared within the current scope.
A user-defined record previously declared within the current scope.
Ø record_type_name
A user-defined record type that was defined using the data type specifies RECORD.
Ø db_table_name
A database table or view, which must be accessible when the declaration is elaborated.
A database table or view, which must be accessible when the declaration is elaborated.
Ø %ROWTYPEA record type that represents a row in a database table or a row fetched from a cursor or strongly typed cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
Ø %TYPE
Provides the datatype of a previously declared user-defined record.
Provides the datatype of a previously declared user-defined record.
Ø type_name
A user-defined cursor variable type that was defined as a REF CURSOR.
A user-defined cursor variable type that was defined as a REF CURSOR.
Cursor_variable_declaration:
cursor_variable_name ref_type_name;
OPEN a REF cursor...
OPEN cursor_variable_name
FOR select_statement;
/*To be sure it's not open already:*/
IF NOT cursor_variable_name%ISOPEN THEN
OPEN cursor_variable_name FOR select_statement;
END IF;
Types of REF CURSOR
Strongly Typed: A REF CURSOR that specifies a specific return type
DECLARE
TYPE EmpCurTyp IS REF CURSOR
RETURN emp%ROWTYPE; -- strongly typed ref cursor
cursor1 EmpCurTyp;
BEGIN
NULL;
END;
Weakly Typed: A REF CURSOR that does not specify the return type
DECLARE
TYPE EmpCurTyp IS REF CURSOR -- Weakly typed ref cursor
cursor1 EmpCurTyp;
BEGIN
NULL;
END;
Three statements to control a cursor variable
· OPEN-FOR
· FETCH
· CLOSE
- OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
- PL/SQL makes sure the return type of the cursor variable is compatible with the INTOclause of the FETCH statement.
Simple Example:
DECLARE
TYPE EmpCurTyp IS REF CURSOR
RETURN emp%ROWTYPE; -- strong cursor
emp1 EmpCurTyp;
PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS
person emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('-----');
DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
LOOP
FETCH emp_cv INTO person;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' || person.ENAME ||' ' || person.JOB);
END LOOP;
END;
BEGIN
OPEN emp1
FOR SELECT *
FROM emp
WHERE ROWNUM < 11;
process_emp_cv(emp1);
CLOSE emp1;
OPEN emp1
FOR SELECT *
FROM emp
WHERE ENAME LIKE 'R%';
process_emp_cv(emp1);
CLOSE emp1;
END;
Difference between REF CURSOR and CURSOR with Example:
DECLARE
TYPE rc IS REF CURSOR;
CURSOR c IS SELECT * FROM dual;
l_cursor rc;
BEGIN
IF (to_char(SYSDATE,'dd') = 30) THEN
OPEN l_cursor FOR SELECT * FROM emp;
ELSIF (to_char(SYSDATE,'dd') = 29) THEN
OPEN l_cursor FOR SELECT * FROM dept;
ELSE
OPEN l_cursor FOR SELECT * FROM dual;
END IF;
OPEN c;
-----
/* some manipulation here */
-----
CLOSE c;
END;
Comparisons
1. Cursor C will always be select * from dual. The ref cursor can be anything.
2. Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).
3. Ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.
Usage Restrictions
The following are restrictions on cursor variable usage.
1. Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null.
2. Null cannot be assigned to a cursor variable.
3. The value of a cursor variable cannot be stored in a database column.
4. Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.
