tailieunhanh - Oracle Built−in Packages- P64

Oracle Built−in Packages- P64: Ah, for the good old days of Version 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 , 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 msgprops 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 . 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 IS NOT NULL THEN - payment_in ELSE Construct new object for enqueue setting up initial balance. layaway_obj layaway_t animal_in customer_in - payment_in END IF Don t wait for a commit. Set the correlation identifier for this message. corr_id customer_in animal_in 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. .