Create a job demo for Oracle

Create a job demo for Oracle 1. first, enable the OracleJobSchedulerORCL server, which is mainly used to manage the timer of oracle tasks 2. then execute the following script in pl/SQL: (Note the command window instead of the SQL window)

Create or replace procedure DeleteDataForJob as hisDataTime number; beginhisDataTime: = 12; // delete data before January 1, December from hps_passvehicleinfo p where p. pass_time <= add_months (sysdate,-hisDataTime); commit; end;/variable job number; begindbms_job.submit (: job, 'deletedataforjob; ', trunc (sysdate + 1 ), 'trunc (sysdate + 1) + 1'); // It is executed once every day at, every day commit; end ;/

 

Get a job id that is an integer. 3. Remember the value of the job variable and pass the value to the following method:

Exec dbms_job.run (82); // 82 is the id of the task scheduler.

 

If you want to execute it once every three months: dbms_job.submit (: job, 'deletedataforjob; ', trunc (sysdate + 1), 'trunc (sysdate + 1) + 90 '); // execute the statement after 90 days. If you want to execute the statement at three o'clock every morning: dbms_job.submit (: job, 'deletedataforjob; ', trunc (sysdate + 1) + 3/24,' (trunc (sysdate + 1) + 3/24) + 1'); // executed after 90 days

 

Create an ORACLE JOBoracle job description the main usage scenarios are scheduled to perform related operations in the background: for example, every night, will save the data of one table to another table, 2: regular backup database and other mature said everything is difficult at the beginning, here I simply describe a simple job creation steps are as follows:

1. create a table g_test create table G_TEST (id number (12), C_DATE DATE) 2. create a sequence create sequence G_SEQ minvalue 1 maxvalue 999999999999999999999999999 start with 141 increment by 1 cache 20; 3. create or replace procedure prc_g_test is begin insert into g_test values (g_seq.nextval, sysdate); end prc_g_test; 4. create a job and use the Submit () process, work is properly planned. This process has five parameters: job, what, next_date, interval, and no_parse. PROCEDURE Submit (job OUT binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean: = FALSE) the job parameter is the binary_ineger returned by the Submit () process. This value uniquely identifies a job. What parameter is the PL/SQL code block to be executed. The next_date parameter specifies when the job will be run. The interval parameter indicates when the job will be re-executed. The no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution -- TRUE indicates that the PL/SQL code should perform syntax analysis during its first execution, FALSE indicates that the PL/SQL code should be analyzed immediately. In the command window, execute the following script variable job1 number; begin sys. dbms_job.submit (job =>: job, what => 'prc _ g_test; ', next_date => to_date ('22-10-2008 10:06:41', 'dd-mm-yyyy hh24: mi: ss'), interval => 'sysdate + 100'); -- 1/1440 minutes a day, that is, one minute to run the test process commit; end; /-------------------------------------------------------------------------------- in plSQL, my approach is: declare job number; beginsys. dbms_job.submit (job, 'prc _ g _ Test; ', sysdate, 'sysdate + 1/1440'); end; 5. view the created job and related job information. 1. The dba_jobs all_jobs user_jobs dba_jobs_running view contains information about the running job. For example, select * from dba_jobs 6. Run JOB description: The Run () process is used to immediately execute a specified JOB. This process only receives one parameter: SQL> begin 2 dbms_job.run (: job); 3 end; 4/else in plSQL, my approach is: begindbms_job.run (3017); end; 7. Delete job SQL> begin 2 dbms_job.remove (: job); --: the job can be replaced by the value of dba_jobs.job, for example, 1198 3 end; 4/

 

Note: You can directly define the variable in the sqlplus command line !!!!