Lab 15.2 Exercises

15.2.1 Make Use of Cursor Variables


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.


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


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


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.


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

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: