Asynchronous calling of stored procedures in Oracle databases

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 queues to learn about 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.

Implementation Test

Create a test environment

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 ".

2. Set User Permissions

Because the specific ORACLE package is used, you must grant the system permission in plain text. Test the user for the current user ).

EXECUTE DBMS_PIPEEXECUTE DBMS_LOCKEXECUTE DBMS_JOB

3. Create a storage process for simulating big business processing

This stored procedure runs for 30 times and calls DBMS_LOCK.SLEEP (1) sleeping for 1 second) and the write pipeline to simulate a large processing process.

The Code is as follows:

Create or replace procedure maxpro ASn integer; status NUMBER; beginfor n in 1 .. 30 LOOPDBMS_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 ); 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;

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 ASn integer; status integer; begin -- accept wait time is 1 second, can be 0, immediate call OR DBMS_PIPE. RECEIVE_MESSAGE ('mypipe'): Wait for 100 days. status: = DBMS_PIPE.RECEIVE_MESSAGE ('maxpro', 0); -- If status is 0, UNPACK_MESSAGE can be obtained; If status is set to 0, no data is generated during timeout, 2: The information is too large. 3: Internal error IF status <> 0 THENDBMS_OUTPUT.PUT_LINE ('no information is returned in the pipeline now '); return; end if; DBMS_PIPE.UNPACK_MESSAGE (n ); DBMS_OUTPUT.PUT_LINE ('maxpro's current running status is '| n); end;

5.-Start the BEGINMAXPRO stored procedure that simulates large business processing and uses the stored procedure as the background process ). Use DBMS_JOB.SUBMIT (JOB, 'maxpro; ', sysdate, NULL, FALSE) to make "maxpro" a task and run it after sysdate at the current time, 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 ASJOB BINARY_INTEGER; BEGINDBMS_JOB.SUBMIT (JOB, 'maxpro; ', sysdate, NULL, FALSE); DBMS_OUTPUT.PUT_LINE ('queue number JOB =' | JOB); COMMIT; end;

Code Execution

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>

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, and set it to execute 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)　ASn integer;status integer;beginstatus:= DBMS_PIPE.RECEIVE_MESSAGE('maxpro',0);IF status <> 0 THENP:=-1;return;END IF;DBMS_PIPE.UNPACK_MESSAGE(p);end;

(