Lab 14.1 Exercises


14.1.1 Create Package Specifications

In this exercise, you will learn more about table-based and cursor-based records discussed earlier in the chapter.

Create the following PL/SQL script:

FOR EXAMPLE

 
 -- ch14_1a.sql   1  CREATE OR REPLACE PACKAGE manage_students   2  AS   3    PROCEDURE find_sname   4      (i_student_id IN student.student_id%TYPE,   5       o_first_name OUT student.first_name%TYPE,   6       o_last_name OUT student.last_name%TYPE   7      );   8    FUNCTION id_is_good   9      (i_student_id IN student.student_id%TYPE)  10      RETURN BOOLEAN;  11  END manage_students; 

Answer the following questions:

a)

Type the preceding code into a text file. Then run the script in a SQL*Plus session. Explain what happened .

b)

If the following script was run from a SQL*PLUS session, what would the result be and why?

 
 -- ch14_2a.sql SET SERVEROUTPUT ON DECLARE    v_first_name student.first_name%TYPE;    v_last_name student.last_name%TYPE; BEGIN    manage_students.find_sname       (125, v_first_name, v_last_name);    DBMS_OUTPUT.PUT_LINE(v_first_name' 'v_last_name); END; 
c)

Create a package specification for a package named student_ta_api. The package contains the procedure discount from Chapter 12 and the function new_instructor_id from Chapter 13.


14.1.2 Create Package Bodies

Now we will create the body of the manage_students package, which was specified in the previous section.

FOR EXAMPLE

 
 -- ch14_3a.sql  1  CREATE OR REPLACE PACKAGE BODY manage_students  2  AS  3    PROCEDURE find_sname  4      (i_student_id IN student.student_id%TYPE,  5       o_first_name OUT student.first_name%TYPE,  6       o_last_name OUT student.last_name%TYPE  7       )  8    IS  9     v_student_id  student.student_id%TYPE; 10     BEGIN 11        SELECT first_name, last_name 12          INTO o_first_name, o_last_name 13          FROM student 14         WHERE student_id = i_student_id; 15      EXCEPTION 16        WHEN OTHERS 17        THEN 18          DBMS_OUTPUT.PUT_LINE 19      ('Error in finding student_id: 'v_student_id); 20      END find_sname; 21      FUNCTION id_is_good 22        (i_student_id IN student.student_id%TYPE) 23        RETURN BOOLEAN 24      IS 25        v_id_cnt number; 26      BEGIN 27        SELECT COUNT(*) 28          INTO v_id_cnt 29          FROM student 30         WHERE student_id = i_student_id; 31        RETURN 1 = v_id_cnt; 32      EXCEPTION 33      WHEN OTHERS 34      THEN 35        RETURN FALSE; 36      END id_is_good; 37    END manage_students; 
a)

Type the preceding code into a text file. Then run the script in a SQL*Plus session. Explain what happens.

b)

Create a package body for the package named cta_api that you just created.


14.1.3 Call Stored Packages

Now we will use elements of the manage_student package in another code block.

FOR EXAMPLE

 
 -- ch14_4a.sql DECLARE   v_first_name student.first_name%TYPE;   v_last_name student.last_name%TYPE; BEGIN   IF manage_students.id_is_good(&v_id)   THEN     manage_students.find_sname(&&v_id, v_first_name,        v_last_name);   DBMS_OUTPUT.PUT_LINE('Student No. '&&v_id' is '       v_last_name', 'v_first_name); ELSE    DBMS_OUTPUT.PUT_LINE    ('Student ID: '&&v_id' is not in the database.'); END IF; END; 
a)

The previous example displays how a procedure within a package is executed. What results do you expect if you run this PL/SQL block?

b)

Run the script and see the results. How does this compare with what you expected? Explain what the script is accomplishing line by line.

c)

Create a script testing the cta_api package.


14.1.4 Create Private Objects

Public and Private Package Elements

Public elements are elements defined in the package specification. If an object is defined only in the package body, then it is private.

Private elements cannot be accessed directly by any programs outside of the package.

You can think of the package specification as being a "menu" of packaged items that are available to users; there may be other objects working behind the scenes, but they aren't accessible. They cannot be called or utilized in any way; they are available as part of the internal "menu" of the package and can only be called by other elements of the package.

a)

Replace the last lines of the manage_students package specification with the following and recompile the package specification:

 
 11      PROCEDURE display_student_count; 12   END manage_students; 

Replace the end of the body with the following and recompile the package body:

 
 37   FUNCTION student_count_priv 38     RETURN NUMBER 39    IS 40     v_count NUMBER; 41    BEGIN 42     select count(*) 43     into v_count 44     from student; 45     return v_count; 46    EXCEPTION 47     WHEN OTHERS 48       THEN 49       return(0); 50    END student_count_priv; 51    PROCEDURE display_student_count 52     is 53     v_count NUMBER; 54    BEGIN 55     v_count := student_count_priv; 56     DBMS_OUTPUT.PUT_LINE 57        ('There are 'v_count' students.'); 58    END display_student_count; 59  END manage_students; 

What have you added to the manage_student package?

b)

If you run the following from your SQL*PLUS session, what are the results?

 
 DECLARE    V_count NUMBER; BEGIN    V_count := Manage_students.student_count_priv;    DBMS_OUTPUT.PUT_LINE(v_count); END; 
c)

If you were to run the following, what do you expect to see?

 
 SET SERVEROUTPUT ON Execute manage_students.display_student_count; 
d)

Add a private function to the school_api called get_course_descript_private. It accepts a course.course_no%TYPE and returns a course.description%TYPE. It searches for and returns the course description for the course number passed to it. If the course does not exist or if an error occurs, it returns a NULL.


14.1.5 Create Package Variables and Cursors

The first time a package is called within a user session, the code in the initialization section of the package will be executed if it exists. This is only done once and is not repeated if other procedures or functions for that package are called by the user .

Variables, cursors, and user-defined datatypes used by numerous procedures and functions can be declared once at the beginning of the package and can then be used by the functions and procedures within the package without having to declare them again.

a)

Add a package wide variable called v_current_date to cta_api; additionally, add an initialization section that assigns the current sysdate to the variable v_current_date.




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