subject part
Oracle JOB What kind of ？
Answer section
Oracle Of JOB There are two kinds of ,DBMS_JOB and DBMS_SCHEDULER, Both can complete timing tasks .
1、DBMS_JOB
DBMS_JOB Of SUBMIT The process parameters are as follows ：
SQL> DESC DBMS_JOB.SUBMIT
Parameter Type Mode Default?
——— ————– —- ——–
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN Y
INTERVAL VARCHAR2 IN Y
NO_PARSE BOOLEAN IN Y
INSTANCE BINARY_INTEGER IN Y
FORCE BOOLEAN IN Y
among , The description of each parameter is as follows ：
（1） Parameters JOB By SUBMIT() The process returns BINARY_INEGER. This value is used to uniquely identify a job , This parameter is a variable , It needs to be declared before use ,JOB The number is DBA_JOBS You can see in the view .
（2） WHAT The value of the parameter is to be JOB Executive PL/SQL Code block , It’s usually the name of the stored procedure , Remember to put a semicolon after the stored procedure . however , if WHAT The parameters for PL/SQL Anonymous block , You need to add a semicolon , for example ,what=> ‘begin null; end;’.
（3） NEXT_DATE Parameter indicates when to run this JOB,NEXT_DATE Need to be modified for the first time that the database performs this JOB Time for ,SYSDATE Means to execute immediately .
（4） INTERVAL The parameter represents this JOB When will it be executed again , Appoint JOB The operating cycle of ,INTERVAL If it is empty, it will be executed only once .
（5） NO_PARSE The parameter indicates this JOB Whether parsing should be done when submitting or executing ,TRUE On behalf of PL/SQL Code should be parsed the first time it executes , and FALSE For Ben PL/SQL Code should be parsed immediately , Creating JOB Check when you need to . The default value is FALSE.
It should be noted that ,NEXT_DATE Is the time type ,INTERVAL Is a character type , Calling SUBMIT The correct parameter type should be specified .
Here’s how to create DBMS_JOB An example of ：
DECLARE
V_JOB NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB =>V_JOB,
WHAT =>’PRO_TEST_JOB;’,
NEXT_DATE =>SYSDATE,
INTERVAL =>’SYSDATE + 1 / (24 * 60)’);
SYS.DBMS_OUTPUT.PUT_LINE(‘JOB NUMBER IS: ‘ || V_JOB);
COMMIT;
END;
2、DBMS_SCHEDULER
DBMS_SCHEDULER yes Oracle 10g A new package in , With the old version of DBMS_JOB Package comparison ,DBMS_SCHEDULER There are many new features . for example ,DBMS_SCHEDULER Can execute stored procedures 、 Anonymous blocks and OS Executable files and scripts （ Include Linux Systematic SHELL Script ）, You can also use DBMS_SCHEDULER Define… In more detail JOB Properties of .DBMS_SCHEDULER It has more detailed job running status and fault handling and reporting functions .
from Oracle 10g Start ,Oracle It is recommended to use SCHEDULER Replace the normal JOB To manage the execution of tasks .
The following example shows how to create a DBMS_SCHEDULER Type of JOB：
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => ‘AGENT_LIQUIDATION_JOB’, — To create JOB name
JOB_TYPE => ‘STORED_PROCEDURE’,
JOB_ACTION => ‘AGENT_LIQUIDATION.LIQUIDATION’, — Stored procedure name
START_DATE => SYSDATE,
REPEAT_INTERVAL => ‘FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0’, — On a monthly basis , The interval is 1 Months , monthly 1 Number , In the morning 1 Point to perform
COMMENTS => ‘SECOND’);–JOB Notes
END;