PL/SQL-如何在IN子句中使用数组 [英] PL/SQL - How to use an array in an IN Clause

问题描述

我试图在IN子句中将过程的输入值数组用作游标的where子句的一部分.我知道以前已经有人问过这个问题，但是我还没有看到如何使我的语法正确编译.

I'm trying to use an array of input values to my procedure in an IN Clause as part of the where clause of a cursor. I know that this has been asked before, but I haven't seen how to make my syntax compile correctly.

在包装说明中，类型为

TYPE t_brth_dt IS TABLE OF sourceTable.stdt_brth_dt%TYPE INDEX BY PLS_INTEGER;

sourceTable.std_brth_dt是表中的日期列.

我的光标的简化版是在程序包主体中的-

Simplified version of my cursor is in the package body is -

 cursor DataCursor_Sort( p_brth_dt in t_brth_dt) is
    SELECT *
      FROM sourceTable 
     WHERE a.brth_dt IN (select column_value 
                           from table(p_brth_dt))

当我尝试对此进行编译时，出现以下错误.

When I try to compile this, I'm getting the following errors.

• [1] :(错误):PLS-00382:表达式的类型错误
• [2] :(错误):PL/SQL:ORA-22905:无法访问非嵌套表项中的行

• [1]:(Error): PLS-00382: expression is of wrong type
• [2]:(Error): PL/SQL: ORA-22905: cannot access rows from a non-nested table item

我知道这看起来与其他问题相似，但是我不明白语法错误是什么.

I know this looks similar to other questions, but I don't understand what the syntax error is.

推荐答案

要使用在查询的from子句中定义为嵌套表或关联数组的集合，您应该要么正确使用@Alex Poole指出出来，创建一个架构级别(SQL)类型或使用一种，您打算通过ODCIConst包-odcidatelist使用它，因为您打算使用日期列表.例如，您的光标定义可能如下所示:

In order to use collection defined as a nested table or an associative array in the from clause of a query you either should, as @Alex Poole correctly pointed out, create a schema level (SQL) type or use one, that is available to you trough ODCIConst package - odcidatelist as you intend to use a list of dates. For example, your cursor definition might look like this:

cursor DataCursor_Sort(p_brth_dt in sys.odcidatelist) is
  select *
    from sourceTable 
   where a.brth_dt IN (select column_value 
                         from table(p_brth_dt))

OR

cursor DataCursor_Sort(p_brth_dt in sys.odcidatelist) is
  select s.*
    from sourceTable      s
    join table(p_brth_dt) t
      on (s.brth_dt = t.column_value)

注意:执行日期比较时，应考虑日期的时间部分.如果只想比较日期部分，使用trunc()函数来摆脱时间部分可能会很有用.

Note: You should take into consideration the time part of a date when performing a date comparison. If you want to compare date part only it probably would be useful to get rid of time part by using trunc() function.

这篇关于PL/SQL-如何在IN子句中使用数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！