Oracle Foundation <4>--Package

One: Package definition (includes 1. Package Specification 2. Package Body )

A package is a database object that encapsulates the related PL/SQL types, subroutines, cursors, exceptions, variables, and constants.

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

　2, Package body: You can implement the package specification defined in the cursor, subroutine.

Two: 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:

creat [or replace] package package_name --pack name

[is | as]

[Public type and item Declations] --declaring public types, constants, variables, exceptions and cursors, etc.

[Subprogram Specifications] --Declaring PL/SQL subroutines, i.e. stored procedures and functions

end [Package_name];

-- Declaration of package Specification
CREATE OR REPLACE package Package_me is
PROCEDURE Pro_emp_select (ENO number ); -- declaring a stored procedure: Querying employee information based on employee number
FUNCTION Fun_emp_select (ENO number ) RETURN number ; -- declare a function to inquire employee's salary according to employee number.
END Package_me;

Three: Package body

Grammar:

create [or replace] package body package_name

Is|as

[Public type and item declarations]--Declares a variable, constant, cursor, exception, or type

[Subprogram bodies] --Define public and private PL/SQL subroutines.

[begin

Initialization_statements] -The implementation of the specification code in the package.

end [Package_name];

--Create a package body that provides the implementation of the content of the package, consistent with the declaration of stored procedures, functions
CREATEORREPLACE Package BODY Package_meIs
PROCEDURE Pro_emp_select (ENONumber)As
V_name EMP. Ename%TYPE;
V_sal EMP. SAL%TYPE;
BEGIN
SELECT ename, SALInto V_name, v_salFrom EMPWHERE EMPNO=ENO;
Dbms_output. Put_Line (‘Name‘|| V_name||‘Sal‘||V_sal);
ENDPro_emp_select;

FUNCTION Fun_emp_select (ENONumber) RETURN number as
V_sal EMP. SAL%TYPE;
BEGIN
SELECT SAL into v_sal from EMP WHERE EMPNO = ENO;
RETURN v_sal;
END Fun_emp_select;
END package_me;

Iv. Calling Packages　

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

Oracle Foundation <4>--Package