plsql - PL/SQL 简单逻辑错误，想不通

Question

好的，这是我的代码....

      DECLARE
  V_INVENTORY_ITEM     INVENTORY.ITEM%TYPE;
  V_INVENTORY_PRICE    INVENTORY.PRICE%TYPE;
  V_INVENTORY_ONHAND   INVENTORY.ONHAND%TYPE;
  V_TRANS_ITEM          TRANSACTION.ITEM%TYPE;
  V_TRANS_CODE          TRANSACTION.CODE%TYPE;
  V_NEW_INVE_ITEM       NEW_INVENTORY.ITEM%TYPE;
  V_NEW_INVE_SOLD       NEW_INVENTORY.SOLD%TYPE;
  V_NEW_INVE_RETURNED   NEW_INVENTORY.RETURNED%TYPE;
  V_NEW_INVE_ONHAND     NEW_INVENTORY.ONHANDNEW%TYPE;
  V_NEW_INVE_PURCHASED  NEW_INVENTORY.PURCHASED%TYPE;
  V_NEW_INVE_ORIGINAL   NEW_INVENTORY.ONHANDORIG%TYPE;
CURSOR INVEN_CURSOR IS
     SELECT ITEM, PRICE, ONHAND FROM INVENTORY
     ORDER BY ITEM;
  CURSOR TRANS_CURSOR IS
     SELECT ITEM, CODE FROM TRANSACTION
     WHERE V_INVENTORY_ITEM = ITEM
     ORDER BY ITEM;
BEGIN
    OPEN INVEN_CURSOR;
    LOOP
    FETCH INVEN_CURSOR INTO V_INVENTORY_ITEM, V_INVENTORY_PRICE, V_INVENTORY_ONHAND;
    EXIT WHEN INVEN_CURSOR%NOTFOUND;
    V_NEW_INVE_SOLD := 0;
    V_NEW_INVE_RETURNED := 0;
    V_NEW_INVE_ONHAND := 0;
    V_NEW_INVE_PURCHASED := 0;
    V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
    OPEN TRANS_CURSOR;
    LOOP
    FETCH TRANS_CURSOR INTO V_TRANS_ITEM, V_TRANS_CODE;
    EXIT WHEN TRANS_CURSOR%NOTFOUND;

IF V_TRANS_CODE = 'P' THEN
V_NEW_INVE_ONHAND := V_INVENTORY_ONHAND + 1;
V_NEW_INVE_PURCHASED := V_NEW_INVE_PURCHASED + 1;
V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
END IF;
IF V_TRANS_CODE = 'R' THEN
V_NEW_INVE_RETURNED := V_NEW_INVE_RETURNED + 1;
V_NEW_INVE_ONHAND := V_INVENTORY_ONHAND + 1;
V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
END IF;
IF V_TRANS_CODE = 'S' THEN
V_NEW_INVE_SOLD := V_NEW_INVE_SOLD + 1;
V_NEW_INVE_ONHAND := V_INVENTORY_ONHAND - 1;       
V_NEW_INVE_ORIGINAL := V_INVENTORY_ONHAND;
END IF;

END LOOP;
INSERT INTO NEW_INVENTORY 
VALUES(V_INVENTORY_ITEM, V_NEW_INVE_SOLD, V_NEW_INVE_PURCHASED, V_NEW_INVE_RETURNED, V_INVENTORY_ONHAND, V_NEW_INVE_ONHAND);
CLOSE TRANS_CURSOR;
END LOOP;
CLOSE INVEN_CURSOR;
END;
/

我正在尝试更新一个表，它是一个库存表...这会读取交易表并更新一个新表...(新库存)在我的 if 语句中出现了错误，因为每个变量都显示为 0；有什么建议吗？

我的 table

SQL> select * from inventory;

ITEM                 PRICE     ONHAND
--------------- ---------- ----------
BALL                 12.99          5
PEN                   1.99         10
PENCIL                2.99          1
PAPER                 5.99          3
ERASER                 .99          6
BACKPACK             19.99         10
STAPLER               3.99         12
RULER                 4.99          9
NOTEBOOK              6.99         12

9 rows selected.

SQL>

SQL> select * from transaction;

ITEM            CO
--------------- --
BALL            P
BALL            R
BALL            S
BALL            S
BALL            S
PEN             R
PEN             S
PEN             S
PEN             P
PENCIL          S
PENCIL          R
PENCIL          S
PENCIL          P
PAPER           S
PAPER           S
PAPER           S
ERASER          R
ERASER          S
ERASER          S
ERASER          P
BACKPACK        S
BACKPACK        S
BACKPACK        S
BACKPACK        P
STAPLER         R
STAPLER         S
RULER           S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S
NOTEBOOK        S

33 rows selected.

SQL>


SQL> select * from new_inventory;

ITEM                  SOLD   RETURNED     ONHAND
--------------- ---------- ---------- ----------
BACKPACK                 0          0          0
BALL                     0          0          0
ERASER                   0          0          0
NOTEBOOK                 0          0          0
PAPER                    0          0          0
PEN                      0          0          0
PENCIL                   0          0          0
RULER                    0          0          0
STAPLER                  0          0          0

9 rows selected.

Answer 1

尝试在打开 TRAN_CURSOR 之前输入以下语句

V_NEW_INVE_SOLD := 0;
V_NEW_INVE_RETURNED := 0;
V_NEW_INVE_ONHAND := 0;