oracle sql查询中的参数：SSRS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/547235/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
parameter in sql query :SSRS
提问by Davos
I am using oracleclientprovider. I was wondering how do I use a parameter in the query.
我正在使用oracleclient提供商。我想知道如何在查询中使用参数。
select * from table A where A.a in ( parameter).
The parameter should be a multivalueparameter.
参数应该是一个multivalue参数。
how do I create a data set?
如何创建数据集？
回答by James
Simple. Add the parameter to the report and make sure to check it off as multi-valued. Then in the data tab and go in and edit the query click the "..." button to edit the dataset. Under the parameters tab create a mapping parameter so it looks something like this (obviously you will have different names for your parameters):
简单的。将参数添加到报告中并确保将其检查为多值。然后在数据选项卡中并进入并编辑查询，单击“...”按钮以编辑数据集。在参数选项卡下创建一个映射参数，它看起来像这样（显然你的参数会有不同的名称）：
@ids | =Parameters!ContractorIDS.Value
Then in the query tab use the coorelated sub-query like your example above. I have done this many times with SQL server and there is no reason it should not work with Oracle since SSRS is going to build an ANSI compliant SQL statement which it will pass to Oracle.
然后在查询选项卡中使用 coorelated 子查询，就像上面的例子一样。我已经用 SQL 服务器多次这样做了，没有理由它不应该与 Oracle 一起使用，因为 SSRS 将构建一个符合 ANSI 标准的 SQL 语句，它将传递给 Oracle。
where A.myfield in (@ids)
回答by Daniel Emge
You can't have a variable in list in oracle directly. You can however, break apart a comma seperated list into rows that can be used in your subquery. The string txt can be replaced by any number of values seperated by a comma.
您不能直接在 oracle 中的列表中有变量。但是，您可以将逗号分隔的列表分成可在子查询中使用的行。字符串 txt 可以替换为任意数量的以逗号分隔的值。
select * from a where a.a in (
SELECT regexp_substr(txt,'[^,]+',1,level)
FROM (SELECT 'hello,world,hi,there' txt -- replace with parameter
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (txt, '[^,]'))+1
)
The query works by first counting the number of commas that are in the text string. It does this by using a reqular expression to remove all non commas and then counts the length of the remainder.
该查询首先计算文本字符串中的逗号数。它通过使用正则表达式删除所有非逗号，然后计算余数的长度来实现这一点。
It then uses an Oracle "trick" to return that number + 1 number of rows from the dual table. It then uses the regexp_substr function to pull out each occurence.
然后它使用 Oracle 的“技巧”从双表中返回该数字 + 1 个行数。然后它使用 regexp_substr 函数来提取每个出现。
回答by Davos
Firstly in SSRS with an Oracle OLEDB connection you need to use the colon, not the @ symbol e.g. :parameter not @parameter but then you aren't able to do this as a multi-valued parameter, it only accepts single values. Worse, if you are using an ODBC connection you have to use the question mark by itself e.g. ? not @parameter and then the ordering of parameters becomes important, and they also cannot be multi-valued. The only ways you are left with is using an expression to construct a query (join() function for the param) or calling a stored proc.
首先在具有 Oracle OLEDB 连接的 SSRS 中，您需要使用冒号，而不是 @ 符号，例如 :parameter 而不是 @parameter 但您无法将其作为多值参数执行，它只接受单个值。更糟糕的是，如果您使用的是 ODBC 连接，则必须单独使用问号，例如？不是@parameter，那么参数的顺序就变得很重要，而且它们也不能是多值的。剩下的唯一方法是使用表达式来构造查询（参数的 join() 函数）或调用存储过程。
The stored proc option is best because the SSRS can handle the parameters for stored procs to both SQL Server and Oracle very cleanly, but if that is not an option you can use this expression:
存储过程选项是最好的，因为 SSRS 可以非常干净地处理 SQL Server 和 Oracle 的存储过程参数，但如果这不是一个选项，您可以使用以下表达式：
="select column1, column2, a from table A where A.a in (" + Join(Parameters!parameter.Value,", ") + ")"
Or if the parameter values are strings which need apostrophes around them:
或者，如果参数值是需要在它们周围加上撇号的字符串：
="select column1, column2, a from table A where A.a in ('" + Join(Parameters!parameter.Value,"', '") + "')"
When you right-click on the dataset, you can select "dataset properties" and then use the fx button to edit the query as an expression, rather than using the query designer which won't let you edit it as an expression.
当您右键单击数据集时，您可以选择“数据集属性”，然后使用 fx 按钮将查询作为表达式进行编辑，而不是使用不允许您将其作为表达式进行编辑的查询设计器。
This expression method is limited to a maximum limit of about 1000 values but if you have that many this is the wrong way to do it anyway, you'd rather join to a table.
此表达式方法的最大限制为大约 1000 个值，但如果您有这么多值，无论如何这是错误的方法，您宁愿加入一个表。
回答by Frederik Gheysels
I don't think you can use a parameter in such a situation. (Unless oracle and the language you're using supports array-type parameters ? )
我认为您不能在这种情况下使用参数。（除非 oracle 和您使用的语言支持数组类型参数？）
回答by Victor Grimaldo
The parameters in oracle are defined as ":parametername", so in your query you should use something like:
oracle 中的参数被定义为“:parametername”，所以在你的查询中你应该使用类似的东西：
select * from table A where value in (:parametername)
Add the parameter to the paramaters folders in the report and mark the checkbox "Allow multiple values".
将参数添加到报告中的参数文件夹并选中“允许多个值”复选框。
回答by Chandra Raju
As Victor Grimaldo mentioned… below worked for me very fine. As soon as I use the :parameterin my SQL query in SSRS dataset1.. it asked me to enter the values for these parameters, for which I choose already created SSRS parameters.
正如维克多格里马尔多提到的......下面对我来说非常好。只要:parameter我在 SSRS dataset1 中的 SQL 查询中使用 .. 它就会要求我输入这些参数的值，为此我选择了已创建的 SSRS 参数。
SELECT * FROM table a WHERE VALUE IN (**:parametername**)
SELECT * FROM table a WHERE VALUE IN (**:parametername**)
Thanks Victor.
谢谢维克多。
