Chinaunix首页 | 论坛 | 博客
  • 博客访问: 5175661
  • 博文数量: 1004
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 13042
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(1004)

文章存档

2020年(1)

2019年(93)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2011-06-08 16:20:58

 
以下列举几个有关使用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;
 
阅读(4148) | 评论(0) | 转发(0) |
0

上一篇:EXP各参数使用详解

下一篇:undo相关

给主人留下些什么吧!~~