Oracle queue的enqueue & dequeue

最新推荐文章于 2023-10-04 23:17:13 发布

iteye_15570

最新推荐文章于 2023-10-04 23:17:13 发布

阅读量581

点赞数

分类专栏： DB 文章标签：数据库

DB 专栏收录该内容

8 篇文章

订阅专栏


EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE( 
	Queue_table => '&1'||'.MARK_APPLY_QUEUE_TAB', 
	Queue_payload_type => '&1'||'.MARK_TYPE_EVENT', 
	storage_clause => 'PCTFREE 10 PCTUSED 40 INITRANS 32 MAXTRANS 255 TABLESPACE MARK_'||'&1'||'_DATA00', 
	Sort_list => 'ENQ_TIME', 
	Message_grouping => 1, 
	Compatible => '8.1.3');

EXECUTE DBMS_AQADM.CREATE_QUEUE( 
	Queue_name => '&1'||'.MARK_APPLY_QUEUE', 
	Queue_table => '&1'||'.MARK_APPLY_QUEUE_TAB', 
	Queue_type => 0, 
	Max_retries => 5, 
	Retry_delay => 0, 
	dependency_tracking => FALSE);

EXECUTE dbms_aqadm.start_queue (
	queue_name => '&1'||'.MARK_APPLY_QUEUE', 
	enqueue => TRUE , 
	dequeue => TRUE);

create or replace
TYPE "MARK_TYPE_EVENT"
AS
  OBJECT
  (
    ACCT_ID     VARCHAR2(32),
    SECURITY_ID VARCHAR2(32),
    EVENT_CODE  VARCHAR2(4),
    EVENT_TIME TIMESTAMP );

  sql_code    NUMBER;
  sql_msg     VARCHAR2(132);
  queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_id RAW(16);
  event MARK_TYPE_EVENT;
  rid varchar2(20);
  no_messages           exception;
  PRAGMA EXCEPTION_INIT (no_messages, -25228);

DBMS_AQ.DEQUEUE(
    queue_name => 'MARK_APPLY_QUEUE',
    dequeue_options => queue_options,
    message_properties => message_properties,
    payload => event,
    msgid => message_id);
    ACCT_ID := event.acct_id;
    SECURITY_ID :=event.security_id;
    EVENT_CODE := event.event_code;
    EVENT_TIME := event.event_time;
    MSG_ID := RAWTOHEX(message_id);