带有 IN 子句参数的 Oracle 存储过程 [英] Oracle stored procedure with parameters for IN clause

问题描述

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

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

这就是我想要达到的目标.我不知道如何在 PLSQL 中声明以传递我要更新的行的主键变量列表.

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.

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;

另外，我想从 C# 调用这个过程，所以它必须与 .NET 功能兼容.

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

谢谢，罗伯特

推荐答案

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

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

另一种可能更健壮的方法是创建一个自定义类型作为字符串表.假设您的字符串不超过 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:

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

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

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).

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

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

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

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

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

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

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 JPublisher to 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.

这篇关于带有 IN 子句参数的 Oracle 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！