Oracle9i Application Developer's Guide - Advanced Queuing Release 1 (9.0.1) Part Number A88890-02 |
|
Scripts for Implementing 'BooksOnLine', 3 of 6
Rem Rem $Header: tkaqdocd.sql 26-jan-99.17:51:23 aquser1 Exp $ Rem Rem tkaqdocd.sql Rem Rem Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved. Rem Rem NAME Rem tkaqdocd.sql - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem <other useful comments, qualifications, etc.> Rem Rem Rem Rem Schedule propagation for the shipping, billing, order entry queues Rem connect OE/OE; execute dbms_aqadm.schedule_propagation(queue_name => 'OE.OE_bookedorders_que'); connect WS/WS; execute dbms_aqadm.schedule_propagation(queue_name => 'WS.WS_backorders_que'); execute dbms_aqadm.schedule_propagation(queue_name => 'WS.WS_shippedorders_ que'); connect CBADM/CBADM; execute dbms_aqadm.schedule_propagation(queue_name => 'CBADM.CBADM_billedorders_ que'); Rem Rem Customer service application Rem Rem This application monitors the status queue for messages and updates Rem the Order_Status table. connect CS/CS Rem Rem Dequeus messages from the 'queue' for 'consumer' CREATE OR REPLACE PROCEDURE DEQUEUE_MESSAGE( queue IN VARCHAR2, consumer IN VARCHAR2, message OUT BOLADM.order_typ) IS dopt dbms_aq.dequeue_options_t; mprop dbms_aq.message_properties_t; deq_msgid raw(16); BEGIN dopt.dequeue_mode := dbms_aq.REMOVE; dopt.navigation := dbms_aq.FIRST_MESSAGE; dopt.consumer_name := consumer; dbms_aq.dequeue( queue_name => queue, dequeue_options => dopt, message_properties => mprop, payload => message, msgid => deq_msgid); commit; END; / Rem Rem Updates the status of the order in the status table Rem CREATE OR REPLACE PROCEDURE update_status( new_status IN VARCHAR2, order_msg IN BOLADM.ORDER_TYP) IS old_status VARCHAR2(30); dummy NUMBER; BEGIN BEGIN /* query old status from the table */ SELECT st.status INTO old_status from order_status_table st where st.customer_order.orderno = order_msg.orderno; /* Status can be 'BOOKED_ORDER', 'SHIPPED_ORDER', 'BACK_ORDER' * and 'BILLED_ORDER' */ IF new_status = 'SHIPPED_ORDER' THEN IF old_status = 'BILLED_ORDER' THEN return; /* message about a previous state */ END IF; ELSIF new_status = 'BACK_ORDER' THEN IF old_status = 'SHIPPED_ORDER' OR old_status = 'BILLED_ORDER' THEN return; /* message about a previous state */ END IF; END IF; /* update the order status */ UPDATE order_status_table st SET st.customer_order = order_msg, st.status = new_status where st.customer_order.orderno = order_msg.orderno; COMMIT; EXCEPTION WHEN OTHERS THEN /* change to no data found */ /* first update for the order */ INSERT INTO order_status_table(customer_order, status) VALUES (order_msg, new_status); COMMIT; END; END; / Rem Rem Monitors the customer service queues for 'time' seconds Rem CREATE OR REPLACE PROCEDURE MONITOR_STATUS_QUEUE(time IN NUMBER) IS agent_w_message aq$_agent; agent_list dbms_aq.agent_list_t; wait_time INTEGER := 120; no_message EXCEPTION; pragma EXCEPTION_INIT(no_message, -25254); order_msg boladm.order_typ; new_status VARCHAR2(30); monitor BOOLEAN := TRUE; begin_time number; end_time number; BEGIN begin_time := dbms_utility.get_time; WHILE (monitor) LOOP BEGIN agent_list(1) := aq$_agent('BILLED_ORDER', 'CS_billedorders_que', NULL); agent_list(2) := aq$_agent('SHIPPED_ORDER', 'CS_shippedorders_que', NULL); agent_list(3) := aq$_agent('BACK_ORDER', 'CS_backorders_que', NULL); agent_list(4) := aq$_agent('Booked_ORDER', 'CS_bookedorders_que', NULL); /* wait for order status messages */ dbms_aq.listen(agent_list, wait_time, agent_w_message); dbms_output.put_line('Agent' || agent_w_message.name || ' Address '|| agent_ w_message.address); /* dequeue the message from the queue */ dequeue_message(agent_w_message.address, agent_w_message.name, order_msg); /* update the status of the order depending on the type of the message * the name of the agent contains the new state */ update_status(agent_w_message.name, order_msg); /* exit if we have been working long enough */ end_time := dbms_utility.get_time; IF (end_time - begin_time > time) THEN EXIT; END IF; EXCEPTION WHEN no_message THEN dbms_output.put_line('No messages in the past 2 minutes'); end_time := dbms_utility.get_time; /* exit if we have done enough work */ IF (end_time - begin_time > time) THEN EXIT; END IF; END; END LOOP; END; / Rem Rem History queries Rem Rem Rem Average processing time for messages in western shipping: Rem Difference between the ship- time and book-time for the order Rem Rem NOTE: we assume that order id is the correlation identifier Rem Only processed messages are considered. Connect WS/WS SELECT SUM(SO.enq_time - BO.enq_time) / count (*) AVG_PRCS_TIME FROM WS.AQ$WS_orders_pr_mqtab BO , WS.AQ$WS_orders_mqtab SO WHERE SO.msg_state = 'PROCESSED' and BO.msg_state = 'PROCESSED' AND SO.corr_id = BO.corr_id and SO.queue = 'WS_shippedorders_que'; Rem Rem Average backed up time (again only processed messages are considered Rem SELECT SUM(BACK.deq_time - BACK.enq_time)/count (*) AVG_BACK_TIME FROM WS.AQ$WS_orders_mqtab BACK WHERE BACK.msg_state = 'PROCESSED' and BACK.queue = 'WS_backorders_que';
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|