在 Oracle 嵌套表中查找特定的 varchar
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6348559/
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
Find specific varchar in Oracle Nested Table
提问by Andy
I'm new to PL-SQL, and struggling to find clear documentation of operations are nested tables. Please correct any misused terminology etc.
我是 PL-SQL 的新手，正在努力寻找嵌套表的清晰操作文档。请更正任何误用的术语等。
I have a nested table type that I use as a parameters for a stored procedure.
我有一个嵌套表类型，用作存储过程的参数。
CREATE OR REPLACE TYPE "STRARRAY" AS TABLE OF VARCHAR2 (255)
In my stored procedure, the table is initialized and populated. Say I have a VARCHAR2 variable, and I want to know true or false if that varchar exists in the nested table.
在我的存储过程中，表被初始化并填充。假设我有一个 VARCHAR2 变量，我想知道该 varchar 是否存在于嵌套表中。
I tried
我试过
strarray.exists('somevarchar')
but I get an ORA-6502
但我得到了一个 ORA-6502
Is there an easier way to do that other than iterating?
除了迭代之外，有没有更简单的方法来做到这一点？
FOR i IN strarray.FIRST..strarray.LAST
LOOP
IF strarray(i) = value THEN
return 1;--found
END IF;
END LOOP;
回答by zep
For single value check I prefer the "member" operator.
对于单值检查，我更喜欢“成员”运算符。
zep@dev> declare
2 enames strarray;
3 wordToFind varchar2(255) := 'King';
4 begin
5 select emp.last_name bulk collect
6 into enames
7 from employees emp;
8 if wordToFind member of enames then
9 dbms_output.put_line('Found King');
10 end if;
11 end;
12 /
Found King
PL/SQL procedure successfully completed
zep@dev>
回答by Justin Cave
You can use the MULTISET INTERSECT operator to determine whether the string you're interested in exists in the collection. For example
您可以使用 MULTISET INTERSECT 运算符来确定您感兴趣的字符串是否存在于集合中。例如
declare
l_enames strarray;
l_interesting_enames strarray := new strarray( 'KING' );
begin
select ename
bulk collect into l_enames
from emp;
if( l_interesting_enames = l_interesting_enames MULTISET INTERSECT l_enames )
then
dbms_output.put_line( 'Found King' );
end if;
end;
will print out "Found King" if the string "KING" is an element of the l_enames collection.
如果字符串“KING”是 l_enames 集合的一个元素，则将打印出“Found King”。
回答by Kirill Leontev
You should pass an array index, not an array value to an existsin case you'd like to determine whether this element exists in collection. Nested tables are indexed by integers, so there's no way to reference them by strings.
您应该将数组索引而不是数组值传递给 an exists，以防您想确定此元素是否存在于集合中。嵌套表由整数索引，因此无法通过字符串引用它们。
However, you might want to look at associative arrays instead of collections in case you wish to reference your array element by string index. This will look like this:
但是，如果您希望通过字符串索引引用数组元素，您可能需要查看关联数组而不是集合。这将如下所示：
DECLARE
TYPE assocArray IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
myArray assocArray;
BEGIN
myArray('foo') := 'bar';
IF myArray.exists('baz') THEN
dbms_output.put_line(myArray('baz'));
ELSIF myArray.exists('foo') THEN
dbms_output.put_line(myArray('foo'));
END IF;
END;
Basically, if your array values are distinct, you can create paired arrays referencing each other, like,
arr('b') := 'a'; arr('a') := 'b';
基本上，如果您的数组值不同，您可以创建相互引用的成对数组，例如，
arr('b') := 'a'; arr('a') := 'b';
This technique might help you to easily look up any element and its index.
这种技术可以帮助您轻松查找任何元素及其索引。
回答by Dave Costa
When a nested table is declared as a schema-level type, as you have done, it can be used in any SQL query as a table. So you can write a simple function like so:
当嵌套表被声明为模式级类型时，正如您所做的那样，它可以在任何 SQL 查询中用作表。所以你可以像这样写一个简单的函数：
CREATE OR REPLACE FUNCTION exists_in( str VARCHAR2, tab stararray)
RETURN BOOLEAN
AS
c INTEGER;
BEGIN
SELECT COUNT(*)
INTO c
FROM TABLE(CAST(tab AS strarray))
WHERE column_value = str;
RETURN (c > 0);
END exists_in;
