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);
}
因此,如果我运行此查询,我得到:   ORA-01722:编号无效 但是如果我只插入一个数字,即\“ 3857 \”,它将返回带有数据的查询OK。     
已邀请:
        要传递一组值,您需要使用Oracle的表或数组类型。 首先,您创建一个表格类型(例如NUMBER):
CREATE TYPE number_table AS TABLE OF NUMBER; 
为查询创建参数时,将其声明为关联的PL / SQL数组:
OracleParameter param1 = new OracleParameter(); 
param1.OracleDbType = OracleDbType.Int32; 
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray; 
然后分配一些值:
param1 = new int[] { 3857, 3858, 3863, 3285 }; 
而且您的查询需要强制转换:
SELECT * FROM tablename a 
where a.flokkurid in (TABLE(CAST(:manyNumbers AS number_table)))
order by sjodategund, rodun 
    
        这不是参数的工作方式。您不能指定\“ set \”作为参数,必须在字符串中组合SQL查询。并注意SQL注入。 此外,您可能想看看这些: 是否可以使用查询参数来填充IN关键字 参数化SQL IN子句 更新资料 对于Oracle,Codo的答案有一个非常有趣的方法。我现在无法对其进行测试,但是它看起来很有希望。 这里有一个非常相似的问题:@DCookie指出了OracleParameter和IN子句。它不是精确的重复项,因为当数组中项目的类型更改时,SQL强制转换也将更改。     
        
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\'));
希望这对您有用。 我也看到了其他一些代码也可以做到这一点。 就其价值而言,在Microsoft SQL Server中更容易做到这一点 哈夫·萨瑟(Harv Sather)     

要回复问题请先登录注册