以下列举几个有关使用dbms_scheduler创建job的例子(使用dbms_scheduler创建job需要具有create any job的权限)
1.Create Job 用法1 job_action里执行没有变量参数的过程
-- 创建每小时跑一次的job
Begin
dbms_scheduler.create_job(
job_name =>'insert_sql',
job_type =>'PLSQL_BLOCK',
job_action =>'Declare Begin sp_insert_tb_hxl_sql;end;',
number_of_arguments =>0,
start_date =>sysdate,
repeat_interval =>'FREQ=Hourly',
end_date =>NULL,
job_class =>'DEFAULT_JOB_CLASS',
enabled =>TRUE,
auto_drop =>FALSE,
comments =>'TEST',
credential_name =>NULL,
destination_name =>Null
);
End;
2.Create Job 用法2 job_action里执行有固定值参数的过程的时候，需要创建programe
首先创建Program
Begin
Dbms_Scheduler.Create_Program(Program_Name => 'test_plsql_block_prog',
Program_Type => 'PLSQL_BLOCK',
Program_Action => 'Declare
l_sp_name Varchar2(20);
l_run_interval Varchar2(20);
Begin
l_sp_name:=''sp_t2'';
l_run_interval:=''Hourly'';
sp_insert_sched_tb(l_sp_name,l_run_interval);
end;',
Enabled => True,
Comments => 'Program to gather SCOTT''s statistics using a PL/SQL block.');
End;
然后再创建job
Begin
Dbms_Scheduler.Create_Job(Job_Name => 'test_prog_job_definition',
Program_Name => 'test_plsql_block_prog',
Start_Date => Systimestamp,
Repeat_Interval => 'freq=hourly; byminute=0',
End_Date => Null,
Enabled => True,
Comments => 'Job defined by existing program and inline schedule.');
End;
基于Schedule的Job
首先创建Schedule
Begin
Dbms_Scheduler.Create_Schedule(Schedule_Name => 'ten_minutes_run_by_once',
Start_Date => Sysdate,
Repeat_Interval => 'freq=minutely; interval=10',
End_Date => Null,
Comments => 'Run once every 10 minutes!');
End;
然后创建Job
Begin
Dbms_Scheduler.Create_Job(Job_Name => 'job_run_once_by_10minutes',
Program_Name => 'test_plsql_block_prog',
Schedule_Name => 'ten_minutes_run_by_once',
Job_Class => 'DEFAULT_JOB_CLASS',
Enabled => True,
Auto_Drop => False,
Comments => 'my_test',
Job_Style => 'REGULAR',
Credential_Name => Null,
Destination_Name => Null);
End;
4.使用带参数的program创建job
Begin
Dbms_Scheduler.Create_Program(Program_Name => 'pm_test',
Program_Type => 'STORED_PROCEDURE',
Program_Action => 'sp_insert_sched_tb',
Number_Of_Arguments => 3,
Enabled => False,
Comments => 'pm_test');
End;
若指定了Number_Of_Arguments则enabled必须设置为false,待定义了参数后才能设置为true.
设定'sp_insert_sched_tb过程的参数1
Begin
Dbms_Scheduler.Define_Program_Argument(Program_Name => 'pm_test',
Argument_Position => 1,
Argument_Name => 'pi_sp_name',
Argument_Type => 'varchar2',
Default_Value => 'pm_test',
Out_Argument => False);
End;
设定'sp_insert_sched_tb过程的参数2
Begin
Dbms_Scheduler.Define_Program_Argument(Program_Name => 'pm_test',
Argument_Position => 2,
Argument_Name => 'pi_run_interval',
Argument_Type => 'varchar2',
Default_Value => 'pm_test',
Out_Argument => False);
End;
-- 所有参数设置完成后enable刚才创建的过程
Begin
Dbms_Scheduler.Enable(Name => 'pm_test');
End;
-- 创建job
Begin
Dbms_Scheduler.Create_Job(Job_Name => 'pm_job_test',
Program_Name => 'PM_TEST',
Start_Date => Sysdate,
Repeat_Interval => 'FREQ=Hourly',
End_Date => Null,
Job_Class => 'DEFAULT_JOB_CLASS',
Enabled => True,
Auto_Drop => False,
Comments => 'pm_job_test',
Job_Style => 'REGULAR',
Credential_Name => Null,
Destination_Name => Null);
End;
-- 给上面创建的job参数赋予值
Begin
Dbms_Scheduler.Set_Job_Argument_Value(Job_Name => 'PM_JOB_TEST',
Argument_Name => 'PI_SP_NAME',
Argument_Value => 'MY_PM_TEST');
Dbms_Scheduler.Set_Job_Argument_Value(Job_Name => 'PM_JOB_TEST',
Argument_Name => 'PI_RUN_INTERVAL',
Argument_Value => 'HHHHHHH');
End;
Windows相关:
1.基于schedule创建windows
Begin
Dbms_Scheduler.Create_Window(Window_Name => 'W_TEST',
Resource_Plan => Null,
Schedule_Name => 'ten_minutes_run_by_once',
Duration => '0 05:00:00',
Window_Priority => 'LOW',
Comments => 'Windows_Test');
End;
Duration:Window打开的时间,格式为Day HH24:MI:SS,如'0 05:00:00'表示该windows打开时间为5小时,Resource_Plan参数用于指定某个资源计划,若设置为null，则使用默认的资源计划.
2.创建windows group
Begin
Dbms_Scheduler.Create_Window_Group(Group_Name => 'WG_TEST',
Window_List => Null,
Comments => 'Windows_Group_Test!');
End;
3.增加Window_Group_Member
Begin
Dbms_Scheduler.Add_Window_Group_Member(Group_Name => 'WG_TEST',
Window_List => 'W_TEST');
End;
4.删除windows group
Begin
Dbms_Scheduler.Drop_Window_Group(Group_Name => 'WG_TEST', Force => True);
End;
-- 删除job
-- 删除job名为insert_sql的job
Declare
Begin
dbms_scheduler.drop_job(job_name =>'insert_sql',force => True);
End;
