如何创建基本的高级队列之二：创建接收方代码
/*
基本组成
接收方（DB2 ）：
Queue type ：决定接收消息的类型
Queue table ：消息接收的载体
Queue ：队列
*/
--接收方（DB2 ）代码：
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--请先打开文件 CODE_SENDER.SQL 在接收方（DB1 ）数据库执行
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--用账户 SYS 登录
--1.创建 AQ_RECEIVER 用户并赋权
create user WMS_APP identified by "oppo";
grant connect,resource,aq_administrator_role,unlimited tablespace to WMS_APP;
grant create database link to WMS_APP;
grant execute on dbms_aq to WMS_APP;
grant execute on dbms_aqadm to WMS_APP;
begin
dbms_aqadm.grant_system_privilege('ENQUEUE_ANY', 'WMS_APP', FALSE);
dbms_aqadm.grant_system_privilege('DEQUEUE_ANY', 'WMS_APP', FALSE);
end;
--用账户 AQ_RECEIVER 登录
--2.创建一个队列载体对象，一个没有 body 的 type 在 AQ_RECEIVER 下
create or replace type TS_BILL_TRANSFER as object
(
"BILL_NO" VARCHAR2(30),
"LINE_NUMBER" NUMBER,
"STOCK_NO" VARCHAR2(10),
"TO_STOCK_NO" VARCHAR2(10),
"LOCATION" VARCHAR2(60),
"PART_NO" VARCHAR2(60),
"PART_DESC" VARCHAR2(240),
"PART_QTY" NUMBER,
"OPERATOR" VARCHAR2(30),
"DATETIME_CREATED" DATE
);
--4.创建队列表
begin
-- Call the procedure
sys.dbms_aqadm.create_queue_table(queue_table => 'QT_BILL_TRANSFER',
queue_payload_type => 'TS_BILL_TRANSFER',--这就是我们定义的 type
sort_list => 'priority,enq_time',--按优先级和入列时间排序
multiple_consumers => TRUE, --多消费者
comment => 'QUEUE FOR RECEIVE BILL-TRANSFER DATA FROM WMS',
auto_commit => FALSE --手动控制事务--create queue
);
end;
--5.创建队列
begin
sys.dbms_aqadm.create_queue(
queue_name => 'Q_BILL_TRANSFER',
queue_table => 'QT_BILL_TRANSFER',--刚刚建立的queue表
queue_type => sys.dbms_aqadm.normal_queue,
max_retries => 3,--dequeue失败后重试次数
retry_delay => 1,--重试前等待
retention_time => 0 --dequeue后保持时间，不保持
);
end;
--6.启动队列
begin
dbms_aqadm.start_queue('Q_BILL_TRANSFER',true,true);
end;
--7.创建消息订阅者
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('WMS_APP', NULL, NULL);
DBMS_AQADM.ADD_SUBSCRIBER(queue_name => 'Q_BILL_TRANSFER', subscriber => subscriber);
--DBMS_AQADM.remove_subscriber(queue_name => 'Q_BILL_TRANSFER', subscriber => subscriber);
END;
--8.创建业务相关数据表
create table INV_SP_BILL_TRANSFER
(
INFO VARCHAR2(100),
MESSAGE TS_BILL_TRANSFER
);
--9.出列测试（该段代码每执行一次，只取出一条 Queue 数据）
declare
v_Message TS_BILL_TRANSFER;
v_MsgId RAW(16);
v_options DBMS_AQ.DEQUEUE_OPTIONS_T;
v_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_Recipients DBMS_AQ.AQ$_RECIPIENT_LIST_T;
begin
--v_Recipients(0) := sys.aq$_agent('NOTE','MTQ',0);
--v_properties.recipient_list := v_Recipients;
v_options.visibility :=DBMS_AQ.IMMEDIATE;
v_options.consumer_name := 'WMS_APP';
dbms_aq.dequeue(queue_name => 'Q_BILL_TRANSFER',dequeue_options => v_options,message_properties =>
v_properties,payload => v_Message,msgid => v_MsgId);
dbms_output.put_line('decode success,msgid is '||v_MsgId);
dbms_output.put_line('subject is '||v_Message.BILL_NO);
INSERT INTO INV_SP_BILL_TRANSFER VALUES('Get message on ',v_Message);
end;
--查询QTABLE
SELECT * FROM QT_BILL_TRANSFER;
--查询操作的数据
SELECT * FROM INV_SP_BILL_TRANSFER;
SELECT * FROM Q_TEST;
--QUEUE 相关查询
select * from user_queue_tables;
select * from user_queues;
select * from user_queue_subscribers;
select * from user_queue_schedules;
SELECT * FROM USER_QUEUE_PUBLISHERS;
