Oracle_pl/sql (10) Timer job

Timer Job
1. Definition
A timer is a specific operation that is performed at a specific time.
Can be executed multiple times.
Description
Specific actions: Refers to a stored procedure that completes a specific function.
Multiple executions: It is possible to run periodically, hourly, daily, weekly, Monthly, quarterly, yearly, etc.
2. Preparatory work
SOURCE table: STUDENT,COURSE,SC
CREATE TABLE Student
(
Sno Number (6) Not null primary key,
Sname varchar2 (20),
Birth date
);
CREATE TABLE Course
(
CNO Number (6) Not null primary key,
CNAME varchar2 (20),
Teacher Varchar2 (20)
);
CREATE TABLE SC
(
Sno Number (6) is not NULL,
CNO Number (6) is not NULL,
Grade Number (6)
);
ALTER TABLE SC add constraint PK_SC primary key (Sno, CNO);

--Data
INSERT into student (Sno, sname, birth) VALUES (1, ' sname1 ', to_date (' 06-05-1994 ', ' dd-mm-yyyy '));
INSERT into student (Sno, sname, birth) VALUES (2, ' sname2 ', to_date (' 13-11-1995 ', ' dd-mm-yyyy '));
INSERT into student (Sno, sname, birth) VALUES (3, ' Sname3 ', to_date (' 02-08-1995 ', ' dd-mm-yyyy '));
INSERT into student (Sno, sname, birth) VALUES (4, ' Sname4 ', to_date (' 18-10-1990 ', ' dd-mm-yyyy '));
INSERT into student (Sno, sname, birth) VALUES (5, ' Sname5 ', to_date (' 11-10-1992 ', ' dd-mm-yyyy '));
INSERT into student (Sno, sname, birth) VALUES (6, ' Sname6 ', to_date (' 06-09-1993 ', ' dd-mm-yyyy '));
INSERT into student (Sno, sname, birth) VALUES (7, ' Sname7 ', to_date (' 10-10-1994 ', ' dd-mm-yyyy '));
INSERT into student (Sno, sname, birth) VALUES (8, ' Sname8 ', to_date (' 16-10-1992 ', ' dd-mm-yyyy '));
Insert into course (CNO, CNAME, teacher) VALUES (1, ' math ', ' Zhang San ');
Insert into course (CNO, CNAME, teacher) VALUES (2, ' language ', ' John Doe ');
Insert into course (CNO, CNAME, teacher) VALUES (3, ' English ', ' Harry ');
INSERT INTO SC (SNO, CNO, Grade) VALUES (1, 1, 80);
INSERT INTO SC (SNO, CNO, Grade) VALUES (1, 2, 75);
INSERT INTO SC (SNO, CNO, Grade) VALUES (2, 1, 69);
INSERT INTO SC (SNO, CNO, Grade) VALUES (2, 3, 85);
INSERT INTO SC (SNO, CNO, Grade) VALUES (3, 2, 98);
INSERT INTO SC (SNO, CNO, Grade) VALUES (3, 3, 73);
INSERT INTO SC (SNO, CNO, Grade) VALUES (4, 1, 55);
INSERT INTO SC (SNO, CNO, Grade) VALUES (4, 2, 76);
INSERT INTO SC (SNO, CNO, Grade) VALUES (4, 3, 80);
INSERT INTO SC (SNO, CNO, Grade) VALUES (5, 1, 58);
INSERT INTO SC (SNO, CNO, Grade) VALUES (5, 2, 79);
INSERT INTO SC (SNO, CNO, Grade) VALUES (5, 3, 65);
INSERT INTO SC (SNO, CNO, Grade) VALUES (6, 2, 78);
INSERT INTO SC (SNO, CNO, Grade) VALUES (6, 3, 86);
INSERT INTO SC (SNO, CNO, Grade) VALUES (7, 1, 68);
INSERT INTO SC (SNO, CNO, Grade) VALUES (8, 1, 90);

Target table: S_SC
CREATE TABLE S_SC
(
Sname varchar2 () NOT NULL primary key,
C_grade Number (6),
M_grade Number (6),
E_grade Number (6)
);

