Oracle PL/SQL Tutorial - PL/SQL Packages

• « Previous
• Next »

In practice, you'll rarely create a stand-alone stored function or procedure.

Instead, you'll use a package.

A package can organize related functions and procedures together, like creating a library, but in PL/SQL the library is called a package.

A PL/SQL package has two parts:

• A package specification
• A package body

A package specification is the public interface to the package.

It lists any globally accessible constants, cursors, functions, procedures, and variables.

The package specification describes what you can read from that library, while the package body contains the that implements the package specification.

Create a Package Specification

The following code is an example of a package specification.

The DDL syntax used to create the package specification is as follows:

CREATE [OR REPLACE] PACKAGE <package_name> AS 
    -- one or more: constant, cursor, function, procedure, or variable declarations 
END <package_name>; 

where <package_name> is the name of the package you're creating.

The following code shows how to create a package specification for MY_PACKAGE.

create or replace package MY_PACKAGE as 
    FUNCTION to_number_or_null ( aiv_number in varchar2 ) 
    return number; 
end MY_PACKAGE; 
/ 

The code above generates the following result.

Create a Package Body

The code goes in the package body.

A package body is the implementation for a package specification.

It contains the code for any functions or procedures declared in its corresponding package specification.

The body can also contain any constant, cursor, function, procedure, or variable that should be accessible within the package body which is not publicly accessible.

The DDL syntax used to create the package body is as follows:

CREATE [OR REPLACE] PACKAGE BODY <package_name> AS 
    -- one or more constant, cursor, or variable declarations 
    -- one or more function, or procedure implementations 
[BEGIN] 
    -- a PL/SQL block called an initialization section that is 
    -- executed only once per session
[EXCEPTION] 
    -- an exception-handling section for the initialization section 
END <package_name>; 

where <package_name> is the name of the package body you're creating.

To handle exception for initialization section, add it by using the keyword EXCEPTION.

Your implementations of functions and procedures are embedded functions and procedures in the declaration section of a PL/SQL block!

Any constant, cursor, or variable declared in the declaration section of the package body is accessible to all the declared functions/procedures in that section, but not accessible to other PL/SQL code.

Only the items you declared in the package specification are accessible to other stored procedures.

The following code shows how to create The MY_PACKAGE Package Body.

create or replace package body MY_PACKAGE as 
    FUNCTION to_number_or_null ( aiv_number in varchar2 ) 
    return number is 
    begin --   w  ww. ja  v  a 2  s.  c o m
        return to_number(aiv_number); 
    exception 
        when OTHERS then 
        return NULL; 
    end to_number_or_null; 
end MY_PACKAGE; 
/ 

The code above generates the following result.

• Next »
• « Previous