Oracle Packages (Package)

Quote this big:

Http://www.cnblogs.com/lovemoon714/archive/2012/02/29/2373695.html

1. Why should I use the package?

A: In a large project, there may be many modules, and each module has its own procedures, 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. Therefore, you can classify management processes and functions by using packages.
Custom types can also be custom-defined in packages, allowing you to use custom variables directly in procedures and functions. The concept of a package in Oracle is very similar to the concept of a package in Java, except that the packages in Java are for classification management classes, but the keyword is the package.
The package is divided into two parts, package specification and package body .

2, the use of the package

(1) Defining the package specification, the package specification can exist separately.

--Defining Package SpecificationsCreate 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;

(2) Implement the package specification, that is, the package body, the name must be consistent, the same cursor definition cannot appear, but the structure can, methods, procedures must be implemented.

--Implement the package body, the name is the same. 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      --open a cursor and take a value from select        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;

(3) Use

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 ('C001', 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;

Oracle Packages (Package)