如何在收到订单时更新订单数量，但如果已收到订单则将其保留

Hi so I am trying to write a script that receives an order and updates the quantity when the order is received. My problem is I am not sure how to do this without always updating the quantity. For example if the order was already received, it won't update the quantity. Here is a sample of what the output should be. Note: I have most of the script done just need the quantity update part.

嗨所以我正在尝试编写一个接收订单的脚本,并在收到订单时更新数量。我的问题是我不知道怎么做而不总是更新数量。例如,如果已收到订单,则不会更新数量。以下是输出应该是什么的示例。注意:我完成的大部分脚本只需要数量更新部分。

SQL> @ c:\receive
**********  Receive Order Screen  **********


Date:  13-APR-15

Enter Order Number to Receive (format 9999): 1010


Order Found.  Verify the following:

Part Number               : 101
Part Description          : Air Filter
Current Inventory Quantity: 120

Supplier Code: 102
Supplier Name: Better Auto Buys

Date Ordered    : 13-APR-15
Date Received   :
Quantity Ordered: 20

** Again, verify order information:                               **
** In case of discrepancy (Order not found, Wrong quantity, etc.) **
**    Press [CTRL] [C] twice to ABORT                             **
** If correct, press [ENTER] to continue                          **


New Quantity in Stock: 140

Code:

SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF

PROMPT ******** RECEIVE ORDER SCREEN ********
PROMPT
SELECT 'Date: ', TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;
PROMPT

DEFINE v_ord_num = 'NA'
DEFINE v_part_num = 'NA'
DEFINE v_part_description = 'N/A'
DEFINE v_part_qtyonhand = 'N/A'
DEFINE v_supplier_code = 'NA'
DEFINE v_supplier_name = 'NA'
DEFINE v_date_ordered = 'NA'
DEFINE v_date_received = 'NA'
DEFINE v_ord_qty = 'NA'

ACCEPT v_ord_num NUMBER FORMAT 9999 PROMPT 'Enter order number (format 9999):'

SET TERMOUT OFF
SPOOL C:\is380\receive.sql

SELECT 'DEFINE v_ord_num =' || '''' || ORD_NUM || '''' || CHR(13) || CHR(10) ||
   'DEFINE v_part_num =' || '''' || P.PART_NUM || '''' || CHR(13) || CHR(10) ||
   'DEFINE v_part_description =' || '''' || PART_DESCRIPTION || '''' || CHR(13) || CHR(10) ||
   'DEFINE v_part_qtyonhand =' || '''' || PART_QTYONHAND || ''''
FROM ORD O, PART P
WHERE ORD_NUM = &v_ord_num
AND O.PART_NUM = P.PART_NUM;

SELECT 'DEFINE v_ord_num =' || '''' || ORD_NUM || '''' || CHR(13) || CHR(10) ||
   'DEFINE v_supplier_code =' || '''' || S.SUPPLIER_CODE || '''' || CHR(13) || CHR(10) ||
   'DEFINE v_suplier_name =' || '''' || SUPPLIER_NAME || ''''
FROM ORD O, SUPPLIER S
WHERE ORD_NUM = &v_ord_num
AND S.SUPPLIER_CODE = O.SUPPLIER_CODE;

UPDATE ORD
SET ORD_RECDATE = NVL(ORD_RECDATE,SYSDATE)
WHERE ORD_NUM = &v_ord_num;

SELECT 'DEFINE v_ord_num =' || '''' || ORD_NUM || '''' || CHR(13) || CHR(10) ||
   'DEFINE v_date_ordered =' || '''' || ORD_DATE|| '''' || CHR(13) || CHR(10) ||
   'DEFINE v_date_received =' || '''' || ORD_RECDATE || '''' || CHR(13) || CHR(10) ||
   'DEFINE v_ord_qty =' || '''' || ORD_QTY || ''''
FROM ORD
WHERE ORD_NUM = &v_ord_num;

UPDATE ORD
SET ORD_QTY =

TABLES:

