Home » SQL & PL/SQL » SQL & PL/SQL » nested table
nested table [message #263743] Thu, 30 August 2007 23:00 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
CREATE OR REPLACE TYPE numTypeObj AS object (id NUMBER, name varchar2(20));

CREATE OR REPLACE TYPE numTypelst AS table of numTypeObj;

Am having values in the numTypelst as

101,x
102,y
103,z
etc..

How to select values within this nested table .
Normally we can use select stmt for a table , but is it possible
that I can use select statement for this numTypelst based on some conditions.

for ex
if id > 102 then
.....

Here i have to select id from the nested table numTypelst .
Re: nested table [message #263775 is a reply to message #263743] Fri, 31 August 2007 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE TYPE numTypeObj AS object (id NUMBER, name varchar2(20));
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE numTypelst AS table of numTypeObj;
  2  /

Type created.

SQL> declare
  2    v numTypelst := numTypelst(numTypeObj(101,'x'),numTypeObj(102,'y'),numTypeObj(103,'z'));
  3  begin
  4    for i in v.first..v.last loop
  5     if v(i).id = 102 then dbms_output.put_line('name is '||v(i).name); end if;
  6    end loop;
  7  end;
  8  /
name is y

PL/SQL procedure successfully completed.

Regards
Michel
Re: nested table [message #264074 is a reply to message #263775] Fri, 31 August 2007 11:52 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
A table-valued function can be used if you NEED to utilize the power of SQL to query a nested table
CREATE OR REPLACE TYPE numTypeObj AS object (id NUMBER, name varchar2(20));
/

CREATE OR REPLACE TYPE numTypelst AS table of numTypeObj;
/

CREATE OR REPLACE FUNCTION numlst 
 ( p_numlst IN numTypelst )
RETURN numTypelst PIPELINED IS
  i  BINARY_INTEGER;
BEGIN
  i := p_numlst.FIRST;
  WHILE i IS NOT NULL LOOP
    PIPE ROW (p_numlst(i));
    i := p_numlst.NEXT(i);    
  END LOOP;
  
  RETURN;
END numlst;
/

SET SERVEROUT ON
DECLARE
  CURSOR cur (p_numlst numTypelst) IS
  SELECT name
  FROM   TABLE(numlst(p_numlst))
  WHERE  id >= 2;

  l_numlst  numTypelst := numTypelst( numTypeObj(1, 'JOE')
                                    , numTypeObj(2, 'AMY')
                                    , numTypeObj(3, 'MARY'));

  l_name    VARCHAR2(20);
BEGIN
  FOR rec IN cur(l_numlst) LOOP
    dbms_output.put_line(rec.name);
  END LOOP;
END;
/

AMY
MARY

PL/SQL procedure successfully completed.

I emphasized NEED because this is not necessarily an efficient method of manipulating the data. In most cases, use Michel's technique.
Re: nested table [message #264079 is a reply to message #264074] Fri, 31 August 2007 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"My" Cool technique was just an example to use object type in PL/SQL but you can also no more use PL/SQL:
SQL> select name from table(numTypelst(numTypeObj(1, 'JOE'),
  2                                    numTypeObj(2, 'AMY'),
  3                                    numTypeObj(3, 'MARY')))
  4  where id >= 2;
NAME
--------------------
AMY
MARY

2 rows selected.

Regards
Michel

[Updated on: Fri, 31 August 2007 12:19]

Report message to a moderator

Re: nested table [message #264082 is a reply to message #263743] Fri, 31 August 2007 12:32 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
That is true. I got a little carried away with the extra function. It is not necessary in this case. It would only be required if there was a need to process or manipulate the collection data prior to querying. Thanks for the correction.
SET SERVEROUT ON
DECLARE
  CURSOR cur (p_numlst numTypelst) IS
  SELECT name
  FROM   TABLE(p_numlst)
  WHERE  id >= 2;

  l_numlst  numTypelst := numTypelst( numTypeObj(1, 'JOE')
                                    , numTypeObj(2, 'AMY')
                                    , numTypeObj(3, 'MARY'));

  l_name    VARCHAR2(20);
BEGIN
  FOR rec IN cur(l_numlst) LOOP
    dbms_output.put_line(rec.name);
  END LOOP;
END;
Previous Topic: Merge over Multiple Schemas
Next Topic: oracle sql short
Goto Forum:
  


Current Time: Wed Dec 07 04:58:37 CST 2016

Total time taken to generate the page: 0.14853 seconds