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

Home -> Community -> Usenet -> c.d.o.tools -> Re: abstract data types

Re: abstract data types

From: DriftWood <drift_wood_at_my-deja.com>
Date: Thu, 18 Jan 2001 21:49:07 GMT
Message-ID: <947ocf$uji$1@nnrp1.deja.com> 






Examples follow.


--



-cheers


  DW




"It is a kind of good deed to say well; and yet words are not deeds.
  -William Shakespeare"





SIMPLE TABLE






CREATE:





CREATE TYPE TYPE1 AS OBJECT(


	TYPE1_C1 NUMBER(5),
	TYPE1_C2 VARCHAR2(40));

CREATE TYPE TYPE2 AS OBJECT(
	TYPE2_C1 NUMBER(5),
	TYPE2_C2 VARCHAR2(40));

CREATE TYPE TABLE_OF_OBJECTS AS OBJECT(
	FIELD1 TYPE1,
	FIELD2 TYPE2);





--The following Create statements will create two different tables of

the same type.


CREATE TABLE TABLE_OF_TYPES(


	FIELD1 TYPE1,
	FIELD2 TYPE2);





CREATE TABLE MYTABLE_OF_OBJECTS OF TABLE_OF_OBJECTS


INSERT:





--These insert statement will work for either table.

INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(1, 'DAVE'), TYPE2

(100, 'JIM'));



INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(2, 'BILL'), TYPE2

(200, 'AL'));


INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(1, 'DAVE'), TYPE2(NULL,

NULL));



INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(NULL, NULL), TYPE2

(200, 'LISA'));


SELECT:





--Generic select



SELECT * FROM MYTABLE_OF_OBJECTS;



--Select specific data from Field1



SELECT X.FIELD1.TYPE1_C1, X.FIELD1.TYPE1_C2 FROM MYTABLE_OF_OBJECTS X;



--Select specific data from Field2



SELECT X.FIELD2.TYPE2_C1, X.FIELD2.TYPE2_C2 FROM MYTABLE_OF_OBJECTS X;


UPDATE:





--Updates only one of the two fields contained in Field1's datatype

UPDATE MYTABLE_OF_OBJECTS X



SET X.FIELD1.TYPE1_C2 = 'JIM'



WHERE X.FIELD2.TYPE2_C2 = 'LISA';


DELETE:





--Deletes one record based on one of Field2's datatypes

DELETE MYTABLE_OF_OBJECTS X WHERE X.FIELD2.TYPE2_C2 = 'JIM';







Complex tables with a Simple VARRAY and a VARRAY of a Structure (NO
REFs)





CREATE:





CREATE TYPE TYPE_VARRAY1 AS VARRAY(10) OF INT;


CREATE TYPE TYPE1_STRUCT AS OBJECT (C1 NUMBER(5), C2 VARCHAR2(30));

CREATE TYPE TYPE2_STRUCT_VARRAY AS VARRAY(10) OF TYPE1_STRUCT;


CREATE TYPE CUSTOM_TYPE2 AS OBJECT (



    CUSTOMFIELD1 TYPE_VARRAY1,



    CUSTOMFIELD2 TYPE2_STRUCT_VARRAY);


CREATE TABLE CUSTOM_TABLE2(ID NUMBER(5), CUSTOMFIELD CUSTOM_TYPE2);



INSERT:





INSERT INTO CUSTOM_TABLE2 VALUES (1,



        CUSTOM_TYPE2( TYPE_VARRAY1(20), TYPE2_STRUCT_VARRAY(TYPE1_STRUCT

(1, 'ROW1'))))


INSERT INTO CUSTOM_TABLE2 VALUES (3,


        CUSTOM_TYPE2( TYPE_VARRAY1(30,31,32),
   TYPE2_STRUCT_VARRAY(TYPE1_STRUCT(1, 'ROW 1'),
                       TYPE1_STRUCT(2, 'ROW 2'),
                       TYPE1_STRUCT(3, 'ROW 3') ) ) )





--The following PL/SQL block will append a new element to the array of

an existing record


