rem
rem $Header: timesten/quickstart/sample_code/plsql/update_inventory.sql /main/1 2009/04/03 14:13:41 rolawson Exp $
rem
Rem  Copyright (c) 1991 by Oracle Corporation
Rem    NAME
Rem      pls_examp1.sql - plsql example file
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    RETURNS
Rem
Rem    NOTES
Rem      renamed from examp1.sql to pls_examp1.sql
Rem    MODIFIED   (MM/DD/YY)
Rem     rolawson   04/03/09  - moved from sample_code/plsql/scripts to
Rem                            sample_code/plsql
Rem     rolawson   03/04/09  - initial checkin of quickstart files
Rem     nmeng      11/29/06  - renamed file
Rem     rvasired   05/12/92 -  Creation
/*
** This block processes an order for tennis rackets.  It decrements
** the quantity of rackets on hand only if there is at least one
** racket left in stock.
**
** Copyright (c) 1989,1992 by Oracle Corporation
*/

DECLARE
    qty_on_hand NUMBER(5);
BEGIN
    SELECT quantity INTO qty_on_hand FROM inventory2
        WHERE product = 'TENNIS RACKET'
        FOR UPDATE OF quantity;

    IF qty_on_hand > 0 THEN  -- check quantity
        UPDATE inventory2 SET quantity = quantity - 1
            WHERE product = 'TENNIS RACKET';
        INSERT INTO purchase_record
            VALUES ('Tennis racket purchased', SYSDATE);
    ELSE
        INSERT INTO purchase_record
            VALUES ('Out of tennis rackets', SYSDATE);
    END IF;

    COMMIT;
END;
/