Home » SQL & PL/SQL » SQL & PL/SQL » Element exists in a collection (Oracle 11.2.0.3)
Element exists in a collection [message #658068] Fri, 02 December 2016 04:28 Go to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
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 Go to previous message
Solomon Yakobson
Messages: 3269
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.
Previous Topic: How to check if time is attached with date?
Next Topic: Query For N number to top Sales in Region
Goto Forum:
  


Current Time: Fri Mar 29 05:16:37 CDT 2024