带有 IN 子句参数的 Oracle 存储过程

Oracle stored procedure with parameters for IN clause

提问by Robert Mircea

How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause?

如何创建一个 Oracle 存储过程，它接受用于提供 IN 子句的可变数量的参数值？

This is what I am trying to achieve. I do not know how to declare in PLSQL for passing a variable list of primary keys of the rows I want to update.

这就是我正在努力实现的目标。我不知道如何在 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;

Also, I would like to call this procedure from C#, so it must be compatible with .NET capabilities.

此外，我想从 C# 调用此过程，因此它必须与 .NET 功能兼容。

Thanks, Robert

谢谢，罗伯特

回答by Ashley Mercer

Using CSV is probably the simplest way, assuming you can be 100% certain that your elements won't themselves contain strings.

使用 CSV 可能是最简单的方法，假设您可以 100% 确定您的元素本身不包含字符串。

An alternative, and probably more robust, way of doing this is to create a custom type as a table of strings. Supposing your strings were never longer than 100 characters, then you could have:

另一种可能更健壮的方法是创建一个自定义类型作为字符串表。假设您的字符串永远不会超过 100 个字符，那么您可以：

CREATE TYPE string_table AS TABLE OF varchar2(100);

You can then pass a variable of this type into your stored procedure and reference it directly. In your case, something like this:

然后，您可以将这种类型的变量传递到您的存储过程中并直接引用它。在你的情况下，是这样的：

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

The table()function turns your custom type into a table with a single column "COLUMN_VALUE", which you can then treat like any other table (so do joins or, in this case, subselects).

该table()函数将您的自定义类型转换为具有单列“COLUMN_VALUE”的表，然后您可以像对待任何其他表一样对待它（联接或在这种情况下，子选择也是如此）。

The beauty of this is that Oracle will create a constructor for you, so when calling your stored procedure you can simply write:

这样做的好处是 Oracle 将为您创建一个构造函数，因此在调用存储过程时，您可以简单地编写：

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

I'm assuming that you can handle building up this command programatically from C#.

我假设您可以从 C# 以编程方式构建此命令。

As an aside, at my company we have a number of these custom types defined as standard for lists of strings, doubles, ints and so on. We also make use of Oracle JPublisherto be able to map directly from these types into corresponding Java objects. I had a quick look around but I couldn't see any direct equivalents for C#. Just thought I'd mention it in case Java devs come across this question.

顺便说一句，在我的公司，我们将许多自定义类型定义为字符串、双精度、整数等列表的标准。我们还利用Oracle JPublisher将这些类型直接映射到相应的 Java 对象。我快速浏览了一下，但看不到 C# 的任何直接等价物。只是想我会提到它以防 Java 开发人员遇到这个问题。

回答by bowthy

I found the following article by Mark A. Williams which I thought would be a useful addition to this thread. The article gives a good example of passing arrays from C# to PL/SQL procs using associative arrays (TYPE myType IS TABLE OF mytable.row%TYPE INDEX BY PLS_INTEGER):

我找到了 Mark A. Williams 的以下文章，我认为这将是对本主题有用的补充。这篇文章给出了使用关联数组（TYPE myType IS TABLE OF mytable.row%TYPE INDEX BY PLS_INTEGER）将数组从 C# 传递到 PL/SQL procs 的一个很好的例子：

Great article by Mark A. Williams

马克 A. 威廉姆斯的精彩文章

回答by rics

I think there is no direct way to create procedures with variable number of parameters. However there are some, at least partial solutions to the problem, described here.

我认为没有直接的方法来创建具有可变数量参数的过程。但是，这里描述了该问题的一些解决方案，至少是部分解决方案。

1. If there are some typical call types procedure overloading may help.
2. If there is an upper limit in the number of parameters (and their type is also known in advance), default values of parameters may help.
3. The best option is maybe the usage of cursor variables what are pointers to database cursors.

1. 如果有一些典型的调用类型，过程重载可能会有所帮助。
2. 如果参数数量有上限（并且它们的类型也是预先知道的），参数的默认值可能会有所帮助。
3. 最好的选择可能是使用游标变量，什么是指向数据库游标的指针。

Unfortunately I have no experience with .NET environments.

不幸的是，我没有 .NET 环境的经验。

回答by rics

Why not just use a long parameter list and load the values into a table constructor? Here is the SQL/PSM for this trick

为什么不使用长参数列表并将值加载到表构造函数中？这是这个技巧的 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);

回答by RB.

I've not done it for Oracle, but with SQL Server you can use a function to convert a CSV string into a table, which can then be used in an IN clause. It should be straight-forward to re-write this for Oracle (I think!)

我没有为 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

Then you can pass in a CSV string (e.g. '0001,0002,0003') and do something like

然后你可以传入一个 CSV 字符串（例如'0001,0002,0003'）并执行类似的操作

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

回答by flukus

Why does it have to be a stored procedure? You could build up a prepared statement programmatically.

为什么它必须是存储过程？您可以以编程方式构建准备好的语句。

回答by Gazmo

There is an article on the AskTom web site that shows how to create a function to parse the CSV string and use this in your statement in a similar way to the SQL Server example given.

AskTom 网站上有一篇文章展示了如何创建一个函数来解析 CSV 字符串，并以类似于给定的 SQL Server 示例的方式在您的语句中使用它。

See Asktom

见Asktom