Shulou(Shulou.com)05/31 Report--

This article focuses on "Oracle scheduling Schedule feature chains analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "Oracle scheduling Schedule feature chains analysis"!

Take a simple example: run PROGRAM:An and PROGRAM:B

Continue to run PROGRAM:C if successful, otherwise run PROGRAM:D. The logical relationships between Programs:A, B, C, D, and execution constitute the simplest CHAIN.

CHAIN has many administrative operations: create / delete / modify Chains, add / modify / delete Chain Steps, and so on.

1. Create a Chains

Creating a CHAIN uses the DBMS_SCHEDULER.CREATE_CHAIN procedure, which is very simple to call because there are very few parameters to specify

When creating a Chain, it can even be as simple as specifying a CHAIN name and leaving everything else empty.

Begin

Dbms_scheduler.create_chain ('mychain1')

End

The created Chains can be viewed in the * _ SCHEDULER_CHAINS view, for example:

SQL > select chain_name from user_scheduler_chains

CHAIN_NAME

-

MYCHAIN1

Note that creating a CHAIN is far from enough, there is only one CHAIN object ORACLE and nothing can be done (of course, I believe you can see from the creation statement executed above)

After the CHAIN object is created, the work to be done has only just begun. After that, you need to define Chain Steps and Chain rules.

2. Create Chain Step

Chain Steps is used to specify the operations and steps to be performed by CHAIN, and CHAIN STEP is created through the DBMS_SCHEDULER.DEFINE_CHAIN_STEP process.

Add a step to the mychain1 you just created

Begin

Dbms_scheduler.define_chain_step (chain_name = > 'mychain1'

Step_name = > 'mystep1'

Program_name = > 'myprogram1')

End

Chain Steps can call PROGRAM (note that it is program, not procedure, of course, execution procedure can be defined in program), can also call EVENT, or even call other CHAIN (nested CHAIN).

Next, add two step for mychain1, as follows:

Begin

Dbms_scheduler.define_chain_step (chain_name = > 'mychain1'

Step_name = > 'mystep2'

Program_name = > 'myprogram2')

Dbms_scheduler.define_chain_step (chain_name = > 'mychain1'

Step_name = > 'mystep3'

Program_name = > 'myprogram3')

End

To query the defined Chain Steps, use the * _ SCHEDULER_CHAIN_STEPS view, for example:

Select chain_name,step_name,program_name from user_scheduler_chain_steps

CHAIN_NAME STEP_NAME PROGRAM_NAME

-

MYCHAIN1 MYSTEP1 MYPROGRAM1

MYCHAIN1 MYSTEP2 MYPROGRAM2

MYCHAIN1 MYSTEP3 MYPROGRAM3

3. Create Chain Rule

Next, you define the rules for the operation of CHAIN. Rules are defined using DBMS_SCHEDULER.DEFINE_CHAIN_RULE procedures, and Chain Rules depends on Chain Steps

Each CHAIN RULE has condition and action attributes, and when the condition is met, the step specified in the action is executed.

For example: to create a CHAIN RULE, first execute mystep1, then continue to execute mystep2 if the mystep1 is successfully executed, and end the CHAIN if the mystep2 is also successfully executed. The creation script is as follows

Begin

Dbms_scheduler.define_chain_rule (chain_name = > 'mychain1'

Condition = > 'true'

Action = > 'start mystep1'

Rule_name = > 'myrule1')

Dbms_scheduler.define_chain_rule (chain_name = > 'mychain1'

Condition = > 'mystep1 completed'

Action = > 'start mystep2'

Rule_name = > 'myrule2')

Dbms_scheduler.define_chain_rule (chain_name = > 'mychain1'

Condition = > 'mystep2 completed'

Action = > 'end 0'

Rule_name = > 'myrule3')

End

CHAIN_NAME will not talk about it. What you need to pay attention to here are the parameters CONDITION and ACTION. When you specify a value for the CONDITION parameter, the syntax looks slightly more complex, or flexible, and the CONDITION parameter value supports the following syntax forms:

TRUE

FALSE

Stepname [NOT] SUCCEEDED

Stepname [NOT] FAILED

Stepname [NOT] STOPPED

Stepname [NOT] COMPLETED

Stepname ERROR_CODE IN (integer, integer, integer...)

Stepname ERROR_CODE NOT IN (integer, integer, integer...)

Stepname ERROR_CODE = integer

Stepname ERROR_CODE! = integer

Stepname ERROR_CODE integer

Stepname ERROR_CODE > integer

Stepname ERROR_CODE > = integer

Stepname ERROR_CODE

'mystep1')

End

There may be an error in the execution of this statement. Pay attention to read on.

The statement was executed successfully. Check the result of the execution. The program objects such as myprogram1 we defined earlier actually call procedure to insert records into a specified table test. If you query the table directly, you will know the execution (before that, the test table is empty):

3 procedure and 3 program:

Create or replace procedure P_INSERT_INTOTEST is

Begin

Insert into test values (1)

Commit

End

Create or replace procedure insert_test1 is

Begin

Insert into test values (1, 'beer')

End

Begin

Dbms_scheduler.create_program (program_name = > 'myprogram1', program_type = >' STORED_PROCEDURE', program_action = > 'insert_test1', enabled = > true)

End

Create or replace procedure insert_test2 is

Begin

Insert into test values (2, 'rabbit')

End

Begin

Dbms_scheduler.create_program (program_name = > 'myprogram2', program_type = >' STORED_PROCEDURE', program_action = > 'insert_test2', enabled = > true)

End

Create or replace procedure insert_test3 is

Begin

Insert into test values (3, 'horse')

End

Begin

Dbms_scheduler.create_program (program_name = > 'myprogram3', program_type = >' STORED_PROCEDURE', program_action = > 'insert_test3', enabled = > true)

End

SQL > select * from test

ID NAME

--

1 beer

2 rabbit

You see, there are two records in the test table, corresponding to the CHAIN RULE set above, indicating that both mystep1 and mystep2 have been executed correctly.

Tip: Chains must be placed in enabled state before execution. By default, the CHAIN you just created is in disabled state. You need to modify the Chains state.

Or through the DBMS_SCHEDULER.ENABLE and DBMS_SCHEDULER.DISABLE processes

Begin

Dbms_scheduler.enable ('mychain1')

End

There is no system-level logging for manual CHAIN, so if you want to see detailed execution, you can create a job to execute the CHAIN, for example:

Begin

Dbms_scheduler.create_job (job_name = > 'chainjob1'

Job_type = > 'CHAIN'

Job_action = > 'mychain1'

Repeat_interval = > 'freq=daily: interval=1'

Enabled = > true)

End

The administrator or creator can then confirm the execution of the chain by periodically observing the * _ scheduler_job_run_details view.

At this point, I believe that everyone has a deeper understanding of the "Oracle scheduling Schedule feature chains analysis", might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.