PL/SQL Packages

Introduction

Oracle defines packages as "schema objects that aggregate logically related PL/SQL types, variables, and subprograms." 

A package is a schema object that groups PL/SQL types, variables, constants, subprograms, cursors, and exceptions that are logically related. A package is compiled and stored in a database, where its contents can be shared by multiple applications. Wondering how? 

This article describes how to bundle related PL/SQL code and data into a package whose contents are accessible to a wide range of applications.

Let us get started now: 

Package In PL/SQL

A package, as the name suggests, is a grouping/bundling of the related things into a single common unit. The related word is very crucial here. In PL/SQL or any other language, a package offers the same functionality only differentiated by the composition. For example, in java, a package is used to encapsulate the group of classes, interfaces, and sub-packages. In contrast, in PL/SQL, a package is used to wrap up the procedures, functions, exceptions, cursors, and many more. 

In oracle, a package can be defined as an object that is compiled and stored in the database. Once it is compiled and stored, users of the database who have executable permissions on the Oracle database can use the stored packages. 

Benefits Of Using Packages

• Modularity
  A huge application can be built by simply developing modules (or subprograms) that are well-defined and simple to use.
   
• Easier Application Design
  All you need to start building an application is the interface information from the package specs. Without their bodies, you can code and compile specifications. After that, you can construct independent subprograms that use the packages as a reference. You don't have to define the package bodies in their whole until you're ready to finish the application.
   
• Hiding Information
  Packages allow you to disclose your interface details in the package specification while keeping the implementation details hidden in the package body. Some of the benefits of hiding the implementation details in the body:

1. The implementation details can be changed without affecting the application interface.
2. Users of the application are unable to write code that is dependent on implementation specifics that you may choose to alter.

• Additional Functionality
  Public variables and cursors in a package can last for the duration of a session. They can be shared by all of the environment's subprograms. They allow you to keep track of data across transactions without having to save it in the database.
   
• Better Performance
  When you run a package subprogram for the first time, Oracle Database loads the entire package into memory. No disc I/O is required for subsequent invocations of other subprograms in the same package.

Let us discuss the components in packages: 

Recommended topics, procedure call in compiler design, and Tcl Commands in SQL

Components In Packages

There are basically two components in a package: 

• Specification: It's a Package's declaration section.
• Body: It is a Package definition section.

The following picture illustrates the PL/SQL packages: 

Source: OracleTutorial

Let us now understand them one by one: 

Package Specification

The package specification is the interface to the package. It simply DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be used from outside of the package. To put it another way, it contains all information about the package's content but not the code for the subprograms.

Public items are declared in a package specification.

• The scope of a public item is defined by the package's schema. 
• A public item is visible across the schema.
• All objects placed in the specification are called "public objects." Whereas, a “private object'' is any subprogram not in the package specification but coded in the package body.
• A package specification is a self-contained object that may exist in the absence of a package body.

Let us now discuss the syntax for the same: 

Syntax:

CREATE [OR REPLACE] PACKAGE <package_name> 
IS/AS
<subprogram and public_element declaration>
.
.
END <package name>
/

where,

• CREATE OR REPLACE PACKAGE are keywords used to create or replace a package. 
• <package_name>, <procedure_name> are user-defined names. 
• IS/AS are keywords used to declare a package.

Package Body

The package body contains the implementation of the subprograms and cursors declared in the package specification. It should be noted that the subprograms implemented in the package body can be accessed outside the package if they are declared in the package specification.

The subprogram declared in the package specification and body must the same. This comparison is done token-by-token of their headers. In case they do not match, PL/SQL throws an exception.

The package body may contain other subprograms that are not declared in the package specification. In that case, those are private to that package body only. CREATE PACKAGE BODY is the keyword used for creating a package body.

Syntax:

CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<globalDeclaration part>
<PrivateElement definition>
<subProgram and public element definition>
END <package_name>
/

where,

• CREATE OR REPLACE PACKAGE BODY are keywords used to create a package. <package_name> is the user-defined name. 
• IS/AS are keywords used to define a package body.
• <globalDeclaration part> is the global declaration part that includes variables, private elements, cursors, and so on. 

Note: Creating a package simply specifies it; we must refer to it using the package object to use it.

Package refers to an object:

Package_name.object_name;

The Object can be a function, method, cursor, or exception declared in the package specification and defined in the package body, and the above syntax is used to access their executable statements.

Time for an example

Consider the Customers table having all the student's data consisting of columns like ID, Name, Age, City, Salary.

Query says:

Create a package that contains three procedures namely addCustomer, delCustomer, and listCustomer. The addCustomer will take all the required parameters. The delCustomer deletes a customer from the table based on the customer ID and listCustomer will print all the data of the customer.

Package Specification

CREATE OR REPLACE PACKAGE customers_package AS 
  -- Adds a customer 
  PROCEDURE addCustomer(
   customers_id   customers.id%TYPE, 
   customers_name customers.Name%TYPE, 
  customers_age  customers.age%TYPE, 
  customers_city customers.city%TYPE,  
  customers_sal  customers.salary%TYPE); 
  
  -- Removes a customer 
  PROCEDURE delCustomer(customers_id  customers.id%TYPE); 
   --Lists all customers 
  PROCEDURE listCustomer; 
  
END customers_package; 
/

When the above code is executed, it creates the above package and prints the following output: 

Package created. 

Package Body

CREATE OR REPLACE PACKAGE BODY customer_package AS 
  PROCEDURE addCustomer(
  customers_id   customers.id%TYPE, 
   customers_name customers.Name%TYPE, 
  customers_age  customers.age%TYPE, 
  customers_city customers.city%TYPE,  
  customers_sal  customers.salary%TYPE) 
  IS 
  BEGIN 
      INSERT INTO customers (id,name,age,city,salary) 
        VALUES(customers_id, customers_name, customers_age, customers_city, customers_sal); 
  END addCustomer; 
  
  PROCEDURE delCustomer(customers_id   customers.id%TYPE) IS 
  BEGIN 
      DELETE FROM customers 
      WHERE id = customers_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 customer_package; 
/

The above example makes use of the nested table. 

When the above code is executed, it generates the following output:

Package body created.

Using The Package 

DECLARE 
  code customers.id%type:= 8; 
BEGIN 
  customer_package.addcustomer(7, 'Jainish', 25, 'J&k', 55000); 
  customer_package.addcustomer(8, 'Dhruv', 32, 'Surat', 75000); 
  customer_package.listcustomer; 
  customer_package.delcustomer(code); 
  customer_package.listcustomer; 
END; 
/ 

Output:

After applying the above code Customers table will get changed to:

Recommended Topic, DCL Commands in SQL

Advantages of Packages

• REUSABILITY
  A PL package is compiled and stored in the database whenever it is produced. As a result, you only have to develop the code once, and other apps may utilise it.
   
• OVERLOADING
  In a package with the same name, two or more procedures or functions can be constructed.
   
• CREATING MODULES
  A large application can be created by simply creating modules(or subprograms) clearly defined and easy to use.
   
• IMPROVE PERFORMANCE
  During the first call, the package code is loaded into Oracle's SGA (system global area), allowing subsequent calls to run quickly.
   
• GLOBAL DECLARATION
  When objects (procedures, functions, variables, constants, exceptions, cursors, and so on) are defined globally in a package, they can be quickly used.