EXECAQ.sql-CSDN博客

本文链接：https://blog.csdn.net/zhaojian1988/article/details/8843374

EXECAQ.sql

--2.停止和删除aq
exec dbms_aqadm.stop_queue('sms_mt_queue');
exec dbms_aqadm.drop_queue('sms_mt_queue');
exec dbms_aqadm.drop_queue_table('ss_mt_tab');
/

--3.创建队列表
begin dbms_aqadm.create_queue_table(queue_table=>'ss_mt_tab', queue_payload_type=>'SYS.AQ$_JMS_TEXT_MESSAGE',multiple_consumers => true); end;
/

--4.创建队列
exec dbms_aqadm.create_queue(queue_name=>'sms_mt_queue', queue_table=>'ss_mt_tab');
/
--5.启动队列
EXECUTE DBMS_AQADM.START_QUEUE (Queue_name => 'SMS_MT_QUEUE');

--6.调度队列
exec dbms_aqadm.schedule_propagation(queue_name => 'SMS_MT_QUEUE',latency => 0);
/
--7.aq处理包
create or replace package PKG_AQ is

-- Author : bgz
-- Created : 2011-06-22 14:01:37
-- Purpose : AQ处理功能包

--Purpose: 增加消息队列订阅者
--para: v_queueName,队列名称；v_subscriber，订阅者名称
--author: bgz
--date: 2011-06-29
PROCEDURE addSubscriber(v_queueName in varchar2,v_subscriber in varchar2);

--Purpose: 发送消息
--para: v_messageText,消息文本内容；v_kind（表名）；v_tag；v_fldid；v_opetionType,消息类型:DESTRORY_ALL
--author: bgz
--date: 2011-06-29
procedure sendMessage(v_messageText in varchar2,v_kind in varchar2,v_tag in varchar2,v_fldid in varchar2, v_opetionType in varchar2 default null);
end PKG_AQ;
/
create or replace package body PKG_AQ is

PROCEDURE addSubscriber(v_queueName in varchar2,v_subscriber in varchar2) as
begin
dbms_aqadm.add_subscriber (queue_name => v_queueName,subscriber => sys.aq$_agent(v_subscriber,null,null));
end addSubscriber;

PROCEDURE sendMessage(v_messageText in varchar2,v_kind in varchar2,v_tag in varchar2,v_fldid in varchar2, v_opetionType in varchar2 default null) as
    message            SYS.AQ$_JMS_TEXT_MESSAGE;
    enqueue_options    dbms_aq.enqueue_options_t;
    message_properties SYS.dbms_aq.message_properties_t;
    msgid raw(16);

    java_exp           exception;
    pragma EXCEPTION_INIT(java_exp, -24197);
begin
    -- Consturct a empty bytes message object
    message := SYS.AQ$_JMS_TEXT_MESSAGE.construct;

message.set_text(v_messageText);

    -- Shows how to set JMS user properties
    message.set_string_property('kind', v_kind);
    message.set_string_property('tag', v_tag);
    message.set_string_property('fldid', v_fldid);
    message.set_string_property('operationtype', v_opetionType);

    -- Enqueue this message into AQ queue using DBMS_AQ package
    dbms_aq.enqueue(queue_name => 'SMS_MT_QUEUE',
                    enqueue_options => enqueue_options,
                    message_properties => message_properties,
                    payload => message,
                    msgid => msgid);
                    EXCEPTION
    WHEN OTHERS THEN
      RETURN;
end sendMessage;
end PKG_AQ;
/

--8.处理大量aq消息缓存
create table TSM_AQ_MESSAGE_CACHE
(
FLDTABLE         VARCHAR2(256),
FLDID            VARCHAR2(256),
FLDENTITY        VARCHAR2(256),
FLDOPERATIONTYPE VARCHAR2(128)
)
/
-- Add comments to the table
comment on table TSM_AQ_MESSAGE_CACHE
is '缓存处理AQ发送的消息';
-- Add comments to the columns
comment on column TSM_AQ_MESSAGE_CACHE.FLDTABLE
is '触发消息的表名';
comment on column TSM_AQ_MESSAGE_CACHE.FLDID
is '触发消息的记录ID';
comment on column TSM_AQ_MESSAGE_CACHE.FLDENTITY
is '消息关联的实体配置信息';
comment on column TSM_AQ_MESSAGE_CACHE.FLDOPERATIONTYPE
is '对触发消息的表的操作类型，ADD:UPDATE:DELETE';
/

