在 SELECT IN 中使用 Oracle 参数的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6155146/
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
problem using Oracle parameters in SELECT IN
提问by aghaux
I have a problem when inserting a string of numbers into sql query
在sql查询中插入一串数字时出现问题
SELECT *
FROM tablename a
WHERE a.flokkurid IN (3857,3858,3863,3285)
ORDER BY sjodategund, rodun
...or:
...或者：
SELECT *
FROM tablename a
WHERE a.flokkurid IN (:strManyNumbers)
ORDER BY sjodategund, rodun
...with this code:
...使用此代码：
using (OracleCommand sel = new OracleCommand(SQL, connectionstring)) {
sel.Parameters.Add(":strManyNumbers",
OracleDbType.Varchar2,
"Client",
ParameterDirection.Input);
}
So if i run this query i get:
所以如果我运行这个查询，我会得到：
ORA-01722: invalid number
ORA-01722: 无效号码
but if i insert just one number, i.e. "3857" it will return query OK with data.
但如果我只插入一个数字，即“3857”，它将返回查询 OK 数据。
回答by Codo
To pass a set of values, you need to use Oracle's table or array types.
要传递一组值，您需要使用 Oracle 的表或数组类型。
At first, you create a table type (e.g. for NUMBER):
首先，您创建一个表类型（例如 NUMBER）：
CREATE TYPE number_table AS TABLE OF NUMBER;
When you create the parameter for the query, declare it as an associative PL/SQL array:
为查询创建参数时，将其声明为关联 PL/SQL 数组：
OracleParameter param1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Int32;
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Then assign some values:
然后分配一些值：
param1 = new int[] { 3857, 3858, 3863, 3285 };
And your query needs a cast:
并且您的查询需要强制转换：
SELECT * FROM tablename a
where a.flokkurid in (TABLE(CAST(:manyNumbers AS number_table)))
order by sjodategund, rodun
回答by Adriano Carneiro
That's nothow parameters work. You cannot specify a "set" as a parameter, you have to assemble the SQL query in the string. And watch out for SQL Injection.
这不是参数的工作方式。您不能指定“set”作为参数，您必须在字符串中组装 SQL 查询。并注意SQL 注入。
In addition, you might want to take a look at these:
此外，您可能想看看这些：
Update
更新
Codo's answer has a very interesting approach for Oracle. I cannot test it right now, but it sure looks promising.
Codo 的回答对 Oracle 有一个非常有趣的方法。我现在无法测试它，但它确实看起来很有希望。
There's a very similar question here: OracleParameter and IN Clause, as pointed out by @DCookie. It's not an exact duplicate because when the type of the item in array changes, the SQL cast also changes.
这里有一个非常相似的问题：OracleParameter and IN Clause，正如@DCookie 所指出的。它不是完全重复的，因为当数组中项目的类型发生变化时，SQL 类型转换也会发生变化。
回答by Harv
CREATE OR REPLACE PACKAGE IH_FORMS_TRIAL.STRING_TO_TABLE IS
type grs_list_row is record ( varchar_list varchar2(512), int_list number, date_list date );
type grs_list_tab is table of grs_list_row;
FUNCTION ft_string_to_table(av2_list varchar2, av2_delimiter varchar2 := ',', av2_list_type varchar2 := 'V', av2_date_mask varchar2 := 'DD-MON-YY') return grs_list_tab PIPELINED;
END STRING_TO_TABLE;
/
CREATE OR REPLACE package body IH_FORMS_TRIAL.STRING_TO_TABLE IS
FUNCTION ft_string_to_table(av2_list varchar2, av2_delimiter varchar2 := ',', av2_list_type varchar2 := 'V', av2_date_mask varchar2 := 'DD-MON-YY') return grs_list_tab PIPELINED
IS
/**********************************************************************************************************
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Table_Functions_Cursor_Expressions/Pipelined_Table_Functions.htm
http://www.akadia.com/services/ora_pipe_functions.html
PIPLELINED TABLE FUNCTION
PURPOSE -
------------------
This function takes a string as input and returns a table.
The table that is returned will normally be used in an SQL "IN" clause
=====================================================================================
ARGUMENTS
------------------
av2_list - this is a comma delimited list of values that will be converted into single rows of a table
av2_delimiter - this is a character value and should only be one character long.
It is the delimiter that is between valid values in the av2_list
The default value is a comma ','
av2_list_type - This function can return various types of lists or tables
For this parameter
A value of 'V' will return a table of varchar2
A value of 'I' will return a table of integers
A value of 'D' will return a table of dates
av2_date_mask - This is required if the value of av2_list_type is 'D' for date
The date mask will be used by the Oracle built-in TO_DATE function
A default value of 'DD-MON-YY' is used
=====================================================================================
RETURNS
Table of values for input to an IN portion of a WHERE clause
=====================================================================================
EXAMPLES
SELECT * FROM <TABLE> WHERE <VARCHAR_COLUMN> IN (select varchar_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454')));
SELECT * FROM <TABLE> WHERE <INTEGER_COLUMN> IN (select int_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('1,2,3,4,5,6,7,8,9', ',', 'I')));
SELECT * FROM <TABLE> WHERE <DATE_COLUMN> IN (select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MM-DD')));
=====================================================================================
TEST CASES
select varchar_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454', ',', 'V'));
select int_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('1,2,3,4,5,6,7,8,9', ',', 'I'));
select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('04-mar-10, 05-mar-10', ',', 'D'));
select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MM-DD'));
test using and invalid list type
Use Y instead of V, I or D
Should produce an error
select varchar_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454', ',', 'Y'));
test using a date format that does not match the date format passed
Should produce an error
select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MON-DD'));
select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-MAR-04, 2010-MAR-05', ',', 'D', 'YYYY-MM-DD'));
----------
=====================================================================================
REVISION HISTORY
Called by misc systems
----------------------------------------------------------------
Modification History
Date User Description
------------ -------------- ----------------------------------
2006-03-03 HarvS Initial Release
2010-04-09 HarvS Translated from SQL Server to ORACLE
Combined functions that returned lists of varchar, integer, and date into one function with optional parameters
REVISION HISTORY
----------------
Build Version - 11.02.01.001
Build Date - 08-June-2010
Modified By - has986
Description - Created
******************************************************************************/
--local variable of type grs_list_row
lrs_row grs_list_row;
E_INVALID_LIST_TYPE EXCEPTION;
li_delimiter_position int;
li_previous_delimiter_position int;
lv2_value varchar2(512);
BEGIN
if av2_list_type not in ('V', 'I', 'D') THEN
raise E_INVALID_LIST_TYPE;
end if;
li_delimiter_position := 1;
li_previous_delimiter_position := 1;
li_delimiter_position := INSTR(av2_list, av2_delimiter, li_delimiter_position);
while li_delimiter_position > 0 loop
lv2_value := substr(av2_list, li_previous_delimiter_position, (li_delimiter_position - li_previous_delimiter_position));
--Trim the value
lv2_value := RTRIM(LTRIM(lv2_value));
if length(lv2_value) > 0 THEN
if av2_list_type = 'V' then --varchar
lrs_row.varchar_list := lv2_value;
elsif av2_list_type = 'I' then --integer
lrs_row.int_list := to_number(lv2_value);
elsif av2_list_type = 'D' then --date
lrs_row.date_list := to_date(lv2_value, av2_date_mask);
end if;
pipe row ( lrs_row );
END IF;
--set the new delimiter positions
li_previous_delimiter_position := li_delimiter_position + 1;
li_delimiter_position := INSTR(av2_list, av2_delimiter, li_delimiter_position + 1);
END loop;
--Get the last value
lv2_value := SUBSTR(av2_list, li_previous_delimiter_position, length(av2_list));
--Trim the value
lv2_value := RTRIM(LTRIM(lv2_value));
if length(lv2_value) > 0 THEN
--Insert the value into the in memory table
if av2_list_type = 'V' then --varchar
lrs_row.varchar_list := lv2_value;
elsif av2_list_type = 'I' then --integer
lrs_row.int_list := to_number(lv2_value);
elsif av2_list_type = 'D' then --date
lrs_row.date_list := to_date(lv2_value, av2_date_mask);
end if;
pipe row ( lrs_row );
END IF;
return;
EXCEPTION
WHEN E_INVALID_LIST_TYPE then
/*
The developer should be notified of this error during the development phase.
*/
raise_application_error (-20001, av2_list_type || ' is not a valid type. Valid types are (V, I, or D)' );
WHEN OTHERS THEN
RAISE;
END ft_string_to_table;
end string_to_table;
/
select * FROM table( string_to_table.ft_string_to_table('1, 2, 3', ',', 'I'));
select * FROM table( string_to_table.ft_string_to_table('fred, wilma, betty, barney', ',', 'V'));
select * FROM table( string_to_table.ft_string_to_table('2011-5-1, 1950-1-1, 1960-1-2, 2023-12-1', ',', 'D', 'yyyy-mm-dd'));
Hope this works for you. I have seen some other code that does this as well. For what its worth, this is much easier to do in Microsoft SQL Server
希望这对你有用。我也看到过其他一些代码也可以做到这一点。就其价值而言，这在 Microsoft SQL Server 中更容易做到
Harv Sather
哈夫·萨瑟
