PL/SQL Packages:

A package is a collection of PL/SQL objects grouped together as one logical name, much like a class in C++ or Java and a Module in Visual Basic. However they are NOT object oriented and do not share all of the characteristics of objects such as inheritance. It does however have a form of encapsulation and polymorphism.

Encapsulation: In older, procedural languages data and code were separate entities. With object-oriented technologies, the data and code are packaged together in entities known as objects. When you create an object, it has its own set of data along with code that can operate on its data. The only thing the developer needs to worry about is the object's public interface. The public interface determines which code and data belonging to that object can be accessed from the outside world. Encapsulation is one of the major advantages of object-oriented programming.

By providing a public interface and hiding the details of an object, developers can change objects so that they perform completely differently without affecting any other code. This works much like your television set does. All that you, the user, know is that you can turn the TV on or off, change the volume, and adjust the color. How the TV actually shows a picture is unknown to you. This allows TV manufactures to change the way televisions work inside without having to teach you a new interface.

Two important principles about Encapsulation:

1. Data is almost always private. I.e. Not accessible to the outside world.

2. The public interface is made up of two types of public methods (function)
A. Accessor: Access to the data. Ex: getLname()
B. Mutator: Change the data. Ex: setLname(‘Smith’)

Polymorphism: With object-oriented technology, you can define different objects with similar public interfaces. It is known as polymorphism when two objects have identical interfaces and potentially different implementations. For instance, you might create an invoice object (form) and a purchase order object (form) such that each has a public "save button" interface (click-event). With this in place, you can hand an object to a generic save routine. When the object is handed (clicked) to the save routine, it can use the code associated with it's own implementation. The object-oriented language determines which method (function) to call based on it’s signature (the number and type or arguments/parameters passed to it).

There are many benefits to using packages:

1. It allows you to group related functions, procedures, shared variables, and cursors.

2. The entire package is loaded when one of it’s functions or procedures is executed, so that the first call is expensive but calls to other functions or procedures in the package are essentially free.

3. Packages allow the creation of private members that can only be accessed within the package. This allows for a form of encapsulation.

Packages are made up of two parts:

1. The package header section. This section is where you declare all of your public functions, procedures, and shared variables. This is the public interface to the package.

2. The package body is where all of your actual code for the functions/procedures declared in the header section are implemented, also you may declare private functions, procedures, variables (data-members) in this area.

The following rules apply to Package bodies:

1. There must be an exact match between the cursor and module headers and their definitions in package header.

2. Do not repeat declaration of variables, exceptions, type, or constants in the header again in the body.

3. Any element declared in the header can be referenced in the body

Example:

CREATE OR REPLACE PACKAGE manage_customers

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER;

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER;

FUNCTION get_balance (

v_customer_number IN customer.customer_number%TYPE

) RETURN NUMBER;

END manage_customers;

CREATE OR REPLACE PACKAGE BODY manage_customers

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER

IRetVal Integer;

BEGIN

INSERT INTO customer (customer_number, lname, fname)

VALUES (customer_seq.NextVal, v_custlast, v_custfirst);

SELECT customer_seq.currval INTO iRetVal FROM DUAL;

RETURN iRetVal;

END;

FUNCTION get_balance (

v_customer_number IN customer.customer_number%TYPE)

RETURN NUMBER

V_balance customer.balance%TYPE;

BEGIN

SELECT NVL(balance,0) INTO v_balance FROM customer
WHERE customer_number = v_customer_number;

RETURN v_balance;

END get_balance;

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER

BEGIN

IF (get_balance(v_cust_nr) <> 0) THEN

RETURN 0; -- Don’t delete a customer with a balance.

END IF;

DELETE FROM customer WHERE customer_number = v_cust_nr;

RETURN 1; -- Success

END;

END manage_customers;

Assume the following is in a file called manage_customers.sql

In SQL*Plus, compile as such:

SQL> @manage_customers.sql

To execute a function in a package do the following.

SQL> VARIABLE g_balance NUMBER;

SQL> EXECUTE :g_balance := manage_customers.get_balance (4);
SQL> PRINT :g_balance;

USING NAMED ARGUMENTS:

SQL> exec :g_balance := manage_customers.get_balance(v_customer_number=>3);

SQL> VARIABLE g_num NUMBER;

SQL> EXECUTE :g_num := manage_customers.add_customer ('Smith', 'John');
SQL> PRINT :g_num ;

So a function or procedure member of a function is called as such: package_name.member_name()

This is basically the same as in C++ and Java.

The above package is a very basic version of what you will have to do in your project, of course you will have to have exception handling and you will need Add, Update, and Delete function or procedures for each table.

Creating Private Objects:

Public members are declared in the Header and are accessible to the outside world. Members not declared in the Header but declared in the Body are NOT visible to the outside world, they are only visible to other members. This is very similar to public vs. private members of a class in C++ or Java.

For example, let’s re-write the above package as follows:

