| Lab Objectives After this Lab, you will be able to: 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:  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. |
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.  | 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): _____ It follows the trendy object method of programming. _____ It is a more efficient way of utilizing the processor memory. _____ It makes greater use of the security privileges of various users. _____ It is a more efficient method to maximize tablespace storage. _____ 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. _____ True _____ False
| | 3) | All procedures and functions in a package body must be declared in the package specification. _____ True _____ False
| | 4) | The initialization section of a package refers to _____ another term for the package header. _____ the first part of the package. _____ the executable code at the end of the package. _____ 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. _____ True _____ False
|
|
