/*

基本組成

發(fā)送方（DB1）：

Queue type ：決定發(fā)送消息的類型

Queue table ：消息發(fā)送的載體

Queue ：隊列

Subscriber ：訂購者，同一個隊列可以有n個訂購者

Propagation ：傳播進程

*/

?

--發(fā)送方（DB1 ）代碼：

--用賬戶 SYS 登錄

--1.創(chuàng)建 AQ_SENDER 用戶并賦權

?

create user AQ_SENDER identified by "oppo";

grant connect,resource,aq_administrator_role,unlimited tablespace to phs;

grant create database link to phs;

grant execute on dbms_aq to phs;

grant execute on dbms_aqadm to phs;

begin

dbms_aqadm.grant_system_privilege('ENQUEUE_ANY', 'phs', FALSE);

dbms_aqadm.grant_system_privilege('DEQUEUE_ANY', 'phs', FALSE);

end;

?

--用賬戶 AQ_SENDER 登錄

--2.創(chuàng)建 db link

create database link MES_LINK

? connect to WMS_APP identified by "oppo"

? using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.115)(PORT = 1521))(CONNECT_DATA = (SID = testassy)))';

?

--3.創(chuàng)建一個隊列載體對象，一個沒有 body 的 type 在 AQ_SENDER 下

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.創(chuàng)建隊列表

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',--按優(yōu)先級和入列時間排序

????????? multiple_consumers => TRUE, --多消費者

????????? comment => 'QUEUE FOR SEND BILL-TRANSFER DATA TO ERP',

????????? auto_commit => FALSE --手動控制事務--create queue

????????? );

end;

?

--5.創(chuàng)建隊列

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失敗后重試次數(shù)

retry_delay => 1,--重試前等待

retention_time => 0 --dequeue后保持時間，不保持

);

end;

?

--6.啟動隊列

begin

dbms_aqadm.start_queue('Q_BILL_TRANSFER',true,true);

end;

?

--7.創(chuàng)建消息訂閱者

DECLARE

subscriber sys.aq$_agent;

BEGIN

subscriber := sys.aq$_agent('PHS', 'PHS.Q_BILL_TRANSFER', 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.創(chuàng)建 propagation

BEGIN

DBMS_AQADM.SCHEDULE_PROPAGATION(queue_name => 'Q_BILL_TRANSFER', destination => 'MES_LINK');

--同一數(shù)據(jù)庫，不同用戶

--DBMS_AQADM.SCHEDULE_PROPAGATION(queue_name => 'Q_BILL_TRANSFER', destination => '');

END;

?

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

--9.創(chuàng)建接收方（DB2 ）相關代碼，請打開文件 CODE_RECEIVER.SQL 在接收方（DB2 ）數(shù)據(jù)庫執(zhí)行

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

?

--10.入列測試（該段代碼每執(zhí)行一次，只導入一條 Queue 數(shù)據(jù)）

declare

v_Message TS_BILL_TRANSFER;

v_MsgId RAW(16);

v_options DBMS_AQ.ENQUEUE_OPTIONS_T;

v_properties DBMS_AQ.MESSAGE_PROPERTIES_T;

v_Recipients DBMS_AQ.AQ$_RECIPIENT_LIST_T;

begin

? v_Message:=TS_BILL_TRANSFER(

? BILL_NO => 'B001',

LINE_NUMBER => 003,

STOCK_NO => '327',

TO_STOCK_NO => '303',

LOCATION? => 'B378',

PART_NO => '1100825',

PART_DESC => '揚聲器 0.5W 8Ω 15×11×3.5 彈片 4#',

PART_QTY => 35,

OPERATOR => 'admin',

DATETIME_CREATED=> SYSDATE);

? v_properties.priority := 1; --該消息的優(yōu)先級別

? v_options.visibility :=DBMS_AQ.IMMEDIATE;

? dbms_aq.enqueue(queue_name => 'Q_BILL_TRANSFER',enqueue_options => v_options,message_properties =>

? v_properties, payload => v_Message, msgid => v_MsgId);

? dbms_output.put_line('encode success,msgid is '||v_MsgId);

end;

?

--查詢QTABLE

SELECT * FROM QT_BILL_TRANSFER;

--查詢操作的數(shù)據(jù)

SELECT * FROM INV_SP_BILL_TRANSFER;

?

SELECT * FROM Q_TEST;

?

begin

? -- Call the procedure

? q_sp_test;

end;

?

?

--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/BLUE-SWORD.html

總結

以上是生活随笔為你收集整理的如何创建基本的高级队列之一：创建发送方代码的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。