PL/SQL:ORA-00932:不一致的数据类型:预期的编号获取user_name.varchar_array

Question

以下是我创建的函数，以接受varchar2项目的数组，并返回该记录的内部PK，这是每个记录的NUMBER。我正在努力使语法正确地将VARCHAR_ARRAY类型的数组传递给光标中的简单SQL查询，并返回类型NUMBER_ARRAY的变量。错误在第8,42行，即传递给该函数的FROM table_name WHERE column_name IN VARCHAR_ARRAY。当我学习PLSQL时，请帮助我解决这个错误。

  create or replace TYPE VARCHAR_ARRAY AS VARRAY(1000000) OF VARCHAR2(1000);
   /
  create or replace TYPE NUMBER_ARRAY AS VARRAY(1000000) OF NUMBER;
   /
  create or replace Function GET_PRODUCT_ID_ARR(V_PRODUCT_NUM_ARR IN VARCHAR_ARRAY)
  RETURN NUMBER_ARRAY 
  IS
     product_id_list number_array := number_array(); 
     CURSOR c1
     IS 
     SELECT cat_map_id 
     FROM mn_cat_map WHERE product_num IN (V_PRODUCT_NUM_ARR) and catalog_type = 'INT';
  v_output NUMBER;   
  BEGIN
      OPEN c1; 
      LOOP
          fetch c1 into product_id_list;
          EXIT WHEN c1%notfound;
          product_id_list.extend;
          product_id_list(product_id_list.count)  := v_output;
         dbms_output.put_line('Product ('||v_output ||'):'||product_id_list(v_output));
      END LOOP;
  Close c1;
  RETURN product_id_list;
  END;
  /

Answer 1

有两个问题：

1. 您必须将varray施放到表：

   CURSOR c1
   IS 
   SELECT cat_map_id 
   FROM mn_cat_map 
   WHERE product_num IN (select column_value from table(V_PRODUCT_NUM_ARR))
     and catalog_type = 'INT';
   

2. fetch之后添加 bulk collect：

   LOOP
     fetch c1 bulk collect into product_id_list limit 100;
     EXIT WHEN c1%notfound;
     product_id_list.extend;
     product_id_list(product_id_list.count)  := v_output;
     dbms_output.put_line('Product ('||v_output ||'):'||product_id_list(v_output));
     END LOOP;
   

如果您编写limit 100，则每个循环将在product_id_list中放置100个记录。您可以省略limit子句，在这种情况下，您将以一个获取的方式获取所有记录。

编辑
如何查看结果：

declare 
  myarray varchar_array; 
  my_num_array number_array;
begin 
  myarray := varchar_array(); 
  myarray.extend(2);
  myarray(1) := '151043'; 
  myarray(2) := '2895'; 
  my_num_array := GET_PRODUCT_ID_ARR(myarray);
  for i in 1 .. my_num_array.count loop
    dbms_output.put_line(my_num_array(i)); 
  end loop; 
end;
/

Answer 2

@william所说的是安静的真实。不建议使用VARRAY(1000000)。您可以创建一种类型的table。但是，如果我遵循您所做的事情，您的代码似乎有些错误。请在下面查看如何做。

表准备：

create table mn_cat_map(cat_map_id number,
                        product_num varchar2(1000),
                        catalog_type varchar2(10));
/
INSERT INTO T541682.MN_CAT_MAP (CAT_MAP_ID, PRODUCT_NUM, CATALOG_TYPE)
     VALUES (10, 'A123', 'INT');
INSERT INTO T541682.MN_CAT_MAP (CAT_MAP_ID, PRODUCT_NUM, CATALOG_TYPE)
     VALUES (2, 'B121', '2Wheer');
INSERT INTO T541682.MN_CAT_MAP (CAT_MAP_ID, PRODUCT_NUM, CATALOG_TYPE)
     VALUES (3, 'C645', '4Wheer');
COMMIT;
create or replace TYPE VARCHAR_ARRAY AS VARRAY(1000000) OF VARCHAR2(1000);
/
create or replace TYPE NUMBER_ARRAY AS VARRAY(1000000) OF NUMBER;
/

代码：阅读解释性评论内联

CREATE OR REPLACE FUNCTION GET_PRODUCT_ID_ARR (V_PRODUCT_NUM_ARR  VARCHAR_ARRAY)
   RETURN NUMBER_ARRAY
IS
   product_id_list   number_array := number_array ();
   CURSOR c1(tbl_list VARCHAR_ARRAY)
   IS
      SELECT cat_map_id
        FROM mn_cat_map
       WHERE product_num  in (select column_value from table(tbl_list)) ---Checking if the item exists in the table with passed collection 
       AND catalog_type = 'INT';
   v_output     NUMBER:= 0;
BEGIN
    --not opening cursor and am not looking for processing any records.
   --OPEN c1(V_PRODUCT_NUM_ARR);
   --passing the input varray to the cursor.
   for i in c1(V_PRODUCT_NUM_ARR)
   loop
    v_output:=v_output + 1;
    product_id_list.extend;
    product_id_list(product_id_list.COUNT):= i.cat_map_id;
     DBMS_OUTPUT.put_line('Product (' || v_output || '):' ||product_id_list(product_id_list.COUNT));

   end loop;
   RETURN product_id_list;
END;
/

执行：

SQL> select GET_PRODUCT_ID_ARR(VARCHAR_ARRAY('A123','B121','C645')) COl1 from dual;
COL1
--------------------------------------------------------------------------------
NUMBER_ARRAY(10)
Product (1):10