Oracle Timing Plan Fast Usage

Oracle Timing Plan Quick Use preface:

SQL Server has a related timing schedule that can be configured directly by opening the SQL Server Task Manager, allowing you to quickly and easily implement the appropriate tasks. The corresponding Oracle also has a corresponding timing plan, but not a good graphical interface for users to operate. This article is designed to make it easy for users to quickly create timing plans in Oracle and execute appropriate SQL or stored procedures on a regular basis.

1. Create a timing plan

Note: All of the following sample code requires the user to first connect to Sql/plus and execute it in Sql/plus.

1.1 Creating a stored procedure

A stored procedure is not required, but a series of SQL statements related to the integration of a piece, easy to execute.

Example:

Create or replace procedure update_black_gray_list as

Begin

UPDATE sms_blacklist//SQL statement to execute

SET flag= ' 0 ', "ENABLE" = ' 2 ',

Fdate=to_date (To_date (To_char (sysdate, ' yyyy-mm-dd '), ' yyyy-mm-dd '),

Tdate=to_date (To_date (To_char (sysdate+7, ' yyyy-mm-dd '), ' yyyy-mm-dd ')

WHERE flag= ' 0 ' and ' ENABLE ' = ' 0 ' and

Tdate<to_date (To_date (To_char (sysdate, ' yyyy-mm-dd '), ' yyyy-mm-dd '));

End

/

1.2 Creating a timed schedule

Example:

Variable update_list_job number;

Begin

Dbms_job.submit (: Update_list_job, ' update_black_gray_list; ', TRUNC (sysdate), ' TRUNC (sysdate+1) ');

End

/

The variable update_list_job is to store the unique identification number of the plan that was created to make it easy to delete and view information about the timing plan

1.2.1 Dbms_job.submit () description

PROCEDURE Submit (

Job out Binary_ineger,

What in Varchar2,

Next_date in Date,

Interval in varchar2,

No_parse in Booean:=false)

This process has five parameters: job, what, next_date, interval, and No_parse.

The L job parameter is the Binary_inegerreturned by the Submit () procedure. This value is used to uniquely identify the job.

L What parameter is a PL/SQL code block that will be executed (typically many stored procedures).

L next_date parameter indicates when the job will run.

L interval parameter when this work will be re-executed.

Interval is the interval between the last execution and the next execution, and when interval is set to NULL, the job execution ends and is removed from the queue. If we need the job to be executed periodically, use ' sysdate+m '.

Example:

Run ' sysdate + 1 ' once a day

Run once per hour ' sysdate + 1/24 '

Run once per minute ' sysdate + 1/(60*24) '

Run once per second ' sysdate + 1/(60*24*60) '

Run once every other week ' sysdate + 7 '

Every night 12 o'clock ' TRUNC (sysdate + 1) '

Daily 1:30 A.M. ' TRUNC (sysdate + 1) + (1*60+30)/(24*60) '

Every Tuesday noon 12 o'clock ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '
Midnight of the first day of the Month 12 o'clock ' TRUNC (Last_day (sysdate) + 1) '
The last day of each quarter 11 o'clock ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
Every Saturday and Sunday 6:10 A.M. ' TRUNC (LEAST (Next_day (sysdate, "SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60) '

No longer runs the task and deletes it NULL

The L no_parse parameter Indicates whether this work should be parsed at the time of submission or execution (by default , False)--true indicates this The PL/SQL code should parse the first time it executes, while FALSE indicates that this PL/SQL code should be parsed immediately.

1.3 Executing a timing plan

Example:

Begin

Dbms_job.run (: update_list_job);

End

/

1.4 Deleting a scheduled schedule

Example:

Begin

Dbms_job.remove (: update_list_job);

End

/

2. View all Timing Plans

SELECT * from User_jobs;

3. Delete Scheduled Schedule 3.1 Delete all scheduled schedules

Begin

For V. (select Job from User_jobs) loop
Dbms_job.remove (V.job);
End Loop;
Commit

End

3.2 To delete a scheduled schedule for a specified content

Begin

For-V in (the Select job from User_jobs where Whatin (' the_what_name; '))

Loop

Dbms_job.remove (V.job);

Endloop;

Commit

End

/

3.3 Delete a scheduled schedule for a specified user

Begin

For V. (select Job from User_jobs wherelog_user= ' userxxxx ') loop

Dbms_job.remove (V.job);

End Loop;

Commit

End

/

Oracle Timing Plan Fast Usage