Oracle PL/SQL Tutorial/Collections/MEMBER OF — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 13:04, 26 мая 2010
Содержание
MEMBER OF is a logical comparison operator
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE list IS TABLE OF NUMBER; 3 n VARCHAR2(10) := "One"; 4 a LIST := list("One", "Two", "Three"); 5 BEGIN 6 IF n MEMBER OF a THEN 7 dbms_output.put_line(""n" is member."); 8 END IF; 9 END; 10 / DECLARE
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 4</source>
MEMBER OF Operator
MEMBER OF operator checks whether an element is in a nested table.
<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE member_of_example AS
2 TYPE nestedTableType IS TABLE OF VARCHAR2(10); 3 myTable1 nestedTableType; 4 result BOOLEAN; 5 BEGIN 6 myTable1 := nestedTableType("F", "G", "S"); 7 result := "George" MEMBER OF myTable1; 8 IF result THEN 9 DBMS_OUTPUT.PUT_LINE("""George"" is a member"); 10 END IF; 11 END member_of_example; 12 /
Procedure created. SQL> CALL member_of_example(); Call completed. SQL> SQL></source>
MEMBER OF operator finds if the left operand is a member of the collection used as the right operand
<source lang="sql">
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000); 3 BEGIN 4 5 FOR i IN set_in.FIRST..set_in.LAST LOOP 6 returnValue := set_in(i)||" "; 7 END LOOP; 8 RETURN returnValue; 9 END format_list; 10 /
Function created. SQL> SQL> DECLARE
2 n VARCHAR2(10) := "One"; 3 a LIST := list("One","Two","Three"); 4 BEGIN 5 IF n MEMBER OF a THEN 6 dbms_output.put_line(""n" is member."); 7 END IF; 8 END; 9 /
DECLARE
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 3
SQL></source>
MEMBER OF table collection
<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE member_of_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10); 3 emp1 charTable; 4 result BOOLEAN; 5 BEGIN 6 emp1 := charTable("A", "B", "C"); 7 result := "A" MEMBER OF emp1; 8 IF result THEN 9 DBMS_OUTPUT.PUT_LINE("""A"" is a member"); 10 END IF; 11 END member_of_example; 12 /
Procedure created.</source>