检查 Oracle 中是否存在集合元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1347788/
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
Checking if a collection element exists in Oracle
提问by Micha? Ziober
I create a simple type:
我创建了一个简单的类型：
create or replace TYPE SIMPLE_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20));
Simple test:
简单测试：
DECLARE
TYPE ObjectList IS TABLE OF SIMPLE_TYPE;
tmp SIMPLE_TYPE := SIMPLE_TYPE(1, 'a');
o ObjectList := new ObjectList(SIMPLE_TYPE(2, 'a'), SIMPLE_TYPE(3, 'a'));
BEGIN
IF tmp.EXISTS(tmp) THEN
dbms_output.put_line('OK, exists.');
END IF;
END;
I get an exception: PLS-00302: component 'EXISTS' must be declared
我得到一个例外：PLS-00302：必须声明组件“EXISTS”
But this example work:
但是这个例子有效：
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
BEGIN
n.DELETE(2);
IF n.EXISTS(1) THEN
dbms_output.put_line('OK, element #1 exists.');
END IF;
IF n.EXISTS(3) = FALSE THEN
dbms_output.put_line('OK, element #2 has been deleted.');
END IF;
IF n.EXISTS(99) = FALSE THEN
dbms_output.put_line('OK, element #99 does not exist at all.');
END IF;
END;
Is it possible to implement EXISTS method in SIMPLE_TYPE type?
是否可以在 SIMPLE_TYPE 类型中实现 EXISTS 方法？
采纳答案by Quassnoi
tmp SIMPLE_TYPEE := SIMPLE_TYPE(1, 'a');
…
IF tmp.EXISTS(tmp) THEN
You declare tmpas SIMPLE_TYPE, not ObjectList.
您声明tmp为SIMPLE_TYPE，而不是ObjectList。
SIMPLE_TYPEis scalar type, not a collection.
SIMPLE_TYPE是标量类型，不是集合。
Probably you wanted to check o.EXISTSinstead (which is an ObjectList)?
可能您想改为检查o.EXISTS（哪个是ObjectList）？
Update:
更新：
EXISTSwhen applied to a collection takes an integer index as an argument and checks if the element with this index exists (not its value).
EXISTS当应用于集合时，将整数索引作为参数并检查具有此索引的元素是否存在（而不是其值）。
To check that SIMPLE_TYPE(1, 'a')exists in your table, you should so the following:
要检查SIMPLE_TYPE(1, 'a')表中是否存在，您应该执行以下操作：
Create ObjectListin a dictionary:
ObjectList在字典中创建：
CREATE TYPE ObjectList IS TABLE OF SIMPLE_TYPE;
Issue the SELECTquery:
发出SELECT查询：
DECLARE
tmp SIMPLE_TYPE := SIMPLE_TYPE(1, 'a');
o ObjectList := new ObjectList(SIMPLE_TYPE(2, 'a'), SIMPLE_TYPE(3, 'a'));
myid INT;
BEGIN
SELECT 1
INTO myid
FROM TABLE(o) q
WHERE SIMPLE_TYPE(q.id, q.name) = tmp
AND rownum = 1;
IF (myid = 1) THEN
dbms_output.put_line('OK, exists.');
END IF;
END;
回答by APC
As the documentation states, EXISTS() tests for the existence of a numbered entry in a collection. That is, array.exists(3)asserts that the third element of arrayis populated.
正如文档所述，EXISTS() 测试集合中是否存在编号条目。也就是说，array.exists(3)断言 的第三个元素array已填充。
What you are trying to do in your first example is test whether the instance tmpmatches an element in ObjectList. From 10g onwards we can do this using the MEMBER OFsyntax. Unfortunately, in order to make that work we have to declare a MAPmethod, which is rather clunky and would get rather annoying if the object has a lot of attributes.
您在第一个示例中尝试做的是测试该实例是否tmp与ObjectList. 从 10g 开始，我们可以使用MEMBER OF语法来做到这一点。不幸的是，为了完成这项工作，我们必须声明一个MAP方法，该方法相当笨重，如果对象具有很多属性，则会变得相当烦人。
SQL> create or replace type simple_type as object
2 ( id number
3 , name varchar2(30)
4 , map member function compare return varchar2);
5 /
Type created.
SQL>
SQL> create or replace type body simple_type as
2 map member function compare return varchar2
3 is
4 return_value integer;
5 begin
6 return to_char(id, '0000000')||name;
7 end compare;
8 end;
9 /
Type body created.
SQL>
Running the example...
运行示例...
SQL> set serveroutput on size unlimited
SQL>
SQL> declare
2 type objectlist is table of simple_type;
3 tmp simple_type := simple_type(1, 'a');
4 o objectlist := new objectlist(simple_type(2, 'a'), simple_type(3, 'a'));
5 begin
6 if tmp MEMBER OF o then
7 dbms_output.put_line('ok, exists.');
8 else
9 dbms_output.put_line('search me');
10 end if;
11 end;
12 /
search me
PL/SQL procedure successfully completed.
SQL>
