Examples of the use of Oracle job + stored Procedures

Guide:

Part I: The following steps complete the process of creating a job:

Part II: Job Related knowledge:

Part III: Practical application

Discovery: Delete jobs must use the job owner to do, Sys can not delete other people's job. (Understand this first and then correct it later.) Hey hehe ·)

Body:

The first part: The following steps complete the job the creation process:

1, first create a pig table, the field is a date format

sql> CREATE TABLE Pig (a date);

Table created

Sql> commit;

Commit Complete

2, create a stored procedure BB action is to insert data into the Pig table

sql> Create or replace procedure BB as

2 begin

3 INSERT INTO pig values (sysdate);

4 End;

5/

Procedure created

3, create a job, the name is job2009, the function is to execute a stored procedure every minute (60x24=1440) BB.

sql> variable job2009 number;

Sql> begin

2 Dbms_job.submit (: job2009, ' BB; ', Sysdate, ' sysdate+1/1440 ');

3 END;

4/

Note: This system automatically generates a job ID of 41

Pl/sql procedure successfully completed

job2009

---------

41

4, run job2009

Sql> begin

2 Dbms_job.run (: job2009);

3 END;

4/

Pl/sql procedure successfully completed

job2009

---------

41

5, delete job2009

Sql> begin

2 Dbms_job.remove (: job2009);

3 END;

4/

6, check the related view of jobs

Selectjob,last_date,last_sec,broken,failures,interval, Whatfromdba_jobs

Detailed dba_jobs several more important fields

Job: Refers to the job's ID number. Like the top 41.

Failures:job execution times, if more than 15 times, then the broken column will be labeled Y, will not run the job in the future

Broken: The default is n, if y, which means that the job is no longer executed.

Interval: The interval between the execution of the job.

What: The actual work of the job.

Part Two: Job the relevant knowledge:

1,dba_jobs

Dba_jobs

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

field (column) type description

Unique identifier of the job number task

Log_user VARCHAR2 (30) the user who submitted the task

Priv_user VARCHAR2 (30) Users assigned permissions to the task

Schema_user VARCHAR2 (30) User mode for parsing a task

Last_date date the last time the task was successfully run

Last_sec VARCHAR2 (8) such as HH24:MM:SS format last_date date hours, minutes, and seconds

This_date date is running the start time of the task, or null if the task is not running

This_sec VARCHAR2 (8) such as HH24:MM:SS format this_date date hours, minutes, and seconds

Next_date date the next time the task is scheduled to run

Next_sec VARCHAR2 (8) such as HH24:MM:SS format next_date date hours, minutes, and seconds

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

Broken VARCHAR2 (1) flag parameter, y indicates that the task is interrupted and will not run later

INTERVAL VARCHAR2 (200) An expression used to calculate the next run time

Failures number task runs continuously without success

WHAT VARCHAR2 (2000) Pl/sql block to perform a task

Current_session_label RAW Mlslabel Trusted Oracle session character for this task

Clearance_hi RAW Mlslabel Oracle maximum gap that the task can trust

Clearance_lo RAW Mlslabel Oracle minimum gap that the task can trust

Nls_env VARCHAR2 (2000) The NLS session settings that the task runs

Some other session parameters that the Misc_env RAW (32) task runs

－－－－－－－－－－－－－－－－－－－－－－－－－－

2, describe interval parameter value

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

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

Every Tuesday noon 12 o'clock ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '

12 O'Clock ' TRUNC (Last_day (sysdate) + 1) ' At midnight on the first day of the month

Each quarter on the last day of the evening 11 o'clock ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '

Every Saturday and 6:10 A.M. ' TRUNC (Next_day (sysdate, ' SATURDAY '), Next_day (Sysdate, "SUNDAY"))) + (6X60+10)/(24x60) '

－－－－－－－－－－－－－－－－－－－－－－－－－－

1: Per minute execution

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

Or

Interval => sysdate+1/1440

2: Regular daily execution

Example: Daily 1 o'clock in the morning execution

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

3: Regular Weekly execution

For example: Every Monday 1 o'clock in the morning execution

Interval => TRUNC (Next_day (sysdate, ' Monday ')) +1/24

4: Regular Monthly execution

Example: Monthly 1st 1 o'clock in the morning

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

5: Quarterly Regular execution

For example, the first day of every quarter, 1 o'clock in the morning execution

Interval => TRUNC (add_months (sysdate,3), ' Q ') + 1/24

6: Every six months regular execution

For example: July 1 and January 1 every year 1 o'clock in the morning

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

7: Regular Annual execution

For example: January 1 every year 1 o'clock in the morning execution

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

Part III: Practical application

-->-->--></style. < p>

Step One: Create a job, 25th every month to execute the following SQL.

sql>variable job2010 number;

Sql>begin

Sql>dbms_job.submit (: job2010, ' delete from I0216_inv_balance_curstock where trunc (sysdate-mtime) > 90and ib_qty =     0;commit ', Sysdate, ' TRUNC (Last_day (sysdate) + 25) '); <== 25th # 00:00 per month for execution

sql>end;

sql>/

Step Two: query-generated Job ID

Sql> Select Job, what from Dba_jobs;

Job what

61 .....

Step three: Run the job you just created

Sql> begin

Sql> Dbms_job.run (61);

Sql> end;

Sql>/

Step four: Query the next execution time for the job

Sql> Select Job,next_date,what from Dba_jobs

sql> where job= ' 61 ';

Step Five: Delete the job

Sql> begin

Sql> Dbms_job.remove (61);

Sql> end;

Sql>/

Reference Documentation:

-->-->-->-->--></style. < p>

Http://blog.oracle.com.cn/index.php/237208/viewspace-28674.html