CREATE OR REPLACE PACKAGE manage_customers

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER;

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER;

           FUNCTION update_customer (
                    v_cust_nr IN customer.customer_number%TYPE,
                    v_custlast IN customer.lname%TYPE,
                    v_custfirst IN customer.fname%TYPE,
                    v_street IN customer.street%TYPE,
                    v_city IN customer.city%TYPE,
                    v_state IN customer.state%TYPE,
                    v_zip IN customer.zip_code%TYPE
            ) RETURN BOOLEAN;

-- Function get_balance is no-longer here, but still below, that makes it private

END manage_customers;

CREATE OR REPLACE PACKAGE BODY manage_customers

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER

IRetVal Integer;

BEGIN

INSERT INTO customer (customer_number, lname, fname)

VALUES (customer_seq.NextVal, v_custlast, v_custfirst);

SELECT customer_seq.currval INTO iRetVal FROM DUAL;

RETURN iRetVal;

END;

FUNCTION get_balance (

v_customer_number IN customer.customer_number%TYPE)

RETURN NUMBER

V_balance customer.balance%TYPE;

BEGIN

SELECT balance INTO v_balance FROM customer
WHERE customer_number = v_customer_number;

RETURN v_balance;

END get_balance;

        FUNCTION update_customer (
                v_cust_nr IN customer.customer_number%TYPE,
                v_custlast IN customer.lname%TYPE,
                v_custfirst IN customer.fname%TYPE,
                v_street IN customer.street%TYPE,
                v_city IN customer.city%TYPE,
                v_state IN customer.state%TYPE,
                v_zip IN customer.zip_code%TYPE

) RETURN BOOLEAN IS

IRetVal Integer;

BEGIN

                UPDATE customer
                            SET lname = v_custlast,
                                    fname = v_custfirst,
                                    street = v_street,
                                    city = v_city,
                                    state = v_state,
                                    zip_code = v_zip
                WHERE customer_number = v_cust_nr;

COMMIT;

RETURN TRUE;

           EXCEPTION
                WHEN OTHERS THEN
                   ROLLBACK;

RETURN FALSE;

END;

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER

BEGIN

IF (get_balance(v_cust_nr) <> 0) THEN

RETURN 0; -- Don’t delete a customer with a balance.

END IF;

DELETE FROM customer WHERE customer_number = v_cust_nr;

RETURN 1; -- Success

END;

END manage_customers;

We can no longer call get_balance as we did in the last example, however member function del_customer can still call get_balance, because they belong to the same package. We can still call del_customer from outside the packages as such:

SQL> VARIABLE g_retval NUMBER;

SQL> EXECUTE :g_retval := manage_customers.get_balance (124);
SQL> PRINT :g_retval;

SQL> VARIABLE g_num NUMBER;

SQL> EXECUTE :g_num := manage_customers.del_customer (124);

SQL> PRINT :g_num;

Creating Shared Variables and Cursors:

As I said earlier you can create public package wide variables and cursors by placing them in the header section. Additionally you can have them initialized in the body the first time the package is loaded. To create private package wide variables just declare them in the body.

For example

CREATE OR REPLACE PACKAGE manage_customers

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER;

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER;

-- Public Interface to v_call_count, Accessor Method.

FUNCTION getCallCount RETURN NUMBER;

-- Public Interface to v_call_count, Mutator Method.

PROCEDURE setCallCount (p_num Integer);

p_call_count Integer;

END manage_customers;

CREATE OR REPLACE PACKAGE BODY manage_customers

v_call_count Integer;

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER

IRetVal Integer;

BEGIN

p_call_count := p_call_count +1;

v_call_count := v_call_count +1;

INSERT INTO customer (customer_number, lname, fname)

VALUES (customer_seq.NextVal, v_custlast, v_custfirst);

SELECT customer_seq.currval INTO iRetVal FROM DUAL;

RETURN iRetVal;

END;

FUNCTION get_balance (

v_customer_number IN customer.customer_number%TYPE)

RETURN NUMBER

V_balance customer.balance%TYPE;

BEGIN

SELECT balance INTO v_balance FROM customer
WHERE customer_number = v_customer_number;

RETURN v_balance;

END get_balance;

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER

BEGIN

P_call_count := p_call_count +1;

v_call_count := v_call_count +1;

IF (get_balance(v_cust_nr) <> 0) THEN

RETURN 0; -- Don’t delete a customer with a balance.

END IF;

DELETE FROM customer WHERE customer_number = v_cust_nr;

RETURN 1; -- Success

END;

-- This is an example of a accessor variable.

FUNCTION getCallCount RETURN NUMBER IS

BEGIN

return v_call_count;

END;

-- Public Interface to v_call_count, Mutator Method.

PROCEDURE setCallCount (p_num Integer) IS

BEGIN

IF (p_num >= 0) THEN

v_call_count := p_num;
ELSE

dbms_output.put_line('Invalid Amount for Call Count. Cant be negative');
END IF;

END;

BEGIN -- This is an initialization, it only get's executed once.

p_call_count := 0;

v_call_count := 0;

END manage_customers;

SQL> VARIABLE g_count NUMBER;

SQL> EXECUTE :g_count := manage_customers.p_call_count;

SQL> PRINT :g_count;

