UPDATE Data in a Table of Row Objects : Object Column « Object Oriented « Oracle PL/SQL Tutorial






SQL>
SQL> CREATE OR REPLACE TYPE addressType 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 addressType)
  4  /

Table created.

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

1 row created.

SQL>
SQL> select * from emp;

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

SQL>
SQL>
SQL> CREATE TABLE address_table OF addressType;

Table created.

SQL>
SQL>
SQL> INSERT INTO address_table VALUES ('4 D St.', 'Gulf','FL','32563')
  2
SQL>
SQL> UPDATE address_table
  2  SET zip = '0000'
  3  WHERE zip = '32563';

0 rows updated.

SQL>
SQL> UPDATE address_table
  2  SET street = 'AAAAAAA'
  3  WHERE city LIKE 'Mob%'
  4  SELECT *
  5  FROM address_table
  6
SQL>
SQL> SELECT *
  2  FROM address_table
  3
SQL>
SQL>
SQL> drop table address_table;

Table dropped.

SQL> drop table emp;

Table dropped.

SQL> drop type addresstype;

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