Bo's Oracle Station

查看: 2346|回复: 0

第29次活动:2017-11-01(星期三晚上7:00-9:30)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-11-1 19:35:53 | 显示全部楼层 |阅读模式
  1. DROP TABLE scheduler_test;

  2. DROP SEQUENCE scheduler_test_seq;

  3. CREATE TABLE scheduler_test (

  4.   id            NUMBER(10)    NOT NULL,

  5.   description   VARCHAR2(20)  NOT NULL,

  6.   created_date  DATE          NOT NULL,

  7.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)

  8. );


  9. --------
  10. CREATE SEQUENCE scheduler_test_seq;

  11. -----

  12. BEGIN
  13.   DBMS_SCHEDULER.create_program (
  14.     program_name   => 'test_program_1',
  15.     program_type   => 'PLSQL_BLOCK',
  16.     program_action => 'BEGIN
  17.                          INSERT INTO scheduler_test (id, description, created_date)
  18.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
  19.                          COMMIT;
  20.                        END;',
  21.     enabled        => TRUE,
  22.     comments       => 'Step2');

  23.   DBMS_SCHEDULER.create_program (
  24.     program_name   => 'test_program_2',
  25.     program_type   => 'PLSQL_BLOCK',
  26.     program_action => 'BEGIN
  27.                          INSERT INTO scheduler_test (id, description, created_date)
  28.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
  29.                          COMMIT;
  30.                        END;',
  31.     enabled        => TRUE,
  32.     comments       => 'Step3');

  33.   DBMS_SCHEDULER.create_program (
  34.     program_name   => 'test_program_3',
  35.     program_type   => 'PLSQL_BLOCK',
  36.     program_action => 'BEGIN
  37.                          INSERT INTO scheduler_test (id, description, created_date)
  38.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
  39.                          COMMIT;
  40.                        END;',
  41.     enabled        => TRUE,
  42.     comments       => 'Step4');
  43. END;
