Http://tech.ccidnet.com/Art/294/20030526/47427_1.html
Asynchronous call of ORACLE stored procedure: Wang Hui issued Time: 2003.05.26 1. About the author Wang Hui began work in 1994, served as teacher, database administrator, chief programmer, project manager, and now a company in Shenzhen as a project manager. You can contact ddxxkk@21cn.com. 2. Foreword During the actual application of Oracle Database, the service processing logic is often placed in the stored procedure, and the client is called via ADO. Some business logic has a large amount and complex, so that the client calls the stored procedure, there is no reaction for a long time, and I don't know how the storage procedure is running. This paper tells how to use the application of the task and pipeline in Oracle, asynchronously call the stored procedure. 3. Basic Principle 1. Use the DBMS_JOB package to submit the main process stored procedure as a task to task queue 2. The main process stored procedure will handle the processing in the pipe through the DBMS_PIPE package during operation 3. Monitor stored procedure read the pipeline, Therefore, it is understood that the processing is not described in this paper, the monitoring stored procedure can be sent to the main processing stored procedure by pipeline transmission information 4. Specific implementation test establishment test environment 4.1. Related modifications to the database, so that the system task queue Function Opening For ORACLE9I, only "job_queue_proces = non-0 value" in INI in the database is required, such as "job_queue_processes = 10", for Oracle8i, "Job_Queue_Processes = Non 0 value" in the set instance INI file, To set "Job_Queue_Interval = 1" 4.2. Set User Permissions Due to the use of Oracle's specific packages, you should pay attention to the user's system privilege. For the current user (test user), the following power EXECUTE DBMS_PIPEEXECUTE DBMS_LOCKEXECUTE DBMS_JOB 4.3. Establish analog large business processing stored procedure This memory process 30 times, call DBMS_LOCK.SLEP (1) (Sleep 1 second) and write pipe simulation Large treatment process.
The specific 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 information 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 into the buffer, use 999999 to complete Status: = dbms_pipe.send_message ('maxpro'); End MaxPro; 4.4. Establishing a Reading Pipe Dynamic Understanding Processing Storage The following code is the status of the MAXPRO stored procedure The process, mainly using the method of reading the pipeline, note: status: = dbms_pipe.receive_message ('maxpro', 0); "0" parameter is critical, indicating that the waiting time is 0 seconds, indicates that when calling If there is no information, it will not wait to return directly. Specific code as follows: CREATE OR REPLACE PROCEDURE readmaxpro ASn integer; status integer; begin-- acceptance waiting time of 1 second, it can be 0, or immediately call DBMS_PIPE.RECEIVE_MESSAGE ( 'mypipe') 100 days waiting status: = DBMS_PIPE.RECEIVE_MESSAGE ( 'MaxPro', 0); - Status is 0 for success in unpack_message, 1 is timeout without data, 2 is too large, 3 is internal error if status <> 0 thndbms_output.put_line ('pipeline now no information ); return; end if; dbms_pipe.unpack_message (n); dbms_output.put_line (current state of 'MaxPro is' || N); end; 4.5. - Start analog large business processing stored procedure for stored processes for background processes ( BeginmaxPro) passes DBMS_Job.Submit (Job, 'MaxPro;', sysdate, null, false); making "maxpro" as a task, executed after the current time (sysdate), of course, if you change sysdate to make a specific time, then DETAILED off with time to execute the code 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; 5. Code execution 5.1. Execution process Note: The black body is the screen output, the underscore word is the input content in the CMD row input SQLPLUS Depending on the prompt, the DBMS_OUTPUT can be seen, the SQL> SET Serveroutput is below the execution process and the corresponding return information SQL> CALL BEGINMAXPRO (); Queue Number JOB = 21 Call is completed.
SQL> Call readmaxpro (); the current state of MAXPRO is 14 calls completed. SQL> Call readmaxpro (); the current state of MAXPRO is 16 calls completed. SQL> Call readmaxPro (); the current state of MAXPRO is 23 calls. SQL> Call readmaxPro (); the current state of MaxPro is 999999 call completion. SQL> Call readmaxpro (); there is no information in the pipeline to return to the call. SQL> 5.2. Execution Analysis BeginMaxPro (), put the main process stored procedure into the queue process, the post-execution store is immediately returned, the MaxPro stored procedure is placed in the task queue and set to execute (immediately) MAXPRO is the main process stored procedure, starting to perform analog big business processing, and process progress information is written to the pipe readmaxPro () during processing; to check the status stored procedure, first read the information, if the information does not exist, it means that there is no information in the pipe. Read and exit; if there is information, the read information This example can be improved, the output does not pass DBMS_OUTPUT.PUT_LINE, then use the return value, then use ADO to call, publish tasks, and read the progress from the client during the process. . 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; 6. Appendix: 6.1 Related packages for 1 DBMS_JOB.SUBMIT (JOB OUT BINARY_INTEGER, WHAT IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE, INTERVAL IN VARCHAR2 DEFAULT 'NULL', nO_PARSE IN BOOLEAN DEFAULT FALSE, Instance in binary_integer default any_instance, force in boolean default false function: Submit job entry team parameter description: job: job number, output parameter What: Job PL / SQL code (generally used for process name) Next_date: Time INTERVAL: Operation Interval Time, Default is NULL, only once NO_PARSE: Whether to analyze the WHAT, the default is analyzed.
