Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What is purpose of multiset collections?

Re: What is purpose of multiset collections?

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 26 Mar 2004 07:08:10 -0800
Message-ID: <3722db.0403260708.493bdf42@posting.google.com>


If the multiset function stayed the same as in 8i, it is "The inverse of the TABLE operator, converting a set of data (table, view, query) into a VARRAY or NESTED TABLE" (quote from Steven Feuerstein). He also says that it can be used to get all the children's information in one trip only. Here's an example (from him again):

DROP TYPE color_array_t FORCE;
DROP TABLE bird_habitats;
DROP TABLE birds;
DROP TYPE country_tab_t FORCE;

CREATE TYPE Color_array_t AS VARRAY (16) OF VARCHAR2(30); /

CREATE TABLE birds (

   genus VARCHAR2(128),
   species VARCHAR2(128),
   colors Color_array_t,
   PRIMARY KEY (genus, species)
);

INSERT INTO birds VALUES (

'spiro glockeel', 'northern hamrall',
   color_array_t ('RED', 'YELLOW', 'GREEN'));    

INSERT INTO birds VALUES (

'blouring tumswitch', 'darkwing chilata',
   color_array_t ('BROWN', 'ORANGE'));    

CREATE TABLE bird_habitats (

   genus VARCHAR2(128),
   species VARCHAR2(128),
   country VARCHAR2(60),
   FOREIGN KEY (genus, species) REFERENCES birds (genus, species) );

INSERT INTO bird_habitats VALUES (

'spiro glockeel', 'northern hamrall', 'HOLBIND');
INSERT INTO bird_habitats VALUES (

'spiro glockeel', 'northern hamrall', 'NORTH OTAWANDIA');
INSERT INTO bird_habitats VALUES (

'spiro glockeel', 'northern hamrall', 'SPELACKER');
   

CREATE TYPE Country_tab_t AS TABLE OF VARCHAR2(60); /

DECLARE
   CURSOR bird_curs IS

      SELECT b.genus, b.species, 
         CAST(MULTISET(SELECT bh.country FROM bird_habitats bh
                        WHERE bh.genus = b.genus
                          AND bh.species = b.species)
           AS country_tab_t) countries
        FROM birds b
       WHERE genus = 'spiro glockeel';
       

   bird_row bird_curs%ROWTYPE;    

   v_row PLS_INTEGER;
BEGIN
   OPEN bird_curs;
   FETCH bird_curs into bird_row;
   CLOSE bird_curs;    

   DBMS_OUTPUT.PUT_LINE (

      'Countries in which the "' || 
      bird_row.genus || '" is found:');
      

   v_row := bird_row.countries.FIRST;
   LOOP

      EXIT WHEN v_row IS NULL;
      DBMS_OUTPUT.PUT_LINE (
         '   ' || bird_row.countries(v_row));
      v_row := bird_row.countries.NEXT (v_row);
   END LOOP;
END;
/

Daniel Received on Fri Mar 26 2004 - 09:08:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US