Oracle Built−in Packages- P42: 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 SELECT UPDATE on AUCTION_ITEMS INSERT on BIDS SELECT on HIGH_BIDS Execution Requirements AS exceptions raised and handled in PLACE_BID procedure invalid_item EXCEPTION bid_too_low EXCEPTION item_is_closed EXCEPTION place a bid on an item the bid must exceed any other bids on the item and the minimum bid bidding on an item registers interest in the item using only this procedure should be used to add rows to the bids table since it also updates column PROCEDURE place_bid item_id_IN IN VARCHAR2 bid_IN IN NUMBER close bidding on an item PROCEDURE close_item item_id_IN IN VARCHAR2 watch for any alerts on items bid by the user indicating other users have raised the bid PROCEDURE watch_bidding timeout_secs_IN IN NUMBER 300 END auction Place_bid procedure The place_bid procedure is intended to be used by the GUI application to place all bids in the auction. No INSERTS or UPDATES to the BIDS table should be allowed except through this procedure as it maintains the complex integrity constraint on the table updates the curr_bid column of AUCTION_ITEMS and registers the session for receiving alerts on the item. The body of place_bid looks like this Filename on companion disk PROCEDURE place_bid item_id_IN IN VARCHAR2 bid_IN IN NUMBER IS temp_curr_bid TYPE TYPE CURSOR auction_item_cur IS SELECT NVL curr_bid min_bid status FROM auction_items WHERE id item id IN DBMS_ALERT Examples 196 Appendix A What s on the Companion Disk FOR UPDATE OF curr_bid BEGIN lock row in auction_items OPEN auction_item_cur FETCH auction_item_cur INTO temp_curr_bid temp_status do some validity checks IF auction_item_cur NOTFOUND THEN RAISE invalid_item ELSIF temp_status CLOSED THEN RAISE item_is_closed ELSIF bid_IN temp_curr_bid THEN RAISE bid_too_low ELSE insert to bids AND update auction_items bidders identified by session username .
