Oracle Foundation Packages

First, the package

A package is a database object that encapsulates the related plsql types, subroutines, cursors, exceptions, variables, and constants. The package contains two parts:

1, Package Specification: You can declare types, variables, constants, exceptions, cursors, and subroutines.

2. Package Theme: You can implement cursors and subroutines defined in the package specification.

Second, the package specification

The package specification contains some application courseware for common objects and types of claims that it is an excuse for the application. The specification contains the resources required by the application, and if the package specification declares only types, constants, variables, and exceptions, no package body is required. Only subroutines and cursors have package principals.

Grammar:

create [or replace] package package_name

Is|as

[Public type and item Declations]

[Subprogram Specifications]

End [Package_name]

Description

Package_name: The name of the package.

Public type and item Declations: declares common types, constants, variables, exceptions, cursors, and so on.

Subprogram Specifications: Declares PL/SQL subroutines, which are stored procedures and functions.

Items declared in the package specification can also be used outside of the package. Such items become "common objects."

　　

Cases:

-- Declaration of package Specifications CREATE OR REPLACE  is  PROCEDURE  Number -- declaring a stored procedure: Querying employee Information  based on employee number FUNCTION  Number RETURN  Number -- declare a function to query the employee's salary based on the employee number.  END package_me;

Third, the package body:

The package topic contains the specific implementation of each cursor and subroutine declared in the package specification. Private declarations can also be included in the package body. The package topic partial initialization is optional and can be used to initialize variables in the package. The initialization portion of the package can neither call the package nor pass parameters to the package, and the initialization section runs only once.

create [or replace] package body package_name

Is|as

[Public type and item declarations]

[Subprogram bodies]

[begin

Initialization_statements]

End [Package_name]

Description

Public type and item declarations: declares a variable, constant, cursor, exception, or type.

Subprogram bodies: Defines public and private PL/SQL subroutines.

Initialization_statements: Implementation of canonical code in packages.

　　

Example: Provides implementations of stored procedures and functions for the canonical part declared above.

--Create a package body that provides the implementation of the content of the package, consistent with the declaration of stored procedures, functionsCREATE OR REPLACEPackage BODY Package_me is  PROCEDUREPro_emp_select (ENO Number) asv_name EMP. Ename%TYPE; V_sal EMP. SAL%TYPE; BEGIN    SELECTEname, SAL. intoV_name, V_sal fromEmpWHEREEMPNO=ENO; Dbms_output. Put_Line ('Name:' ||V_name|| 'Sal:' ||v_sal); ENDPro_emp_select; FUNCTIONFun_emp_select (ENO Number)RETURN  Number  asv_sal EMP. SAL%TYPE; BEGIN    SELECTSAL intoV_sal fromEmpWHEREEMPNO=ENO; RETURNv_sal; ENDFun_emp_select;ENDPackage_me;

Iv. Calling Packages　

DECLARE   v_sal emp.sal%TYPE; BEGIN     package_me.pro_emp_select (7788);                -- The same way you call stored procedures, but you need to add package names    before stored procedures V_sal:= Package_me. Fun_emp_select (7788);       -- The same way you call a function, but you need to add the package name before the function     Dbms_output.put_line (v_sal); END;

Oracle Foundation Packages