Lab 14.1 The Benefits of Utilizing Packages

Team-Fly    

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


Lab Objectives

After this Lab, you will be able to:

  • Create Package Specifications

  • Create Package Bodies

  • Call Stored Packages

  • Create Private Objects

  • Create Package Variables and Cursors

There are numerous benefits of using packages as a method to bundle your functions and procedures, the first being that a well-designed package is a logical grouping of objectssuch as functions, procedures, global variables, and cursors. All of the code (parse tree and pseudocode [p-code]) is loaded on the first call of the package. This means that the first call to the package is very expensive (involves a lot of processing on the server), but all subsequent calls will result in an improved performance. Packages are therefore often used in applications where procedures and functions are used repeatedly.

There is also an additional level of security using packages. When a user executes a procedure in a package (or stored procedures and functions), the procedure operates with the same permissions as its owner. Packages also allow the creation of private functions and procedures, which can only be called from other functions and procedures in the package. This enforces information hiding. The structure of the package also encourages top-down design.

The Package Specification

The package specification contains information about the contents of the package, but not the code for the procedures and functions. It also contains declarations of global/public variables. Anything placed in the declarative section of a PL/SQL block may be coded in a package specification. All objects placed in the package specification are called public objects. Any function or procedure not in the package specification but coded in a package body is called a private function or procedure.

The Package Body

The package body contains the actual executable code for the objects described in the package specification. The package body contains code for all procedures and functions described in the specification and may additionally contain code for objects not declared in the specification; the latter type of packaged object is invisible outside the package and is referred to as hidden. When creating stored packages, the package specification and body can be compiled separately.

Rules for the Package Body

There are a number of rules that must be followed in package body code: (1) There must be an exact match between the cursor and module headers and their definitions in package specification; (2) do not repeat declaration of variables, exceptions, type, or constants in the specification again in the body; and (3) any element declared in the specification can be referenced in the body.

Referencing Package Elements

Use the following notation when calling packaged elements from outside of the package: package_name.element.

You do not need to qualify elements when declared and referenced inside the body of the package or when declared in a specification and referenced inside the body of the same package.

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:

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

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

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

Lab 14.1 Exercise Answers

14.1.1 Answers

a)

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

A1:

Answer: The specification for the package manage_students has been compiled into the database. The specification for the package now indicates that there is one procedure and one function. The procedure find_sname requires one IN parameter, which is the student ID, and it returns two OUT parameters, one being the student's first name and the other being the student's last name. The function id_is_good takes in a single parameter of a student ID and returns a Boolean (true or false). Although the body has not yet been entered into the database, the package is still available for other applications. For example, if you included a call to one of these procedures in another stored procedure, that procedure would compile (but would not execute).

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; 
A2:

Answer: The procedure cannot run because only the specification for the procedure exists in the database, not the body. The SQL*Plus session returns the following:

 ERROR at line 1:  ORA-04068: existing state of packages has been discarded  ORA-04067: not executed, package body             "STUDENT.MANAGE_STUDENTS" does not exist  ORA-06508: PL/SQL: could not find program             unit being called  ORA-06512: at line 5 
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.

A3:

 1   CREATE OR REPLACE PACKAGE  school_api as  2        PROCEDURE discount_cost;  3        FUNCTION new_instructor_id  4           RETURN instructor.instructor_id%TYPE;  5    END school_api; 

14.1.2 Answers

a)

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

A1:

Answer: The package body manage_students is compiled into the database. The package contains the procedure manage_students.find_sname, which accepts the parameter student_id and returns the student's last_name and first_name from the Student table.

b)

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

A2:

 -- ch14_5a.sql   1 CREATE OR REPLACE PACKAGE BODY school_api AS   2    PROCEDURE discount_cost   3    IS   4       CURSOR c_group_discount   5       IS   6       SELECT distinct s.course_no, c.description   7         FROM section s, enrollment e, course c   8        WHERE s.section_id = e.section_id   9       GROUP BY s.course_no, c.description,  10                e.section_id, s.section_id  11       HAVING COUNT(*) >=8;  12    BEGIN  14       FOR r_group_discount IN c_group_discount  14       LOOP  15       UPDATE course  16          SET cost = cost * .95  17         WHERE course_no = r_group_discount.course_no;  18         DBMS_OUTPUT.PUT_LINE  19           ('A 5% discount has been given to'  20           ||r_group_discount.course_no||'  21          '||r_group_discount.description);  22       END LOOP;  23      END discount_cost;  24     FUNCTION new_instructor_id  25        RETURN instructor.instructor_id%TYPE  26     IS  27        v_new_instid instructor.instructor_id%TYPE;  28     BEGIN  29        SELECT INSTRUCTOR_ID_SEQ.NEXTVAL  30          INTO v_new_instid  31          FROM dual;  32        RETURN v_new_instid;  33     EXCEPTION  34        WHEN OTHERS  35         THEN  36          DECLARE  37             v_sqlerrm VARCHAR2(250) :=                     SUBSTR(SQLERRM,1,250);  38          BEGIN  39            RAISE_APPLICATION_ERROR(-20003,  40            'Error in    instructor_id: '||v_sqlerrm);  41          END;  42     END new_instructor_id;  43   END school_api; 

14.1.3 Answers

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?

A1:

Answer: This is a correct PL/SQL block for running the function and the procedure in the package manage_students. If an existing student_id is entered, then the name of the student is displayed. If the id is not valid, then the error message is displayed.

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.

