1.Procedure named *ship_order* that will accept an order# parameter. It should verify that the ship date for the order# is null and each book ordered has a sufficient quantity on hand. If every book is available, the quantity on hand for each book should be decremented and the ship date for the order should be updated to the current date.

This is what i have so far but i don't think i'm anywhere close to right

create or replace
  p_ORDER# in orders.order#%type,
  p_quantity out orderitems.quantity%type,
  p_shipdate out orders.shipdate%type,
  p_on_hand out books.on_hand_quantity%type
) AS 
cursor cur_ship_order is
select i.quantity, o.shipdate, b.on_hand_quantity
into p_quantity, p_shipdate, p_on_hand
from orderitems i, orders o, books b
where b.isbn = i.isbn
  AND i.order# = p_order#  AND o.shipdate is NULL;
  for i in cur_ship_order loop
    if p_quantity > 0 then

Recommended Answers

All 2 Replies

Need more clarification to answer this.

Member Avatar

1. The number of books you have on-hand, has got nothing to do with the order.
Your on-hand should be in your table which stores your inventory.
For your order, you may want to track how many books ordered, and how many books shipped.
The number of books shipped will be the least of how many ordered and how many you have on-hand.
On-hand should be deducted by how many shipped.
In your inventory table, you may also want a reorder count.
When your inventory reaches the reorder count, order more books from the supplier.
That way, you don't run out of books to sell to your client.

2. Where is the rest of your Ship_Order procedure?

ps. It's bad practice to use special characters in naming... anything.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.