时间戳

首页 > 解决方案 > 在 Oracle PL/SQL 循环索引变量“I”中执行存储过程时出错，使用无效

oracle - 在 Oracle PL/SQL 循环索引变量“I”中执行存储过程时出错，使用无效

问题描述

我对 Oracle PL/SQL 相当陌生，我试图执行一个存储过程，该过程插入到orders表中并循环通过以将一组项目插入order_item表中。我创建了一个已定义的类型，但是当我尝试执行我的过程时，我得到了这个loop index variable 'I' use is invalid.

SQL> -- Execute procedure
SQL> DECLARE
  2  order_id_ NUMBER;
  3
  4  BEGIN
  5      insert_order(p_order_id => 4, p_order_num => 'O223PS56', p_name => 'Test Test', p_email => 'test@test.co.uk', p_address => '123 Test Street', p_city => 'Newcastle Upon Tyne', p_province => 'Tyne and Wear', p_postcode => 'NE98 4TN', p_telephone => '123456789', p_total => 7.97, p_order_date => to_date('11-apr-2021', 'DD-mon-YYYY'));
  6      FOR i IN i..order_items
  7      LOOP
  8        insert_order_items(order_id_, order_items(i) => 5, order_items(i) => 2, order_items(i) => 2, order_items(i) => 3073748221, order_items(i) => 2, order_items(i) => 'Brand New', order_items(i) => 1.99, order_items(i) => 1.99);
  9       COMMIT;
 10     END LOOP;
 11  END;
 12  /
    FOR i IN i..order_items
             *
ERROR at line 6:
ORA-06550: line 6, column 14:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored
SQL>

CREATE OR REPLACE PROCEDURE insert_order
(
    p_order_id IN INT, p_order_num IN CHAR,
    p_name IN CHAR, p_email IN CHAR,
    p_address IN VARCHAR2, p_city IN VARCHAR2,
    p_province IN VARCHAR2, p_postcode IN VARCHAR2,
    p_telephone IN NUMBER, p_total IN NUMBER,
    p_order_date IN DATE
)
AS
BEGIN
    INSERT INTO orders(order_id, order_number, billing_name, billing_email, billing_address, billing_city, billing_province, billing_postcode, billing_telephone, billing_total, order_date)
    values(p_order_id, p_order_num, p_name, p_email, p_address, p_city, p_province, p_postcode, p_telephone, p_total, p_order_date);

    COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE insert_order_items
(
    p_order_item_id IN INT, p_order_id IN INT, 
    p_product_id IN INT, p_seller_id IN INT,
    p_sub_order_number IN CHAR, p_quantity IN INT,
    p_condition IN CHAR, p_unit_price IN NUMBER, 
    p_cost_charge IN NUMBER
)
AS
BEGIN
    INSERT INTO order_item(order_item_id, order_id, product_id, seller_id, sub_order_number, quantity, condition, unit_price, cost_charge)
    values(p_order_item_id, p_order_id, p_product_id, p_seller_id, p_sub_order_number, p_quantity, p_condition, p_unit_price, p_cost_charge);
    COMMIT;
END;
/

CREATE OR REPLACE TYPE order_items_collection as object(
    order_id int, product_id int,
    seller_id int, sub_order_number char(10),
    quantity int, condition char(100),
    unit_price number, cost_charge number
);
/

CREATE OR REPLACE TYPE order_items is table of order_items_collection;
/

标签： oracleplsqloracle11g

解决方案

这个：

FOR i IN i..order_items

没有意义；i是循环变量，所以它也不能是范围。你可以这样做：

for i in order_items.FIRST..order_items.LAST

或者因为这不是一个稀疏数组，也许更简单：

for i in 1..order_items.COUNT loop

但是order_items是一个类型，你不能迭代一个类型。您将遍历集合类型的实例。您尚未声明或填充该类型的变量。在循环中，您对类型的引用没有意义，如果它是一个实例，也不会。

据我所知，您正在尝试执行以下操作：

DECLARE
  order_id_ NUMBER;
  order_items_ order_items;
BEGIN
...
  -- create and populate instance of the collection type
  order_items_ := new order_items(
    order_items_collection(5, 2, 2, 3073748221, 2, 'Brand New', 1.99, 1.99)
  );

  -- iterate over the collection type
  for i in 1..order_items_.COUNT loop
    insert_order_items(
      p_order_item_id => order_id_,            -- these two values might be reversed?
      p_order_id => order_items_(i).order_id,  -- 
      p_product_id => order_items_(i).product_id,
      p_seller_id => order_items_(i).seller_id,
      p_sub_order_number => order_items_(i).sub_order_number,
      p_quantity => order_items_(i).quantity,
      p_condition => order_items_(i).condition,
      p_unit_price => order_items_(i).unit_price,
      p_cost_charge => order_items_(i).cost_charge
    );
  end loop;
END;
/

db<>小提琴演示

这将允许您在不重复过程调用的情况下插入多个值，例如通过更改：

  -- create and populate instance of the collection type
  order_items_ := new order_items(
    order_items_collection(5, 2, 2, 3073748221, 2, 'Brand New', 1.99, 1.99)
  );

至

  -- create and populate instance of the collection type
  order_items_ := new order_items(
    order_items_collection(5, 2, 2, 3073748221, 2, 'Brand New', 1.99, 1.99),
    order_items_collection(6, 3, 4, 3073748222, 1, 'Used', 19.99, 19.99),
    order_items_collection(7, 8, 9, 3073748223, 7, 'Refurb', 9.99, 9.99)
  );

（尽管从中您可以看到您的对象命名有点混乱）。

使用硬编码值似乎并不比将这些值硬编码到三个过程调用中更容易或更简单，但也许您的目标是从其他地方传递集合，例如前端应用程序。或者，也许您想将集合传递到您的过程中，并让它遍历集合并执行插入。目前，这两个过程似乎都让事情变得有点复杂，并阻止您进行批量插入。

在过程中提交通常也不是一个好主意，尤其是在循环中为相关数据调用的过程。如果第三项插入失败，则您已经提交了订单中的插入和前两项，因此您处于不一致的状态。看起来所有这些插入都应该插入到相同的事务中，并作为一个单元提交（或回滚）。

推荐阅读