A2:

Answer: Initially the following appears:

 Enter value for v_id:  If you enter "145," then you see:  old 5: IF manage_students.id_is_good(&v_id)  new 5: IF manage_students.id_is_good(145)  old  7: manage_students.find_sname(&&v_id, v_first_name,  new  7: manage_students.find_sname(145, v_first_name,  old  9: DBMS_OUTPUT.PUT_LINE('Student No. '||&&v_id||          ' is '  new 9: DBMS_OUTPUT.PUT_LINE('Student No. '||145||' is '  old 14: ('Student ID: '||&&v_id||' is not in the           database.');  new 14: ('Student ID: '||145||' is not in the           database.');  Student No. 145 is Lefkowitz, Paul  PL/SQL procedure successfully completed. 

The function id_is_good returns TRUE for an existing student_id such as 145. The control then flows to the first part of the IF statement and the procedure manage_students.find_sname finds the first and last name for student_id 145, which happens to be Lefkowitz, Paul.

c)

Create a script testing the cta_api package.

A3:

 SET SERVEROUTPUT ON  DECLARE     V_instructor_id instructor.instructor_id%TYPE;  BEGIN     cta_api.Discount;     v_instructor_id := cta_api.new_instructor_id;     DBMS_OUTPUT.PUT_LINE        ('The new id is: '||v_instructor_id);  END; 

14.1.4 Answers

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?

A1:

Answer: A private function, student_count_privs, and a public procedure, display_ student_count, calling the private function.

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; 
A2:

Answer: Since the private function, student_count_privs, cannot be called from outside the package, you receive an error message as follows:

 ERROR at line 1:  ORA-06550: line 4, column 31:  PLS-00302: component 'STUDENT_COUNT_PRIV'             must be  declared  ORA-06550: line 4, column 3:  PL/SQL: Statement ignored 

It appears as if the private function does not exist. This is important to keep in mind. You can see this can be useful when you are writing PL/SQL packages used by other developers. In order to simplify the package for them, they only need to see the package specification. This way they know what is being passed into the procedures and functions and what is being returned. They do not need to see the inner workings. If a number of procedures make use of the same logic, it may make more sense to put them into a private function called by the procedures.

c)

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

 SET SERVEROUTPUT ON  Execute manage_students.display_student_count; 
A3:

Answer: This is a valid method of running a procedure. A line is displayed indicating the number of students in the database. Note that the procedure in the package manage_ students is using the private function student_count_priv to retrieve the student count.

graphics/intfig07.gif

Note that if you forget to include a procedure or function in a package specification, it becomes private. On the other hand, if you declare a procedure or function in the package specification, and then you do not define it when you create the body, you receive the following error message:

 PLS-00323: subprogram or cursor 'procedure_name' is  declared in a package specification and must be  defined in the package body 
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.

A4:

Answer: Add the following lines to the package body: There is nothing that needs to be added to the package specification, since you are only adding a private object.

 43    FUNCTION get_course_descript_private  44      (i_course_no  course.course_no%TYPE)  45      RETURN course.description%TYPE  46    IS  47       v_course_descript course.description%TYPE;  48    BEGIN  49       SELECT description  50         INTO v_course_descript  51         FROM course  52        WHERE course_no = i_course_no;  53       RETURN v_course_descript;  54    EXCEPTION  55       WHEN OTHERS  56       THEN  57          RETURN NULL;  58    END get_course_descript_private;  59  END school_api; 

14.1.5 Answers

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.

A1:

Answer: Add the following line to the beginning of the package specification:

 1  CREATE OR REPLACE PACKAGE  school_api as  2     v_current_date DATE;  3     PROCEDURE Discount;  4     FUNCTION new_instructor_id  5        RETURN instructor.instructor_id%TYPE;  6   END school_api; 

Add the following to the end of the package body:

 59  BEGIN  60    SELECT trunc(sysdate, 'DD')  61      INTO v_current_date  62      FROM dual;  63  END school_api; 

Lab 14.1 Self-Review Questions

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

Answers appear in Appendix A, Section 14.1.

1)

The main advantages to grouping procedures and functions into packages are (check all that apply):

  1. _____ It follows the trendy object method of programming.

  2. _____ It is a more efficient way of utilizing the processor memory.

  3. _____ It makes greater use of the security privileges of various users.

  4. _____ It is a more efficient method to maximize tablespace storage.

  5. _____ It keeps you on good terms with the DBA.

2)

If user Tashi has SELECT privilege on the student table and user Sonam does not, then Sonam can make use of a procedure created by Tashi to get access to the student table if he has execute privileges on Tashi's procedure.

  1. _____ True

  2. _____ False

3)

All procedures and functions in a package body must be declared in the package specification.

  1. _____ True

  2. _____ False

4)

The initialization section of a package refers to

  1. _____ another term for the package header.

  2. _____ the first part of the package.

  3. _____ the executable code at the end of the package.

  4. _____ the evolutionary rudiments in code that are left over from programming methods of cavemen.

5)

The package specification is merely a formality for other programmers to let them know what parameters are being passed in and out of the procedures and functions. It hides the program logic but in actuality it is not necessary and is incorporated into the package body.

  1. _____ True

  2. _____ False


    Team-Fly    
    Top
     



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

    Similar book on Amazon

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