Oracle Packages (Package)

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 Specifications
CreateOrReplace Package P_stu
As
--Define Structure body

            Type Re_stuIs record (

             Rname Student.name%type

              Rage Student.age%type 

                ); 
-- define cursor  

                    Type c_ Stu is ref cursor; 
-- 
function Numadd (num1 number,num2 number) return number; 
-- 
procedure getstulist (CID in 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.
CreateOrReplace package 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
function Numadd (NUM1Number,num2NumberReturnNumber
As

                     NumNumber
Begin

                       Num:=num1+num2;
return num;
End

--Implementation process
Procedure Getstulist (CIDVarchar2,c_st out C_stu)
As

                              R_stu Re_stu;--Directly using the structure in the package specification
Begin
Open C_stForSelect Name,ageFrom studentwhere ClassID=CID; 
-- 
-- 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

Declare
C_stu P_stu.c_stu;--Defining Package Midstream Variables
R_stu P_stu.re_stu;--Defining struct-body variables in packages

         NumNumber
Begin
--Use and traverse the result set returned by a procedure in a package

              P_stu. Getstulist (‘C001', c_stu);

                 Loop
Fetch C_stuinto R_stu;
Exitwhen c_stu%notfound; 

                        Dbms_output.put_line ( ' | | NUM); 
end;      

Oracle Packages (package)