Create the object and collection types : Object « Object Oriented Database « Oracle PL / SQL






Create the object and collection types

    

SQL> CREATE or replace TYPE addressType AS OBJECT (
  2    street VARCHAR2(15),
  3    city   VARCHAR2(15),
  4    state  CHAR(2),
  5    zip    VARCHAR2(5)
  6  );
  7  /

Type created.

SQL>
SQL> CREATE or replace TYPE addressTypeVArray AS VARRAY(2) OF VARCHAR2(50);
  2  /

Type created.

SQL>
SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
  2  /

Type created.

SQL>
SQL> -- varray in temporary table example
SQL> CREATE GLOBAL TEMPORARY TABLE empTempTable (
  2    id         INTEGER PRIMARY KEY,
  3    fname VARCHAR2(10),
  4    lname  VARCHAR2(10),
  5    addresses  addressTypeVArray
  6  );

SQL>
SQL> CREATE TABLE empTable (
  2    id         INTEGER PRIMARY KEY,
  3    fname VARCHAR2(10),
  4    lname  VARCHAR2(10),
  5    addresses  addressTypeNestedTable
  6  )
  7  NESTED TABLE
  8    addresses
  9  STORE AS
 10    nested_addresses2 TABLESPACE users;

Table created.

SQL>
SQL>
SQL> -- equal/not equal example
SQL> CREATE OR REPLACE PROCEDURE equal_example AS
  2    TYPE charTable IS TABLE OF VARCHAR2(10);
  3    emp1 charTable;
  4    emp2 charTable;
  5    emp3 charTable;
  6    result BOOLEAN;
  7  BEGIN
  8    emp1 := charTable('A', 'B', 'C');
  9    emp2 := charTable('A', 'B', 'C');
 10    emp3 := charTable('B', 'C', 'D');
 11
 12
 13    result := emp1 <> emp3;
 14    IF result THEN
 15      DBMS_OUTPUT.PUT_LINE('emp1 not equal to emp3');
 16    END IF;
 17  END equal_example;
 18  /

Procedure created.

SQL>
SQL> drop type addressType force;

Type dropped.

SQL> drop type addressTypeVArray force;

Type dropped.

SQL> drop TYPE addressTypeNestedTable force;

Type dropped.

SQL> drop TABLE empTable;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.Create Object
2.CREATE OR REPLACE TYPE
3.Create a stored type which is visible to SQL and PL/SQL.
4.reference user-defined data type in another block
5.Student type
6.Point type
7.Use user-defined type as parameter
8.This script demonstrates complex objects
9.Name type
10.Behavior of dependent objects.
11.Build data type with another user type
12.Create type and use it in inner query
13.Create types and then use it in pl/sql block
14.Combine user-defined type to create new type
15.PriceType becomes the datatype of the price attribute in the ProductType object type
16.Use self to reference member variable in constructor
17.One to list using object references