Lab 15.2 Cursor Variables


Lab 15.2 Cursor Variables

Lab Objective

After this Lab, you will be able to:

Make Use of Cursor Variables


Up to this point in this book you have seen cursors used to gather specific data from a single SELECT statement. In Chapter 14, "Packages," you learned how to bring a number of procedures into a large program called a package. A package may have one cursor that is used by a few procedures. In this case, each of the procedures that use the same cursor would have to declare, open , fetch, and close the cursor. In the current version of PL/SQL, cursors can be declared and manipulated like any other PL/SQL variable. This type of variable is called a cursor variable or a REF CURSOR. A cursor variable is just a reference or a handle to a static cursor. It permits a programmer to pass this reference to the same cursor among all the program's units that need access to the cursor. A cursor variable binds the cursor's SELECT statement dynamically at runtime.

Explicit cursors are used to name a work area that holds the information of a multirow query. A cursor variable may be used to point to the area in memory where the result of a multirow query is stored. The cursor always refers to the same information in a work area, while a cursor variable can point to different work areas. Cursors are static, and cursor variables can be seen as dynamic because they are not tied to any one specific query. Cursor variables give you easy access to centralized data retrieval.

You can use a cursor variable to pass the result set of a query between stored procedures and various clients . A query work area remains accessible as long as a cursor variable points to it. So you can freely pass a cursor variable from one scope to another. There are two types of cursor variables; one is called strong and the other is called weak.

To execute a multirow query, the Oracle server opens a work area called a cursor to store processing information. To access the information, you either name the work area, or you use a cursor variable that points to the work area. A cursor always refers to the same work area, and a cursor variable can refer to different work areas. Hence, cursors and cursor variables are not interoperable. An explicit cursor is static and is associated with one SQL statement. A cursor variable can be associated with different statements at runtime. Primarily you use a cursor variable to pass a pointer to query results sets between PL/SQL stored subprograms and various clients, such as a client Oracle Developer Forms application. None of them owns the result set; they simply share a pointer to the query work area that stores the result set. You can declare a cursor variable on the client side, open and fetch from it on the server side, and then continue to fetch from it on the client side.

Cursor variables differ from cursors the way constants differ from variables. A cursor is static; a cursor variable is dynamic. In PL/SQL a cursor variable has a REF CURSOR data type, where REF stands for reference and CURSOR stands for the class of the object. You will now learn the syntax for declaring and using a cursor variable.

To create a cursor variable, you first need to define a REF CURSOR type and then declare a variable of that type.

Before you declare the REF CURSOR of a strong type, you must first declare a record that has the data types of the result set of the SELECT statement that you plan to use (note that this is not necessary for a weak REF CURSOR).

FOR EXAMPLE

 
 TYPE inst_city_type IS RECORD (first_name instructor.first_name%TYPE;  last_name  instructor.last_name%TYPE;  city       zipcode.city%TYPE;  state      zipcode.state%TYPE) 

Second, you must declare a composite data type for the cursor variable that is of the type REF CURSOR. The syntax is as follows :

 
 TYPE ref_type_name is REF CURSOR [RETURN return_type]; 

The ref_type_name is a type specified in subsequent declarations. The return type represents a record type for a strong cursor; a weak cursor does not have a specific return type but can handle any combination of data items in a SELECT statement. The REF CURSOR keyword indicates that the new type will be a pointer to the defined type. The return_type indicates the types of SELECT list that are eventually returned by the cursor variable. The return type must be a record type.

FOR EXAMPLE

 
 TYPE inst_city_cur IS REF CURSOR RETURN inst_city_type; 

A cursor variable can be strong ( restrictive ) or weak (nonrestrictive). A strong cursor variable is a REF CURSOR type definition that specifies a return_type; a weak definition does not. PL/SQL enables you to associate a strong type with type-comparable queries only, while a weak type can be associated with any query. This makes a strong cursor variable less error prone but weak REF CURSORS types more flexible.

These are the key steps for handling a cursor variable:

  1. Define and declare the cursor variable.

    Open the cursor variable. Associate a cursor variable with a multirow SELECT statement, execute the query, and identify the result set. An OPEN FOR statement can open the same cursor variable for different queries. You do not need to close a cursor variable before reopening it. Keep in mind that when you reopen a cursor variable for a different query, the previous query is lost.

  2. Fetch rows from the result set.

    Retrieve rows from the result set one at a time. Note that the return type of the cursor variable must be compatible with the variable named in the INTO clause of the FETCH statement.

    The FETCH statement retrieves rows from the result set one at a time. PL/SQL verifies that the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each query column value returned, there must be a type type-comparable variable in the INTO clause. Also, the number of query column values must equal the number of variables. In case of a mismatch in number or type, the error occurs at compiletime for strongly typed cursor variables and at runtime for weakly typed cursor variables.

  3. Close the cursor variable.

The next example is a complete example showing the use of a cursor variable in a package.

FOR EXAMPLE

 
 -- csh15_18a.sql CREATE OR REPLACE PACKAGE course_pkg AS   TYPE course_rec_typ IS RECORD     (first_name    student.first_name%TYPE,      last_name     student.last_name%TYPE,      course_no     course.course_no%TYPE,      description   course.description%TYPE,      section_no    section.section_no%TYPE      );   TYPE course_cur IS REF CURSOR RETURN course_rec_typ;   PROCEDURE get_course_list     (p_student_id    NUMBER ,      p_instructor_id NUMBER ,      course_list_cv IN OUT course_cur); END course_pkg; CREATE OR REPLACE PACKAGE BODY course_pkg AS   PROCEDURE get_course_list     (p_student_id    NUMBER ,      p_instructor_id NUMBER ,      course_list_cv IN OUT course_cur)   IS   BEGIN     IF p_student_id IS NULL AND p_instructor_id       IS NULL THEN       OPEN course_list_cv FOR         SELECT 'Please choose a student-' First_name,                'instructor combination'   Last_name,           NULL     course_no,           NULL     description,           NULL     section_no           FROM dual;     ELSIF p_student_id IS NULL  THEN       OPEN course_list_cv FOR         SELECT s.first_name    first_name,           s.last_name     last_name,           c.course_no     course_no,           c.description   description,           se.section_no   section_no    FROM   instructor i, student s,           section se, course c, enrollment e    WHERE  i.instructor_id = p_instructor_id      AND  i.instructor_id = se.instructor_id      AND  se.course_no    = c.course_no      AND  e.student_id    = s.student_id      AND  e.section_id    = se.section_id      ORDER BY  c.course_no, se.section_no;     ELSIF p_instructor_id IS NULL  THEN       OPEN course_list_cv FOR            SELECT i.first_name    first_name,           i.last_name     last_name,           c.course_no     course_no,           c.description   description,           se.section_no   section_no    FROM   instructor i, student s,           section se, course c, enrollment e    WHERE  s.student_id = p_student_id      AND  i.instructor_id = se.instructor_id      AND  se.course_no    = c.course_no      AND  e.student_id    = s.student_id      AND  e.section_id    = se.section_id         ORDER BY  c.course_no, se.section_no;         END IF;      END get_course_list; END course_pkg; 

You can pass query results sets between PL/SQL stored subprograms and various clients. This works because PL/SQL and its clients share a pointer to the query work area identifying the result set. This can be done in a client program like SQL*Plus by defining a host variable with a data type of REFCURSOR to hold the query result generated from a REF CURSOR in a stored program. In order to see what is being stored in the SQL*Plus variable, use the SQL*Plus PRINT command. Optionally you can have the SQL*Plus command SET AUTOPRINT ON to display the query results automatically.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net