ORACLE调度之基于事件的调度（二）【weber出品】

一、回顾

　　调度分基于时间的调度和基于事件的调度。

　　稍微复习一下前面的只是请浏览：《ORACLE调度之基于时间的调度（一）【weber出品】》

二、知识补充

　　1、队列：一种数据结构，就像一根管道一样，进程一个个的塞进去，然后一个个的出来，讲究的是先进先出。

　　2、高级队列：

　　a、高级队列管理是Oracle数据库的一个特性，它提供消息队列管理功能。这是一个非常可靠、安全和可伸缩的消息管理系统，因为它使用与其他基于Oracle技术的应用程序相同的数据库特性。

　　b、高级队列管理的一个很大优点是它可以通过PL/SQL、Java或C来访问，这样你就可以把来自一个Java servlet的消息入队列和使PL/SQL存储过程中的相同消息出队列。 
　　c、高级队列管理的另一个优点是你可以利用这一软件通过Oracle Net Services (SQL*Net)、HTTP(S)和SMTP，在远程节点之间传播消息。高级队列甚至可以通过消息网关与非Oracle的消息管理系
  统（如IBM MQSeries）相集成。 
　　d、Oracle高级队列管理提供了单消费者队列和多消费者队列。单消费者队列只面向单一的接收者。多消费者队列可以被多个接收者使用。当把消息放入多消费者队列时，应用程序的程序员必须显式地在消息属性中指定这些接收者，或者建立决定每条消息的接收者的基于规则的订阅过程。 

 

三、基于事件的调度

创建测试用表

conn hr/hr

create table event_job_test(id number,createdatae date);

alter table event_job_test add constraint pk_event_job_test primary key(id);

create sequence seq_event_job_test;

创建一个类型：

create or replace type t_event_queue as object(object_owner varchar2(50),event_name varchar2(50));

创建一个队列表，该队列包含的字段就是我们刚才创建的类型t_event_queue所包含的属性。

conn /as sysdba
grant execute on dbms_aqadm to hr;
conn hr/hr

begin 
dbms_aqadm.create_queue_table(
queue_table=>'event_queue_tab',
queue_payload_type=>'t_event_queue',
multiple_consumers=>true);
end;
/

创建一个队列，并将该队列与前面创建的队列表关联

begin
dbms_aqadm.create_queue(
queue_name=>'event_queue',
queue_table=>'event_queue_tab');
end;
/

启动队列

begin
dbms_aqadm.start_queue(queue_name=>'event_queue');
end;
/

 创建一个基于事件的任务

conn /as sysdba

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."EVENT_BASE_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
  insert into hr.event_job_test values(seq_event_job_test.nextval,sysdate);
  commit;
