Lab 15.2 Cursor Variables

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 15.  Advanced Cursors


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).

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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.

Lab 15.2 Exercises

15.2.1 Make Use of Cursor Variables

a)

Take a look at the previous example and explain why the package has two different TYPE declarations. Also explain how the procedure get_course_list is making use of the cursor variable.

b)

Create a SQL*Plus variable that is a cursor variable type.

c)

Execute the procedure course_pkg.get_course_list, with three different types of variable combinations to show the three possible results sets. After you execute the procedure, display the values of the SQL*Plus variable you declared in question (a).

d)

Create another package called student_info_pkg that has a single procedure called get_student_info. The get_student_info package will have three parameters. The first one is the student_id and the second is a number called p_choice; the last is a weak cursor variable. The p_choice indicates what information will be delivered about the student. The p_choice indicates what information will deliver about the student. If it is 1, then return the information about the student from the STUDENT table. If it is 2, then list all the courses the student is enrolled in with the student names of the fellow students enrolled in the same section as the student with the student_id that was passed in. If it is 3, then return the instructor name for that student, with the information about the courses that the student is enrolled in.

e)

Run the get_student_info procedure in SQL*Plus and display the results.

Lab 15.2 Exercise Answers

15.2.1 Answers

a)

Take a look at the previous example and explain why the package has two different TYPE declarations. Also explain how the procedure get_course_list is making use of the cursor variable.

A1:

Answer: In script ch15_18a there are two declarations of a TYPE in the package header. The first is for the record type course_rec_type. This record type is declared to define the result set of the SELECT statements that will be used for the cursor variable. When data items in a record do not match a single table, it is necessary to create a record type. The second TYPE declaration is for the cursor variable also known as REF CURSOR. The variable has the name, course_cur, and it is declared as a strong cursor, meaning that it can only be used for a single record type. The record type is, course_rec_type. The procedure get_course_list in the course_pkg is made so that it can return a cursor variable that holds three different result sets. Each of the result sets is of the same record type. The first type is for when both IN parameters of student ID and instructor ID are null. This will produce a result set that is a message, 'Please choose a student-instructor combination.' The next way the procedure runs is if the instructor_id is passed in but the student_id is null (note that the logic of the procedure is a reverse negative; saying in the second clause of the IF statement p_student_id IS NULL, means when the instructor_id is passed in). This will run a SELECT statement to populate the cursor variable that holds a list of all the courses this instructor teaches and the students enrolled in these classes. The last way this can run is for a student_id and no instructor_id. This will produce a result set of all the courses the student is enrolled in and the instructors for each section. Also note that while the cursor variable is opened it is never closed.

b)

Create a SQL*Plus variable that is a cursor variable type.

A2:

 SQL> VARIABLE course_cv REFCURSOR 

c)

Execute the procedure course_pkg.get_course_list, with three different types of variable combinations to show the three possible results sets. After you execute the procedure, display the values of the SQL*Plus variable you declared in question (a).

A3:

Answer: There are three ways to execute this procedure. The first way would be to pass a student ID and not an instructor ID.

 SQL> exec course_pkg.get_course_list(102,       NULL, :course_cv);  PL/SQL procedure successfully completed.  SQL> print course_cv  FIRST_NAME LAST_NAME   COURSE_NO DESCRIPTION            SECTION_NO  ---------- ---------- ---------- ---------------------- ---------- Charles    Lowry              25 Intro to Programming            2  Nina       Schorin            25 Intro to Programming            5 

