Oracle PL/SQL Tutorial/Object Oriented/Object Column
Содержание
- 1 Create a new type and add it to a table
- 2 Create type and use it as table column
- 3 Loading the "row object" Table
- 4 Nested type Column
- 5 Nested varray and table collection column
- 6 Query a table with user-defined column type
- 7 SELECT with a WHERE Clause
- 8 The Object Type Column Objects
- 9 UPDATE Data in a Table of Row Objects
- 10 Use "table of custom type" as table column type
- 11 Use * to reference all columns from a table
- 12 Using Object Types to Define Column Objects and Object Tables
- 13 Using UPDATE with TYPEed Columns
- 14 You can use an object type to define an entire table, and the table is known as an object table.
Create a new type and add it to a table
<source lang="sql">
SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> create table e
2 as 3 select empno, ename, init, mgr, deptno 4 from emp;
SQL> SQL> SQL> create or replace type numberlist_t
2 as varray(4) of varchar2(20); 3 /
Type created. SQL> SQL> column numlist format a60 SQL> SQL> alter table e add (numlist numberlist_t); Table altered. SQL> SQL> describe e
Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(8) INIT VARCHAR2(5) JOB VARCHAR2(8) MGR NUMBER(4) BDATE DATE SAL NUMBER(6,2) COMM NUMBER(6,2) DEPTNO NUMBER(2) NUMLIST NUMBERLIST_T
SQL> SQL> SQL> select empno, numlist from e; SQL> SQL> SQL> drop table e; Table dropped. SQL> drop table emp; Table dropped. SQL></source>
Create type and use it as table column
<source lang="sql">
SQL> create or replace type addressType as object
2 ( 3 Street VARCHAR2(50), 4 City VARCHAR2(25), 5 State CHAR(2), 6 Zip NUMBER 7 ); 8 /
SQL> create table CUSTOMER
2 ( 3 Name VARCHAR2(25), 4 Address addressType 5 );
SQL> /
SQL> insert into CUSTOMER values(1,addressType("My Street", "Some City", "ST", 10001)); SQL> SQL> drop type addressType force; SQL> drop table customer;</source>
Loading the "row object" Table
One way is to use the existing ADDRESS_OBJ values in some other table (e.g., Emp) like this:
<source lang="sql">
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",
2 addressType("1 A St.","Mobile","AL","36608"))
3
SQL> select * from emp; no rows selected SQL> SQL> SQL> CREATE TABLE address_table OF addressType; Table created. SQL> SQL> SQL> INSERT INTO Address_table
2 SELECT e.address 3 FROM emp e;
0 rows created. SQL> SQL> drop table address_table; Table dropped. SQL> drop table emp; Table dropped. SQL> SQL> drop type addresstype; Type dropped. SQL></source>
Nested type Column
<source lang="sql">
SQL> create or replace type addressType as object
2 ( 3 Street VARCHAR2(50), 4 City VARCHAR2(25), 5 State CHAR(2), 6 Zip NUMBER 7 ); 8 /
SQL> create or replace type personType as object
2 ( 3 Name VARCHAR2(25), 4 Address addressType 5 ); 6 /
SQL> create table CUSTOMER
2 ( 3 cid NUMBER, 4 Person personType 5 );
SQL> SQL> insert into CUSTOMER values(444, personType("Name",addressType("1 RD", "Van", "MA", "10002"))); SQL> SQL> SQL> drop table customer; SQL> drop type personType; SQL> drop type addressType; SQL></source>
Nested varray and table collection column
<source lang="sql">
SQL> SQL> create or replace type myScalarType as object( x int, y date, z varchar2(25) );
2 /
Type created. SQL> SQL> create or replace type myArrayType as varray(25) of myScalarType
2 /
Type created. SQL> SQL> create or replace type myTableType as table of myScalarType
2 /
Type created. SQL> SQL> create table t(a int,b myArrayType,c myTableType)nested table c store as c_tbl
2 /
Table created. SQL> SQL> insert into t values ( 1,
2 myArrayType( myScalarType( 2, sysdate, "hello" ) ), 3 myTableType( myScalarType( 3, sysdate, "GoodBye" ) ) 4 );
1 row created. SQL> drop table t; Table dropped. SQL> drop type myTableType; Type dropped. SQL> drop type myArrayType; Type dropped. SQL> drop type myScalarType; Type dropped.</source>
Query a table with user-defined column type
<source lang="sql">
SQL> SQL> SQL> create or replace type address_type as object
2 ( city varchar2(30), 3 street varchar2(30), 4 state varchar2(2), 5 zip number 6 ) 7 /
Type created. SQL> create or replace type person_type as object
2 ( name varchar2(30), 3 dob date, 4 home_address address_type, 5 work_address address_type 6 ) 7 /
Type created. SQL> create table people of person_type
2 /
Table created. SQL> insert into people values ( "Tom", "15-mar-1965",
2 address_type( "R", "1 Street", "Va", "45678" ), 3 address_type( "R", "1 Way", "Ca", "23456" ) );
1 row created. SQL> / 1 row created. SQL> SQL> select * from people; NAME DOB
---------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
Tom 15-MAR-65 ADDRESS_TYPE("R", "1 Street", "Va", 45678) ADDRESS_TYPE("R", "1 Way", "Ca", 23456) Tom 15-MAR-65 ADDRESS_TYPE("R", "1 Street", "Va", 45678) ADDRESS_TYPE("R", "1 Way", "Ca", 23456) NAME DOB
---------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
SQL> SQL> drop table people; Table dropped. SQL> drop type person_type; Type dropped. SQL> drop type address_type; Type dropped.</source>
SELECT with a WHERE Clause
<source lang="sql">
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></source>
The Object Type Column Objects
A "column object" is an entity that can be used as a column in an Oracle table.
Column objects usually consist of columns defined with predefined types.
<source lang="sql">
SQL> --CREATE a TABLE with the Column Type in It 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 (
2 empno NUMBER(3), 3 name VARCHAR2(20), 4 address ADDRESS_OBJ) 5 /
Table created. SQL> SQL> drop table emp; Table dropped. SQL> drop type ADDRESS_OBJ; Type dropped. SQL></source>
UPDATE Data in a Table of Row Objects
<source lang="sql">
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></source>
Use "table of custom type" as table column type
<source lang="sql">
SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace type emp_type
2 as object 3 (empno number(4), 4 ename varchar2(10), 5 job varchar2(9), 6 mgr number(4), 7 hiredate date, 8 sal number(7, 2), 9 comm number(7, 2) 10 ); 11 /
Type created. SQL> SQL> create or replace type emp_tab_type
2 as table of emp_type 3 /
Type created. SQL> create table dept_and_emp
2 (deptno number(2) primary key, 3 dname varchar2(14), 4 loc varchar2(13), 5 emps emp_tab_type 6 ) 7 nested table emps store as emps_nt;
Table created. SQL> SQL> alter table emps_nt add constraint emps_empno_unique
2 unique(empno) 3 /
Table altered.
SQL> SQL> insert into dept_and_emp
2 select dept.*, 3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm 4 from emp 5 where emp.deptno = dept.deptno ) AS emp_tab_type ) 6 from dept 7 /
4 rows created. SQL> SQL> SQL> SQL> select d.deptno, d.dname, emp.*
2 from dept_and_emp D, table(d.emps) emp 3 / DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------- ---------- ---------- --------- ---------- --------- ---------- ----------
10 ACCOUNTING 7782 CLARK MANAGER 7839 09-JUN-81 2450
10 ACCOUNTING 7839 KING PRESIDENT 17-NOV-81 5000
10 ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-82 1300
20 RESEARCH 7369 SMITH CLERK 7902 17-DEC-80 800
20 RESEARCH 7566 JONES MANAGER 7839 02-APR-81 2975
20 RESEARCH 7788 SCOTT ANALYST 7566 09-DEC-82 3000
20 RESEARCH 7876 ADAMS CLERK 7788 12-JAN-83 1100
20 RESEARCH 7902 FORD ANALYST 7566 03-DEC-81 3000
30 SALES 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 SALES 7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 SALES 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------- ---------- ---------- --------- ---------- --------- ---------- ----------
30 SALES 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 SALES 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 SALES 7900 JAMES CLERK 7698 03-DEC-81 950
14 rows selected. SQL> SQL> SQL> SQL> drop table dept cascade constraint; Table dropped. SQL> drop table emp; Table dropped. SQL> drop table dept_and_emp; Table dropped. SQL> drop type emp_tab_type; Type dropped. SQL> drop type emp_type; Type dropped. SQL></source>
Use * to reference all columns from a table
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace type emp_type
2 as object 3 (empno number(4), 4 ename varchar2(10), 5 job varchar2(9), 6 mgr number(4), 7 hiredate date, 8 sal number(7, 2), 9 comm number(7, 2) 10 ); 11 /
Type created. SQL> SQL> create or replace type emp_tab_type
2 as table of emp_type 3 /
Type created. SQL> create table dept_and_emp
2 (deptno number(2) primary key, 3 dname varchar2(14), 4 loc varchar2(13), 5 emps emp_tab_type 6 ) 7 nested table emps store as emps_nt;
Table created. SQL> SQL> alter table emps_nt add constraint emps_empno_unique
2 unique(empno) 3 /
Table altered.
SQL> SQL> insert into dept_and_emp
2 select dept.*, 3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm 4 from emp 5 where emp.deptno = dept.deptno ) AS emp_tab_type ) 6 from dept 7 /
4 rows created. SQL> SQL> select d.deptno, d.dname, emp.*
2 from dept_and_emp D, table(d.emps) emp 3 / DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------- ---------- ---------- --------- ---------- --------- ---------- ----------
10 ACCOUNTING 7782 CLARK MANAGER 7839 09-JUN-81 2450
10 ACCOUNTING 7839 KING PRESIDENT 17-NOV-81 5000
10 ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-82 1300
20 RESEARCH 7369 SMITH CLERK 7902 17-DEC-80 800
20 RESEARCH 7566 JONES MANAGER 7839 02-APR-81 2975
20 RESEARCH 7788 SCOTT ANALYST 7566 09-DEC-82 3000
20 RESEARCH 7876 ADAMS CLERK 7788 12-JAN-83 1100
20 RESEARCH 7902 FORD ANALYST 7566 03-DEC-81 3000
30 SALES 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 SALES 7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 SALES 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------- ---------- ---------- --------- ---------- --------- ---------- ----------
30 SALES 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 SALES 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 SALES 7900 JAMES CLERK 7698 03-DEC-81 950
14 rows selected. SQL> SQL> drop table dept cascade constraint; Table dropped. SQL> drop table emp; Table dropped. SQL> drop table dept_and_emp; Table dropped. SQL> drop type emp_tab_type; Type dropped. SQL> drop type emp_type; Type dropped. SQL> SQL></source>
Using Object Types to Define Column Objects and Object Tables
You can use an object type to define a column in a table.
The column is known as a column object.
If an object type contains an embedded object type, that embedded object type is also a column object.
The following example creates a table that contains a column object of EmployeeType.
<source lang="sql">
SQL> SQL> CREATE Or Replace TYPE EmployeeType AS OBJECT (
2 id NUMBER, 3 name VARCHAR2(15), 4 description VARCHAR2(22), 5 salary NUMBER(5, 2) 6 ); 7 /
Type created. SQL> CREATE TABLE employee (
2 employee EmployeeType, 3 count NUMBER 4 );
Table created. SQL> / SQL> SQL> desc employee;
Name Null? Type EMPLOYEE EMPLOYEETYPE ID NUMBER NAME VARCHAR2(15) DESCRIPTION VARCHAR2(22) SALARY NUMBER(5,2) COUNT NUMBER
SQL> SQL> drop table employee; Table dropped.</source>
Using UPDATE with TYPEed Columns
To use UPDATE, the alias must also be used:
<source lang="sql">
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 (
2 empno NUMBER(3), 3 name VARCHAR2(20), 4 address ADDRESS_OBJ) 5 /
Table created. SQL> SQL> INSERT INTO emp VALUES (101, "A",ADDRESS_OBJ("1 St.","M","AL","36608")); 1 row created. SQL> SQL> select * from emp;
EMPNO NAME ADDRESS(STREET, CITY, STATE, ZIP)
-------------------- --------------------------------------------------
101 A ADDRESS_OBJ("1 St.", "M", "AL", "36608")
SQL> SQL> UPDATE emp e
2 SET e.address.zip = "34210" 3 WHERE e.address.city LIKE "A%" 4
SQL> select * from emp;
EMPNO NAME ADDRESS(STREET, CITY, STATE, ZIP)
-------------------- --------------------------------------------------
101 A ADDRESS_OBJ("1 St.", "M", "AL", "36608")
SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop type ADDRESS_OBJ; Type dropped. SQL> SQL></source>
You can use an object type to define an entire table, and the table is known as an object table.
<source lang="sql">
SQL> SQL> SQL> CREATE or replace TYPE EmployeeType AS OBJECT (
2 id NUMBER, 3 name VARCHAR2(15), 4 description VARCHAR2(22), 5 salary NUMBER(5, 2) 6 ); 7 /
Type created. SQL> CREATE TABLE object_employee OF EmployeeType; Table created. SQL> SQL> desc object_employee;
Name Null? Type ID NUMBER NAME VARCHAR2(15) DESCRIPTION VARCHAR2(22) SALARY NUMBER(5,2)
SQL> SQL> drop table object_employee; Table dropped. SQL> SQL> SQL></source>
