Oracle 10g Implementation stored procedure asynchronous invocation

What is Dbms_job?
Dbms_job is one of the expert packages provided by the Oracle database.
mainly used in the background to run the program, is an excellent tool in the database. Can be used to automatically adjust schedule routine tasks, such as analyzing data tables, performing some archive operations, cleaning up draft tables, and so on.
Use the syntax description.
using the Dbms_job master routine is the submit routine, explaining the meaning of each variable in the Submit routine:
Job : A job identifier that is assigned by the system (out parameter).
What: The SQL text that will be run must be a valid PL/SQL statement or a piece of code. For example: Run stored procedure p, you can pass the string p; (including semicolons) to this routine. Whatever is committed in the what parameter will be encapsulated as a PL/SQL block as follows:
Declare
job Binary_integer: =: job;
next_date Date: =: mydate;
broken Boolean: = False;
Begin
What
: mydate: = next_date;
If broken Then:b: = 1;
else:b: = 0;
End If;
End;
next_date: A date function string that is used to calculate the time of the next run of the job.
No_parse: Determines if the what parameter is being parsed for validity at the time of submission.
INSTANCE: It is only meaningful on loosely coupled machines, in parallel server mode (a mode that Oracle can run), which specifies on which instance the job can be executed.
Force : Meaningful only in parallel server mode. If set to True (the default), you can use any instance database to submit a job, such as false, if the related instance is not available, and the commit request will fail.
There are also other entry points in the Dbms_job package. Submit is the one that is used to schedule the job, other operations that allow the operation to be scheduled, and perform operations such as run,remove and change operations.
Example
The original stored procedure is: pr_movedetail (Business stored procedure), perform big data volume operation, the operation is time consuming.
With the dbms_job package feature, the execution of the stored procedure is submitted centrally to the Oracle job for processing, and the client does not have to wait.
1. Create a table that contains the fields for each parameter, plus an ID Master Code field.
Create Table Run_movedetail
(
ID number primary key,
Param1 varchar2 (255),
Param1 varchar2 (255),
rundate Date
)
This table is not only a place to queue the processing that will be submitted, but also a place where a permanent log can be saved as a committed process when committed.
2. Create the Fast_movedetail stored procedure (create the Job ID and insert the parameters into the Run_movedetail).
Create or Replace procedure Fast_movedetail (p_param1 varchar2,p_param2 varchar2)
as
l_job number;
Begin
dbms_job.submit (L_job, ' background_movedetail (Job); ');
Insert into Run_movedetail (ID,PARAM1,PARMA2) VALUES (L_job, p_param1, p_param2);
End;
the routine submits a job background_movedetail and passes it to the job parameter.
3. Create a background_movedetail stored procedure.
Create or Replace procedure Background_movedetail (P_job in number)
as
L_rec Run_movedetail%rowtype;
Begin
Select * into L_rec from run_movedetail where id = p_job;
Pr_movedetail (L_rec. Param1,l_rec. PARAM2); Business stored Procedure calls
Update run_movedetail Set rundate = sysdate where id = p_job;
//Call the Pr_movedetail routine that is actually used, and then update the record to record the actual commit time.
End;
Finally, you can call Fast_movedetail directly on the client.

Author "Ladder"

Oracle 10g Implementation stored procedure asynchronous invocation