end;',
event_condition => 'tab.user_data.object_owner=''HR'' and
tab.user_data.event_name=''give_me_an_event''',
queue_spec => 'HR.EVENT_QUEUE',
start_date => systimestamp at time zone '+8:00',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;

向队列中插入消息

没插入之前，查询表，发现没数据。

conn hr/hr

select * from event_job_test;

向队列里插入消息

conn /as sysdba

grant execute on dbms_aq to hr;

conn hr/hr

declare
    l_enqueue_options dbms_aq.enqueue_options_t;
    l_message_properties dbms_aq.message_properties_t;
    l_message_handle raw(16);
    l_queue_msg t_event_queue;
begin
    l_queue_msg := t_event_queue('HR','give_me_an_event');
    dbms_aq.enqueue(
       queue_name=>'event_queue',
       enqueue_options=>l_enqueue_options,
       message_properties=>l_message_properties,
       payload=>l_queue_msg,
       msgid=>l_message_handle);
    commit;
end;


select * from event_job_test;

删除作业：

begin
  dbms_scheduler.drop_job(job_name => '"HR"."EVENT_BASE_JOB"', force => true);
end;

创建基于事件的调度加载数据

创建测试用表

conn scott/tiger

create table t as select * from emp where 1=2;

vi /u01/load.ctl

load data
infile '/u01/data.txt'
badfile '/u01/bad.emp'
discardfile '/u01/discadr.emp'
truncate
into table t
fields terminated by ','
trailing nullcols
(EMPNO,
 ENAME,
 JOB,
 MGR,
 HIREDATE,
 SAL,
 COMM,
 DEPTNO)

vi /u01/load.sh

#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1
export ORACLE_SID=orcl
$ORACLE_HOME/bin/sqlldr scott/tiger control=/u01/load.ctl log=/u01/load.log

保存退出

chmod +x /u01/load.sh

将emp中的数据转储到/u01/data.txt中：

set trims on
spool /u01/data.txt

select 
    EMPNO||','||
    ENAME||','||
    JOB||','||
    MGR||','||
    HIREDATE||','||
    SAL||','||
    COMM||','||
    DEPTNO from emp;

spool off

创建一个类型：

sqlplus scott/tiger

create or replace type t_event_queue as object
(
  object_owner    varchar2(10),
  object_name     varchar2(20),
  event_type      varchar2(20),
  event_timestamp number(2)
);
/

创建一个队列表，该队列包含的字段就是我们刚才创建的类型t_event_queue所包含的属性。

conn /as sysdba

grant execute on dbms_aqadm to scott;

conn scott/tiger

begin 
dbms_aqadm.create_queue_table(
queue_table=>'event_queue_tab',
queue_payload_type=>'t_event_queue',
multiple_consumers=>true);
end;
/

创建一个队列，并将该队列与前面创建的队列表关联

begin
dbms_aqadm.create_queue(
queue_name=>'event_queue',
queue_table=>'event_queue_tab');
end;
/

启动队列

begin
dbms_aqadm.start_queue(queue_name=>'event_queue');
end;
/

创建一个基于事件的任务

conn /as sysdba


BEGIN
  sys.dbms_scheduler.create_job(job_name        => '"SYS"."PERFORM_DATA_LOAD"', --属主必须是sys
                                job_type        => 'EXECUTABLE',
                                job_action      => '/u01/load.sh',
                                event_condition => 'tab.user_data.object_owner =   ''SCOTT''
and tab.user_data.object_name = ''DATA.TXT''   
and tab.user_data.event_type = ''FILE_ARRIVAL''
and tab.user_data.event_timestamp < 9',--创建一个作业，如果成批装入的数据文件在上午 9:00 之前到达文件系统，则运行此作业
                                queue_spec      => 'SCOTT.EVENT_QUEUE',
                                start_date      => systimestamp at time zone
                                                   '+8:00',
                                job_class       => 'DEFAULT_JOB_CLASS',
                                auto_drop       => FALSE,
                                enabled         => TRUE);
END;

/

向队列中插入消息

conn scott/tiger

select * from t;

向队列里插入消息

conn /as sysdba

grant execute on dbms_aq to scott;

conn scott/tiger


declare
  l_enqueue_options    dbms_aq.enqueue_options_t;
  l_message_properties dbms_aq.message_properties_t;
  l_message_handle     raw(16);
  l_queue_msg          t_event_queue;
begin
  l_queue_msg := t_event_queue('SCOTT', 'DATA.TXT', 'FILE_ARRIVAL',8);
  dbms_aq.enqueue(queue_name         => 'event_queue',
                  enqueue_options    => l_enqueue_options,
                  message_properties => l_message_properties,
                  payload            => l_queue_msg,
                  msgid              => l_message_handle);
  commit;
end;

/



select * from t;

删除作业：

conn /as sysdba

begin
  dbms_scheduler.drop_job(job_name => '"SYS"."PERFORM_DATA_LOAD"', force => true);
end;
/

总结一下oracle db里用job调度shell的注意点：

1、shell脚本里开头要加#!/bin/bash等指定使用的shell类型
2、所有相关环境变量都得在shell里明确指定
3、如果要写入文件必需使用绝对路径
4、使用sys用户建立job