Oracle job scheduled execution of Stored Procedures

I. Simple test jobExample of the creation process:

1. Create a job_test table with the field in a date format.

SQL> Create Table job_test (a date );

Table created

SQL> commit;

Commit complete

2. Create a stored procedure. BB inserts data into the pig table.

SQL> Create or replace procedure job_pro_test

2 begin

3 insert into job_test values (sysdate );

4 end;

5/

Procedure created

 

3. Create a job named job2010 to execute the Stored Procedure job_pro_test every minute (60 × 24 = 1440.

SQL> variable job2010 number;

SQL> begin

2 dbms_job.submit (: job2010, 'job _ pro_test; ', sysdate, 'sysdate + 100 ');

3 end;

4/

Note: here the system automatically generates a job ID of 41

PL/SQL procedure successfully completed

Job2010

---------

41

4. Run job2010.

SQL> begin

2 dbms_job.run (: job2010 );

3 end;

4/

PL/SQL procedure successfully completed

Job2010

---------

41

5. Delete job2010

SQL> begin

2 dbms_job.remove (: job2010 );

3 end;

4/

6. query jobs-related views

Select job, last_date, last_sec, broken, failures, interval, what from dba_jobs

Several important fields in dba_jobs

Job: the ID of a job. For example, the above 41

Failures: number of failed job execution times. If the number of failed jobs exceeds 15, the broken column is marked as Y, and the job will not run in the future.

Broken: The default value is N. If it is Y, the job is not executed!

Interval: The interval between job execution.

What: the actual work of the job.

Ii. JobRelated Knowledge:

1, dba_jobs

Dba_jobs

========================================================== ===

Field (column) type description

Unique ID of a job number

Log_user varchar2 (30) user who submits the task

Priv_user varchar2 (30) user with task Permissions

Schema_user varchar2 (30) User Mode for Syntactic Analysis of tasks

Last_date date the time when the last task was successfully run

Last_sec varchar2 (8) such as hh24: mm: hour, minute, and second of the SS format last_date

The start time of the task being run by this_date. If no task is running, the value is null.

This_sec varchar2 (8) such as hh24: mm: the hour, minute, and second of the this_date date in SS format

Next_date date the next scheduled task running time

Next_sec varchar2 (8) such as hh24: mm: the hour, minute, and second of the SS format next_date

Total_time number the total time required to run the task, in seconds

The broken varchar2 (1) Flag parameter. y indicates that the task is interrupted and will not run later.

Interval varchar2 (200) is the expression used to calculate the next line time.

Failures number the number of times the task failed to run continuously

What varchar2 (2000) PL/SQL block for task execution

Current_session_label raw mlslabel trust Oracle session character of the task

Clearance_hi raw mlslabel maximum gap between Oracle databases trusted by this task

Clearance_lo raw mlslabel minimum Oracle gap trusted by this task

NLS session settings for running the nls_env varchar2 (2000) task

Other session parameters of the misc_env raw (32) task

--------------------------

2. Description of the interval parameter value

Midnight every day: 'trunc (sysdate + 1 )'

08:30 every morning: 'trunc (sysdate + 1) + (8*60 + 30)/(24*60 )'

Every Tuesday: 'Next _ day (trunc (sysdate), ''tuesday'') + 123'

Midnight on the first day of each month: 'trunc (last_day (sysdate) + 1 )'

On the last day of each quarter: 'trunc (add_months (sysdate + 2/24, 3), 'q')-100'

06:10 every Saturday and morning: 'trunc (least (next_day (sysdate, ''saturday "), next_day (sysdate," Sunday ") + (6 × 60 + 10) /(24 × 60 )'

Run 'trunc (last_day (sysdate) + 25) 'at every month )'

--------------------------

1: executed per minute

Interval => trunc (sysdate, 'mi') + 1/(24*60)

Or

Interval = & gt; sysdate + 1/1440

 

2: daily scheduled execution

Example: Execute at every day

Interval => trunc (sysdate) + 1 + 1/(24)

3: scheduled weekly execution

Example: Execute at every Monday

Interval => trunc (next_day (sysdate, 'monday') + 1/24

 

4: scheduled monthly execution

Example: Execute at on the first day of every month

Interval => trunc (last_day (sysdate) + 1 + 1/24

 

5: Periodical execution on a quarterly basis

For example, the statement is executed at on the first day of each quarter.

Interval => trunc (add_months (sysdate, 3), 'q') + 1/24

 

6: scheduled execution every six months

For example, at a.m. on January 1, July 1 and January 1, January 1

Interval => add_months (trunc (sysdate, 'yyyy'), 6) + 1/24

 

7: scheduled execution every year

Example: Execute at on January 1, January 1 every year.

Interval => add_months (trunc (sysdate, 'yyyy'), 12) + 1/24

3. View related job information

Related View

Dba_jobs

All_jobs

User_jobs

Dba_jobs_running contains information about running jobs.

Iii. Practical Application

Step 1: Create a job and execute the following SQL statement on the 25 th day of each month.

SQL> variable job2010 number;

SQL> begin

SQL> dbms_job.submit (: job2010, 'delete from i0216_inv_balance_curstock where trunc (sysdate-mtime)> 90 and ib_qty = 0; commit; ', sysdate, 'trunc (last_day (sysdate) + 25) '); <= execution at on the 25 th of every month

SQL> end;

SQL>/

 

Step 2: query the generated job ID

SQL> select job, what from dba_jobs;

Job what

61 ..........

 

Step 3: run the created job

SQL> begin

SQL> dbms_job.run (61 );

SQL> end;

SQL>/

 

Step 4: query the next execution time of the job

SQL> select job, next_date, what from dba_jobs

SQL> where job = '61 ';

 

Step 5: Delete the job

SQL> begin

SQL> dbms_job.remove (61 );

SQL> end;

SQL>/

Related Materials1. Explain the use and management skills of Oracle job http://blog.csdn.net/apicescn/archive/2009/09/01/4507961.aspx2. Apply Oracle job and stored procedure http://hi.baidu.com/tokios/blog/item/50b43e6d468592ff431694b8.html