--Procedure: Proc_ssc_insert
Create or replace procedure Proc_ssc_insert
is
v_cnt number;
Begin
for RS in (select Sno,sname from student) loop
Select COUNT (1) to v_cnt from S_SC where sname=rs.sname;< br> if v_cnt=0 then
INSERT into S_SC (sname) VALUES (rs.sname),
End If;
for rs2 in (select Grade,cname from SC , course
where sc.cno=course.cno and Sno=rs.sno) loop
If rs2.cname= ' language ' then
Update S_SC set C_grade=rs2.grade where Sname=rs.sname;
elsif rs2.cname= ' math ' then
Update s_sc set m_grade=rs2.grade where sname=rs.sname;
elsif rs2.cname= ' English ' then Update S_SC set e_grade=rs2.grade where Sname=rs.sname;
End If;
End Loop;
End Loop;
End;
/
Show err;
Check data:
SELECT * FROM Student
Select * FROM Course
SELECT * FROM SC
SELECT * from S_SC

3. Create a job
Variable Jobid number;
Begin
Sys.dbms_job.submit (Job =: Jobid,
what = ' proc_ssc_insert; ',
Next_date = To_date (' 2018-05-14 10:45:00 ', ' yyyy-mm-dd hh24:mi:ss '),
Interval = ' TRUNC (sysdate) +1+1/(24) ');
Commit
End
/

var jobid number;
Begin
Dbms_job.submit (: Jobid, ' Proc_ssc_insert; ', Trunc (sysdate) +16/24, ' Trunc (sysdate) +1+16/24 ');
End
/
Show err;

4. Execution interval interval
This means that it can run periodically, hourly, daily, weekly, Monthly, quarterly, yearly, etc.
4.1: Execute every Minute
Interval = trunc (sysdate, ' mi ') + 1/(24*60)
Executes every 5 minutes
Interval = trunc (sysdate, ' mi ') + 5/(24*60)
4.1: Hourly Execution
Interval = trunc (sysdate, ' hh24 ') + 1/(24)
4.2: Regular execution every day
Example: Daily 1 o'clock in the morning execution
Interval = trunc (sysdate) +1+1/(24)
4.3: Regular Weekly execution
For example: Every Monday 1 o'clock in the morning execution
Interval = trunc (Next_day (sysdate, ' Monday ')) +1/24
4.4: Regular Monthly execution
For example: 1st 1 o'clock in the morning every month to execute
Interval =>trunc (Last_day (sysdate)) +1+1/24
Interval =>trunc (add_months (sysdate,1), ' mm ') +1/24
4.5: Quarterly Scheduled execution
Example: 1 o'clock in the morning on the first day of each quarter
Interval = trunc (add_months (sysdate,3), ' Q ') + 1/24
4.6: Scheduled execution every year
For example: January 1 1 o'clock in the morning every year to execute
Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +1/24

5. Related views of the job in the data dictionary
Job Information
SELECT * from User_jobs;
A running job
SELECT * from Dba_jobs_running;

6. Run the job
Begin
Dbms_job.run (: Job);
End
/

7. Delete Job
EXEC dbms_job.remove (: Job);
Commit
Example:
EXEC Dbms_job.remove (23);
commit;--is not deleted without a commit.

8. Abort Job
EXEC Dbms_job.broken (: job,true);
9. Modify the Job
Dbms_job.change (: Job,:what,:next_date,:interval);
Dbms_job.what (: Job,:what);
Dbms_job.next_date (: job,:next_date);
Dbms_job.instance (: job,:instance);
Dbms_job.interval (: Job,:interval);
Dbms_job.broken (: job,:broken,:next_date);

10.job-related features.
sql> desc dbms_job;
Element Type
------------------ ---------
Any_instance CONSTANT
Isubmit PROCEDURE
SUBMIT PROCEDURE Create Job
Remove PROCEDURE Delete Job
Change PROCEDURE Modify Job
What PROCEDURE Modify the call procedure
Next_date PROCEDURE Modify the next run time
INSTANCE PROCEDURE
INTERVAL PROCEDURE Modify Run interval
Broken PROCEDURE Abort Job
Run PROCEDURE running the job
User_export PROCEDURE
Background_process FUNCTION
IS_JOBQ FUNCTION

Oracle_pl/sql (10) Timer job