SELECT IN中使用Oracle参数时出现问题
|
将一串数字插入sql查询时出现问题
SELECT *
FROM tablename a
WHERE a.flokkurid IN (3857,3858,3863,3285)
ORDER BY sjodategund, rodun
SELECT *
FROM tablename a
WHERE a.flokkurid IN (:strManyNumbers)
ORDER BY sjodategund, rodun
using (OracleCommand sel = new OracleCommand(SQL, connectionstring)) {
sel.Parameters.Add(\":strManyNumbers\",
OracleDbType.Varchar2,
\"Client\",
ParameterDirection.Input);
}
没有找到相关结果
已邀请:
3 个回复
掸牛浓疗
为查询创建参数时，将其声明为关联的PL / SQL数组：然后分配一些值：param1 = new int[] { 3857, 3858, 3863, 3285 };古擅坛犯
靛取糕奖穿
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\'));