复制代码

  1. BEGIN
  2. sys.dbms_scheduler.create_chain(
  3. comments => 'chain1',
  4. chain_name => '"HR"."CHAIN1"');
  5. sys.dbms_scheduler.define_chain_event_step(
  6. chain_name => '"HR"."CHAIN1"',
  7. step_name => '"STEP1"',
  8. event_schedule_name => '"HR"."SCHEDULE4"');
  9. sys.dbms_scheduler.alter_chain(
  10. chain_name => '"HR"."CHAIN1"',
  11. step_name => '"STEP1"',
  12. attribute => 'pause',
  13. value => FALSE);
  14. sys.dbms_scheduler.alter_chain(
  15. chain_name => '"HR"."CHAIN1"',
  16. step_name => '"STEP1"',
  17. attribute => 'skip',
  18. value => FALSE);
  19. sys.dbms_scheduler.define_chain_step(
  20. chain_name => '"HR"."CHAIN1"',
  21. step_name => '"STEP2"',
  22. program_name => '"HR"."TEST_PROGRAM_1"');
  23. sys.dbms_scheduler.alter_chain(
  24. chain_name => '"HR"."CHAIN1"',
  25. step_name => '"STEP2"',
  26. attribute => 'pause',
  27. value => FALSE);
  28. sys.dbms_scheduler.alter_chain(
  29. chain_name => '"HR"."CHAIN1"',
  30. step_name => '"STEP2"',
  31. attribute => 'skip',
  32. value => FALSE);
  33. sys.dbms_scheduler.define_chain_step(
  34. chain_name => '"HR"."CHAIN1"',
  35. step_name => '"STEP3"',
  36. program_name => '"HR"."TEST_PROGRAM_2"');
  37. sys.dbms_scheduler.alter_chain(
  38. chain_name => '"HR"."CHAIN1"',
  39. step_name => '"STEP3"',
  40. attribute => 'pause',
  41. value => FALSE);
  42. sys.dbms_scheduler.alter_chain(
  43. chain_name => '"HR"."CHAIN1"',
  44. step_name => '"STEP3"',
  45. attribute => 'skip',
  46. value => FALSE);
  47. sys.dbms_scheduler.define_chain_step(
  48. chain_name => '"HR"."CHAIN1"',
  49. step_name => '"STEP4"',
  50. program_name => '"HR"."TEST_PROGRAM_3"');
  51. sys.dbms_scheduler.alter_chain(
  52. chain_name => '"HR"."CHAIN1"',
  53. step_name => '"STEP4"',
  54. attribute => 'pause',
  55. value => FALSE);
  56. sys.dbms_scheduler.alter_chain(
  57. chain_name => '"HR"."CHAIN1"',
  58. step_name => '"STEP4"',
  59. attribute => 'skip',
  60. value => FALSE);
  61. sys.dbms_scheduler.define_chain_rule(
  62. chain_name => '"HR"."CHAIN1"',
  63. condition => '1=1',
  64. rule_name => 'RULE1',
  65. comments => 'Rule1',
  66. action => 'START Step1');
  67. sys.dbms_scheduler.define_chain_rule(
  68. chain_name => '"HR"."CHAIN1"',
  69. condition => 'Step1 SUCCEEDED',
  70. rule_name => 'RULE2',
  71. comments => 'Rule2',
  72. action => 'START Step2');
  73. sys.dbms_scheduler.define_chain_rule(
  74. chain_name => '"HR"."CHAIN1"',
  75. condition => 'Step2 SUCCEEDED',
  76. rule_name => 'RULE3',
  77. comments => 'Rule3',
  78. action => 'START Step3');
  79. sys.dbms_scheduler.define_chain_rule(
  80. chain_name => '"HR"."CHAIN1"',
  81. condition => 'Step3 SUCCEEDED',
  82. rule_name => 'RULE4',
  83. comments => 'Rule4',
  84. action => 'START Step4');
  85. sys.dbms_scheduler.define_chain_rule(
  86. chain_name => '"HR"."CHAIN1"',
  87. condition => 'Step4 SUCCEEDED',
  88. rule_name => 'RULE5',
  89. comments => 'Rule5',
  90. action => 'END');
  91. sys.dbms_scheduler.enable('"HR"."CHAIN1"');
  92. END;
复制代码
组装chain作业(普通的基于时间的Schedule):
Screenshot.png


Screenshot.png


  1. select  * from dba_scheduler_global_attribute;
  2. select  * from dba_scheduler_notifications;

  3. begin
  4. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
  5. ('email_server','station90.example.com');
  6. end;

  7. begin
  8.   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
  9.   ('email_sender','oracle@example.com');
  10. end;

  11. begin
  12.   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  13.   job_name       =>'HR.JOB5',
  14.     events   =>'job_succeeded,job_completed',
  15.   recipients     =>'oracle@example.com');
  16. end;

  17. begin
  18. DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  19.   job_name         =>'HR.JOB5');
  20. end;

  21. 错误
  22. create or replace procedure hr.proc1
  23. begin
  24.   update t123456 set a=2;
  25. end;

  26. begin
  27. DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  28.   job_name       =>'HR.JOB51',
  29.   recipients     =>'oracle@example.com');
  30. end;

  31. BEGIN
  32. DBMS_SCHEDULER.add_job_email_notification (
  33.   job_name         =>  'test_notification_job',
  34.   recipients       =>  'oracle@example.com',
  35.   events           =>  'job_failed',
  36.   filter_condition => ':event.error_code=600');
  37. END;
  38. /



复制代码
  1. BEGIN
  2. sys.dbms_scheduler.disable( '"HR"."JOB51"' );
  3. sys.dbms_scheduler.set_attribute( name => '"HR"."JOB51"', attribute => 'raise_events', value => 0);
  4. sys.dbms_scheduler.enable( '"HR"."JOB51"' );
  5. END;
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 13:31 , Processed in 0.033323 second(s), 27 queries .

快速回复 返回顶部 返回列表