带IN语句的Oracle参数? [英] Oracle Parameters with IN statement?

问题描述

有一个我需要修改的 c#.net 应用程序.目前的查询有效地做到了这一点:

Got a c#.net app which I need to modify. The query at the moment effectively does this:

select * from contract where contractnum = :ContractNum

(非常简化，只是为了表明我们使用了一个 = 和一个参数)

(very simplified, just to show we're using an = and one parameter)

该参数是从 C# 应用程序的 Settings.Settings 文件中读取的，其中包含一个字符串.我需要修改它以包含多个合同，所以我想我可以将 SQL 更改为:

That parameter is read in from the Settings.Settings file on the C# app and has one string in it. I need to modify it to include multiple contracts, so I figure I can change the SQL to:

select * from contract where contractnum in (:ContractNum)

但是无论我如何格式化参数中的字符串，都不会返回任何结果.

but that returns no results, no matter how I format the string in the parameter.

有没有办法让 oracle 用参数做一个输入?

Is there a way I can get oracle to do an IN with a parameter?

推荐答案

尚未找到支持评估包含逗号的单个字符串变量作为唯一 IN 子句的数据库.

Have yet to find a db that supports evaluating a single string variable containing commas to separate as the sole IN clause.

您的选择是对变量进行子字符串化，以便将逗号分隔的变量内容转换为行，然后您就可以加入该变量.或者使用动态SQL，它是在执行语句之前在sproc 中构造为字符串的SQL 语句.

Your options are to substring the variable so the comma delimited variable contents are turned into rows, so you can then join onto this. Or to use dynamic SQL, which is a SQL statement constructed as a string in a sproc before the statement is executed.

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