PL/SQL: ORA-00932: несогласованные типы данных: ожидаемое NUMBER получило USER_NAME.VARCHAR_ARRAY

Ниже приведена функция, которую я создаю, чтобы принимать массив из 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;
  /

function plsql varray
person Sanjay Rao    schedule 08.12.2016    source источник
comment
Я немного озадачен VARRAY(1000000) OF .... Какова возможная бизнес-логика ограничения в 1000000?   -  person William Robertson    schedule 08.12.2016

Ответы (2)


arrow_upward
1
arrow_downward

Есть две проблемы:

  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. Добавьте bulk collect после fetch:

    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, каждый цикл будет помещать 100 записей в product_id_list. Вы можете опустить предложение 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;
/
person Dmitriy    schedule 08.12.2016
comment
Когда я выполняю это следующим образом, объявить myarray varchar_array; начать мой массив := varchar_array(); моймассив.extend(2); мой массив (1): = '151043'; мой массив (2): = '2895'; dbms_output.put_line (GET_PRODUCT_ID_ARR (myarray)); конец; / ORA-06550: строка 8, столбец 4: PLS-00306: неправильное количество или типы аргументов при вызове PUT_LINE PL/SQL: оператор игнорируется 06550. 00000 - строка %s, столбец %s:\n%s * Причина: обычно ошибка компиляции PL/SQL. *Действие: - person Sanjay Rao; 08.12.2016
comment
@SanjayRao Это потому, что ваша функция GET_PRODUCT_ID_ARR возвращает массив. DBMS_OUTPUT.PUT_LINE не может принимать допустимый массив, для этого требуется строка. - person Dmitriy; 08.12.2016
comment
@SanjayRao Я добавил пример к ответу. - person Dmitriy; 08.12.2016
comment
Спасибо за подробное объяснение, все понятно. - person Sanjay Rao; 08.12.2016

arrow_upward
1
arrow_downward

То, что говорит @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
person XING    schedule 08.12.2016
comment
Спасибо, Син, цикл For легче понять. - person Sanjay Rao; 08.12.2016