| 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. |
| A2: | Answer: SQL> VARIABLE course_cv REFCURSOR |
| 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 |
| A4: | Answer: 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; |
| A5: | Answer: 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 |
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.