Oracle 11G basic learning --- (5) Development subroutines and packages

 

Where can I start learning about Oracle? Here, I will take you to the door of Oracle. One chapter a day, seven days of entry is not a dream.

 

1. Development subroutine

A subroutine is a named PL/SQL block. This block can contain parameters and can be called multiple times in different applications.

PL/SQL has two types of subprograms: process and function.

A process is used to perform specific operations, while a function is used to return specific data.

By integrating business logic and enterprise rules into PL/SQL subprograms, You can simplify the development and maintenance of client applications and improve application performance.

 

 

1. 1 Development Process

1.1 Development Process

A process is generally used to execute a specified operation and encapsulate a common operation into a process.

Development Process

CREATE [or replace] PROCEDURE procedure_name

(Argument1 [mode1] datatype1, argument2 [mode2] datatype2 ,...)

IS [AS]

Declaration

BEGIN

Execution part

EXCEPTION

Exception Handling

END;

 

Creation process: No Parameters

Development Process

Creation process: With IN Parameters

If the parameter mode is not specified when it is a process-defined parameter, the input parameter is used by default.

Development Process

Creation process: With the OUT Parameter

A process can be used not only to perform specific operations, but also to output data.

When outputting data IN the process, you need to use the OUT or in out parameters.

Development Process

Creation process: With the in out Parameter

During the creation process, you can specify not only the IN and OUT parameters, but also the in out parameters.

The in out parameter is also called an input/output parameter. When this parameter is used, data must be transmitted to this parameter through a variable before the call process. After the call is completed, oracle will pass the process result to the application through this variable.

1.2 passing variables and data to parameters during the call Process

You can use

Location Transfer

Name Transfer

Combined Transfer

Variables and data are transferred to parameters during the call process.

Deliver by location

Location-Based Transmission refers to writing the name of the real parameter in sequence according to the parameter arrangement during the call, and associating the form to the real parameter for transmission.

In this method, the names of the parameters involved in the form are independent and irrelevant, so the order is important.

It is easier to write than the pass by name method, but if the order of the parameters of a process is updated, all calls of the process must be updated accordingly, therefore, it will increase the difficulty of maintaining the application.

Variables and data are transferred to parameters during the call process.

Pass by name

Passing by name means that the parameters corresponding to the real parameters are written according to the name of the real parameters involved in the call.

In this method, the names of the parameters involved in the form are independent and irrelevant. The name correspondence is very important, but the order is not important.

It is more complex than the location-based transfer method in writing. However, if only the order of the parameters of a process is updated, no update is required for any calls to the process.

However, if the name of a process parameter is updated, all calls to the process must be updated accordingly, making it more difficult to maintain the application.

The name is passed to specify the parameter name when calling the subroutine, and the associated symbol "=>" is used to provide corresponding values or variables for it.

Variables and data are transferred to parameters during the call process.

Combined Transfer

Based on application requirements, you can use both location-based and name-based methods in the same call.

However, the previous real parameters must use the location-based transfer method, while the rest of the real parameters can use the name-based transfer method.

1.3 Develop Functions

A function is used to return specific data. If you need to execute SQL statements to return specific data in an application, you can create a specific function based on these operations.

Using functions not only simplifies the development and maintenance of client applications, but also improves the execution performance of applications.

CREATE [or replace] FUNCTION function_name

(Argument1 [model] datatype1,

Argument2 [mode2] datatype2,

...)

RETURN datatype

IS |

Declaration

BEGIN

Execution part

EXCEPTION

Exception Handling

END;

 

 

Develop Functions

Create a function without any parameters

Develop Functions

Create a function with the IN Parameter

When creating a function, you can use input parameters to Pass Application Data to the function, and finally return the results to the application by executing the function.

If you do not specify the parameter mode when defining parameters, the default parameter is input.

Develop Functions

Create a function with the OUT Parameter

Generally, a function only needs to return a single data. If you want to use the function to return multiple data (for example, return both the employee name and salary), you can use the OUT output parameter to implement this function.

Develop Functions

Create a function with the in out Parameter

Similar to the creation process, you can specify the in out parameter when creating a function.

1.4 comparison of processes and functions

