Oracle AQ使用实例.doc

AQ使用实例 一、AQ的安装 创建bz_admin, bz用户 alter system set job_queue_processes=4; alter system set aq_tm_processes=4; grant connect,resource to bz_adm,bz; GRANT aq_administrator_role TO bz_adm; GRANT EXECUTE ON dbms_aq TO bz_adm; GRANT EXECUTE ON dbms_aqadm TO bz_adm; GRANT select any dictionary to bz_adm; GRANT EXECUTE ON dbms_aq to bz; GRANT ExECUTE ON dbms_aqadm to bz; 建立payload type connect bz_adm/bz_adm CREATE OR REPLACE TYPE bzcardorder_typ AS OBJECT ( employee_id NUMBER(6), first_name VARCHAR2(20), last_name VARCHAR2(25), ordtyp VARCHAR(10)); grant execute on bzcardorder_typ to bz; 建立queue table execute dbms_aqadm.create_queue_table( - queue_table = ’bzcardorders_qt’, - comment = ’Business Card Orders queue table’, - multiple_consumers = true, - queue_payload_type = ’bzcardorder_typ’); 建立Queue dbms_aqadm.create_queue ( queue_name = ’bzcardorders_q’, queue_table = ’bzcardorders_qt’); select name, queue_table, queue_type from user_queues; dbms_aqadm.grant_queue_privilege(privilege = ’ALL’, queue_name = ’bzcardorders_q’, grantee = ’bz’, grant_option = TRUE); 添加Subscriber dbms_aqadm.add_subscriber ( - queue_name = ’BZCARDORDERS_Q’, - subscriber = sys.aq$_agent(’SHIPPING’,null,null)); dbms_aqadm.add_subscriber ( - queue_name = ’BZCARDORDERS_Q’, - subscriber = sys.aq$_agent(’BILLING’,null,null)); -- 基于规则的Subscriber dbms_aqadm.add_subscriber ( queue_name = ’BZCARDORDERS_Q’, subscriber = sys.aq$_agent(’RUSH_ORDER’,null,null), rule = ’tab.user_data.ordtyp = ’’RUSH’’’); 启动queue dbms_aqadm.start_queue ( queue_name = ’bzcardorders_q’); SELECT * FROM aq$bzcardorders_qt_s; 二、AQ的操作 1.Enqueue declare enqopt dbms_aq.enqueue_options_t; mprop dbms_aq.message_properties_t; enq_msgid RAW(16); begin dbms_aq.enqueue( queue_name = ’bz_adm.bzcardorders_q’, enqueue_options = enqopt, message_properties = mprop, payload = bz_adm.bzcardorder_typ( 101, ’First’, User’,’NORMAL’), msgid = enq_msgid); commit; end; -- Subscriber的Enqueue declare enqopt dbms_aq.enqueue_options_t;