Oracle jobs beginners

[Oracle] jobs for beginners: a job is an oracle object. It can be understood as a program for scheduled execution. Objective: To automatically execute specific code at regular intervals-create a job

1. create the test table JOB_TEST [SQL] create table JOB_TEST (a date ); 2. create a stored procedure and insert data into the test table [SQL] create or replace procedure JOB_PRO_TEST as begin insert into JOB_TEST values (sysdate); end;/3. create a job named jobtest, used to execute the stored procedure once per minute [SQL] variable jobtest number; begin dbms_job.submit (: jobtest, 'job _ PRO_TEST; ', sysdate, 'sysdate + 100', true); end; /another one: [SQL] declare jobtest number; begin dbms_job.submit (job => jobtest, what => 'job _ PRO_TEST; ', next_date => sysdate, interval => 'sysdate + 1/1440 ', no_parse => true); end ;/

 

After the job is created, it is run. Instead of using run to complete the creation, the job starts to work and is automatically inserted to see the effect. 4. run jobtest manually.

[SQL] begin dbms_job.run (: jobtest); end;/5. delete jjobtest [SQL] begin dbms_job.remove (: jobtest); end;/another [SQL] begin dbms_job.remove (jobnomuber); end ;/

 

Jobnomuber is the job number, which can be blocked through queries -- Problem 1: solution: In pl/SQL, if you declare the job number name using variable, an 'invalid SQL statement' exception is thrown, use the declare keyword. Problem 2: solution: Use the following method: jobtest. Note that the colon may throw an exception where not all variables are bound. solution: remove the colon. Question 3: If the name such as jobtest is used during run or remove, an exception 'not all variables are bind' may be thrown; removing the colon and directly using the name jobtest may throw 'jobtest not declared '. solve this problem: query the job ID you created, and then run the job number.

[sql] select * from user_jobs;   select job  from all_jobs;  

 

PlSqlDev operation job create job1, select job folder, right-click 2, click New 3, fill in the corresponding complete, you can click "view SQL" to view the SQL statement, OK, click "application". 4. At this time, the corresponding job in the job folder is created. The job number is directly visible. Right-click the job and you can perform relevant operations to find out why-knowledge understanding of the job parameters: is the binary_ineger returned by the Submit () process. Work ID, which uniquely identifies a job. What parameter: the PL/SQL code block to be executed. Next_date parameter: specifies when the job will be run. Interval parameter: the time when the job will be re-executed. 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 when it is executed for the first time, FALSE indicates that the PL/SQL code should be analyzed immediately. Description of the INTERVAL parameter value, which defines the execution frequency of the job (some to be verified) at midnight every day: 'trunc (SYSDATE + 1) '08:30 every day: 'trunc (SYSDATE + 1) + (8*60 + 30)/(24*60) 'every TUESDAY: 'Next _ DAY (TRUNC (SYSDATE), 'tuesday '') + 12/24 'midnight on the first day of each month: 'trunc (LAST_DAY (SYSDATE) + 1) 'on the last day of each quarter: 'trunc (ADD_MONTHS (SYSDATE + 2/24, 3), 'q')-1/24 'every SATURDAY and 06:10 AM: 'trunc (LEAST (NEXT_DAY (SYSDATE, ''saturday "), NEXT_DAY (SYSDATE, "SUNDAY") + (6 × 60 + 10)/(24 × 60) 'execution per minute: 'trunc (sysdate, 'mi ') + 1/(24*60) 'or 'sysdate + 100' run at every day: 'trunc (sysdate) + 1 + 1/(24)' scheduled weekly execution: run 'trunc (next_day (sysdate, 'monday') + 1000' at every Monday: 'trunc (LAST_DAY (SYSDATE) + 1 + 100' quarterly scheduled execution: am on the first day of each quarter: 'trunc (ADD_MONTHS (SYSDATE, 3 ), 'q') + 1/24 'scheduled execution every six MONTHS: A.M. on January 1, July 1 and January 1, January 1: 'add _ MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/24 'annual scheduled execution: Execute at on January 1, 1/24 each year: 'add _ MONTHS (trunc (sysdate, 'yyyy'), 12) + 100'