时间戳

首页 > 解决方案 > 批量收集的订阅数不胜数

sql - 批量收集的订阅数不胜数

问题描述

嗨，我有一个错误问题：订阅超出计数。我有一个创建订单的过程（为了代码的清晰，这部分被删除）。程序从篮子中挑选产品，然后降低循环中仓库（可以有 1 个或多个仓库）中特定产品的数量。你能解释一下我做错了什么吗？非常感谢。

这是我的程序的主体：

PROCEDURE cpr_create_order (pin_intCustomer       IN customer.customer_id%TYPE,
                            pin_strPayment        IN payment.payment_type%TYPE,
                            pin_strTransp         IN transp.transp_type%TYPE,
                            pout_strErrorCode     OUT NUMBER,
                            pout_strErrorMessage  OUT VARCHAR2)
IS

    lv_warehouse_balance    NUMBER;
    lv_intQuantity          NUMBER;
    lv_intOrder             NUMBER;
    
    TYPE collection_list IS TABLE OF NUMBER;
    lv_products     collection_list := collection_list();
    lv_quantities   collection_list := collection_list();
    
    lv_warehouses   collection_list := collection_list();
    lv_balances     collection_list := collection_list();
    
BEGIN
    pout_strErrorCode    := 0;
    pout_strErrorMessage := '';
    
    SAVEPOINT S1;
        
        -- fill collections of products and quantities from basket
        SELECT product_id, basket_quantity
        BULK COLLECT INTO lv_products, lv_quantities
        FROM basket
        WHERE order_id IS NULL
        ORDER BY product_id;
        
        FOR i IN 1.. lv_products.COUNT LOOP    -- 1
            
             -- fill collection of warehouses and actual product balances
             SELECT warehouse_id, product_balance  --1,2   -- 5,10
             BULK COLLECT INTO lv_warehouses, lv_balances
             FROM warehouse
             WHERE product_id = lv_products(i)
             ORDER BY warehouse_id;
             
             lv_intQuantity := lv_quantities(i);   --8
             
             FOR j IN 1..lv_warehouses.COUNT LOOP
                IF lv_intQuantity > 0 THEN
                    SELECT product_balance
                    INTO lv_warehouse_balance
                    FROM warehouse
                    WHERE product_id = lv_products(i)
                    AND warehouse_id = lv_warehouses(j);
                    
                    -- lower the balance of the product at the warehouse by the given quantity
                    IF lv_intQuantity < lv_warehouse_balance THEN   --8 --10
                        UPDATE warehouse
                        SET product_balance = product_balance - lv_intQuantity
                        WHERE product_id = lv_products(i)
                        AND warehouse_id = lv_warehouses(j);
                        
                        lv_intQuantity := lv_intQuantity - lv_intQuantity; -- 0
                    
                    -- remove product from the warehouse    
                    ELSIF lv_intQuantity >= lv_warehouse_balance THEN
                        DELETE FROM warehouse
                        WHERE product_id = lv_products(i)
                        AND warehouse_id = lv_warehouses(j);
                        
                        lv_intQuantity := lv_intQuantity - lv_balances(i);
                        
                    END IF;
                END IF;
             END LOOP;  
        END LOOP;     
    
EXCEPTION
    WHEN OTHERS THEN
      pout_strErrorCode := -1;
      pout_strErrorMessage  := substr(SQLERRM,
                                instr(SQLERRM, 'ORA') + 11,
                                length(SQLERRM));
      ROLLBACK TO S1;
END cpr_create_order;

标签： sqloracleplsql

解决方案

这一行：

lv_intQuantity := lv_intQuantity - lv_balances(i);

应该：

lv_intQuantity := lv_intQuantity - lv_balances(j);

lv_products和集合由第lv_quantities一个批量集合填充，因此它们具有相同数量的成员，并且应该由相同的变量索引i- 您正在这样做。

和集合由第二个批量集合填充lv_warehouses，lv_balances因此它们具有相同数量的成员，并且应该由相同的变量索引j。

你不应该参考lv_balances(i)- 它有时会给出一个结果，但它不会是你所期望的，当j它更低时它会得到这个错误，因为该集合i中没有具有索引的成员。i

推荐阅读