Oracle PL/SQL package learning notes

A package consists of a package specification and a package body.

 

1. package specification)

The packet specification, also known as the packet header, contains information about the package content. However, it does not contain any process code.

The syntax for creating a Baotou is generally as follows:

 

Create [or replace] package package_name {is |}

Procedure_name | function_name | variable_declaration | type_definition | prediction_declaration | cursor_declaration

End [package_name];

 

The Declaration header must follow the following syntax rules:

• Package parts can appear in any order. However, objects must be declared before being referenced.
• No parts of all types need to be used. For example, a package can only contain procedures and function specifications without declaring exception handling or type.
• All declarations of procedures and functions must be forward declarations.

 

2. Package body)

The packet subject and header are stored in different data dictionaries. If the packet header is not compiled successfully, it is impossible to compile the package body successfully. The subject contains the code that is previously declared to the subroutine in the header.

 

The package subject is optional. If the packet header does not contain any process or function, the packet body may not. This technique is useful for declaring global variables because all objects in the package are visible outside the package.

 

All forward declarations in the header must be updated in the package body. The process or function specifications must be the same in the header and the package body. This specification includes the subprogram name, parameter name, and parameter mode.

 

3. Package and scope

Any object defined in the header has a certain range. These objects can still be used outside the package by using the package name. For example, you can call the inventoryops. deleteisbn process like the PL/SQL block below.

 

Begin

Inventoryops. deleteisbn ('20140901 ');

End;

 

The call of a package process is the same as that of a separate process. The only difference is that a prefix of the package name is added before the package process. The package process can contain default parameters and can be called using location or name notation, just like a separate stored procedure.

 

Objects in the packet header can be used directly in the package body without the package name prefix.

 

4. Heavy Load of Steamed Stuffed Bun Program

In the package, the process and function can be reloaded. This means that multiple procedures or functions can share the same name but have different parameters. This is a very useful feature because it allows the same operation to be executed on different types of objects.

 

The following example shows how to reload the Steamed Stuffed Bun program.

 

Create or replace package inventoryops as </P> <p>... </P> <p> -- returns an array containing the books with the specified status. </P> <p> procedure statuslist (p_status in inventory. status % type, </P> <p> p_books out t_isbntable, </P> <p> p_numbooks out binary_integer); </P> <p> type c_isbncur is ref cursor; </P> <p> -- returns an opened cursor containing the books with the specified status. </P> <p> procedure statuslist (p_status in inventory. status % type, -- overload </P> <P> p_bookcur out c_isbncur); </P> <p> end inventoryops; </P> <p>/</P> <p> Create or replace package body inventoryops as </P> <p>... </P> <p> -- returns an array containing the books with the specified status. </P> <p> procedure statuslist (p_status in inventory. status % type, </P> <p> p_books out t_isbntable, </P> <p> p_numbooks out binary_integer) is </P> <p> v_isbn inventory. ISBN % type; </P> <p> cursor c_books is </P> <p> select ISBN </P> <p> from Inventory </P> <p> where status = p_status; </P> <p> begin </P> <p> validatestatus (p_status); </P> <p>/* p_numbooks will be the array index. it will start at </P> <p> * 0, and be incremented each time through the fetch loop. </P> <p> * at the end of the loop, it will have the number of rows </P> <p> * fetched, and therefore the number of rows returned in </P> <p> * p_books. */</P> <p> p_numbooks: = 0; </P> <p> open c_books; </P> <p> loop </P> <p> fetch c_books into v_isbn; </P> <p> exit when c_books % notfound; </P> <p> p_numbooks: = p_numbooks + 1; </P> <p> p_books (p_numbooks): = v_isbn; </P> <p> end loop; </P> <p> close c_books; </P> <p> end statuslist; </P> <p> -- returns an opened cursor containing the books with the specified status. </P> <p> procedure statuslist (p_status in inventory. status % type, </P> <p> p_bookcur out c_isbncur) is </P> <p> begin </P> <p> validatestatus (p_status ); </P> <p> open p_bookcur for </P> <p> select ISBN </P> <p> from Inventory </P> <p> where status = p_status; </P> <p> end statuslist; </P> <p> end inventoryops; </P> <p>/</P> <p>

 

Reload is useful when different types of parameters are required to perform the same operation. However, there are some restrictions on overloading:

 

• If the two subprograms are different only in the parameter name and mode, the two subprograms cannot be reloaded.
• If two functions have different return types, they cannot be reloaded.
• The parameters of the overload function must belong to different type series. You cannot use the overload function on parameters of the same type series.

 

 

5. Package Initialization

When the Steamed Stuffed Bun program is called for the first time, or any variable or data type of the package is referenced, the package is instantiated. Instantiation means that the system has read the package from the disk to the memory, and the compiled code of the called subroutine is also running. In this case, the memory is allocated to all variables defined in the package. Each session has a copy of the package variable, which ensures that the two sessions that execute the same sub-Program of the same package use different memory locations.

 

In most cases, once a package in a session is instantiated, the initialization code needs to be run immediately. This can be achieved by adding an initialization part to the package body. The initialization part is located after all other objects. The initialization syntax is as follows:

 

Create or replace package body package_name {is |}

...

Begin

Initialization_code;

End [pckage_name];

 

 

6. Default Parameters

When calling a function from a procedural statement, you can set the default value for the formal parameter if necessary. However, when calling a function from an SQL statement, you must specify the default value for all parameters. In addition, you can only specify the default value using the Location Identification Method instead of the name-based identification method. The following calls to fullname are invalid.

 

Select fullname (p_studentid = & gt; 10000) from dual;