Oracle PL/SQL/Object Oriented Database/Table

Материал из SQL эксперт
Перейти к: навигация, поиск

CREATE a TABLE with the Column Type in It

   <source lang="sql">

SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(

 2        street VARCHAR2(20),
 3        city VARCHAR2(20),
 4        state CHAR(2),
 5        zip CHAR(5));
 6  /

Type created. SQL> CREATE TABLE emp (empno NUMBER(3),

 2                    name    VARCHAR2(20),
 3                    address ADDRESS_OBJ);

Table created. SQL> SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL>

      </source>
   
  


Create table from Object type

   <source lang="sql">

SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(

 2        street VARCHAR2(20),
 3        city VARCHAR2(20),
 4        state CHAR(2),
 5        zip CHAR(5));
 6  /

Type created. SQL> SQL> CREATE TABLE emp (empno NUMBER(3),

 2                    name    VARCHAR2(20),
 3                    address ADDRESS_OBJ);

Table created. SQL> SQL> SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608")); 1 row created. SQL> SQL> SQL> CREATE TABLE address_table OF ADDRESS_OBJ; Table created. SQL> SQL> SQL> INSERT INTO Address_table

 2  SELECT e.address
 3  FROM emp e;

1 row created. SQL> SQL> SQL> SELECT city

 2  FROM address_table
 3

SQL> SQL> drop table address_table; Table dropped. SQL> SQL> drop table emp; Table dropped. SQL> SQL>

      </source>
   
  


dependencies between object types and tables.

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE TYPE RowObject AS OBJECT (

 2    attributeA NUMBER
 3  );
 4  /

Type created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE TYPE ColObject AS OBJECT (

 2    attributeB NUMBER
 3  );
 4  /

Type created. SQL> show errors No errors. SQL> SQL> CREATE TABLE RowTable OF RowObject; Table created. SQL> SQL> CREATE TABLE ColTable (

 2    theObject ColObject
 3  );

Table created. SQL> SQL> SELECT object_name, object_type, status

 2    FROM user_objects
 3    WHERE object_name IN ("COLTABLE", "ROWTABLE");

OBJECT_NAME OBJECT_TYPE STATUS


------------------- -------

COLTABLE TABLE VALID ROWTABLE TABLE VALID SQL> SQL> SQL> DROP TABLE RowTable; Table dropped. SQL> DROP TABLE ColTable; Table dropped. SQL> SQL>

</source>
   
  


Loading the "row object" Table

   <source lang="sql">

SQL> --Loading the "row object" Table SQL> SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(

 2        street VARCHAR2(20),
 3        city VARCHAR2(20),
 4        state CHAR(2),
 5        zip CHAR(5));
 6  /

Type created. SQL> SQL> CREATE TABLE emp (empno NUMBER(3),

 2                    name    VARCHAR2(20),
 3                    address ADDRESS_OBJ);

Table created. SQL> SQL> SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608")); 1 row created. SQL> SQL> SQL> CREATE TABLE address_table OF ADDRESS_OBJ; Table created. SQL> SQL> INSERT INTO Address_table

 2  SELECT e.address
 3  FROM emp e;

1 row created. SQL> SQL> select * from address_table; STREET CITY ST ZIP


-------------------- -- -----

1 A St. Mobile AL 36608

SQL> SQL> drop table emp; Table dropped. SQL> SQL> drop table address_table; Table dropped. SQL> SQL>

      </source>