Oracle Learning (24) Timed tasks

We sometimes have a demand that the database can help us do something regularly. For example, we want the database to be able to aggregate data from one table in the library, or from some tables, into another sheet on a regular basis. Then we can do this by using the periodic tasks of the database.

Let's give a simple example, if we have the following two tables, T1 and T2.

T1 and the corresponding sequence:

CREATE Table t1 (A1 integer primary key, A2 VARCHAR2, A3 varchar2 (), A4 date);  
      
Create sequence seq_t1 start with 1 increment by 1;

T2 and the corresponding sequence:

CREATE Table t2 (A1 integer primary key, A2 varchar2 (), A3 date);  
      
Create sequence seq_t2 start with 1 increment by 1;

We need to insert the A2 and A4 in the table T1 into the table A2 as the A3 and T2 inside the table T2 every two o'clock in the morning. Oracle's recurring tasks are timed to perform a task, but the specific tasks need to be specified by ourselves. So first we have to build our own task, which is usually a stored procedure. Here we set up the following stored procedures:

Create or replace procedure copy_t1_to_t2 as
  starttime Date: = Trunc (sysdate-1);--Yesterday at 0 o '  
  endtime   Date: = Trun C (sysdate); --Today 0 o'clock  
  cursor t1_cursor
    is select A2, A4 from T1 where A4 >= starttime and A4  
       < Endtime;--Define a tour Mark Query T1 yesterday's records  
begin for
  T in T1_cursor loop  
    inserts into T2 (A1, A2, A3) VALUES (Seq_t2.nextval, T.A2, t.a4); The relevant data inside the T1 is

inserted into the T2  
    commit;--inserts a record to commit an end  
  loop;  
End

Set up a timed task

Once the task is established, we can submit it to Oracle's timed task queue, where we build the code as follows:

sql> var job number;

Sql> begin

2 Dbms_job.submit (

3 Job =>: Job,

4 What => ' copy_t1_to_t2; ',

5 next_date => to_date (' 2013-11-20 02:00:00 ', ' yyyy-mm-dd hh24:mi:ss '),

6 interval => ' trunc (sysdate+1) +2/24 '

7);

8 commit;

9 End;

10/