First, create a test form
--Create table
CREATE TABLE J_stock_month
(
CLASS3 VARCHAR2 () NOT NULL,
AMOUNT number (a) is not NULL,
Create_time DATE NOT NULL,
CREATOR VARCHAR2 (12)
)
Tablespace USERS
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);
--ADD comments to the columns
Comment on column j_stock_month. CLASS3
Is ' Level three directory ';
Comment on column j_stock_month. AMOUNT
is ' quantity ';
Ii. creation of stored procedures to be executed on a timed basis
Create or replace procedure Pro_j_stock_month as
BEGIN
INSERT into J_stock_month (class3,amount,create_time) SELECT substr (product_id,0,6), sum (AMOUNT), sysdate from J_stock GROUP by substr (product_id,0,6);
End
Create a job, which is the process of creating a scheduled task to be executed
------Execute every Minute
Variable job_j_stock_month number;
Begin
Dbms_job.submit (: Job_j_stock_month, ' pro_j_stock_month; ', sysdate, ' sysdate+1/1440 ');
End
----at 12 on the first day of the month.
Variable job_j_stock_month number;
Begin
Dbms_job.submit (: Job_j_stock_month, ' pro_j_stock_month; ', Sysdate, ' TRUNC (Last_day (sysdate)) +1+1/24 ');
End
Four, start the job, that is, start and run the scheduled task process
Begin
Dbms_job.run (: Job_j_stock_month);
End
Begin
Dbms_job.remove (: Job_j_stock_month);
End
V. Viewing the effect of running
Begin
Dbms_job.run (: Job_j_stock_month);
End
One: simple test job Create a process case:
1 , first create a job_test table, field is a Date Format
Sql> CREATE TABLE Job_test (a date);
Table created
Sql> commit;
Commit Complete
2 , create a stored procedure BB the effect is toward pig inserting data into the table
sql> Create or replace procedure job_pro_test as
2 begin
3 INSERT into job_test values (sysdate);
4 End;
5/
Procedure created
3 , create a job , the name is job2010 The function is every minute ( x24=1440 ) Executes the stored procedure once job_pro_test .
sql> variable job2010 number;
Sql> begin
2 Dbms_job.submit (: job2010, ' job_pro_test; ', sysdate, ' sysdate+1/1440 ');
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 The associated View
Select Job,last_date,last_sec,broken,failures,interval, what from Dba_jobs
A few more important fields in Dba_jobs
Job: Refers to the job ID number. Like the 41 above.
Failures:job the number of failures executed, if more than 15 times, then the broken column will be labeled Y, the job will not be run in the future
Broken: The default is N, and if y means that the job! is no longer executed
Interval: The interval between the time the job was executed.
What: The job's actual work.
II: Job relevant knowledge of:
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 to task permissions
Schema_user VARCHAR2 (30) User mode for syntactic analysis of tasks
Last_date date the last time the task was successfully run
Last_sec VARCHAR2 (8) hours, minutes and seconds of last_date date in HH24:MM:SS format
This_date date the start time of the task is running, or null if no task is running
This_sec VARCHAR2 (8) hours, minutes and seconds of this_date date in HH24:MM:SS format
Next_date date Time of the next scheduled run of the task
Next_sec VARCHAR2 (8) hours, minutes and seconds of next_date date in HH24:MM:SS format
Total_time number The total time, in seconds, required for the task to run
Broken VARCHAR2 (1) flag parameter, y indicates the task is interrupted and will not run later
INTERVAL VARCHAR2 (200) expression for calculating the next run time
Failures number task runs with no successive successes
What VARCHAR2 (2000) PL/SQL block for performing tasks
Current_session_label RAW MLSLABEL Trust Oracle Session break 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) NLS session settings for task runs
Misc_env RAW (32) Some other session parameters that the task runs
--------------------------
2 , Description INTERVAL parameter Values
12 O'Clock Midnight: ' 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 '
Midnight of the first day of every month 12 o'clock: ' TRUNC (Last_day (sysdate) + 1) '
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) '
25th # 00:00 per month executed: ' TRUNC (Last_day (sysdate) + 25) '
--------------------------
1: Execute Every Minute
Interval = TRUNC (sysdate, ' mi ') + 1/(24*60)
Or
Interval = sysdate+1/1440
2: Regular execution every day
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
For example: 1st 1 o'clock in the morning every month to execute
Interval =>trunc (Last_day (sysdate)) +1+1/24
5: Quarterly Scheduled execution
For example, the first day of each quarter is 1 o'clock in the morning execution
Interval = TRUNC (Add_months (sysdate,3), ' Q ') + 1/24
6: Every half-yearly scheduled execution
For example: Every July 1 and January 1 1 o'clock in the morning
Interval = Add_months (trunc (sysdate, ' yyyy '), 6) +1/24
7: Scheduled execution every year
For example: January 1 1 o'clock in the morning every year to execute
Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +1/24
3. View related job information
Related views
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running job
Three: Practical application
Step One: Create a job , monthly Day 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) > and Ib_qty = 0;commit; ', Sysdate, ' TRUNC (Last_day (sysdate) + 25) '); <== 25th # 00:00 per month
Sql> end;
Sql>/
Step two: Query the 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 job time of next execution
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>/
Related Information1.a detailed description of Oracle job usage and management skills http://blog.csdn.net/apicescn/archive/2009/09/01/4507961.aspx 2. Applying Oracle job and stored procedures http://hi.baidu.com/tokios/blog/item/50b43e6d468592ff431694b8. HTML 3. Turn from: http://blog.sina.com.cn/s/blog_5384afff010171b3.html