Oracle in-box (package)

One What to use the package?

In a large project, there may be many modules, and each module has its own processes, functions, and so on. And these procedures, functions are put together by default (such as in PL/SQL, the process is put together by default, that is, procedures), which is very inconvenient to query and maintenance, and even the occurrence of accidental deletion of events.

PL/SQL in order to meet the needs of the program modularization, the construction of the package is introduced. By using packages, you can classify management processes and functions, and so on.

(1) A package is a database object, which is equivalent to a container. Combines logically related procedures, functions, variables, constants, and cursors into a larger unit. Users can reference them from other PL/SQL blocks

(2) packages are similar to classes in the C + + and Java languages, where variables are equivalent to member variables in a class, and procedures and functions are equivalent to class methods. By categorizing the relevant modules into packages, developers can use the object-oriented approach to develop, with the characteristics of object-oriented programming language,

(4) PL/SQL packages have information concealment (information hiding) and are only visible in the relevant layers of algorithms and data structure design. The process description and process body can be composed of a program unit. You can also separate the process description from its process body. You can also define a procedure in a package that does not define a procedure description in the package description, so that the defined procedure is used only within the package.

(5) in PL/SQL programming, the use of packages can not only make the program design modular, hide the information used in the package (by using private variables), and write can improve the execution efficiency of the program. Because Oracle calls the entire package into memory when the program first invokes the in-package function or procedure, Oracle reads directly from memory when the elements inside the package are accessed again, eliminating the need for disk I/O operations, which increases the efficiency of the execution of the program.

Second, the package structure

A package consists of two separate parts: package Specification and package body

2.1 Package Definition

(1) Package definition: The package Definition section is an interface for an application that declares elements such as data types, variables, constants, cursors, subroutines, and exception handling in the package that are the public elements of the package.

CREATE [OR REPLACE] Package package_name   {is | as} [public   data type definition] [public   Cursor Declaration] [Public   variable, constant declaration]   [public subroutine declaration]end   [Package_name];

2.2 Packet Body (package body)

Package Body: The package body is the specific implementation of the package Definition section, which defines the cursors and subroutines declared in the package Definition section, and the private elements of the package can also be declared in the package body. If a cursor or subroutine in the package body is not defined in the header, then the cursor or subroutine is private.

CREATE [OR REPLACE] Package BODY Package_name{is | as} [private                   data type definition] [private                   variable, constant declaration] [                   Private subroutine declaration and definition]                   [public subroutine definition]begin PL/                   SQL statement end [package_name];

As with classes, the program elements in a package are also divided into common and private elements, which differ in the scope of the programs they allow to access, that is, their scope is different. Common elements can be accessed not only by functions and procedures in the package, but also by PL/SQL programs outside the package, while private elements can only be accessed by functions and programs within the package.

The package definition is compiled separately from the package body and is stored in the database dictionary as a two-part separate object. Package definition must be in front of the package body to compile, package body can not, but package definition must have) package name and package body name to be consistent

Three, package example definition package specification

CREATE OR REPLACEPackage P_stu as    --Define structure BodyType Re_stu isrecord (Rname student.name%type, rage student.age%type); --Defining CursorsType C_stu isRefcursor; --Defining Functions    functionNumadd (NUM1 Number, num2 Number)return  Number; --Defining the Process    procedureGetstulist (CIDinch varchar2, c_st out c_stu); End;

Description type C_stu is REF CURSOR;

Type c_stu: Define type variable

is REF CURSOR: Corresponds to a data type, but a data type that refers to a cursor.

This variable is typically used when stored procedures and functions return a result set, because PL/SQL does not allow a stored procedure or function to return a result set directly, but can return a type variable, so referencing the cursor's type variable as an output parameter or return value arises.

Define Package Body:

CREATE OR REPLACEPackage Body P_stu as    --cursors and structs, which are declared in the package specification, are not declared in the package body and are used directly.         --Implementation Method    functionNumadd (NUM1 Number, num2 Number)return  Number     asNum Number; beginNum:=Num1+num2; returnnum; End; --implementation Process    procedureGetstulist (CIDvarchar2, C_st out C_stu) asR_stu Re_stu;--directly using the structure in the package specification    begin        OpenC_st for SelectName,age fromStudentwhereClassID=CID; --If the cursor has been traversed in the procedure, there will be no value in the block that uses the procedure.        --Loop       --fetch c_st into r_stu;        --exit when C_st%notfound;       --dbms_output.put_line (' name = ' | | R_stu.rname);       --end Loop;    End;End;

Call Package

DeclareC_stu P_stu.c_stu; --Defining package Midstream variablesR_stu P_stu.re_stu;--defining struct-body variables in packagesNum Number;begin    --use and traverse the result set returned by a procedure in a packageP_stu. Getstulist ('5', C_stu); LoopFetchC_stu intoR_stu; Exit  whenC_stu%NotFound; Dbms_output.put_line ('name ='||r_stu.rname); EndLoop; --using the methods in the package    SelectP_stu.numadd (5,6) intoNum fromdual; Dbms_output.put_line ('num='||num);End;

Reference

Oracle Packages (Package)

Seventh chapter the creation and application of package

Oracle in-box (package)