如何创建基本的高级队列之二：创建接收方代码

/*

基本组成

接收方（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;

 

 

 

转载于:https://www.cnblogs.com/blue-sword/archive/2013/01/08/2851255.html