--9.在实体表和实体字段表上增加生成aq消息的触发器
create or replace trigger TRI_ENTITY_CONFIG_CACHE_ALL
after update on tsm_entity_config

declare
cursor cEntityConfig is select * from tsm_aq_message_cache t where upper(t.fldtable)='TSM_ENTITY_CONFIG';
nCount number(9);
begin
select count(*) into nCount from tsm_aq_message_cache t where upper(t.fldtable)='TSM_ENTITY_CONFIG';
if(nCount>50) then
    Pkg_AQ.sendMessage('tsm_entity_config','tsm_entity_config','','','DESTROY_ALL');
else
    for config in cEntityConfig loop
      Pkg_AQ.sendMessage('tsm_entity_config','tsm_entity_config',config.fldentity,config.fldid);
    end loop;
end if;
delete from tsm_aq_message_cache t where upper(t.fldtable)='TSM_ENTITY_CONFIG';
end TRI_ENTITY_CONFIG_CACHE_ALL;
/
create or replace trigger TRI_ENTITY_CONFIG_CACHE_DELETE
after delete on tsm_entity_config
for each row
declare

begin
   Pkg_AQ.sendMessage('tsm_entity_config','tsm_entity_config',:old.Fldtag,:old.Fldid);
end TRI_ENTITY_CONFIG_CACHE_UPDATE;
/
create or replace trigger TRI_ENTITY_CONFIG_CACHE_UPDATE
after update on tsm_entity_config
for each row
declare

begin
   insert into TSM_aq_message_cache(Fldtable,Fldid,Fldentity ,Fldoperationtype) values
   ('tsm_entity_config',:new.fldid,:new.fldtag,'UPDATE');

end TRI_ENTITY_CONFIG_CACHE_UPDATE;
/
create or replace trigger TRI_ENT_FIELD_CONFIG_CACHE_ALL
after update on tsm_entity_field_config

declare
cursor cEntityConfig is select * from tsm_aq_message_cache t where upper(t.fldtable)='TSM_ENTITY_FIELD_CONFIG';
nCount number(9);
begin
select count(*) into nCount from tsm_aq_message_cache t where upper(t.fldtable)='TSM_ENTITY_FIELD_CONFIG';
if(nCount>100) then
    Pkg_AQ.sendMessage('TSM_ENTITY_FIELD_CONFIG','TSM_ENTITY_FIELD_CONFIG','','','DESTROY_ALL');
else
    for config in cEntityConfig loop
      Pkg_AQ.sendMessage('TSM_ENTITY_FIELD_CONFIG','TSM_ENTITY_FIELD_CONFIG',config.fldentity,config.fldid);
    end loop;
end if;
delete from tsm_aq_message_cache t where upper(t.fldtable)='TSM_ENTITY_FIELD_CONFIG';
end TRI_ENT_FIELD_CONFIG_CACHE_ALL;
/
create or replace trigger TRI_FIELD_CONFIG_CACHE_ADD
after insert on tsm_entity_field_config
for each row
declare
-- local variables here
begin
Pkg_AQ.sendMessage('tsm_entity_field_config','tsm_entity_field_config',:NEW.Fldentityid,:new.fldid);
end TRI_FIELD_CONFIG_CACHE_ADD;
/
create or replace trigger TRI_FIELD_CONFIG_CACHE_DELETE
after delete on tsm_entity_field_config
for each row
declare

begin
    Pkg_AQ.sendMessage('tsm_entity_field_config','tsm_entity_field_config',:old.Fldentityid,:old.Fldid);

end TRI_FIELD_CONFIG_CACHE_DELETE;

create or replace trigger TRI_FIELD_CONFIG_CACHE_UPDATE
after update on tsm_entity_field_config
for each row
declare

begin
    insert into TSM_aq_message_cache(Fldtable,Fldid,Fldentity ,Fldoperationtype) values
   ('tsm_entity_field_config',:new.fldid,:new.fldentityid,'UPDATE');
end TRI_FIELD_CONFIG_CACHE_UPDATE;
/

--10.清空aq表中的记录
truncate table aq$_ss_MT_tab_D;
truncate table aq$_ss_MT_tab_g;
truncate table aq$_ss_MT_tab_h;
truncate table aq$_ss_MT_tab_i;
truncate table aq$_ss_MT_tab_p;
truncate table aq$_ss_MT_tab_s;
truncate table aq$_ss_MT_tab_t;
/

exit