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:
-- 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:
14.1.2 Create Package Bodies
Now we will create the body of the manage_students package, which was specified in the previous section.
-- 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;
14.1.3 Call Stored Packages
Now we will use elements of the manage_student package in another code block.
-- 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;
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.
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.