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
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>
Create type and use it as table column
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;
Loading the "row object" Table
One way is to use the existing ADDRESS_OBJ values in some other table (e.g., Emp) like this:
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>
Nested type Column
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>
Nested varray and table collection column
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.
Query a table with user-defined column type
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.
SELECT with a WHERE Clause
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>
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.
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>
UPDATE Data in a Table of Row Objects
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>
Use "table of custom type" as table column type
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>
Use * to reference all columns from a table
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>
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.
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.
Using UPDATE with TYPEed Columns
To use UPDATE, the alias must also be used:
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>
You can use an object type to define an entire table, and the table is known as an object table.
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>