sample.sql

Oracle Advance Queue(AQ)的使用例子

--
-- Enqueue message
CONNECT aquser/aquser;
DECLARE
  queue_options 			DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties	DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_id					RAW(16);
  my_message 					aqadm.queue_message_type;
BEGIN
  my_message := aqadm.queue_message_type(
			1, 
			'This is a sample message',
			'This message has been posted on ' || TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS'));
  DBMS_AQ.ENQUEUE(
    queue_name => 'aqadm.message_queue',
    enqueue_options => queue_options,
    message_properties => message_properties,
    payload => my_message,
    msgid => message_id);
  COMMIT;
END;
/
--
-- Dequeue message
CONNECT aquser/aquser;
SET SERVEROUTPUT ON;
DECLARE
  queue_options 			DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties	DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_id					RAW(2000);
  my_message 					aqadm.queue_message_type;
BEGIN
  DBMS_AQ.DEQUEUE(
    queue_name =>'aqadm.message_queue',
    dequeue_options => queue_options,
    message_properties => message_properties,
    payload => my_message,
    msgid => message_id
  );
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Dequeued no:    ' || my_message.no);
  DBMS_OUTPUT.PUT_LINE('Dequeued title: ' || my_message.title);
  DBMS_OUTPUT.PUT_LINE('Dequeued text:  ' || my_message.text);
END;
/