PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.
A package will have two mandatory parts:
• Package specification
• Package body or definition
Package SpecificationThe specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.
All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.
The following code snippet shows a package specification having a single procedure. You can have many global variables defined and multiple procedures or functions inside a package.
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
When the above code is executed at SQL prompt, it produces the following result:
Package created.
Package Body The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.
The CREATE PACKAGE BODY Statement is used for creating the package body. The following code snippet shows the package body declaration for the cust_sal package created above. I assumed that we already have CUSTOMERS table created in our database as mentioned in PL/SQL - Variables chapter.
CREATE OR REPLACE PACKAGE BODY cust_sal AS
PROCEDURE find_sal(c_id customers.id%TYPE) IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;
/
When the above code is executed at SQL prompt, it produces the following result:
Package body created.
Using the Package Elements
The package elements (variables, procedures or functions) are accessed with the following syntax:
package_name.element_name;
Consider, we already have created above package in our database schema, the following program uses the find_sal method of the cust_sal package:
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
When the above code is executed at SQL prompt, it prompt to enter customer ID and when you enter an ID, it displays corresponding salary as follows:
Enter value for cc_id: 1
Salary: 3000
PL/SQL procedure successfully completed.
Example:
The following program provides a more complete package. We will use the CUSTOMERS table stored in our database with the following records:
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 3000.00 |
| 2 | Khilan | 25 | Delhi | 3000.00 |
| 3 | kaushik | 23 | Kota | 3000.00 |
| 4 | Chaitali | 25 | Mumbai | 7500.00 |
| 5 | Hardik | 27 | Bhopal | 9500.00 |
| 6 | Komal | 22 | MP | 5500.00 |
+----+----------+-----+-----------+----------+
THE PACKAGE SPECIFICATION:
CREATE OR REPLACE PACKAGE c_package AS
-- Adds a customer
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);
-- Removes a customer
PROCEDURE delCustomer(c_id customers.id%TYPE);
--Lists all customers
PROCEDURE listCustomer;
END c_package;
/
When the above code is executed at SQL prompt, it creates the above package and displays the following result:
Package created.
CREATING THE PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type)
IS
BEGIN
INSERT INTO customers (id,name,age,address,salary)
VALUES(c_id, c_name, c_age, c_addr, c_sal);
END addCustomer;
PROCEDURE delCustomer(c_id customers.id%type) IS
BEGIN
DELETE FROM customers
WHERE id = c_id;
END delCustomer;
PROCEDURE listCustomer IS
CURSOR c_customers is
SELECT name FROM customers;
TYPE c_list is TABLE OF customers.name%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list.extend;
name_list(counter) := n.name;
dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
END LOOP;
END listCustomer;
END c_package;
/
Above example makes use of nested table which we will discuss in the next chapter. When the above code is executed at SQL prompt, it produces the following result:
Package body created.
USING THE PACKAGE:
The following program uses the methods declared and defined in the package c_package.
DECLARE
code customers.id%type:= 8;
BEGIN
c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
c_package.listcustomer;
c_package.delcustomer(code);
c_package.listcustomer;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
PL/SQL procedure successfully completed
Before we write any code, we need to define what our package actually does; here is a description for
our package:-
• Maintain a bank balance
• Make withdrawals (reduce balance)
• Make deposits (increase balance)
• Query balance
Also, we can define some business rules at this point:-
• Balance cannot be overdrawn
• There should be some kind of limit to how much
can be withdrawn per transaction. We now have the definition of the bank account package, now for its implementation.
First, you need to create the package specification, this is done with the following statement:-
CREATE OR REPLACE PACKAGE package_name
IS|AS
<package-specification>
END [package name]
The name can be almost anything you like, the same restrictions apply as with all identifiers. Try to make the name useful, generally the package name will relate to what its contents relate to, for example, if a package contains functions and procedures for working with bank accounts, such as withdraw and deposit, then a good package
name might be bank_account. The package specification can be anything that would normally appear in the declarative section of a block of code (except for function and procedure bodies, this code is in the body).
The package specification and body can appear in a single file or separate files. The usual practice is to
give the source filename an extension of pkg for a single file or pks (pkh) and pkb for separate files
Looking at the description for our bank account
package, we can see that:-
• We require 3 public functions or procedures:-
1. Withdrawal
2. Deposit
3. Balance query
• We require private data
1. Current balance
2. Maximum allowed per withdrawal
Using packages, we can protect our data; by making data such as balance private and providing procedures or functions to access this data, we are assured that nothing else can modify this data.
Now, let's create our package specification:-
CREATE OR REPLACE PACKAGE bank_account
IS
-- Procedure for making a withdrawal
PROCEDURE withdraw(p_amount IN NUMBER);
-- Procedure for making a deposit
PROCEDURE deposit(p_amount IN NUMBER);
-- Function to query the balance
FUNCTION balance RETURN NUMBER;
END;
That’s it!!! Our Package specification is done. This can be thought of as the public interface for our package, i.e. anything that appears in here is visible and within scope for all programs outside the package. Keeping the package specification in a separate file facilitates modifications later, this means that you can change the body of a package (so long as the spec is not affected) without affecting any programs which use the package. It also allows you to hide your implementation. We now need to create the package body, this is where the actual code appears for all public subprograms as well as any other private objects. You create the body with the following statement:-
CREATE OR REPLACE PACKAGE BODY package_name
IS|AS
<package-body>
END [package name]
As you can see, the only major difference for the body is the inclusion of the word BODY in the first line.
The following code will create our package body:-
CREATE OR REPLACE PACKAGE BODY bank_account
IS
--
-- Private data objects
--
-- Hold account balance, initialised to 0
v_balance NUMBER := 0;
-- Hold maximum withdrawl amount
v_max_withdrawl CONSTANT NUMBER := 250;
--
-- Private functions/procedures
--
-- Print text to screen
PROCEDURE p(p_text IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line(p_text);
END;
--
-- Public function/procedure definitions
--
-- Procedure for making a withdrawal
PROCEDURE withdraw(p_amount IN NUMBER)
IS
l_new_balance NUMBER:= v_balance - p_amount;
BEGIN
IF p_amount > l_max_withdrawal THEN
-- Ensure amount is within limit
p('Withdrawals limited to '||
TO_CHAR(l_max_withdrawl)||
' per transaction');
-- No overdrafts allowed
ELSIF l_new_balance < 0 THEN
-- No overdrafts allowed
p('No overdraft available');
p('Cash available : '
||TO_CHAR(v_balance));
ELSE
v_balance := v_balance - p_amount;
p('Here is your cash!');
END IF;
END withdraw;
-- Procedure for making a deposit
PROCEDURE deposit(p_amount IN NUMBER)
IS
BEGIN
IF p_amount <= 0 THEN
p('Deposit must be at least £1');
ELSE
v_balance := v_balance + p_amount;
p('Thankyou!');
END IF;
END;
-- Function to query the balance
FUNCTION balance RETURN NUMBER
IS
BEGIN
RETURN v_balance;
END;
END;
To make use of this package, you might do the following:-
BEGIN
DBMS_OUTPUT.put_line(BANK_ACCOUNT.balance);
BANK_ACCOUNT.withdraw(100);
BANK_ACCOUNT.deposit(500);
DBMS_OUTPUT.put_line(BANK_ACCOUNT.balance);
BANK_ACCOUNT.withdraw(275);
DBMS_OUTPUT.put_line(BANK_ACCOUNT.balance);
END;
