Element exists in a collection [message #658068] |
Fri, 02 December 2016 04:28 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I would like to verify if an element exists in a collection.
I could achieve this using -
1. MEMBER OF Function using single dimensional
2. Element by Element processing
For e.g.
-- Method 1:
--Member of using Nested Table using single dimentional collection
DECLARE
TYPE empid_nt IS TABLE OF NUMBER;
TYPE empname_nt IS TABLE OF VARCHAR2(20);
l_empid empid_nt := empid_nt();
l_empname empname_nt := empname_nt();
l_val VARCHAR2(20) := 'hello2';
BEGIN
l_empid.extend;
l_empname.extend;
FOR i IN (SELECT rownum rn FROM DUAL CONNECT BY LEVEL <=10)
LOOP
l_empid.extend;
l_empname.extend;
l_empid(l_empid.last) := i.rn;
l_empname(l_empname.last) := 'hello'||i.rn;
END LOOP;
IF l_val MEMBER OF l_empname
THEN
DBMS_OUTPUT.PUT_LINE('Emp exists');
END IF;
END;
/
-- Method 2:
-- Element by Element by Verification
DECLARE
TYPE empid_nt IS TABLE OF NUMBER;
TYPE empname_nt IS TABLE OF VARCHAR2(20);
l_empid empid_nt := empid_nt();
l_empname empname_nt := empname_nt();
l_val VARCHAR2(20) := 'hello2';
BEGIN
l_empid.extend;
l_empname.extend;
FOR i IN (SELECT rownum rn FROM DUAL CONNECT BY LEVEL <=10)
LOOP
l_empid.extend;
l_empname.extend;
l_empid(l_empid.last) := i.rn;
l_empname(l_empname.last) := 'hello'||i.rn;
END LOOP;
FOR i IN l_empname.first..l_empname.last
LOOP
IF l_empname(i) = l_val
THEN
DBMS_OUTPUT.PUT_LINE('Emp Exists');
EXIT;
END IF;
END LOOP;
END;
/
I would like to know if we can achieve the same results using multi dimensional array. Thus, I will have only one collection.
Something like below.
DECLARE
TYPE emp_rec IS RECORD (emp_id NUMBER, emp_name VARCHAR2(20));
TYPE emp_nt IS TABLE OF emp_rec;
l_emp emp_nt := emp_nt();
l_emp_name VARCHAR2(20) := 'hello2';
BEGIN
FOR i IN (SELECT rownum rn FROM DUAL CONNECT BY LEVEL <=10)
LOOP
l_emp(i.rn).emp_id := i.rn;
l_emp(i.rn).emp_name := 'hello'||i.rn;
END LOOP;
IF l_emp_name MEMBER OF l_emp.emp_name --<-----Here, I am trying to get an element from multi-dimensional array, not sure how to use with MEMBER OF
THEN
DBMS_OUTPUT.PUT_LINE('Emp exists');
END IF;
END;
/
I get the following error.
ORA-06550: line 13, column 34:
PLS-00302: component 'EMP_NAME' must be declared
ORA-06550: line 13, column 4:
PL/SQL: Statement ignored
Thank you in advance.
Regards,
Pointers
|
|
|
Re: Element exists in a collection [message #658070 is a reply to message #658068] |
Fri, 02 December 2016 05:49 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You don't have multi-dimensional array. You have array of records. So code would be something like:
DECLARE
TYPE emp_rec IS RECORD (emp_id NUMBER, emp_name VARCHAR2(20));
TYPE emp_nt IS TABLE OF emp_rec;
l_emp emp_nt := emp_nt();
l_emp_name VARCHAR2(20) := 'hello2';
l_emp_rec emp_rec;
BEGIN
l_emp_rec.emp_id := 2;
l_emp_rec.emp_name := 'hello2';
FOR i IN (SELECT rownum rn FROM DUAL CONNECT BY LEVEL <=10)
LOOP
l_emp(i.rn).emp_id := i.rn;
l_emp(i.rn).emp_name := 'hello'||i.rn;
END LOOP;
IF l_emp_name MEMBER OF l_emp
THEN
DBMS_OUTPUT.PUT_LINE('Emp exists');
END IF;
END;
/
But it will fail since MEMBER OF works with collections of SQL types and RECORD is PL/SQL type:
IF l_emp_name MEMBER OF l_emp
*
ERROR at line 16:
ORA-06550: line 16, column 7:
PLS-00306: wrong number or types of arguments in call to 'MEMBER OF'
ORA-06550: line 16, column 4:
PL/SQL: Statement ignored
SQL>
SY.
|
|
|
Re: Element exists in a collection [message #658072 is a reply to message #658070] |
Fri, 02 December 2016 06:34 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Thank you Solomon for you reply.
I would like to compare only one element of the record i.e. only emp_id. But not the combination of emp_id and emp_name
How to achieve this.
What would be the best or other ways of doing it. I could find 2 ways which I posted above.
Regards,
Pointers
|
|
|
Re: Element exists in a collection [message #658073 is a reply to message #658072] |
Fri, 02 December 2016 06:43 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Record is not an array. It is composite scalar. It doesn't have elements. It has attributes. MEMBER OF checks array elements which in your case would be whole record. But again, you wouldn't be able to check whole record via MEMBER OF either since MEMBER OF, as I already mentioned, supports SQL types only.
SY.
|
|
|