CREATE TABLE PART (
PART_NUM        INTEGER,
PART_DESCRIPTION    VARCHAR2(20),
PART_QTYONHAND      INTEGER,
CONSTRAINT PART_PART_NUM_PK PRIMARY KEY(PART_NUM));

INSERT INTO PART VALUES (100,'Oil Filter',75);
INSERT INTO PART VALUES (101,'Air Filter',120);
INSERT INTO PART VALUES (102,'Oil Can',300);
INSERT INTO PART VALUES (103,'Spark Plugs',200);
INSERT INTO PART VALUES (104,'Antifreeze',100);


CREATE TABLE SUPPLIER (
SUPPLIER_CODE       INTEGER,
SUPPLIER_NAME       VARCHAR2(20),
SUPPLIER_ADDRESS    VARCHAR2(20),
SUPPLIER_CITY       VARCHAR2(20),
SUPPLIER_STATE      CHAR(2),
SUPPLIER_ZIP        CHAR(5),
SUPPLIER_AREACODE   CHAR(3),
SUPPLIER_PHONE      CHAR(8),
CONSTRAINT SUPP_CODE_PK PRIMARY KEY(SUPPLIER_CODE));

INSERT INTO SUPPLIER VALUES (101,'ACE Auto','123 Able Lane','Long    Beach','CA','90840','562','555-1234');
INSERT INTO SUPPLIER VALUES (102,'Better Auto Buys','456 Main Street','Huntington Beach','CA','92647','714','555-2222');
INSERT INTO SUPPLIER VALUES (103,'Cars R Us','321 Car Lane','Long Beach','CA','90840','562','555-3333');
INSERT INTO SUPPLIER VALUES (104,'Delta Parts','444 Auto Way','Long Beach','CA','90840','562','555-4444');


CREATE TABLE ORD (
ORD_NUM         INTEGER,
PART_NUM        INTEGER,
SUPPLIER_CODE       INTEGER,
ORD_QTY         INTEGER,
ORD_DATE        DATE,
ORD_RECQTY      INTEGER,
ORD_RECDATE     DATE,
CONSTRAINT ORD_NUM_PK PRIMARY KEY(ORD_NUM),
CONSTRAINT ORD_PARTNUM_SUPPCODE_FK FOREIGN KEY(SUPPLIER_CODE, PART_NUM)   REFERENCES SUPPPART);

INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1000, 101, 101, 20, '03-APR-2015', 20, '06-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1001, 102, 101, 30, '03-APR-2015', 30, '05-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1002, 103, 101, 30, '04-APR-2015', 30, '05-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1003, 102, 101, 10, '04-APR-2015', 10, '05-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1004, 102, 101, 10, '03-APR-2015', 10, '06-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1005, 102, 102, 25, '04-APR-2015', 25, '04-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1006, 101, 102, 20, '03-APR-2015', 20, '06-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1007, 103, 103, 40, '05-APR-2015', 40, '07-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1008, 102, 104, 10, '05-APR-2015', 10, '06-APR-2015');
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE, ORD_RECQTY, ORD_RECDATE)
   VALUES (1009, 103, 104, 10, '03-APR-2015', 10, '05-APR-2015');

1 个解决方案

#1

Instead of your unfinished UPDATE statement towards the end of your script, I would use something like this:

而不是你的脚本结束时未完成的UPDATE语句,我会使用这样的东西:

UPDATE PROD
SET PART_QTYONHAND =
   (SELECT CASE WHEN ORD_RECDATE IS NULL
                THEN PART_QTYONHAND + ORDERQTY
                ELSE PART_QTYONHAND END Q
    FROM PART P, ORD O
    WHERE P.PART_NUM = O.PART_NUM
    AND O.ORD_NUM = &v_ord_num)
WHERE PART_NUM = &v_part_num

This will update the part's PART_QTYONHAND only if the order's ORD_RECDATE is still null, and this value is only updated when the order is received. You'll probably want to have your script output a message to notify the user that the order they wanted to receive had already been received.

仅当订单的ORD_RECDATE仍为空时,这将更新零件的PART_QTYONHAND,并且仅在收到订单时更新此值。您可能希望让脚本输出一条消息,通知用户已收到他们想要接收的订单。