Multi-process communication in PL/SQL using two development packages

Pl/sql is a mainstream application programming language based on Oracle, and its main feature is to combine SQL statements with procedural development languages to achieve more complex business logic. This article mainly discusses the multiple process communication.

Obviously, multi-process technology is used to improve the concurrency of the application, and thus improve the execution efficiency of the whole system, so how to implement the Pl/sql communication in the process? In fact, Pl/sql's design was designed primarily to enhance the functionality of SQL statements, without taking into account the advanced functionality of other programming languages, so that pl/sql communication can only be achieved with the help of Oracle's two development packages: Dbms_pipe and Dbms_alert.

1. Dbms_pipe

The package provides methods for pipeline communication between multiple processes, such as the ability to communicate through pipelines between two separate sessions connected to the same database, and also to communicate between stored procedures and pro*c, which greatly enhances the processing power of the pl/sql. The package mainly provides two pairs of functions:

Pack_message (v_msg varchar2)-----Packaging v_msg information into the buffer, ready to send;

Send_message (v_pipename varchar2)-----Send a buffer of the pipe named V_pipename;

Unpack_message (v_msg varchar2)-----Parsing the information into v_msg;

Receive_message (v_pipename varchar2)----receive a buffer of a pipe named V_pipename;

The principle of its implementation is: first to establish a well-known pipeline (this is familiar with Unix very clear), the pipeline's sender and receiver have a corresponding buffer to receive and send processing, it should be noted that the text information must be packaged to send, through parsing to read information.

To understand the previous description, the following lists an instance of communication between two sessions.

Send process:

declare
v_pipename varchar2(30):=’pipe1’;
v_status integer;
begin
dbms_pipe.pack_message(’ hello,this is sending process!’);
v_status:=dbms_pipe.send_message(v_pipename);
if v_status !=0 then
dbms_output.put_line(’error!’);
end if;
end;
/
接受进程：
declare
v_pipename varchar2 (30):=’pipe1’;
v_status integer;
v_msg varchar2 (20);
begin
v_status:=dbms_pipe.receive_message (v_pipename);
if v_status !=0 then
dbms_output.put_line (’error’);
end if;
dbms_pipe.unpack_message (v_msg);
dbms_output.put_line(v_msg);
end;
/