The benefits of using packages
Create package specifications
Create package bodies
Call stored packages
Create private objects
The first call to the package is very expensive (it involves a lot of processing on the
server), but all subsequent calls result in improved performance Packages are
often used in applications that use procedures and functions repeatedly.
Packages allow you to incorporate some of the concepts involved in object-oriented
programming
PACKAGE package_name
IS
[declarations of variables and types]
[specifications of cursors]
[specifications of modules]
END [package_name];
CREATE OR REPLACE PACKAGE manage_students
AS
PROCEDURE find_sname(
i_student_id IN student.student_id%TYPE,
o_first_name OUT student.first_name%TYPE,
o_last_name OUT student.last_name%TYPE);
FUNCTION id_is_good(i_student_id IN student.student_id%TYPE)
RETURN BOOLEAN;
END manage_students;
PACKAGE BODY package_name
IS
[declarations of variables and types]
[specification and SELECT statement of cursors]
[specification and body of modules]
[BEGIN
executable statements]
[EXCEPTION
exception handlers]
END [package_name];
There must be an exact match between the cursor and module headers and
their definitions in the package specification.
Do not repeat in the body the declaration of variables, exceptions, types, or
constants in the specification.
Any element declared in the specification can be referenced in the body.
CREATE OR REPLACE PACKAGE BODY FUNCTION id_is_good
manage_students (i_student_id IN
AS student.student_id%TYPE)
PROCEDURE find_sname RETURN BOOLEAN
(i_student_id IN student.student_id%TYPE, IS
o_first_name OUT student.first_name%TYPE, v_id_cnt number;
o_last_name OUT student.last_name%TYPE )
BEGIN
IS
v_student_id student.student_id%TYPE; SELECT COUNT(*)
BEGIN INTO v_id_cnt
SELECT first_name, last_name FROM student
INTO o_first_name, o_last_name WHERE student_id = i_student_id;
FROM student RETURN 1 = v_id_cnt;
WHERE student_id = i_student_id; EXCEPTION
EXCEPTION WHEN OTHERS THEN
WHEN OTHERS THEN RETURN FALSE;
DBMS_OUTPUT.PUT_LINE END id_is_good;
('Error in finding student_id: '||v_student_id);
END manage_students;
END find_sname;
SET SERVEROUTPUT ON
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;
Public elements are elements defined in the package specification.
An object is defined only in the package body, it is private.
CREATE OR REPLACE PACKAGE manage_students
AS
…
PROCEDURE display_student_count;
END manage_students;
Public
CREATE OR REPLACE PACKAGE BODY manage_students
AS
FUNCTION student_count_priv RETURN NUMBER Private Function
IS
v_count NUMBER;
BEGIN
select count(*) into v_count from student;
return v_count;
EXCEPTION
WHEN OTHERS THEN return(0);
END student_count_priv;
PROCEDURE display_student_count Public Procedure
is
v_count NUMBER;
BEGIN
v_count := student_count_priv;
DBMS_OUTPUT.PUT_LINE ('There are '||v_count||' students.');
END display_student_count;
END manage_students;
Run the script
DECLARE
V_count NUMBER;
BEGIN
V_count := Manage_students.student_count_priv;
DBMS_OUTPUT.PUT_LINE(v_count);
END;
Result?
Oracle PL SQL by Example – Chapter 21
