Liren Large Software Subsidy 5/05.iso/a/a524/37.ddi/demo/demo1_pls.sql

Text File | 1991-03-04 | 761 b | 25 lines

rem
rem $Header: demo1_pls.sql,v 6.1 90/02/16 18:39:58 nsalah Exp $ demo1_pls.sql Copyr (c) 1989 Oracle
rem
DECLARE
    num_in_stock    NUMBER(5);
    prod_name       CHAR(15);
    PROD_NUM        CONSTANT  NUMBER(5) := 1234;
BEGIN
    SELECT product, quantity INTO prod_name, num_in_stock
      FROM inventory_table WHERE prod_id = PROD_NUM;

        -- make sure there's at least one in stock
    IF num_in_stock > 0 THEN
        UPDATE inventory_table SET quantity = quantity - 1
               WHERE prod_id = PROD_NUM;
        INSERT INTO purchase_record
               VALUES (prod_name || ' PURCHASED.', SYSDATE);
    ELSE
        INSERT INTO purchase_record
               VALUES ('OUT OF ' || prod_name || 'S.', SYSDATE);
    END IF;

    COMMIT;
END;