SQL> EXECUTE :g_count := manage_customers.v_call_count;

SQL> PRINT :g_count;

SQL> EXECUTE :g_count := manage_customers.getCallCount();

SQL> PRINT :g_count;

SQL> exec manage_customers.setCallCount(10);

-- Notice for the procedure call we don't need a return variable, that is because procedures don't return a value.

PL/SQL procedure successfully completed.

SQL> exec :g_count := manage_customers.getCallCount();

PL/SQL procedure successfully completed.

SQL> print :g_count;

G_COUNT
---------
10

Function/Procedure Overloading (Polymorphism):

We mentioned earlier about polymorphism being one of the characteristics of object orientation. One way that it is implemented in object oriented languages such as C++ & Java is through something called Function Overloading. Basically what this mean is that you can have two or more functions/procedures with the same name so long as the parameters into the function differ enough from each other so that the compiler can determine which version of the function/procedure to call. For example I could create two versions of my del_customer function, one to delete by customer number the other to delete by customer name.

Example:

FUNCTION del_customer (v_cust_nr IN customer.customer_number%TYPE)

FUNCTION del_customer (v_last IN customer.lname%TYPE, v_first IN customer.fname%TYPE)

SQL> VARIABLE g_retval NUMBER;

SQL> EXECUTE :g_retval := manage_customers.del_customer (123); -- Will call the first version.

SQL> EXECUTE :g_retval := manage_customers.del_customer (‘Smith’, ‘John’); -- Will call the second version.

This is known as function overloading.

For example

CREATE OR REPLACE PACKAGE manage_customers

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER;

-- Function del_customer is overloaded (Polymorphism)

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER;

-- Function del_customer is overloaded (Polymorphism)

FUNCTION del_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER;

-- Public Interface to v_call_count, Accessor Method.

FUNCTION getCallCount RETURN NUMBER;

-- Public Interface to v_call_count, Mutator Method.

PROCEDURE setCallCount (p_num Integer);

p_call_count Integer;

END manage_customers;

CREATE OR REPLACE PACKAGE BODY manage_customers

v_call_count Integer;

FUNCTION add_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER

IRetVal Integer;

BEGIN

p_call_count := p_call_count +1;

v_call_count := v_call_count +1;

INSERT INTO customer (customer_number, lname, fname)

VALUES (customer_seq.NextVal, v_custlast, v_custfirst);

SELECT customer_seq.currval INTO iRetVal FROM DUAL;

RETURN iRetVal;

END;

FUNCTION get_balance (

v_customer_number IN customer.customer_number%TYPE)

RETURN NUMBER

V_balance customer.balance%TYPE;

BEGIN

SELECT balance INTO v_balance FROM customer
WHERE customer_number = v_customer_number;

RETURN v_balance;

END get_balance;

-- Function del_customer is overloaded (Polymorphism)

FUNCTION del_customer (

v_cust_nr IN customer.customer_number%TYPE

) RETURN NUMBER

BEGIN

P_call_count := p_call_count +1;

v_call_count := v_call_count +1;

IF (get_balance(v_cust_nr) <> 0) THEN

RETURN 0; -- Don’t delete a customer with a balance.

END IF;

DELETE FROM customer WHERE customer_number = v_cust_nr;

RETURN 1; -- Success

END;

-- Function del_customer is overloaded (Polymorphism)

FUNCTION del_customer

(

v_custlast IN customer.lname%TYPE,

v_custfirst IN customer.fname%TYPE

) RETURN NUMBER

BEGIN

P_call_count := p_call_count +1;

/** We don't have the v_cust_nr in this version of del_customer, so for now we will comment it out.

IF (get_balance(v_cust_nr) <> 0) THEN

RETURN 0; -- Don’t delete a customer with a balance.

END IF;

**/

DELETE FROM customer

WHERE lname = v_custlast AND fname = v_custfirst;

RETURN 1; -- Success

END;

-- This is an example of a accessor variable.

FUNCTION getCallCount RETURN NUMBER IS

BEGIN

return v_call_count;

END;

-- Public Interface to v_call_count, Mutator Method.

PROCEDURE setCallCount (p_num Integer) IS

BEGIN

v_call_count := p_num;

END;

BEGIN -- This is an initialization, it only get's executed once.

p_call_count := 0;

v_call_count := 0;

END manage_customers;

SQL> desc user_source;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)

SQL> select distinct name, type from user_source;

NAME                                         TYPE
------------------------------ -            -----------
GET_BALANCE                         FUNCTION
GET_BALANCE_PROC             PROCEDURE
MANAGE_CUSTOMERS         PACKAGE
MANAGE_CUSTOMERS         PACKAGE BODY

SQL> SET PAGESIZE 0;
SQL> SELECT text FROM user_source WHERE name = 'MANAGE_CUSTOMERS' AND TYPE='PACKAGE';

Summary:

1. Packages:
A Header: Public members and interfaces
B. Body: Implementation of Public interfaces as well as private members and methods.

2. Encapsulation: Private data and Public interfaces - (accessor/mutator)

3. Polymorphism: Method (function/procedure) overloading.