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.
1 comment:
HI Mr Prashant
I am raising this query for a problem
Wherein, I want to iterate in loop a certain set of statements( I m putting them in a another Sub-Procedure), to be called for a type of 4 Earning Columns(here my earning columns are known well before),
1. These columns are found in my Monthly Pay Transaction Table.
2. They have to be computed in a similar manner for a final Month Pay for all the employees in company
These example I am taking and stating here just for a demo pupose.
Please guide me into a right Solution in Oracle Store Procedure( in OR under Oracle 9i)
My Procedure to get the Solution on my Logic is mentioned below.
Not to Mention I have failed to get the RESULT.
Create or replace procedure test_compute_pay
Begin
declare
Tmp_Earning_amt number ;
Sql_string varchar2(300) ;
Cursor C1
Is
Select emp_id, month_id,earn_1_amt,earn_2_amt,earn3_amt,earn_4 _amt from MonthTran where
Month_id= 6 ; (say it is month june for eg.)
Begin
For I in c1 -- loop for all the employees
Loop
For j 1.. 4 -- loop for the 4 earning column
Loop
Sql_string:= ‘select i.earn_’||to_char(i)||’_amt ’||’ from dual’;
Execute immediate sql_string into tmp_earning_amt ;
------- do computation work in another procedure with the value retrieved (tmp_earning_amt) ;
Compute_month_earning_proc(tmp_earning_amt) ;
End loop ;
End loop ;
End;
Obviously my purpose is not solved,
As while in executing the test_compute_pay procedure the SELECT Statement RUNS into a Run-time Error
Can you please set the SELECT Statement right and correct for me OR else guide me to any other Alternate Simple OR advance method or procedure.
Post a Comment