Oracle PL/SQL/Object Oriented Database/Table
Содержание
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>