First, create a self-increasing ID field in Oracle
1. Create a sequence ???? Create * sequence_name *? Increment by 1 start with 1 maxValue 999999999; 2, create triggers --- (Create a Before INSERT trigger based on the table, using this sequence in the trigger) ????? create or replace trigger * Triger_name * ????? before insert on ??????? * table_name * ????? Referencing Old AS old new as new for each row ????? Begin ????? SELECT? * SEQUENCE_NAME.NEXTVAL INTO: New.id from Dual; ????? End; ????? / 2, Oracle Application 1, create stored procedures ????? CREATE OR REPLACEDURE ??? * pro_name ????? as ????? begin ?????? INSERT INTO AGRI_EXHIBITION_BASECUR (Messid, Title, Type, Pub_Date) ?? Select ID, Title, Sort, Pub_Date from ?? Agri_Message ??? WHERE? (Trunc (sysdate-pub_date) = 0 and rownum <6) and sort = 'supply'; ?????? INSERT INTO AGRI_EXHIBITION_BASECUR (Messid, Title, Type, Pub_Date) ?? Select ID, title, sort, pub_date from ?? Agri_Message? Where? (trunc (sysdate-pub_date) = 0 and rownum <6) and sort = 'seeking'; ????? End; ????? / 2 Create Job ????? variable jobdxm number; ????? begin ?????? dbms_job.submit (: JobDXM, 'Exhi_Pro_DXM;', Sysdate, 'Trunc (sysdate,' DD ') 32.5 / 24 '); ????? end; three, a simple JOB listed
1. Create a test table ????? SQL> CREATE TABLE A (A Date);
????? Table has been created.
2. Create a custom process
SQL> CREATE OR REPLACE DURE TEST AS? 2? Begin? 3? INSERT INTO a VALUES (SYSDATE);? 4? End ;? 5? /
The process has been created.
3. Create Jobsql> Variable Job1 Number; SQL> SQL> BEGIN? 2? Dbms_job.submit (: Job1, 'Test;', sysdate, 'sysdate 1/1440'); - 1440 minutes a day, one minute run TEST process once? 3? end ;? 4? /
The PL / SQL process has been successfully completed.
4, run jobsql> begin? 2? Dbms_job.run (: job1) ;? 3? End ;? 4? /
The PL / SQL process has been successfully completed.
SQL> SELECT TO_CHAR (A, 'YYYY / MM / DD HH24: MI: SS') Time from A;
Time ------------------- 2001/01/07 23: 51: 212001/01/07 23: 52:222001/01/07 23:53:24
5. Delete Jobsql> Begin? 2? Dbms_job.remove (: JOB1) ;? 3? End ;? 4? / Pl / sql process has been successfully completed. 6, some necessary parameters modified the initsid.ora parameter job_queue_proces = 4job_Queue_Interval = 10JOB_QUEUE_KEEP_CONNACTIONS = TRUE
Modify the number of executable jobs is 20 alter system set job_queue_processes = 20
Modify the cancellation limit mode ALTER SYSTEM Disable Restricted session; 7, two necessary tables ????? user_jobs and dba_jobs_running8, related several Job operation delete job: dbms_job.remove (jobno); modify the action to do: Job: DBMS_Job.What (Jobno, What); Modify Next Performer Time: DBMS_Job.next_Date (Job, Next_Date); Modify Interval: DBMS_Job.ITERVAL (Job, Interval); Stop Job: dbms.broken (Job, Broken, Nextdate) Start Job: DBMS_Job.Run (Jobno);