Procedures and functions have many identical functions and features.

Both input data using IN mode parameters and return data using OUT Mode Parameters

All input parameters can receive default values, and all values can be passed.

All real parameters can be called using location or name notation.

Both declaration, execution, and Exception Handling

Generally, if you need to return multiple values or do not return values, use the process

If you only need to return a value, use the Function

Although the function can return multiple values with parameters in the OUT mode, this method is generally considered to be a bad programming habit or style.

A process is generally used to execute a specified action. A function is generally used to calculate and return a value.

1.5 Management subroutine

View the source code of a subroutine

You can query the data dictionary USER_SOURCE to display all subprograms of the current user and their source code.

List subprograms of the current user

The data dictionary view USER_OBJECTS is used to display all objects contained by the current user.

Listing subroutine compilation errors

Use the show errors command to determine the location and cause of the Error

List object Dependencies

When you create a stored object (process, function, package, view, or trigger), you must reference other objects.

When the structure of the referenced object is modified, the dependent object is changed to the INVALID state.

You can use the data dictionary view USER_DEPENDENCIES to determine the direct dependency.

Re-compile the subroutine

After the structure of the referenced object is modified, the dependent object is changed to the INVALID state.

To avoid call failure, after updating the table structure, recompile the object dependent on it.

 

2. Development Kit

2.1 create a package

Package is used to combine logic-related PL/SQL types, PL/SQL items, and PL/SQL subprograms.

By using PL/SQL packages, you can not only simplify application design, improve application performance, but also implement functions such as information hiding and subroutine overloading.

The package consists of the package specification and package body.

When creating a package, you must first create a package specification and then create a package body.

Create a package specification

A package specification is an interface between a package and an application. It is used to define the public components of a package, including constants, variables, cursors, procedures, and functions.

The public components defined in the package specification can be referenced not only in the package, but also by other subprograms.

Note the following when creating a package specification: to hide information, you should not define all components in the package specification, but only public components.

CREATE [or replace] PACKAGE package_name

IS |

Public type and item declarations

Subprogram specifications

END package_name;

Create a package specification

Create a package

To implement the common procedures and functions defined in the package specification, you must create a package body.

The package body is used to implement the processes and functions defined in the package specification.

When creating a package, private components should be defined in the package body to hide information.

CREATE [or replace] package body package_name

IS |

Private type and item declarations

Subprogram bodies

END package_name;

 

2.2 call package Components

Private components of a package can only be called within the package and can be called directly.

The public components of the package can be called both in the package and in other applications.

Call package components in the same package

You can call other components in the same package directly without adding the package name as the prefix.

Call package public variables

When you call a public variable of a package in another application, you must add the package name as the prefix before the public variable name.

Call the public process of the package

When calling the public procedure of a package in other applications, you must add the package name as the prefix before the public procedure name.

Call the public functions of the package

When calling a public function of a package in other applications, you must add the package name as the prefix before the function name.

Call the public components of the package as other users

When a public component of a package is called as another user, the user must be granted the permission to execute the package, and the package name must be called in the format of "user name, package name, and component name ".

 

2.3 management package

View package source code

You can query the data dictionary USER_SOURCE to display the current user's package and its source code.

Delete package

If you only delete the package body, you can use the drop package body command.

If you delete both the PACKAGE specification and PACKAGE body, you can use the drop package command.

 

Summary

A subroutine is a named PL/SQL block. This block can contain parameters and can be called multiple times in different applications. PL/SQL has two types of subprograms: process and function.

A process is generally used to execute a specified action.

A function is generally used to calculate and return a value.

The subroutine uses parameters in the in mode to input data and parameters IN the OUT mode to return data.

The in out parameter is also called an input/output parameter. It must pass data to the parameter through a variable before calling the process. After the call is complete, Oracle will pass the process result to the application through this variable.

You can use location transfer, name transfer, and combination transfer for passing variables and data for parameters.

You can call a function within an SQL statement to solve complex computing problems, but you cannot call the process.

Package is used for logical combination of PL/SQL types, PL/SQL items, and PL/SQL subprograms.

A package consists of a package specification and a package body. When creating a package, you must first create a package specification and then create a package body.

 

From happy pig's column