Summary: in this tutorial, you will learn how to create a PL/SQL package body using the
CREATE PACKAGE BODY statement.
Introducing to the PL/SQL package body
A PL/SQL package consists of two parts: package specification and package body.
If the package specification has cursors or subprograms, then the package body is mandatory. Otherwise, it is optional. Both the package body and package specification must be in the same schema.
Every cursor or subprogram declared in the package specification must have a corresponding definition in the package body.
Besides the implementation of the cursors and subprograms in the package specification, a package body may have private items that are accessible only within itself.
A package body can have an initialization part which consists of statements that initialize public variables and do other one-time setup tasks. The initialization part only runs once at the first time the package is referenced. It can also include an exception handler.
Creating a package body
To create a package body, you use the
CREATE PACKAGE BODY as shown below:
CREATE [OR REPLACE] PACKAGE BODY [schema_name.]<package_name> IS | AS declarations implementations; [BEGIN EXCEPTION] END [<package_name>];
In this syntax:
- First, you specify the package name after the
CREATE PACKAGE BODYkeywords. The schema name is optional. By default, it is your schema.
- Second, the
OR REPLACEoption replaces the existing package body definition or do nothing if the package body does not exist.
- Third, between the
ENDkeywords are the declarations of private items and the implementations of the public items declared in the package specification. Note that you can use either
The following example illustrates how to create the body of the
CREATE OR REPLACE PACKAGE BODY order_mgmt AS -- get net value of a order FUNCTION get_net_value( p_order_id NUMBER) RETURN NUMBER IS ln_net_value NUMBER BEGIN SELECT SUM(unit_price * quantity) INTO ln_net_value FROM order_items WHERE order_id = p_order_id; RETURN p_order_id; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE( SQLERRM ); END get_net_value; -- Get net value by customer FUNCTION get_net_value_by_customer( p_customer_id NUMBER, p_year NUMBER) RETURN NUMBER IS ln_net_value NUMBER BEGIN SELECT SUM(quantity * unit_price) INTO ln_net_value FROM order_items INNER JOIN orders USING (order_id) WHERE extract(YEAR FROM order_date) = p_year AND customer_id = p_customer_id AND status = gc_shipped_status; RETURN ln_net_value; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE( SQLERRM ); END get_net_value_by_customer; END order_mgmt;
This package body includes the implementations of the two functions declared in the package specification.
Compiling a package body
The process of compiling a package body is the same as compiling a package specification.
From PL/SQL Developer, you click the Run Script button to compile the package body.
If you use SQL*Plus for compiling and creating a package body, you type forward slash (/) as follows:
Compiling a package body from a file
If you manage package body using files, you can compile the package body using the following command:
In SQL Developer, you click the Run Script button to create a package body from a file.
Similarly, you can use forward slash (/) to compile and create a package body from a file as
SQL > @path_to_package_body_file 2 /
Calling functions from a package
The following statement calls the
get_net_value_by_customer function of the
SELECT order_mgmt.get_net_value_by_customer(1,2017) sales FROM dual;
The result is:
In this tutorial, you have learned how to create a package body using the
CREATE PACKAGE BODY statement.