DBMS Packages

Packages

"A package is a container for other database objects."
A package can hold other database objects such as variables , consatants,cursors,exceptions,procedures,functions and sub-programs.
A package has usually two components, a specification and a body.
A package's specification declares the types (variables of the record type), memory variables, constants, exceptions, cursors, and subprograms that are available for use.
A package's body fully defines cursors, functions, and procedures and thus implements the specification.

Package specification:

The package specification contains:
  • Name of the package
  • Names of the data types of any arguments
  • This declaration is local to the database and global to the package
This means that procedures, functions, variables, constants, cursors and exceptions and other objects, declared in a package are accessible from anywhere in the package.
Therefore, all the information a package needs, to execute a stored subprogram, is contained in the package specifications itself.

Syntax:

Create [or replace] package package_name 
 {is | as} 
    Package_specification 
End package_name; 

Example:

create package operation that contains procedure 'add' and function 'sub'.

CREATE OR REPLACE PACKAGE OPERATION
IS 
  PROCEDURE ADDITION(A IN NUMBER,B IN NUMBER);
  FUNCTION SUB(A IN NUMBER,B IN NUMBER)RETURN NUMBER;
END OPERATION;
/

Output:

Run SQL Command Line
SQL>start D://pac.sql
Package created.

Package Body :

The body of a package contains the definition of public objects that are declared in the specification.
The body can also contain other object declarations that are private to the package.
The objects declared privately in the package body are not accessible to other objects outside the package.
Unlike package specification, the package body can contain subprogram bodies.
After the package is written, debugged, compiled and stored in the database applications can reference the package's types, call its subprograms, use its cursors, or raise its exceptions.

Syntax:

Create [or replace] package body package_name
{is | as} 
   Package_body 
End package_name;  

Example:

Create or replace package body OPERATION
is
 PROCEDURE ADDITION(A IN NUMBER,B IN NUMBER)
 is 
begin
  dbms_output.put_line('addtion of two number :'||ADD(A,B));
end;
 FUNCTION SUB(A IN NUMBER,B IN NUMBER)RETURN NUMBER
 is
   ans number(3);
begin
   ans:=A-B;
   return ans;
end;
end OPERATION;
/

Output:

Run SQL Command Line
SQL>start D://pacbody.sql
Package body created.

Advantages of package:

  • Packages enable the organization of commercial applications into efficient modules. Each package is easily understood and the interfaces between packages are simple, clear and well defined.
  • Packages allow granting of privileges efficiently .
  • A package's public variables and cursors persist for the duration of the session. Therefore all cursors and procedures that execute in this environment can share them .
  • Packages enable the overloading of procedures and functions when required .
  • Packages improve performance by loading multiple objects into memory at once. Therefore, subsequent calls to related subprograms in the package require no i/o .
  • Packages promote code reuse through the use of libraries that contain stored procedures and functions, thereby reducing redundant coding .

Share This Page to Download:

Share to Download

Share This Page on:

Question


Pranshu | 21-Jan-2017 07:10:05 pm

Various DBMS application softwares


Ask Question