PL/SQL package

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.

Packages usually have two parts:


  1. A specification (spec)
  2. A body
  • The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package.
  • A package always has a specification, which declares the public items that can be referenced from outside the package.
  • The body defines the queries for the cursors and the code for the subprograms (Function and Procedure).
  • Enable the Oracle server to read multiple objects into memory at once.
  • You can map a package subprogram to an external Java or C subprogram by using a call specification

Advantages of Using Packages


  • Modularity : Encapsulating related constructs
  • Easier maintenance: Keeping logically related functionality together
  • Better Performance: Oracle load whole package into memory. if you change the body of a package function, Oracle Database does not recompile other subprograms that invoke the function, because these subprograms depend only on the parameters and return value that are declared in the specification.
  • Easier to Grant Roles: You can grant roles on the package, instead of granting roles on each object in the package.
  • Easier application design: Coding and compiling the specification and body separately
  • Hiding information: Only the declarations in the package specification are visible and accessible to applications
  • Private constructs in the package body are hidden and inaccessible
  • All coding is hidden in the package body

Creating package


CREATE OR REPLACE
PACKAGE pkg_sal IS
v_sal NUMBER;
PROCEDURE get_max_sal;
END pkg_sal;

Creating package body


create or replace package body pkg_sal 
is
procedure get_max_sal is
begin
select max(salary) into v_sal from employees;
dbms_output.put_line('Maximum Salary '|| v_sal);
end get_max_sal;
end pkg_sal;

Calling package

begin
pkg_sal.get_max_sal;
end;

Output:

Creating and Using Body less Packages


CREATE OR REPLACE PACKAGE pkg_area IS
c_pie CONSTANT NUMBER := 3.14;
c_km CONSTANT NUMBER := 1000;
END pkg_area;
DECLARE
v_area_circle NUMBER;
v_radius NUMBER :=5;
BEGIN
v_area_circle:= pkg_area.c_pie * v_radius;
dbms_output.put_line('area of circle '||v_area_circle);
END;

Output:

Remove the package specification and body

DROP PACKAGE package_name;

Remove the package body only

DROP PACKAGE BODY package_name;

Viewing Packages Using the Data Dictionary


View the package specification.

SELECT text
FROM user_source
WHERE name = 'COMM_PKG' AND type = 'PACKAGE'
ORDER BY LINE;

View the package body.

SELECT text
FROM user_source
WHERE name = 'COMM_PKG' AND type = 'PACKAGE BODY'
ORDER BY LINE;

Reference Link: https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS99924

Leave a Reply

Your email address will not be published. Required fields are marked *