提问人：Robert Mircea 提问时间：10/28/2008 最后编辑：MozenRathRobert Mircea 更新时间：9/28/2022 访问量：54300
带有 IN 子句参数的 Oracle 存储过程
Oracle stored procedure with parameters for IN clause
问：
如何创建一个 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;
另外，我想从 C# 调用此过程，因此它必须与 .NET 功能兼容。
谢谢 罗伯特
答：
-1赞
RB.
10/28/2008
#1
我没有为 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))
评论
0赞
tuinstoel
1/5/2009
PL/SQL 也可以执行类似操作，但将集合作为 csv 字符串传递给过程会更快。csv 字符串需要首先在 PL/SQL 中进行标记化。
-1赞
Gazmo
10/28/2008
#2
AskTom 网站上有一篇文章演示了如何创建一个函数来解析 CSV 字符串，并以与给定的 SQL Server 示例类似的方式在语句中使用它。
请参见 Asktom
1赞
rics
10/28/2008
#3
我认为没有直接的方法可以创建具有可变数量参数的程序。 但是，这里描述了一些（至少是部分）问题的解决方案。
- 如果存在一些典型的调用类型，过程重载可能会有所帮助。
- 如果参数的数量有上限（并且它们的类型也是预先知道的），则参数的默认值可能会有所帮助。
- 最好的选择可能是使用游标变量，即指向数据库游标的指针。
不幸的是，我没有使用 .NET 环境的经验。
评论
0赞
ObiWanKenobi
11/19/2010
“没有直接的方法可以创建具有可变数量参数的程序”......是的，使用 CREATE TYPE xxx 作为 yyy 的表，然后使用该类型作为参数类型。
0赞
rics
11/19/2010
不，这不是一个直接的解决方案。无论如何，您的方法是可追溯到 2008 年的公认答案。
0赞
Fering
1/28/2019
@rics你答案中的链接是死的
29赞
Ashley Mercer
10/28/2008
#4
使用 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
该函数将自定义类型转换为具有单列“COLUMN_VALUE”的表，然后您可以像对待任何其他表一样处理该表（联接也是如此，或者在本例中为子选择）。table()
这样做的好处是 Oracle 会为你创建一个构造函数，所以在调用你的存储过程时，你可以简单地写：
execute_update(string_table('foo','bar','baz'), 32);
我假设您可以处理从 C# 以编程方式构建此命令。
顺便说一句，在我的公司，我们有许多这样的自定义类型被定义为字符串、双精度、整数等列表的标准。我们还利用 Oracle JPublishher 能够直接从这些类型映射到相应的 Java 对象。我快速浏览了一下四周，但我看不到 C# 的任何直接等价物。只是想我会提到它，以防 Java 开发人员遇到这个问题。
评论
2赞
sarsnake
12/16/2010
快速说明 - CSV 本身不起作用，因为 Oracle 会将其视为一个字符串。
0赞
Jeromy French
1/14/2015
那不应该是吗？... from string_table(identifierList)); ...
0赞
Joe Celko
8/6/2009
#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);
1赞
bowthy
12/11/2009
#6
我找到了 Mark A. Williams 的以下文章，我认为这将是对这个线程的有用补充。本文给出了一个很好的示例，如何使用关联数组将数组从 C# 传递到 PL/SQL 过程（TYPE myType IS TABLE OF mytable.row%TYPE INDEX BY PLS_INTEGER）：
0赞
Andrea Coppo
9/28/2022
#7
只需将变量拆分为逗号分隔字符串即可：
declare
schema_n VARCHAR2 (30);
schema_size number;
schemas VARCHAR2 (30);
begin
schema_n := 'USER_PROD01,USER_PROD01' ;
select sum(bytes)/1024/1024 INTO schema_size FROM dba_segments where owner in (
with rws as (
select ''||schema_n||'' str from dual
)
select regexp_substr (
str,
'[^,]+',
1,
level
) value
from rws
connect by level <=
length ( str ) - length ( replace ( str, ',' ) ) + 1
) ;
DBMS_OUTPUT.PUT_LINE ('PAY ATTENTION : PROD '||schemas||' user size is in total of : '||schema_size||' MBs .');
end;
/
注意：PROD用户大小总计为：18.8125 MBs。
PL/SQL 过程已成功完成。
评论