SELECT with a WHERE Clause : Object Column « Object Oriented « Oracle PL/SQL Tutorial






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)
  4  /

Table created.

SQL>
SQL> INSERT INTO emp VALUES (101, 'A',ADDRESS_OBJ('1 St.','Mobile','AL','36608'));

1 row created.

SQL>
SQL> select * from emp;

     EMPNO NAME                 ADDRESS(STREET, CITY, STATE, ZIP)
---------- -------------------- --------------------------------------------------
       101 A                    ADDRESS_OBJ('1 St.', 'Mobile', 'AL', '36608')

SQL>
SQL> SELECT name, e.address.city
  2  FROM emp e
  3  WHERE e.address.state = 'AL'
  4
SQL> drop table emp;

Table dropped.

SQL>
SQL> drop type ADDRESS_OBJ;

Type dropped.

SQL>
SQL>








32.7.Object Column
32.7.1.Using Object Types to Define Column Objects and Object Tables
32.7.2.You can use an object type to define an entire table, and the table is known as an object table.
32.7.3.The Object Type Column Objects
32.7.4.Loading the 'row object' Table
32.7.5.SELECT with a WHERE Clause
32.7.6.UPDATE Data in a Table of Row Objects
32.7.7.Using UPDATE with TYPEed Columns
32.7.8.Query a table with user-defined column type
32.7.9.Use * to reference all columns from a table
32.7.10.Use 'table of custom type' as table column type
32.7.11.Nested varray and table collection column
32.7.12.Create type and use it as table column
32.7.13.Nested type Column
32.7.14.Create a new type and add it to a table