The next method would be to pass an instructor ID and not a student ID.

 SQL> exec course_pkg.get_course_list(NULL, 102,   :course_cv);  PL/SQL procedure successfully completed.  SQL> print course_cv  FIRST_NAME   LAST_NAME    COURSE_NO DESCRIPTION              SECTION_NO  ------------ ------------ --------- ------------------------ ---------- Jeff         Runyan              10 DP Overview                      2  Dawn         Dennis              25 Intro to Programming             4  May          Jodoin              25 Intro to Programming             4  Jim          Joas                25 Intro to Programming             4  Arun         Griffen             25 Intro to Programming             4  Alfred       Hutheesing          25 Intro to Programming             4  Lula         Oates              100 Hands-On Windows                 1  Regina       Bose               100 Hands-On Windows                 1  Jenny        Goldsmith          100 Hands-On Windows                 1  Roger        Snow               100 Hands-On Windows                 1  Rommel       Frost              100 Hands-On Windows                 1  Debra        Boyce              100 Hands-On Windows                 1  Janet        Jung               120 Intro to Java Programming        4  John         Smith              124 Advanced Java Programming        1  Charles      Caro               124 Advanced Java Programming        1  Sharon       Thompson           124 Advanced Java Programming        1  Evan         Fielding           124 Advanced Java Programming        1  Ronald       Tangaribuan        124 Advanced Java Programming        1  N            Kuehn              146 Java for C/C++ Programmers       2  Derrick      Baltazar           146 Java for C/C++ Programmers       2  Angela       Torres             240 Intro to the Basic Language      2 

The last method would be not to pass either the student ID or the instructor ID.

 SQL> exec course_pkg.get_course_list(NULL, NULL,      :course_cv);  PL/SQL procedure successfully completed.  SQL>  print course_cv  FIRST_NAME               LAST_NAME            C DESCRIPTION      S  ----------------------- ------------------------- - --------------- Please choose a student-  instructor combination 
d)

Create another package called student_info_pkg that has a single procedure called get_student_info. The get_student_info package will have three parameters. The first one is the student_id and the second is a number called p_choice; the last is a weak cursor variable. The p_choice indicates what information will be delivered about the student. The p_choice indicates what information will deliver about the student. If it is 1, then return the information about the student from the STUDENT table. If it is 2, then list all the courses the student is enrolled in with the student names of the fellow students enrolled in the same section as the student with the student_id that was passed in. If it is 3, then return the instructor name for that student, with the information about the courses that the student is enrolled in.

A4:

 CREATE OR REPLACE PACKAGE student_info_pkg AS    TYPE student_details IS REF CURSOR;    PROCEDURE get_student_info      (p_student_id    NUMBER ,       p_choice        NUMBER ,       details_cv IN OUT student_details);  END student_info_pkg;  CREATE OR REPLACE PACKAGE BODY student_info_pkg AS    PROCEDURE get_student_info      (p_student_id    NUMBER ,       p_choice        NUMBER ,       details_cv IN OUT student_details)  IS  BEGIN    IF p_choice = 1  THEN      OPEN details_cv FOR        SELECT s.first_name     first_name,               s.last_name      last_name,               s.street_address address,               z.city           city,               z.state          state,               z.zip            zip         FROM  student s, zipcode z        WHERE  s.student_id = p_student_id          AND  z.zip = s.zip;    ELSIF p_choice = 2 THEN      OPEN details_cv  FOR        SELECT c.course_no     course_no,               c.description   description,               se.section_no   section_no,               s.first_name    first_name,               s.last_name     last_name        FROM   student s,  section se,               course c, enrollment e        WHERE  se.course_no    = c.course_no          AND  e.student_id    = s.student_id          AND  e.section_id    = se.section_id          AND  se.section_id in (SELECT e.section_id                                   FROM student s,                                        enrollment e                                  WHERE s.student_id =                                        p_student_id                                    AND  s.student_id =                                         e.student_id)     ORDER BY  c.course_no;    ELSIF p_choice = 3 THEN      OPEN details_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_student_info;  END student_info_pkg; 
e)

Run the get_student_info procedure in SQL*Plus and display the results.

