PL/SQL Packages – Oracle PL/SQL Tutorial

Oracle PL/SQL packages are modular units of code that group related functions, procedures, and types together. They allow you to organize and encapsulate your code in a reusable and maintainable way. A package is made up of two parts: a specification and a body.

The specification defines the interface to the package. It declares the package’s public functions, procedures, and types, and specifies any constants, exceptions, or cursors used by the package. The specification is compiled and stored in the database, but the code it contains is not executed until it is called from the package body.

The package body contains the implementation of the functions, procedures, and types defined in the specification. It also defines any private functions, procedures, and types that are used internally by the package. The package body is compiled and stored in the database along with the specification.

One of the main benefits of using packages is that they allow you to encapsulate your code and hide the implementation details from the outside world. This makes it easier to maintain your code because you can change the implementation of a package without affecting the code that uses it.

Another benefit of using packages is that they provide a namespace for your code. By grouping related functions, procedures, and types together in a package, you can avoid naming conflicts with other code in the database.

Packages can also improve performance by reducing the number of round trips between the database and the application. Because the package code is compiled and stored in the database, it can be executed more efficiently than ad-hoc SQL statements.

Example

To create a package in Oracle PL/SQL, you first create the specification using the CREATE PACKAGE statement, and then you create the body using the CREATE PACKAGE BODY statement. Here’s an example:

CREATE OR REPLACE PACKAGE my_package
IS
  PROCEDURE my_procedure(p_param1 NUMBER, p_param2 VARCHAR2);
  FUNCTION my_function(p_param1 DATE) RETURN NUMBER;
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package
IS
  PROCEDURE my_procedure(p_param1 NUMBER, p_param2 VARCHAR2)
  IS
  BEGIN
    -- implementation code goes here
  END my_procedure;

  FUNCTION my_function(p_param1 DATE) RETURN NUMBER
  IS
    -- implementation code goes here
  END my_function;
END my_package;

Once you’ve created your package, you can call its functions and procedures just like you would any other function or procedure in PL/SQL. For example:

DECLARE
  result NUMBER;
BEGIN
  result := my_package.my_function(SYSDATE);
  my_package.my_procedure(123, 'abc');
END;

In summary, Oracle PL/SQL packages are a powerful tool for organizing, encapsulating, and reusing your code. By grouping related functions, procedures, and types together in a package, you can improve the maintainability, performance, and scalability of your database applications.

Example

Learn PL/SQL