PL/SQL--Package

One, the package

A package is a combination of a set of related procedures, functions, constants, variables, cursors, exceptions, and other PL/SQL programming elements. It is similar to classes in C + + and Java where variables are equivalent to member variables in a class, and procedures and functions are equivalent to methods in a class. By using packages, developers can use the object-oriented approach to develop stored procedures to improve the performance of the system.

The package is structured as follows:

A package consists of a Baotou and a package body:

• Baotou (Package): The Baotou section declares elements of data types, variables, constants, cursors, subroutines (only declarations), and exception error handling in the packet, which are common elements of the package.
• Package Body: The package body is the specific implementation of the package Definition section, which is responsible for the private elements of the package declared in the header.

The Baotou and the package body are compiled separately and stored as two separate objects in the database data dictionary.

The general syntax of the package is as follows:

Baotou Syntax:

CREATE OR REPLACE Package name is
Function name (parameter parameter type) RETURN parameter type;
PROCEDURE stored procedure name (parameter input output type parameter type, parameter input output type parameter type);
End

Package Body Syntax:

Create or Replace package body pack name is
Function name (parameter name parameter type) return parameter type is
Begin
Processing statements
return parameter;
End
Procedure stored procedure name (parameter input output type parameter type, parameter input output type parameter type) is
Begin
Processing statements;
Commit
End
End

Second, the sample code

Examples are as follows:

--BaotouCREATE OR REPLACEPackage Fkpackage isV_comm Number:=  $;--Monthly Bonus   --Define structure BodyType Re_person isrecord (RID person.id%type, Rusername person.username%type, rage person.age%type, Rpassword person.password%type); --Defining CursorsType T_person isRefcursor; --the function of calculating annual salary    FUNCTIONGet_year_sal (v_sal Number)RETURN  Number;--Defining the Process    PROCEDUREPro_person_add (v_id Number, V_usernameVARCHAR2, V_age Number, V_passwordVARCHAR2    ) ; procedureGet_person (v_idinch  Number, C_person out T_person);ENDFkpackage;--Package BodyCreate or ReplacePackage Body Fkpackage is  --function  functionGet_year_sal (v_sal Number)RETURN  Number  is  BEGIN    RETURN(V_sal+V_COMM)*  A ; END; --Process   procedurePro_person_add (v_id Number, V_usernamevarchar2, V_age Number, V_passwordvarchar2) is  BEGIN    INSERT  intoPersonVALUES(v_id, V_username, V_age, V_password); COMMIT; END; procedureGet_person (v_idinch  Number, C_person out T_person) is    begin       OpenC_person for SelectId,username,age,password fromPerson ; End; END;

Calling code:

DeclareR_person Fkpackage.t_person; --defining struct-body variables in packagesRecord_person Fkpackage.re_person; v_id Number;begin    --use and traverse the result set returned by a procedure in a packageDbms_output.put_line ('Annual salary ='||Fkpackage.get_year_sal (12000)); V_ID:= 1;   Fkpackage.get_person (v_id, R_person); LoopFetchR_person intoRecord_person; Exit  whenR_person%NotFound; Dbms_output.put_line ('name ='||record_person.rage); EndLoop; --Add DataFkpackage.pro_person_add (10001,'Zhangsan', -,'1343');End;

Iii. Advantages of PL/SQL packages
The package offers several advantages: modularity, ease of application design, information hiding, additional functionality, and good performance.

1, modular
The package allows us to put logically related types, constants, variables, exceptions, and subroutines into a named PL/SQL module. Each package is easy to understand, and the interface between package and package is simple and clear. This will help the program develop.

2. When designing an application with ease, the first thing we need to determine is the interface information in the package description. We can write and compile the Description section without the package body condition. The stored sub-program that references the package is also compiled. Before we complete the entire application, we do not need to fully implement the package body part.

3. Information hiding
With the package, we can specify which types, constants, variables, exceptions, and subroutines are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four sub-programs, three of them are public and one is private. The package hides the implementation of the private subroutine, so that if the implementation content changes, only the package itself (not our application) is affected. Also, the integrity of the package can be ensured by hiding the implementation details from the user.

4. Additional functions
Packaging public variables and cursors will persist for a session period. Therefore, they can be shared by all sub-programs in the current environment. And they allow us to maintain data across transactions without having to keep it in the database.

5. Good performance
The entire package is loaded into memory when we first call the Bun program. Therefore, when you call the related subroutine in the package later, you do not need to read the disk again. Packets can block cascading dependencies, which avoids unnecessary compilation. For example, if we change the implementation of the wrapper function, Oracle does not need to recompile the calling subroutine because they do not depend on the package body.

Iv. packages in the system
Oracle and various Oracle tools provide a system package to help us build PL/SQL-based applications. Here's a look at some of the more typical packages.

1, about Dbms_alert bag
Dbms_alert enables a database trigger to send an alert to an application when a particular database value changes. Alarms are transaction-based and asynchronous (that is, their operation is independent of the timing mechanism). For example, when new stocks and bonds are listed, the company can update his total investment with this package.

2, about Dbms_output bag
Package Dbms_output allows us to display output from PL/SQL blocks and subroutines, making it easy to test and debug. The process put_line can output information to a cache in the SGA. We can display this information by invoking the procedure get_line or by setting serveroutput on in the Sql*plus.

3, about Dbms_pipe bag
Package Dbms_pipe allows different sessions to communicate through named Pipes (a pipeline is a chunk of memory that the process uses to pass messages to another process). We can use the procedure Pack_message and send_message to encapsulate the message into one pipeline and then send the message to another session in the same instance. Another terminal of the pipeline, we can use the procedure Recieve_message and unpack_message to accept and open the message to be read. Named pipes are useful in many places. For example, we can write a program that collects information in C, and then pass the information through a pipeline to a stored procedure.

4, about Utl_file bag

Package Utl_file allows our PL/SQL program to read and write operating system (OS) text files. It provides standard OS flow file I/O, including open, put, get, and close operations. When we want to read and write a file, we can call the function fopen, which can return a file handle to be used in subsequent procedure calls. For example, a procedure put_line can write a text string to an open file and add a newline character behind it, and the procedure Get_line can read a line of content from an open file into an output cache.

5, about utl_http bag
Package utl_http allows us PL/SQL programs to communicate using Hypertext Transfer Protocol (HTTP). It can receive data from the Internet or invoke the cartridge of an Oracle Web server. This package has two entry points, each of which accepts a URL (Uniform Resource Locator) string, and then connects to a specified Web site and returns the requested data, usually in Hypertext Markup Language HTML format.

PL/SQL--Package