Передача значений в предложение IN в функциональном оракуле

Мне нужно вернуть курсор внутри функции:

CREATE OR REPLACE FUNCTION test_cursor (
   bigstring   IN   VARCHAR2
)
   RETURN cursor

IS
  row_test table_colors := table_colors(bigstring);
  c1 CURSOR;
BEGIN
   OPEN c1 FOR
      select * from cars where color IN (select column_value
                                    from table(row_test));

   RETURN c1;

END test_cursor;

table_colors is:

create or replace type table_colors as table of varchar2(20);

Но когда я проверяю, как он проходит, как blue, red, pink, white или 'blue', 'red', 'pink', 'white', всегда выдает одну и ту же ошибку.

ORA-06502: PL/SQL; numeric or value error: character string buffer too small

в этой строке row table_colors := table_colors(bigstring);

Что я здесь делаю неправильно?


sql oracle plsql sql-function sql-in
person Marllon Nasser    schedule 08.03.2016    source источник
comment
что такое table colors? Вы не можете сделать WHERE var in <string>, вам нужно WHERE var in ( value1, value2 ... valueN) ... вы, вероятно, ищете НАЙТИ_IN_SET()   -  person Juan Carlos Oropeza    schedule 08.03.2016
comment
Я забыл упомянуть его объявление, только что отредактировал вопрос, добавив create or replace type table_colors as table of varchar2(20);   -  person Marllon Nasser    schedule 08.03.2016
comment
Я думаю, будет лучше, если вы попытаетесь объяснить, что вы пытаетесь сделать. Вы не можете передавать переменные в таблицы и ожидать результата.   -  person Juan Carlos Oropeza    schedule 08.03.2016
comment
Я хочу получить результат выбора на основе clause in, который параметризован... select * from cars where colors in ('a', 'b', 'c'); Я хочу, чтобы это значение 'a', 'b', 'c' было параметризовано...   -  person Marllon Nasser    schedule 08.03.2016
comment
Тогда, как я сказал, вам нужно использовать FIND_IN_SET, но он очень похож на оракул stackoverflow.com/questions/28942608/   -  person Juan Carlos Oropeza    schedule 08.03.2016
comment
Но мне не нужна позиция, если она присутствует (аналогично подстроке)... Я хочу искать эти значения, как если бы я выполнял прямой запрос, используя where column in (VALUES)   -  person Marllon Nasser    schedule 08.03.2016

Ответы (2)


arrow_upward
1
arrow_downward

Проблема в том, что bigstring — это одно скалярное значение, которое может содержать запятые и одинарные кавычки, а не список значений. Вам нужно будет проанализировать строку, чтобы извлечь элементы данных. Если каждый из отдельных элементов в bigstring является допустимым идентификатором Oracle, вы можете использовать встроенную функцию dbms_utility.comma_to_table. Однако, если бы это была моя система, я бы чувствовал себя более комфортно со своей собственной функцией синтаксического анализа. Предполагая, что bigstring — это просто список, разделенный запятыми, я бы использовал версию функция str2tbl

create or replace function str2tbl( p_str in varchar2 ) 
  return table_colors
as
  l_str   long default p_str || ',';
  l_n        number;
  l_data    table_colors := table_colors();
begin
  loop
    l_n := instr( l_str, ',' );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
  end loop;
  return l_data;
end;

Теперь вы также можете реализовать str2tbl с помощью регулярных выражений в одном операторе SQL. Это может быть более эффективным. Однако я ожидаю, что синтаксический анализ строк далеко не в вашем списке проблем с производительностью, поэтому я бы предпочел придерживаться самой простой вещи, которая могла бы работать.

Тогда ваша процедура станет

CREATE OR REPLACE FUNCTION test_cursor (
   bigstring   IN   VARCHAR2
)
   RETURN sys_refcursor
IS
  row_test table_colors := str2tbl(bigstring);
  c1 sys_refcursor;
BEGIN
   OPEN c1 FOR
      select * from cars where color IN (select column_value
                                           from table(row_test));

   RETURN c1;

END test_cursor;
person Justin Cave    schedule 08.03.2016
comment
Это просто.....за гранью. Я вижу, что многие люди используют функции разделения для вставки каждого значения в global temporary table.. Я думаю, что ваше решение - лучший подход или лучшая практика... что вы, ребята, думаете? В любом случае... большое спасибо :) - person Marllon Nasser; 08.03.2016
comment
@MarllonNasser - В идеале вызывающая сторона должна передать коллекцию, а не (предположительно) пытаться создать строку, разделенную запятыми, которую ваша процедура затем должна проанализировать. За исключением этого, больше всего меня беспокоит память. Коллекции приходится хранить в достаточно дорогой памяти PGA. Если это вызывается несколько раз в минуту, наличие даже 32 КБ данных памяти для коллекции не имеет большого значения. Если этот код вызывается в сотнях одновременных сеансов, и вы начинаете передавать CLOB, это может быть проблемой. - person Justin Cave; 08.03.2016
comment
Вы можете значительно упростить его, используя: OPEN c1 FOR SELECT * FROM cars WHERE color MEMBER OF str2tbl(bigstring); - person MT0; 09.03.2016
comment
@MT0 - Совершенно верно. Я считаю, что это более новый синтаксис, и я не помню, в какой версии он был добавлен (исходный постер также не упомянул номер версии). - person Justin Cave; 09.03.2016
comment
MEMBER OF составляет 10 г или более поздней версии. - person MT0; 09.03.2016

arrow_upward
0
arrow_downward

Пожалуйста, покажите определение table_colors. Похоже, что table_colors(bigstring) возвращает значение, несовместимое с назначением table_colors.

В соответствии с хорошей практикой инициализация нетривиальных значений должна выполняться внутри begin ... end, а не в разделе определения. Это позволяет вам перехватывать ошибку внутри функции или процедуры, а не каскадировать ошибку наружу. Например, вместо:

IS
  row_test table_colors := table_colors(bigstring);
 c1 CURSOR;
BEGIN ...

Вы должны использовать

IS
  row_test table_colors;
  c1 CURSOR;
BEGIN
  row_test := row_test;
  ...
person Brian Leach    schedule 08.03.2016