oracle - PLS-00497 : cannot mix between single row and multi-row (BULK) in INTO list

Question

我创建了一个过程，使用BULK COLLECT在两个表中显示数据，但是我一直收到此错误。

PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

但是，如果我删除了 BULK COLLECT并在语句中包含where子句，它就会起作用。

create or replace PROCEDURE sktReport IS

  TYPE inventory_table_type is RECORD (
                    v_WH_ID                  INVENTORY.WH_ID%TYPE,
                    v_wa_Product_quantity_id Product_quantity.ST_ID%TYPE); 
  v_inventory_table inventory_table_type;
BEGIN

  SELECT INVENTORY.WH_ID, Product_quantity.ST_ID,  
    BULK COLLECT INTO
     v_inventory_table.v_WH_ID, 
     v_inventory_table.v_wa_Product_quantity_id,  
   FROM INVENTORY
  INNER JOIN Product_quantity
     ON Product_quantity.ST_ID = INVENTORY.ST_ID;

  FOR i IN v_inventory_table.v_WH_ID..v_inventory_table.v_WH_ID
  LOOP
    DBMS_OUTPUT.PUT_LINE ('ID : ' || v_inventory_table.v_WH_ID
                          || ' quantity ID : ' || v_inventory_table.v_in_Product_quantity_id);
  END LOOP;

END;

Answer 1

在这里，我演示了一个简单的示例来复制您的方案。请参见下面的代码。这可以帮助您。

SET serveroutput ON;
DECLARE
TYPE AV_TEST
IS
  RECORD
  (
    lv_att1 PLS_INTEGER,
    lv_att2 PLS_INTEGER );
type av_test_tab
IS
  TABLE OF av_test;
  av_test_tab_av av_test_tab;
BEGIN
  NULL;
  SELECT LEVEL,
    LEVEL+1 BULK COLLECT
  INTO av_test_tab_av
  FROM DUAL
    CONNECT BY LEVEL < 10;
  dbms_output.put_line(av_test_tab_av.count);
  FOR I IN av_test_tab_av.FIRST..av_test_tab_av.LAST
  LOOP
    dbms_output.put_line('working fine '||av_test_tab_av(i).lv_att1||' '||av_test_tab_av(i).lv_att2);
  END LOOP;
END;