With 7.2 on up of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine.
The cursor variables are opened with the privileges of the owner of the procedure and behave just like they were completely contained within the pl/sql routine.
A cursor variable holds the memory location of a query work area, rather than the contents of the area.
Declaring a cursor variable creates a pointer. In SQL, a pointer has the data type REF x, where REF is short for REFERENCE and x represents the entity being referenced. A REF CURSOR, then, identifies a reference to a cursor variable. Because many cursor variables might exist to point to many work areas, REF CURSOR can be thought of as a category or data type specifier that identifies many different types of cursor variables. A REF CURSOR essentially encapsulates the results of a query.
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query.
A cursor variable is :
- is like a pointers to result sets
- You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.
- a parameter that you can pass to local and stored subprograms.
- Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval.
- is useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language, such as Java or Visual Basic.
- is useful for communication client server.
- Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote procedure calls.
- If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. You can also reduce network traffic by having a PL/SQL block open or close several host cursor variables in a single round trip.
- more flexible because it is not tied to a specific query.
REF CURSOR type
Define a REF CURSOR type, then declare cursor variables of that type.
DECLARE TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;
REF CURSOR types can be :
- strong (with a return type)
Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns.
- or weak (with no return type).
Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.
Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram.
DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- strong TYPE genericcurtyp IS REF CURSOR; -- weak cursor1 empcurtyp; cursor2 genericcurtyp; my_cursor SYS_REFCURSOR; -- didn't need to declare a new type TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE; dept_cv deptcurtyp; -- declare cursor variable
To avoid declaring the same REF CURSOR type in each subprogram that uses it, you can put the REF CURSOR declaration in a package spec. You can declare cursor variables of that type in the corresponding package body, or within your own procedure or function.
In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to refer to a strongly typed cursor variable, as shown below or to Return a Record Type
DECLARE TYPE EmpRecTyp IS RECORD ( employee_id NUMBER, last_name VARCHAR2(25), salary NUMBER(8,2)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; emp_cv EmpCurTyp; -- declare cursor variable
The OPEN FOR statement:
- associates a cursor variable with a query,
- allocates database resources to process the query,
- identifies the result set,
- and positions the cursor before the first row of the result set.
use them in a procedure
REF CURSOR in the example below reference a cursor variable.
DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec emp%ROWTYPE; sql_stmt VARCHAR2(200); my_job VARCHAR2(15) := 'CLERK'; BEGIN sql_stmt := 'SELECT * FROM emp WHERE job'; OPEN emp_cv FOR sql_stmt; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- process record END LOOP; CLOSE emp_cv; END; /
Use them in a function
FUNCTION "GET_JOBS" RETURN JobsPkg.ref_cursor AS jobs_cursor JobsPkg.ref_cursor; BEGIN OPEN jobs_cursor FOR SELECT job_id, job_title FROM jobs; RETURN jobs_cursor; END;
Pass Cursor Variables As Parameters
You can declare cursor variables as the formal parameters of functions and procedures.
DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; emp empcurtyp; -- after result set is built, process all the rows inside a single procedure -- rather than calling a procedure for each row PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS person employees%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.first_name || ' ' || person.last_name); END LOOP; END; BEGIN -- First find 10 arbitrary employees. OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11; process_emp_cv(emp); CLOSE emp; -- find employees matching a condition. OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%'; process_emp_cv(emp); CLOSE emp; END; /