oracle PL/SQL - 如何在 IN 子句中使用数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18989249/
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
PL/SQL - How to use an array in an IN Clause
提问by photo_tom
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.
我试图在 IN 子句中对我的过程使用一组输入值，作为游标的 where 子句的一部分。我知道以前有人问过这个问题，但我还没有看到如何正确编译我的语法。
In the package specification, the type is
在包规范中，类型是
TYPE t_brth_dt IS TABLE OF sourceTable.stdt_brth_dt%TYPE INDEX BY PLS_INTEGER;
sourceTable.std_brth_dtis a date column in the table.
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]:(Error): PLS-00382: expression is of wrong type
- [2]:(Error): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
- [1]：（错误）：PLS-00382：表达式类型错误
- [2]：（错误）：PL/SQL：ORA-22905：无法访问来自非嵌套表项的行
I know this looks similar to other questions, but I don't understand what the syntax error is.
我知道这看起来与其他问题相似，但我不明白语法错误是什么。
回答by Nick Krasnov
In order to use collection defined as a nested table or an associative array in the fromclause 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 ODCIConstpackage - odcidatelistas you intend to use a list of dates. For example, your cursor definition might look like this:
为了在from查询的子句中使用定义为嵌套表或关联数组的集合，正如@Alex Poole 正确指出的那样，您应该创建一个模式级别 (SQL) 类型或使用一个，该类型可供您通过ODCIConst包 -odcidatelist因为您打算使用日期列表。例如，您的游标定义可能如下所示：
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)
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.
注意：在执行日期比较时，您应该考虑日期的时间部分。如果您只想比较日期部分，使用trunc()函数去掉时间部分可能会很有用。
回答by Michael O'Neill
It is possible to use a PL/SQL-defined nested table type (as opposed to a SQL-defined nested table type) indirectlyin an INclause of a SELECTstatement in a PL/SQL package. You must use a PIPELINEDfunction as an intermediary. It felt kind of clever to write, but I don't believe in its fundamental usefulness.
可以在 PL/SQL 包的语句的子句中间接使用 PL/SQL 定义的嵌套表类型（与 SQL 定义的嵌套表类型相反）。您必须使用函数作为中介。写起来感觉有点聪明，但我不相信它的基本用途。INSELECTPIPELINED
CREATE OR REPLACE PACKAGE so18989249 IS
TYPE date_plsql_nested_table_type IS TABLE OF DATE;
dates date_plsql_nested_table_type;
FUNCTION dates_pipelined RETURN date_plsql_nested_table_type PIPELINED;
PROCEDURE use_plsql_nested_table_type;
END so18989249;
/
CREATE OR REPLACE PACKAGE BODY so18989249 IS
FUNCTION dates_pipelined RETURN date_plsql_nested_table_type
PIPELINED IS
BEGIN
IF (dates.count > 0)
THEN
FOR i IN dates.first .. dates.last
LOOP
IF (dates.exists(i))
THEN
PIPE ROW(dates(i));
END IF;
END LOOP;
END IF;
END;
PROCEDURE use_plsql_nested_table_type IS
BEGIN
dates := NEW date_plsql_nested_table_type();
-- tweak these values as you see fit to produce the dbms_output results you want
dates.extend(5);
dates(1) := DATE '2013-12-25';
dates(2) := DATE '2013-01-01';
dates(3) := DATE '2013-07-01';
dates(4) := DATE '2013-09-03';
dates(5) := DATE '2008-11-18';
FOR i IN (SELECT o.owner,
o.object_name,
o.object_type,
to_char(o.last_ddl_time, 'YYYY-MM-DD') AS last_ddl
FROM all_objects o
WHERE trunc(o.last_ddl_time) IN
(SELECT column_value FROM TABLE(dates_pipelined))
--uses pipeline function which uses pl/sql-defined nested table
)
LOOP
dbms_output.put_line('"' || i.owner || '"."' || i.object_name || '" ("' || i.object_type || ') on ' || i.last_ddl);
END LOOP;
END;
END so18989249;
/
begin so18989249.use_plsql_nested_table_type; end;
/
回答by Alex Poole
The type has to be created at SQL level, not in a package. An SQL query doesn't know how to use any types defined in PL/SQL. So you'd have to do:
类型必须在 SQL 级别创建，而不是在包中。SQL 查询不知道如何使用 PL/SQL 中定义的任何类型。所以你必须这样做：
CREATE OR REPLACE TYPE t_brth_dt IS TABLE OF date;
/
... and remove the type from your package specification. (Or give them different names, at least, and they won't be interchangeable in use). Because it's at SQL level, you also can't use sourceTable.stdt_brth_dt%TYPEin the declaration, unfortunately.
...并从您的包装规范中删除类型。（或者至少给它们不同的名称，并且它们在使用中不能互换）。sourceTable.stdt_brth_dt%TYPE不幸的是，因为它是在 SQL 级别，所以您也不能在声明中使用。
