需要编码的方式是,如果一个买家购买数量超过库存控制台应该显示一条消息,你只能购买可用的数量,从现在起,其余的数量将很快更新,而且如果库存足够购买它应该显示请继续
简而言之,“在表订单行中插入行之前验证现存量并同时更新表库存现存量的功能。”

CREATE OR REPLACE PACKAGE order_package IS
          global_inv_id NUMBER (6);
          global_quantity NUMBER (6);
    PROCEDURE create_new_order(current_c_id NUMBER,
 current_meth_pmt VARCHAR2, current_os_id NUMBER);
   PROCEDURE create_new_order_line(current_o_id NUMBER);
       END;
        /


CREATE OR REPLACE PACKAGE BODY order_package IS

    PROCEDURE create_new_order(current_c_id NUMBER,
 current_meth_pmt VARCHAR2, current_os_id NUMBER) AS
         current_o_id NUMBER;
       BEGIN
         SELECT order_seq.NEXTVAL
         INTO  current_o_id
         FROM   dual;
           INSERT INTO orders
     VALUES(current_o_id, sysdate,current_meth_pmt, current_c_id,
       current_os_id);
          COMMIT;
       create_new_order_line(current_o_id);
       END create_new_order;
   PROCEDURE create_new_order_line(current_o_id NUMBER)AS
       BEGIN
       INSERT INTO order_line
    VALUES(current_o_id,global_inv_id, global_quantity);
         COMMIT;
        END create_new_order_line;
       END;
        /

最佳答案

你没有显示你的库存表和你的程序似乎没有一个数量订购价值,所以这是一些猜测。您可能要做的是首先更新该表,然后使用RETURNING INTO子句获取更新的库存。

UPDATE inventory SET global_quantity = global_quantity - order_quantity
 WHERE global_inv_id = current_c_id
RETURNING global_quantity INTO l_global_quantity;

IF l_global_quantity < 0 THEN
    ROLLBACK;
    raise_application_error( -20001, 'You ordered too much!' );
ELSE
    [... create order goes here ...]
END IF;

是否正在订购物品?这将引发一个异常,该异常应该被调用您的过程的任何对象捕获。如何向用户显示错误将取决于所使用的应用程序层。

10-06 05:17
查看更多