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

Oracle Built−in Packages- P64: 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 Here is the make_payment procedure PROCEDURE make_payment customer_in IN VARCHAR2 animal_in IN VARCHAR2 payment_in IN NUMBER IS queueopts DBMS_AQ.ENQUEUE_OPTIONS_T msgprops DBMS_AQ.MESSAGE_PROPERTIES_T layaway_obj layaway_t BEGIN Locate this entry in the queue by calling the function. If found decrement the balance and reinsert into the queue. If not found enqueue a new message to the queue. For example purposes the price of all my bean-bag animals is 49.95. layaway_obj one_animal customer_in animal_in Adjust the balance. We SHOULD check for 0 or negative values and not requeue if finished. I will leave that to the reader. IF layaway_obj.animal IS NOT NULL THEN layaway_obj.balance layaway_obj.balance - payment_in ELSE Construct new object for enqueue setting up initial balance. layaway_obj layaway_t animal_in customer_in 49.95 - payment_in END IF Don t wait for a commit. queueopts.visibility DBMS_AQ.IMMEDIATE Set the correlation identifier for this message. msgprops.correlation corr_id customer_in animal_in DBMS_AQ.ENQUEUE c_queue queueopts msgprops layaway_obj g_msgid END The first thing that make_payment does is attempt to retrieve an existing queue entry for this customer-animal combination by calling one_animal. Again notice that I do not repeat the dequeue logic in make_payment. I am always careful to reuse existing code elements whenever possible. If I find a match the animal field is not NULL see the exception section in one_animal to understand how I set the message not found values in the returned object then I update the remaining balance. If no match is found then I construct an object to be placed in the queue. Once my layaway object has been updated or created I set the correlation identifier by calling the same corr_id function. Notice that when I enqueue I set the correlation field of the message properties record. When I dequeue on the other hand I set the correlation field of the dequeue options record. .