A5:

 SQL> VARIABLE student_cv REFCURSOR  SQL> execute student_info_pkg.GET_STUDENT_INFO       (102, 1, :student_cv);  PL/SQL procedure successfully completed.  SQL>  print student_cv  FIRST_ LAST_NAM ADDRESS            CITY            ST ZIP  ------ -------- ------------------ --------------- -- ----- Fred   Crocitto 101-09 120th St.   Richmond Hill   NY 11419  SQL> execute student_info_pkg.GET_STUDENT_INFO                           (102, 2,  :student_cv);  PL/SQL procedure successfully completed.  SQL> print student_cv  COURSE_NO DESCRIPTION          SECTION_NO FIRST_NAME LAST_NAME  ---------- ------------------ ---------- ---------- -----------         25 Intro to Programming          2 Fred       Crocitto          25 Intro to Programming          2 Judy       Sethi          25 Intro to Programming          2 Jenny      Goldsmith          25 Intro to Programming          2 Barbara    Robichaud          25 Intro to Programming          2 Jeffrey    Citron          25 Intro to Programming          2 George     Kocka          25 Intro to Programming          5 Fred       Crocitto          25 Intro to Programming          5 Hazel      Lasseter          25 Intro to Programming          5 James      Miller          25 Intro to Programming          5 Regina     Gates          25 Intro to Programming          5 Arlyne     Sheppard          25 Intro to Programming          5 Thomas     Edwards          25 Intro to Programming          5 Sylvia     Perrin          25 Intro to Programming          5 M.         Diokno          25 Intro to Programming          5 Edgar      Moffat          25 Intro to Programming          5 Bessie     Heedles          25 Intro to Programming          5 Walter     Boremmann          25 Intro to Programming          5 Lorrane    Velasco  SQL> execute student_info_pkg.GET_STUDENT_INFO                              (214,  3,   :student_cv);  PL/SQL procedure successfully completed.  SQL> print student_cv  FIRST_NAME LAST_NAME   COURSE_NO   DESCRIPTION       SECTION_NO  ---------- ------------ ---------- --------------------------- Marilyn    Frantzen      120 Intro to Java Programming        1  Fernand    Hanks         122 Intermediate Java Programming    5  Gary       Pertez        130 Intro to Unix                    2  Marilyn    Frantzen      145 Internet Protocols               1 

Rules for Using Cursor Variables

  • You cannot use cursor variables with remote subprograms on another server.

  • Do not use FOR UPDATE with OPEN FOR in processing a cursor variable.

  • You cannot use comparison operators to test cursor variables.

  • A cursor variable cannot be assigned a null value.

  • A REF CURSOR types cannot be used in a CREATE TABLE or VIEW statements.

  • A stored procedure that uses a cursor variable can only be used as a query block data source; it cannot be used for a DML block data source. Using a ref cursor is ideal for queries that are dependent only on variations in SQL statements and not PL/SQL.

Lab 15.2 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 15.2.

1)

The main benefit of using parameters with cursors is that it makes the cursor reusable.

  1. _____ True

  2. _____ False

2)

Which of the following are acceptable types of parameters to be used with cursors?

  1. _____ IN

  2. _____ OUT

  3. _____ %ROWTYPE

  4. _____ IN OUT

3)

By adding the keywords FOR UPDATE at the end of a cursor, you are

  1. _____ simply alerting the DBA that you are updating a table.

  2. _____ freeing up rollback segments for the update.

  3. _____ locking the indicated rows for an update.

  4. _____ creating a bind variable.

4)

Adding the keywords WHERE CURRENT OF to a FOR UPDATE cursor causes which of the following to take place?

  1. _____ The DBA gets annoyed.

  2. _____ Rows are locked and unlocked one at a time.

  3. _____ The update occurs for the current record in the cursor.

  4. _____ The scope of the cursor is increased.

5)

The principal difference between a FOR UPDATE cursor without a WHERE CURRENT OF clause and one with a WHERE CURRENT OF clause is that

  1. _____ without the clause the update needs to have a WHERE clause.

  2. _____ rows are only locked with the extra clause present.

  3. _____ only the items specified in the WHERE CURRENT OF clause are locked.

  4. _____ processing will only occur for the current row of the cursor.


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

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