oracle sql varray 包含一个元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30391960/
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
oracle sql varray contains an element
提问by wasp256
I have a type declaration like this:
我有一个这样的类型声明：
FUNCTION ...
IS
TYPE stati_va IS VARRAY (10000) OF varchar(1);
stati stati_va;
v_counter INTEGER := 0;
BEGIN
stati := stati_va ();
--this is actually in a loop so the array contains more values
v_counter := v_counter + 1;
stati.EXTEND;
stati (v_counter) := '4';
--here I would like to determine if the array 'stati' contains a value
I have tried the following so far but that gives me an errro
到目前为止，我已经尝试了以下操作，但这给了我一个错误
IF '4' member of stati then <- COMPILE error
IF '4' IN stati then <- COMPILE error
I know that looping through the array would be possible but that is a bit inconvinient since I need to build something like this:
我知道循环遍历数组是可能的，但这有点不方便，因为我需要构建这样的东西：
IF array contains '4' then
elsif array contains '3' then
elseif array contains '2' then
...
回答by Lalit Kumar B
You could use the condition:
你可以使用条件：
IF 'element' member OF <my_array> THEN
For example,
例如，
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 TYPE v_array
3 IS
4 TABLE OF VARCHAR2(200);
5 my_array v_array;
6 BEGIN
7 my_array := v_array('1','2','3','4');
8 IF '4' member OF my_array THEN
9 dbms_output.put_line('yes');
10 ELSE
11 dbms_output.put_line('no');
12 END IF;
13 END;
14 /
yes
PL/SQL procedure successfully completed.
SQL>
回答by Michal Pravda
You can't do it with a VARRAY without looping through it.
你不能用 VARRAY 来完成它而不循环它。
It is possible to accomplish with associative table:
可以用关联表完成：
DECLARE
TYPE stati_va IS TABLE OF NUMBER INDEX BY binary_integer;
l_array stati_va;
BEGIN
FOR i IN 1 .. 1000
LOOP
l_array(i) := dbms_random.random;
END LOOP;
DECLARE
TYPE stati_va IS TABLE OF NUMBER INDEX BY binary_integer;
l_array stati_va;
BEGIN
FOR i IN 1 .. 1000
LOOP
l_array(i) := dbms_random.random;
END LOOP;
IF (l_array.exists(4))
THEN
dbms_output.put_line(l_array(4));
END IF;
IF (l_array.exists(234234))
THEN
dbms_output.put_line('index exists');
ELSE
dbms_output.put_line('index doesnt' exist');
END IF;
IF (l_array.exists(4))
THEN
dbms_output.put_line(l_array(4));
END IF;
IF (l_array.exists(234234))
THEN
dbms_output.put_line('index exists');
ELSE
dbms_output.put_line('index doesnt' exist');
END IF;
END;
/
END;
/
You can use binary_integer or varchar2 as keys to this array, anything as value
您可以使用 binary_integer 或 varchar2 作为该数组的键，任何作为值
Edit:I have forgotten membersyntax. Since it behaves strangely in sql (select statements) we have banned it in our team
编辑：我忘记了成员语法。由于它在 sql（select 语句）中的行为很奇怪，我们已在我们的团队中禁止它
it would be (with varray or nested table) if key member of array then ...
如果数组的关键成员则是（使用 varray 或嵌套表）...
