Application of tasks and pipelines in Oracle stored procedures

1. Preface

In the actual application process of Oracle databases, the business processing logic is often stored in the stored procedure, and the client calls it through ADO. Some business logic processes are large and complex, so that the client does not respond after calling the stored procedure for a long time and does not know the running status of the stored procedure. This article describes how to use tasks and pipelines in Oracle, the method that calls the Stored Procedure asynchronously.

2. Basic Principles

1. Use the dbms_job package to submit the primary processing stored procedure as a task to the task queue;

2. The main processing and storage process puts the processing situation in the pipeline through the dbms_pipe package during operation;

3. Monitor the MPs read queue to learn the processing status;

4. This document does not describe the two-way communication method. The monitoring stored procedure can send information to the master through the pipeline to process the stored procedure.

3. Specific implementation Test

Create a test environment

3.1. Modify the INI of the database to enable the task queue function of the system.

For Oracle9i, you only need to set "job_queue_processes = non-0 value" in the database INI, such as "job_queue_processes = 10 ", in addition to setting the "job_queue_processes = non-0 value" in the instance INI file of Oracle8i, you must also set "job_queue_interval = 1 ".

3.2. Set User Permissions

Because the specific Oracle package is used, you must grant the system permission in plain text. Assign the following powers to the current user (test user:

 

EXECUTE DBMS_PIPE　　EXECUTE DBMS_LOCK　　EXECUTE DBMS_JOB

3.3. Establish a storage process for simulating big business processing

This stored procedure runs for 30 times and calls dbms_lock.sleep (1) (1 S) and the write pipeline to simulate a large processing process.

The Code is as follows:

 

Create or replace procedure maxpro as N integer; Status Number; begin for N in 1 .. 30 loop dbms_pipe.purge ('maxpro'); -- clear the original pipeline information dbms_pipe.pack_message (n); -- put the information in the buffer status: = dbms_pipe.send_message ('maxpro'); dbms_lock.sleep (1 ); end loop; dbms_pipe.purge ('maxpro'); -- clear the original pipeline information dbms_pipe.pack_message (999999); -- put the information into the buffer zone and use 999999 to indicate the process completion status: = dbms_pipe.send_message ('maxpro'); End maxpro;

3.4. Create a read pipeline to dynamically understand the storage process of the processing status

The following code reads the status of the maxpro stored procedure, mainly using the method of reading the pipeline. Note:

 

status:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);

The "0" parameter in is critical, indicating that the acceptance wait time is 0 seconds, indicating that if there is no information during the call, it will be returned directly without waiting.

The Code is as follows:

 

Create or replace procedure readmaxpro as N integer; Status integer; begin -- accept wait time is 1 second, can be 0, call immediately or dbms_pipe.receive_message ('mypipe') wait 100 days status: = dbms_pipe.receive_message ('maxpro', 0, 3: Internal error if status <> 0 then dbms_output.put_line ('no information is returned in pipeline now '); return; end if; dbms_pipe.unpack_message (N ); dbms_output.put_line ('maxpro's current running status is '| N); end;

3.5.-Start to simulate the storage process for processing large businesses as the background process (beginmaxpro)

Use dbms_job.submit (job, 'maxpro; ', sysdate, null, false); To make "maxpro" a task and run it after the current time (sysdate, of course, if you change sysdate to a specific time, it will be executed at the specific time.

The applicable code is as follows:

 

Create or replace procedure beginmaxpro as job binary_integer; begin dbms_job.submit (job, 'maxpro; ', sysdate, null, false); dbms_output.put_line ('queue number job =' | job ); commit; end;

4. Code Execution

4.1. Execution Process

 

Enter sqlplus in cmd and set dbms_output according to the prompt to log on. Visible SQL> set serveroutput on: the execution process and corresponding return information. SQL> call beginmaxpro (); queue number: Job = 21. The call is completed. SQL> call readmaxpro (); the current status of maxpro is 14. SQL> call readmaxpro (); the current status of maxpro is 16. SQL> call readmaxpro (); the current status of maxpro is 23. SQL> call readmaxpro (); the current invocation status of maxpro is 999999. SQL> call readmaxpro (); no information is returned in the pipeline to complete the call. SQL>

4.2. Perform analysis

Beginmaxpro (): puts the main processing and storage process into the queue. After execution, the storage program will return immediately, and the maxpro storage process will be placed in the task queue, it is set to run immediately at the current system time ).

Maxpro is the main processing and storage process. It starts to simulate large business processing and writes the processing progress information to the pipeline.

Readmaxpro (); to check the status of the stored procedure, read the information first. If the information does not exist, it indicates that there is no readable information in the pipeline and exit. If the information exists, read the information.

This example can be improved. If the output value is returned instead of dbms_output.put_line, you can use ADO to call, publish a task, and read the progress from the client during the task process. The Code is as follows:

 

CREATE OR REPLACE PROCEDURE readmaxpro(p out intergr)　AS　　n integer;　　status integer;　　begin　　status:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);　　IF status <> 0 THEN　　P:=-1;　　return;　　END IF;　　DBMS_PIPE.UNPACK_MESSAGE(p);　　end;