Home » SQL & PL/SQL » SQL & PL/SQL » "Member Of" in PL/SQL Collections (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
"Member Of" in PL/SQL Collections [message #545133] Mon, 27 February 2012 08:13 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi All ,

Can we use the 'Member of' key word in sql query? My requirement is like this

create table member_example(dist1  varchar2(20)); 

insert all 
into member_example values ('HYD')
into member_example values ('VZG')
into member_example values ('VJD')
into member_example values ('TRP')
into member_example values ('WGL')
into member_example values ('MBN')
into member_example values ('RGR')
into member_example values ('MDK')
select * from dual;


According to our requirement,total logic will applicable only for some specified values in the procedure.For example in the following PL/SQL block count should only two .
Can we make use 'MEMBER OF' keyword to achieve the requirement

declare 
type city_type_list is table of  varchar2(20);
list1 city_type_list;
cnt number ;
begin 
list1  := city_type_list('HYD','MDK');
select count(distinct dist1)
  into cnt
  from member_example
 --where dist1 member of list1
 ;
 dbms_output.put_line('count  '||cnt);
end;
Re: "Member Of" in PL/SQL Collections [message #545136 is a reply to message #545133] Mon, 27 February 2012 08:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
You must use SQL type:

SQL> create or replace
  2    type city_type_list is table of  varchar2(20);
  3  /

Type created.

SQL> set serveroutput on
SQL> declare
  2      list1 city_type_list := city_type_list('HYD','MDK');
  3      cnt number;
  4  begin
  5      select count(distinct dist1)
  6        into cnt
  7        from member_example
  8        where dist1 member of list1;
  9      dbms_output.put_line('count  '||cnt);
 10  end;
 11  /
count  2

PL/SQL procedure successfully completed.

SQL> 


SY.
Re: "Member Of" in PL/SQL Collections [message #545208 is a reply to message #545136] Mon, 27 February 2012 21:59 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member



Thank you very much syakobson .

Now i am able to solve my problem .

Is there any other differences between SQL Type and Normal type
like this

Thanks
SaiPradyumn
Re: "Member Of" in PL/SQL Collections [message #545234 is a reply to message #545208] Tue, 28 February 2012 01:06 Go to previous message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is a difference between SQL type and PL/SQL type: PL/SQL cannot be accessed by SQL engine.

Regards
Michel
Previous Topic: Commit between insert
Next Topic: View to find DB Link type
Goto Forum:
  


Current Time: Sat Jun 28 23:18:58 CDT 2025