PL/SQL Package Body

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>];Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, you specify the package name after the CREATE PACKAGE BODY keywords. The schema name is optional. By default, it is your schema.
• Second, the OR REPLACE option replaces the existing package body definition or do nothing if the package body does not exist.
• Third, between the AS and END keywords are the declarations of private items and the implementations of the public items declared in the package specification. Note that you can use either IS or AS keyword.

The following example illustrates how to create the body of the order_mgmt package:

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;
Code language: SQL (Structured Query Language) (sql)

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.

PL/SQL Developer

From PL/SQL Developer, you click the Run Script button to compile the package body.

SQL*Plus

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:

@path_to_packge_body_file
Code language: SQL (Structured Query Language) (sql)

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 /
Code language: SQL (Structured Query Language) (sql)

Calling functions from a package

The following statement calls the get_net_value_by_customer function of the order_mgmt package:

SELECT
  order_mgmt.get_net_value_by_customer(1,2017) sales
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

The result is:

In this tutorial, you have learned how to create a package body using the CREATE PACKAGE BODY statement.