ORACLE SQL, PL/SQL: ORACLE PACKAGES

How we define PACKAGES in ORACLE? What are the advantages of using Packages?

A Package is a container that may have many functions, procedures and other constructs like variables, constants, PL/SQL types, cursors, and exceptions. within it. It provides useful capabilities such as scoping, interface definition and modular development.
It's generally advisable to put your procedures and functions into packages with well-designed interfaces.

Advantages of Packages

Modularity- Encapsulate related constructs. Modular approach, Encapsulation/hiding of business logic, security, performance improvement, reusability.

Easier Application Design - Code and compile specification and body separately.

Hiding Information - Only the declarations in the Package specification are visible and accessible to application.
- Private constructs in the package body are hidden and inaccessible.
- All coding is hidden in the package body.

Added Functionality - Persistency of variables and cursors.

Better Performance- The entire package is loaded into memory when the package is first referenced.
- There is only one copy in memory for all users.
- The dependency hierarchy is simplified.

Security- Defining private procedures in the package body which can only be used by the package because they aren't exposed in the specification.

Overloading and polymorphism - Multiple subprograms of the same name. Overloading is the ability to define a procedure or function with the same name but different signatures.

Permissions- We only need to grant EXECUTE on a package rather than on several procedures.

It is recommended that you them as the default program unit for PL/SQL subprograms rather than standalone procedures and functions.

Disadvantages of Package –

· More memory may be required on the Oracle database server when using Oracle PL/SQL packages as the whole package is loaded into memory as soon as any object in the package is accessed.

· Updating one of the functions/procedures will invalid other objects which use different functions/procedures since whole package is needed to be compiled.

Example-

Procedure- The following procedure will display the numbers between upper and lower bounds defined by two parameters.

SQL> Create or Replace Procedure DISPLAY_NUMBERS ( p_lower IN NUMBER, p_upper IN NUMBER)
  2  AS
  3  Begin
  4  For i IN p_lower .. p_upper
  5  LOOP
  6  DBMS_OUTPUT.PUT_LINE(i);
  7  End LOOP;
  8  End;
  9  /

Procedure created.

SQL> EXEC DISPLAY_NUMBERS(5,12);
5
6
7
8
9
10
11
12

PL/SQL procedure successfully completed.

Function- The following function will return the difference between upper and lower bounds defined by two parameters.

SQL> Create or Replace Function DIFFERENCE ( p_lower IN NUMBER, p_upper IN NUMBER)
  2  RETURN NUMBER
  3  AS
  4  Begin
  5  RETURN p_upper-p_lower;
  6  End;
  7  /

Function created.

SQL> /*You can call above Function Using DML Statement or BEGIN...END Block*/


SQL> SELECT DIFFERENCE(5,12) From DUAL;

DIFFERENCE(5,12)
----------------
               7

SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('Difference : '||DIFFERENCE(5,19));
  3  END;
  4  /
Difference : 14

PL/SQL procedure successfully completed.

Package- Package allow related code, along with supporting types, variables and cursors, to be grouped together. The package is made up of a specification that defines the external interface of the package, and body that contains all the implementation code. The following code shows how previous procedure and function could be grouped into a package.

A PL/SQL package has two parts: package specification and package body.

· A package specification is the public interface of your applications. The public means the stored function, procedures, types, etc., are accessible from other applications.

· A package body contains the code that implements the package specification.

Package Specification

If the package specification does not contain any stored functions, procedures and no private code is needed, you don’t need to have a package body.
So, we can create Package specification without Package Body.
These packages may contain only type definition and variables declaration. Those variables are known as package data.
The scope of package data is global to applications.

It is important to note that you must compile the package specification before package body.

Package Body

PL/SQL package body contains all the code that implements stored functions, procedures, and cursors listed in the package specification.
The syntax is similar to the package specification except the keyword BODY and the implemented code of package specification.

Referencing PL/SQL package elements

You reference to package elements by using dot notation: Package_Name.Package_Element

Create Package Specification

Create or Replace Package MyPackage

PROCEDURE DISPLAY_NUMBERS( p_lower IN NUMBER, p_upper IN NUMBER);

FUNCTION DISPLAY_DIFFERECE( p_lower IN NUMBER, p_upper IN NUMBER) RETURN NUMBER;

End;

Create Package Body-

Create or Replace Package BODY MyPackage

Procedure DISPLAY_NUMBERS ( p_lower IN NUMBER, p_upper IN NUMBER)

Begin

For i IN p_lower .. p_upper

LOOP

DBMS_OUTPUT.PUT_LINE(i);

End LOOP;

End;

FUNCTION DISPLAY_DIFFERECE( p_lower IN NUMBER, p_upper IN NUMBER) RETURN NUMBER

Begin

RETURN p_upper-p_lower;

End;

Now, let’s see how to Execute a Procedure/Function that is defined in a Package.

SQL> DECLARE
  2  Result Number;
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('PROCEDURE Output');
  5  MYPackage.DISPLAY_NUMBERS(3,9);
  6  DBMS_OUTPUT.PUT_LINE('FUNCTION Output');
  7  Result := MYPackage.DISPLAY_DIFFERECE(4,9);
  8  DBMS_OUTPUT.PUT_LINE('Difference : ' ||Result);
  9  END;
 10  /
PROCEDURE Output
3
4
5
6
7
8
9
FUNCTION Output
Difference : 5

PL/SQL procedure successfully completed.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.

Translate

ORACLE PACKAGES

2 comments: