Oracle scheduled quick use

Oracle scheduled quick use
Quick use of Oracle scheduled task:

SQLServer has related scheduled tasks. You can directly open the task manager of SQL server to configure the scheduled tasks. The corresponding Oracle has a corresponding timing plan, but there is no good graphic interface for users to operate. This article aims to help you quickly create a scheduled plan in Oracle and regularly execute corresponding SQL statements or stored procedures.

1. Create a scheduled plan

Note: All the following sample codes must be connected to SQL/plus and executed in SQL/plus.

1.1 create a stored procedure

Stored procedures are not mandatory, but they integrate a series of related SQL statements to facilitate execution.

Example:

Create or replace procedureupdate_black_gray_list

Begin

UPDATE SMS_BLACKLIST // SQL statement to be executed

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

FDATE = TO_DATE (TO_CHAR (SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd ')),

TDATE = TO_DATE (TO_CHAR (SYSDATE + 7, 'yyyy-mm-dd'), 'yyyy-mm-dd '))

Where flag = '0' AND "ENABLE" = '0' AND

TDATE

End;

/

1.2 create a scheduled plan

Example:

Variable update_list_job number;

Begin

Dbms_job.submit (: update_list_job, 'Update _ black_gray_list; ', TRUNC (SYSDATE), 'trunc (SYSDATE + 1 )');

End;

/

The update_list_job variable is used to store the unique ID number of the created plan. It is convenient to delete and view information about the scheduled plan.

1.2.1 dbms_job.submit () Description

PROCEDUREsubmit (

Job OUT binary_ineger,

WhatINvarchar2,

Next_date INdate,

IntervalINvarchar2,

No_parseINbooean: = FALSE)

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

LThe job parameter is the binary_ineger returned by the Submit () process. This value uniquely identifies a job.

LWhat parameter is the PL/SQL code block to be executed (usually stored procedure ).

LThe next_date parameter specifies when the job will be run.

LThe interval parameter indicates when the job will be re-executed.

Interval refers to the interval between the last execution and the next execution. When interval is set to null, the job is deleted from the queue after execution. If we need to periodically execute the job, we need to use 'sysdate + M.

Example:

Run 'sysdate + 1' every day'

Run 'sysdate + 1/24 'every hour'

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

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

Run 'sysdate + 7' once every week'

'Trunc (SYSDATE + 1) 'At midnight every day )'

Every morning 01:30 'trunc (SYSDATE + 1) + (1*60 + 30)/(24*60 )'

NEXT _ DAY (TRUNC (SYSDATE), ''tuesday'') + 12/24 at every TUESDAY'
'Trunc (LAST_DAY (SYSDATE) + 1) 'at midnight on the first day of each month )'
'Trunc (ADD_MONTHS (SYSDATE + 2/24, 3), 'q')-100' at on the last day of each quarter'
'Trunc (LEAST (NEXT_DAY (SYSDATE, ''saturday "), NEXT_DAY (SYSDATE," SUNDAY ") + (6 × 60 + 10) /(24 × 60 )'

No longer run the task and delete it NULL

LThe no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution (false by default) -- TRUE indicates that the PL/SQL code should perform syntax analysis when it is executed for the first time, FALSE indicates that the PL/SQL code should be analyzed immediately.

1.3 execute the scheduled plan

Example:

Begin

Dbms_job.run (: update_list_job );

End;

/

1.4 delete a scheduled task

Example:

Begin

Dbms_job.remove (: update_list_job );

End;

/

2. View All scheduled tasks

Select * from user_jobs;

3. delete a scheduled Task 3.1 delete all scheduled tasks

Begin

For v in (select job from user_jobs) loop
Dbms_job.remove (v. job );
End loop;
Commit;

End;

3.2 Delete the scheduled plan of the specified content

Begin

For v in (select job from user_jobs where whatin ('the _ what_name ;'))

Loop

Dbms_job.remove (v. job );

Endloop;

Commit;

End;

/

3.3 Delete the scheduled schedule of a specified user

Begin

For v in (select job from user_jobs wherelog_user = 'userxx') loop

Dbms_job.remove (v. job );

End loop;

Commit;

End;

/