Shulou(Shulou.com)06/01 Report--

The basis of regular execution of stored procedures

Oracle timer

Oracle timer: regular execution of a stored procedure.

You can see it through the graphical interface of pl/SQL. The timer includes: scheduling and stored procedure interface

Stored procedure interface

The stored procedure is connected to the interface, which mainly tells the timer which stored procedure to execute. The stored procedure interface is the entrance to the execution of the stored procedure. From the graphical interface, you can see that "Action" is the stored procedure to be executed. And "Arguments" is the parameter type needed to call the stored procedure and the necessary parameters called by the stored procedure.

Stored procedure

A stored procedure (Stored Procedure) is a set of SQL statements in a large database system that is stored in the database after the first compilation and then called again without recompilation. The user executes the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). Stored procedure is an important object in database, and any well-designed database application should use stored procedure.

Dispatching

The main function of scheduling is to tell the timer God when to execute the scheduled task and how often the task is executed.

The creation of case script

/ * create an executable program * /

Begin

DBMS_SCHEDULER.CREATE_PROGRAM (

Program_name = > 'peace_sj_his.PROG_DATASYNC'

Program_action = > 'peace_sj_his.P_DATASYNC'

Program_type = > 'STORED_PROCEDURE'

Number_of_arguments = > 3

Comments = > 'data synchronizer'

Enabled = > false

);

End

/

/ * set input parameters of the executable program * /

Begin

DBMS_SCHEDULER.define_program_argument (

Program_name = > 'peace_sj_his.PROG_DATASYNC'

Argument_position = > 1

Argument_type = > 'VARCHAR2'

Default_value = >''

);

DBMS_SCHEDULER.define_program_argument (

Program_name = > 'peace_sj_his.PROG_DATASYNC'

Argument_position = > 2

Argument_type = > 'VARCHAR2'

Default_value = >''

);

DBMS_SCHEDULER.define_program_argument (

Program_name = > 'peace_sj_his.PROG_DATASYNC'

Argument_position = > 3

Argument_type = > 'VARCHAR2'

Default_value = >''

);

END

/

/ * create a scheduling table * /

Begin

DBMS_SCHEDULER.create_schedule (

Schedule_name = > 'peace_sj_his.SCH_DATASYNC'

Repeat_interval = > 'FREQ=MINUTELY;INTERVAL=2'

Start_date = > sysdate

Comments = > 'data synchronization scheduling'

);

End

/

/ * create a job * /

Begin

DBMS_SCHEDULER.create_job (

Job_name = > 'peace_sj_his.JOB_PPTN'

Program_name = > 'peace_sj_his.PROG_DATASYNC'

Schedule_name = > 'peace_sj_his.SCH_DATASYNC'

Job_class = > 'DEFAULT_JOB_CLASS'

Comments = > 'rainfall data synchronization operation'

Auto_drop = > false

Enabled = > false

);

End

/

/ * start the executable program * /

Exec DBMS_SCHEDULER.enable ('PROG_DATASYNC')

/ * start a job * /

Exec DBMS_SCHEDULER.enable ('JOB_PPTN')

/ * set different job parameters * /

Begin

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (

Job_name = > 'peace_sj_his.JOB_PPTN'

Argument_position = > 1

Argument_value = > 'ST_PPTN_R'

);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (

Job_name = > 'peace_sj_his.JOB_PPTN'

Argument_position = > 2

Argument_value = > 'TM'

);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (

Job_name = > 'peace_sj_his.JOB_PPTN'

Argument_position = > 3

Argument_value = > 'STCD'

);

End

/

-Job Management

/ * disable Job * /

Exec dbms_scheduler.disable ('JOBTEST')

/ * execute Job * /

Exec dbms_scheduler.run_job ('JOBTEST')

/ * stop Job * /

Exec dbms_scheduler.stop_job ('JOBTEST')

/ * Delete Job * /

Exec dbms_scheduler.drop_job ('JOBTEST')

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.