资源描述：
《Oracle AQ使用实例》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、AQ使用实例一、AQ的安装1.创建bz_admin,bz用户altersystemsetjob_queue_processes=4;altersystemsetaq_tm_processes=4;grantconnect,resourcetobz_adm,bz;GRANTaq_administrator_roleTObz_adm;GRANTEXECUTEONdbms_aqTObz_adm;GRANTEXECUTEONdbms_aqadmTObz_adm;GRANTselectanydictionarytobz_adm;GRANTEXECUTEONdbms_aqtob
2、z;GRANTExECUTEONdbms_aqadmtobz;2.建立payloadtypeconnectbz_adm/bz_admCREATEORREPLACETYPEbzcardorder_typASOBJECT(employee_idNUMBER(6),first_nameVARCHAR2(20),last_nameVARCHAR2(25),ordtypVARCHAR(10));grantexecuteonbzcardorder_typtobz;3.建立queuetableexecutedbms_aqadm.create_queue_table(-queue_
3、table=>’bzcardorders_qt’,-comment=>’BusinessCardOrdersqueuetable’,-multiple_consumers=>true,-queue_payload_type=>’bzcardorder_typ’);4.建立Queuedbms_aqadm.create_queue(queue_name=>’bzcardorders_q’,queue_table=>’bzcardorders_qt’);selectname,queue_table,queue_typefromuser_queues;dbms_aqadm.
4、grant_queue_privilege(privilege=>’ALL’,queue_name=>’bzcardorders_q’,grantee=>’bz’,grant_option=>TRUE);1.添加Subscriberdbms_aqadm.add_subscriber(-queue_name=>’BZCARDORDERS_Q’,-subscriber=>sys.aq$_agent(’SHIPPING’,null,null));dbms_aqadm.add_subscriber(-queue_name=>’BZCARDORDERS_Q’,-subscri
5、ber=>sys.aq$_agent(’BILLING’,null,null));--基于规则的Subscriberdbms_aqadm.add_subscriber(queue_name=>’BZCARDORDERS_Q’,subscriber=>sys.aq$_agent(’RUSH_ORDER’,null,null),rule=>’tab.user_data.ordtyp=’’RUSH’’’);2.启动queuedbms_aqadm.start_queue(queue_name=>’bzcardorders_q’);SELECT*FROMaq$bzcardor
6、ders_qt_s;二、AQ的操作1.Enqueuedeclareenqoptdbms_aq.enqueue_options_t;mpropdbms_aq.message_properties_t;enq_msgidRAW(16);begindbms_aq.enqueue(queue_name=>’bz_adm.bzcardorders_q’,enqueue_options=>enqopt,message_properties=>mprop,payload=>bz_adm.bzcardorder_typ(101,’First’,User’,’NORMAL’),msg
7、id=>enq_msgid);commit;end;--Subscriber的Enqueuedeclareenqoptdbms_aq.enqueue_options_t;mpropdbms_aq.message_properties_t;enq_msgidRAW(16);rcpt_listdbms_aq.aq$_recipient_list_t;beginrcpt_list(0):=sys.aq$_agent(’Shipping’,null,null);rcpt_list(1):=sys.aq$_agent(’Billing’,null,null);mprop.
