bz_enqueue_eg.sql

来自「oracle9i+j2ee开发aq oracle9i+j2ee开发aq or」· SQL 代码 · 共 90 行

REM script name: bz_enqueue_eg.sql
REM Shows various ways to do an enqueue
REM
REM version: 9i

REM====================================================================
REM I. Enqueue with a recipient list
REM====================================================================

declare
	enqopt	dbms_aq.enqueue_options_t;
	mprop	dbms_aq.message_properties_t;
	enq_msgid	RAW(16);
	rcpt_list dbms_aq.aq$_recipient_list_t;
begin
	rcpt_list(0) := sys.aq$_agent('Billing', null, null);
	rcpt_list(1) := sys.aq$_agent('Shipping', null, null);
	mprop.recipient_list := rcpt_list;
	dbms_aq.enqueue(
		queue_name => 'bzcardorders_q',
		enqueue_options => enqopt,
		message_properties => mprop,
		payload => bzcardorder_typ(101, 'Brajesh', 'Goyal','NORMAL'),
		msgid => enq_msgid);

--	You can perform multiple database operations in the same transaction
-- 	You need not commit here Or you can use visibility immediate to commit

	commit;
end;
/


REM====================================================================
REM II. Enqueue with a delay
REM====================================================================

declare
	enqopt	dbms_aq.enqueue_options_t;
	mprop	dbms_aq.message_properties_t;
	enq_msgid	RAW(16);
	rcpt_list dbms_aq.aq$_recipient_list_t;
begin
	rcpt_list(0) := sys.aq$_agent('Billing', null, null);
	rcpt_list(1) := sys.aq$_agent('Shipping', null, null);

	mprop.delay := 30; -- 30 seconds delay
	mprop.recipient_list := rcpt_list;
	dbms_aq.enqueue(
		queue_name => 'bzcardorders_q',
		enqueue_options => enqopt,
		message_properties => mprop,
		payload => bzcardorder_typ(102, 'Shailendra','Mishra','NORMAL'),
		msgid => enq_msgid);

--	You can perform multiple database operations in the same transaction
-- 	You need not commit here Or you can use visibility immediate to commit

	commit;
end;
/
REM====================================================================
REM III. Enqueue with an expiration 
REM====================================================================

declare
	enqopt	dbms_aq.enqueue_options_t;
	mprop	dbms_aq.message_properties_t;
	enq_msgid	RAW(16);
	rcpt_list dbms_aq.aq$_recipient_list_t;
begin
	rcpt_list(0) := sys.aq$_agent('Billing', null, null);
	rcpt_list(1) := sys.aq$_agent('Shipping', null, null);

	mprop.expiration := 30; -- expires after 30 seconds
	mprop.recipient_list := rcpt_list;
	dbms_aq.enqueue(
		queue_name => 'bzcardorders_q',
		enqueue_options => enqopt,
		message_properties => mprop,
		payload => bzcardorder_typ(101,'Brajesh','Goyal','RUSH'),
		msgid => enq_msgid);

--	You can perform multiple database operations in the same transaction
-- 	You need not commit here Or you can use visibility immediate to commit

	commit;
end;
/