bz_dequeue_eg.sql

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

REM script name: bz_enqueue_eg.sql
REM Shows various ways to do a dequeue
REM
REM version: 9i

REM====================================================================
REM I. Non-blocking dequeue
REM====================================================================

declare
	deqopt	dbms_aq.dequeue_options_t;
	mprop	dbms_aq.message_properties_t;
	msgid	RAW(16);
	payload bzcardorder_typ;
begin
	deqopt.consumer_name := 'BILLING';
	deqopt.navigation := DBMS_AQ.FIRST_MESSAGE;
	deqopt.wait := 0;	
	dbms_aq.dequeue(
		queue_name => 'bzcardorders_q',
		dequeue_options => deqopt,
		message_properties => mprop,
		payload => payload,
		msgid => 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. Blocking dequeue
REM====================================================================

declare
	deqopt	dbms_aq.dequeue_options_t;
	mprop	dbms_aq.message_properties_t;
	msgid	RAW(16);
	payload bzcardorder_typ;
begin
	deqopt.consumer_name := 'SHIPPING';
	deqopt.navigation := DBMS_AQ.FIRST_MESSAGE;
	deqopt.wait := 30; -- wait for 30 seconds
	dbms_aq.dequeue(
		queue_name => 'bzcardorders_q',
		dequeue_options => deqopt,
		message_properties => mprop,
		payload => payload,
		msgid => 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. Dequeue with dequeue_condition
REM====================================================================

declare
	deqopt	dbms_aq.dequeue_options_t;
	mprop	dbms_aq.message_properties_t;
	msgid	RAW(16);
	payload bzcardorder_typ;
begin
	deqopt.consumer_name := 'SHIPPING';
	deqopt.navigation := DBMS_AQ.FIRST_MESSAGE;
-- dequeue messages older than 30 seconds
	deqopt.deq_condition := 'sysdate - enq_time > 30/86400';
	dbms_aq.dequeue(
		queue_name => 'bzcardorders_q',
		dequeue_options => deqopt,
		message_properties => mprop,
		payload => payload,
		msgid => 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;
/