Đang chuẩn bị liên kết để tải về tài liệu:
Oracle Built−in Packages- P65

Không đóng trình duyệt đến khi xuất hiện nút TẢI XUỐNG Tải xuống

Oracle Built−in Packages- P65: Ah, for the good old days of Version 1.0 of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms 3.0, and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Filename on companion disk aqtiming.spp FUNCTION onsale product_in IN VARCHAR2 RETURN BOOLEAN IS queueopts DBMS_AQ.DEQUEUE_OPTIONS_T msgprops DBMS_AQ.MESSAGE_PROPERTIES_T obj sale_t BEGIN Take immediate effect no commit required. queueopts.wait DBMS_AQ.NO_WAIT queueopts.visibility DBMS_AQ.IMMEDIATE Reset the navigation location to the first message. queueopts.navigation DBMS_AQ.FIRST_MESSAGE Dequeue in BROWSE. You never dequeue destructively. You let the Queue Monitor automatically expire messages and move them to the exception queue. queueopts.dequeue_mode DBMS_AQ.BROWSE Retrieve only the message for this product. queueopts.correlation UPPER product_in Locate the entry by correlation identifier and return the object. DBMS_AQ.DEQUEUE c_queue queueopts msgprops obj g_msgid RETURN obj.product IS NOT NULL EXCEPTION WHEN aq.dequeue_timeout THEN RETURN FALSE END This is a standard nondestructive dequeue operation. Notice that I use the correlation identifier to make sure that I attempt to dequeue a message only for this particular product. I also set navigation to first message to make sure I get the first message in enqueue time for the product. In this case I do not return any of the sale information. Instead I return TRUE if I found a non-NULL product in the dequeued object. If I timeout trying to retrieve a message I return FALSE. Of course I need to be able to put a product on sale. I do that with the sale.mark_for_sale procedure. Filename on companion disk aqtiming.spp PROCEDURE mark_for_sale product_in IN VARCHAR2 price_in IN NUMBER starts_on IN DATE ends_on IN DATE IS queueopts DBMS_AQ.ENQUEUE_OPTIONS_T msgprops DBMS_AQ.MESSAGE_PROPERTIES_T sale_obj sale_t BEGIN Calculate the delay number of seconds and the expiration in same terms msgprops.delay GREATEST 0 starts_on - SYSDATE 24 60 60 msgprops.expiration GREATEST 0 ends_on - starts_on 24 60 60 DBMS_OUTPUT.PUT_LINE Delayed for msgprops.delay seconds. DBMS_OUTPUT.PUT_LINE