DECLARE


     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := 9;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(9, 'Adding 9 using
PL/SQL');
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = 1;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = 1;
     -- Don't forget to commit




 END;



/



--The following Stored Procedure will append a new element to the array

of an existing --record by passing in the ID of the record to append
followed by the data


--for both arrays.



CREATE OR REPLACE PROCEDURE ADD_ELEMENT



(UPDATEID IN NUMBER, VARRAY1 IN NUMBER, VARRAY2_1 NUMBER, VARRAY2_2

VARCHAR2)



AS

     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := VARRAY1;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(VARRAY2_1,
VARRAY2_2);
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = UPDATEID;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = UPDATEID;
     --Don't forget to commit.




 END;



/




SELECT:





--Generic select



SELECT X.ID FROM CUSTOM_TABLE2 X;



--Selects data contained in the first VARRAY

SELECT X.CUSTOMFIELD.CUSTOMFIELD1 FROM CUSTOM_TABLE2 X;



--Selects data contained in the VARRAY of struct

SELECT X.CUSTOMFIELD.CUSTOMFIELD2 FROM CUSTOM_TABLE2 X;



--In order to select specific fields within the VARRAY structure you

must first create table type of --the object.
CREATE TYPE NESTTYPE IS TABLE OF TYPE1_STRUCT;

SELECT C1 FROM THE



(SELECT CAST(X.CUSTOMFIELD.CUSTOMFIELD2 AS NESTTYPE)

 FROM CUSTOM_TABLE2 X WHERE X.ID = 3);


UPDATE:





--Update of VARRAY elements must be done in a PL/SQL block or Stored

Procedures



--Sample PL/SQL



DECLARE


     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := 9;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(9, 'Adding 9 using
PL/SQL');
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = 1;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =


NEW_ELEMENT2 WHERE ID = 1;



 END;



/



--Sample Stored Procedure which makes the above PL/SQL a little more

dynamic


CREATE OR REPLACE PROCEDURE ADD_ELEMENT



(UPDATEID IN NUMBER, VARRAY1 IN NUMBER, VARRAY2_1 NUMBER, VARRAY2_2

VARCHAR2)



AS

     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := VARRAY1;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(VARRAY2_1,
VARRAY2_2);
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = UPDATEID;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =


NEW_ELEMENT2 WHERE ID = UPDATEID;



 END;



/




DELETE:





--The following PL/SQL block will delete an element from an array.

--NOTE: You can only delete the last element of any given array.

DECLARE



      NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;



   BEGIN



     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM

CUSTOM_TABLE2 X WHERE ID = 3;


     DBMS_OUTPUT.PUT_LINE(NEW_ELEMENT2.COUNT);
      NEW_ELEMENT2.TRIM(1);
      UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = 3;
     DBMS_OUTPUT.PUT_LINE(NEW_ELEMENT2.COUNT);


  END;








Nested Tables (NO REFs)





CREATE:





CREATE TYPE NESTED_TYPE AS OBJECT (C1 NUMBER(5), C2 VARCHAR2(30));

CREATE TYPE NESTED_TYPE_TABLE AS TABLE OF NESTED_TYPE;

CREATE TABLE NESTED_SAMPLE (



FIELD1 NUMBER(5),



FIELD2 NESTED_TYPE_TABLE) NESTED TABLE FIELD2 STORE AS NESTED_TABLE;


INSERT:





INSERT INTO NESTED_SAMPLE VALUES (1,     NESTED_TYPE_TABLE(NESTED_TYPE

(100, 'Entry 1')));


INSERT INTO NESTED_SAMPLE VALUES (2,       NESTED_TYPE_TABLE(NESTED_TYPE

(200, NULL)));



INSERT INTO NESTED_SAMPLE VALUES (3,       NESTED_TYPE_TABLE(NESTED_TYPE

(NULL, 'Entry 3')));


INSERT INTO NESTED_SAMPLE VALUES (5,       NESTED_TYPE_TABLE(NESTED_TYPE

(500, 'Entry 4),

                                              NESTED_TYPE(501, 'Entry
5')))
INSERT INTO NESTED_SAMPLE VALUES (6,       NESTED_TYPE_TABLE(NESTED_TYPE


(100, 'Entry 6')));


--Inserts into the nest table of an existing parent record.

--Note: You can only insert one record at a time.

INSERT INTO THE (SELECT FIELD2 FROM NESTED_SAMPLE D WHERE D.FIELD1 = 6)

VALUES (600, 'NEW ROW');


SELECT:





--Generic select



SELECT * FROM NESTED_SAMPLE;



--Selects only the fields in the Nested Table

SELECT * FROM THE (SELECT FIELD2 FROM NESTED_SAMPLE X WHERE X.FIELD1=1);



--Displays field(s) using a selection wihich is based on criteria

against the nested table


SELECT   D.FIELD1



   FROM  NESTED_SAMPLE D



   WHERE EXISTS (SELECT * FROM TABLE (D.FIELD2) E WHERE E.C1 = 100);


UPDATE:





--Updates parent record based on nest table criteria

UPDATE NESTED_SAMPLE D



   SET D.FIELD1 = 10



   WHERE EXISTS (SELECT * FROM TABLE (D.FIELD2) E WHERE E.C1 = 100);




--Updates nested table record based on parent criteria

UPDATE THE (SELECT D.FIELD2 FROM NESTED_SAMPLE D WHERE D.FIELD1 = 6) I

    SET I.C1 = 10



DELETE:





--Deleting entire record based on nested table selection criteria

DELETE NESTED_SAMPLE D WHERE EXISTS (SELECT * FROM TABLE (D.FIELD2) E

WHERE E.C2 = 'Entry 6');



--Deleting data in the nested table



DELETE THE (SELECT FIELD2 FROM NESTED_SAMPLE D WHERE D.FIELD1 = 6) X

 WHERE X.C1 = 600;




--Deleting data from outer table



DELETE FROM NESTED_SAMPLE D WHERE D.FIELD1 = 2;




Individual elements of the VARRAY are not queryable. You add a method
to your type that will return a condition you can query on or change it
to using nested tables. Queryability of individual elements is one of
the reasons to use a nested table over a varray.







CREATE TYPE STRUCT1_TYPE AS OBJECT (F1 NUMBER(5), F2 VARCHAR2(30));

/



CREATE TYPE VARRAY_OF_STRUCT1_TYPE IS VARRAY(5) OF STRUCT1_TYPE;

/



CREATE OR REPLACE TYPE VARRAY_OF_STRUCT1_OBJECT as object (
field1 VARRAY_OF_STRUCT1_TYPE,


MEMBER FUNCTION containsF1 (testnum NUMBER) RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES (containsF1, WNDS, WNPS));
/



CREATE TYPE NESTTYPE IS TABLE OF STRUCT1_TYPE;

/



CREATE TABLE VARRAY_TABLE_STRUCT1 OF VARRAY_OF_STRUCT1_OBJECT;



insert into varray_table_struct1 values (varray_of_struct1_type
(struct1_type (1,'aaaa'),


struct1_type(2,'bbbb')))


/



insert into varray_table_struct1 values (varray_of_struct1_type
(struct1_type (3,'cccc'),


struct1_type(4,'dddd')))


/


commit;



CREATE OR REPLACE TYPE BODY VARRAY_OF_STRUCT1_OBJECT AS

  MEMBER FUNCTION containsF1 (testnum NUMBER) RETURN NUMBER IS

	BEGIN
	  FOR I in 1..SELF.field1.last LOOP
	    IF (testnum = SELF.field1(i).F1) THEN
		  RETURN 1;
	    END IF;
      END LOOP;
      RETURN 0;
	END;




END;



/



SELECT F1, F2 FROM THE (SELECT CAST(X.FIELD1 AS NESTTYPE)


            FROM VARRAY_TABLE_STRUCT1 X
       WHERE x.containsF1(2) = 1);







Example(s) of extending a VARRAY type.







SQL> drop table user_groups;


Table dropped.


SQL> create table user_groups
   > (  username  varchar2( 30 ),
   >    groupname varchar2( 64 )
   > )
   > /




Table created.



SQL> insert into user_groups values ( 'sdillon', 'admin' );



1 row created.


SQL> insert into user_groups values ( 'sdillon', 'webtech' );



1 row created.


SQL> insert into user_groups values ( 'ykosuru', 'admin' );



1 row created.


SQL> insert into user_groups values ( 'ykosuru', 'webtech' );



1 row created.


SQL> insert into user_groups values ( 'ykosuru', 'expresstools' );



1 row created.


SQL> insert into user_groups values ( 'ykosuru', 'bldg3emps' );
1 row created.


SQL> declare
   >   type user_list is varray( 100 ) of varchar2( 64 );
   >   list user_list := user_list();
   > begin
   >   select groupname
   >     bulk collect into list
   >     from user_groups
   >    where username = 'ykosuru';
   >
   >   for i in 1 .. list.COUNT loop
   >     dbms_output.put_line( list( i ) );
   >   end loop;
   > end;
   > /





admin


webtech


expresstools


bldg3emps



PL/SQL procedure successfully completed.



If you needed to do it the long way around, you could extend as such:


SQL> declare
   >   type   user_list is varray( 100 ) of varchar2( 64 );
   >   list   user_list := user_list();
   >   i      number    := 0;
   >   l_name varchar2( 64 );
   >
   >   cursor cur( name varchar2 ) is
   >     select groupname
   >       from user_groups
   >      where username = name;
   > begin
   >   open cur( 'ykosuru' );
   >   loop
   >     fetch cur into l_name;
   >     exit when cur%NOTFOUND;
   >     i := i + 1;
   >     list.extend();
   >     list( i ) := l_name;
   >   end loop;
   >
   >   for i in 1 .. list.COUNT loop
   >     dbms_output.put_line( list( i ) );
   >   end loop;
   > end;
   > /





admin


webtech


expresstools


bldg3emps



PL/SQL procedure successfully completed.





Sent via Deja.com


http://www.deja.com/
Received on Thu Jan 18 2001 - 15:49:07 CST












Original text of this message













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