pack inventoryops

/**  * 005 InventoryOps  */

CREATE OR REPLACE PACKAGE InventoryOps IS

  -- Modifies the inventory data for the specified book
  PROCEDURE UpdateIsbn(p_isbn IN INVENTORY.ISBN%TYPE
                      , p_status IN INVENTORY.STATUS%TYPE
                      , p_statusdate IN INVENTORY.STATUS_DATE%TYPE
                      , p_amount IN INVENTORY.AMOUNT%TYPE);
      
      
  -- Deletes the inventory data for the specified book             
  PROCEDURE Delete_isbn(p_isbn IN INVENTORY.ISBN%TYPE);
  
  -- Exception raised by UpdateISBN or DeleteISBN when the specified ISBN
  -- is not in the inventory table
  e_isbn_not_found  EXCEPTION;
  
  
  TYPE isbn_table IS TABLE OF inventory.isbn%TYPE INDEX BY BINARY_INTEGER;
  
  -- Returns an array containing the books with the specified status
  PROCEDURE StatusList(p_status IN INVENTORY.STATUS%TYPE
                      , p_books OUT ISBN_TABLE
                      , p_numbooks OUT BINARY_INTEGER);


END InventoryOps;
/

CREATE OR REPLACE PACKAGE BODY InventoryOps IS

  /**
  PROCEDURE ValidateStatus(p_status IN INVENTORY.STATUS%TYPE) IS
  BEGIN
    IF p_status = 'IN STOCK' OR p_status = 'BACKORDERED' OR p_status = 'FUTURE' THEN
      RETURN;
    ELSE
      RAISE_APPLICATION_ERROR(20000, 'Suplied status ' || p_status || ' is not valid');
    END IF;
  END ValidateStatus;
  */

 PROCEDURE UpdateIsbn(p_isbn IN INVENTORY.ISBN%TYPE
                      , p_status IN INVENTORY.STATUS%TYPE
                      , p_statusdate IN INVENTORY.STATUS_DATE%TYPE
                      , p_amount IN INVENTORY.AMOUNT%TYPE) IS
  BEGIN
     -- ValidateStatus(p_status);
     UPDATE inventory
     SET status = p_status, status_date = p_statusdate, amount = p_amount
     WHERE isbn = p_isbn;
     
     IF SQL%ROWCOUNT = 0 THEN
      RAISE e_isbn_not_found;
     END IF;
     
  END UpdateIsbn;
  
  PROCEDURE Delete_isbn(p_isbn IN INVENTORY.ISBN%TYPE) IS
  BEGIN
    DELETE FROM inventory
    WHERE isbn = p_isbn;
    
    IF SQL%ROWCOUNT = 0 THEN
      RAISE e_isbn_not_found;
    END IF;
  END Delete_isbn;
  
  PROCEDURE StatusList(p_status IN INVENTORY.STATUS%TYPE
                      , p_books OUT ISBN_TABLE
                      , p_numbooks OUT BINARY_INTEGER) IS
                      
    l_isbn INVENTORY.ISBN%TYPE;
    
    CURSOR books_cursor IS 
      SELECT isbn 
      FROM inventory i 
      WHERE i.status = p_status;
  
BEGIN
    -- ValidateStatus(p_status);
    p_numbooks := 0;
    
    OPEN books_cursor;
    
    LOOP
      FETCH books_cursor 
      INTO l_isbn;
      EXIT WHEN books_cursor%NOTFOUND;
    
      p_numbooks := p_numbooks + 1;
      p_books(p_numbooks) := l_isbn;
      
    END LOOP;
    
    CLOSE books_cursor;
  END StatusList;
  
END InventoryOps;
/