Хранимая процедура Oracle с параметрами для предложения IN

Как создать хранимую процедуру Oracle, которая принимает переменное число значений параметров, используемых для подачи предложения IN?

Это то, чего я пытаюсь достичь. Я не знаю, как объявить в PLSQL для передачи списка переменных первичных ключей строк, которые я хочу обновить.

FUNCTION EXECUTE_UPDATE
  ( <parameter_list>
   value IN int)
  RETURN  int IS
BEGIN 
    [...other statements...]
    update table1 set col1 = col1 - value where id in (<parameter_list>) 

    RETURN SQL%ROWCOUNT ;
END;

Кроме того, я хотел бы вызвать эту процедуру из С#, поэтому она должна быть совместима с возможностями .NET.

Спасибо, Роберт

Ответ 1

Использование CSV, вероятно, является самым простым способом, предполагая, что вы можете быть на 100% уверены, что ваши элементы сами не будут содержать строки.

Альтернативный и, вероятно, более надежный способ сделать это - создать настраиваемый тип в виде таблицы строк. Предположим, что ваши строки никогда не были длиннее 100 символов, тогда вы могли бы:

CREATE TYPE string_table AS TABLE OF varchar2(100);

Затем вы можете передать переменную этого типа в свою хранимую процедуру и ссылаться на нее напрямую. В вашем случае что-то вроде этого:

FUNCTION EXECUTE_UPDATE(
    identifierList string_table,
    value int)
RETURN int
IS
BEGIN

    [...other stuff...]

    update table1 set col1 = col1 - value 
    where id in (select column_value from table(identifierList));

    RETURN SQL%ROWCOUNT;

END

Функция table() превращает ваш пользовательский тип в таблицу с одним столбцом "COLUMN_VALUE", который затем можно обрабатывать как любую другую таблицу (так что присоединяются или, в данном случае, подзаголовки).

Красота заключается в том, что Oracle создаст для вас конструктор, поэтому при вызове хранимой процедуры вы можете просто написать:

execute_update(string_table('foo','bar','baz'), 32);

Я предполагаю, что вы можете обрабатывать эту команду программно из С#.

В стороне, в моей компании, у нас есть несколько таких пользовательских типов, которые определены как стандартные для списков строк, парных, int и т.д. Мы также используем Oracle JPublisher, чтобы иметь возможность напрямую сопоставлять эти типы с соответствующими объектами Java. Я быстро осмотрелся, но я не видел прямых эквивалентов для С#. Просто подумал, что я упомянул об этом, если бы разработчики Java столкнулись с этим вопросом.

Ответ 2

Я нашел следующую статью Марка А. Уильямса, которая, как я думал, станет полезным дополнением к этой теме. В статье приведен хороший пример передачи массивов из С# в PL/SQL-процессы с использованием ассоциативных массивов (TYPE myType IS TABLE OF mytable.row% TYPE INDEX BY PLS_INTEGER):

Отличная статья Марка А. Уильямса

Ответ 3

Я думаю, что нет прямого способа создания процедур с переменным числом параметров. Однако есть некоторые, по крайней мере частичные решения проблемы, описанные здесь здесь.

Если есть некоторые типичные типы вызовов, может помочь перегрузка процедур.
Если есть верхний предел в числе параметров (и их тип также известен заранее), значения по умолчанию могут помочь.
Лучшим вариантом может быть использование переменных курсора, которые являются указателями на курсоры базы данных.

К сожалению, у меня нет опыта работы с средами .NET.

Ответ 4

На веб-сайте AskTom есть статья, в которой показано, как создать функцию для синтаксического анализа строки CSV и использовать ее в своем заявлении аналогично приведенному примеру SQL Server.

См. Asktom

Ответ 5

Почему бы просто не использовать длинный список параметров и не загружать значения в конструктор таблицы? Вот SQL/PSM для этого трюка

UPDATE Foobar
   SET x = 42
 WHERE Foobar.keycol
      IN (SELECT X.parm
            FROM (VALUES (in_p01), (in_p02), .., (in_p99)) X(parm)
           WHERE X.parm IS NOT NULL);

Ответ 6

Я не делал этого для Oracle, но с SQL Server вы можете использовать функцию для преобразования CSV-строки в таблицу, которая затем может использоваться в предложении IN. Нужно прямо переписать это для Oracle (я думаю!)

CREATE Function dbo.CsvToInt ( @Array varchar(1000)) 
returns @IntTable table 
    (IntValue nvarchar(100))
AS
begin

    declare @separator char(1)
    set @separator = ','

    declare @separator_position int 
    declare @array_value varchar(1000) 

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0 
    begin

      select @separator_position =  patindex('%,%' , @array)
      select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as nvarchar))

      select @array = stuff(@array, 1, @separator_position, '')
    end

    return
end

Затем вы можете передать строку CSV (например, "0001,0002,0003" ) и сделать что-то вроде

UPDATE table1 SET 
       col1 = col1 - value 
WHERE id in (SELECT * FROM csvToInt(@myParam))

Ответ 7

Почему это должна быть хранимая процедура? Вы можете создать подготовленное выражение программным способом.