use user-defined type as the column type : Object Column « Object Oriented Database « Oracle PL / SQL






use user-defined type as the column type

     
SQL>
SQL> create table dept
  2    (deptno number(2) primary key,
  3     dname     varchar2(14),
  4     loc       varchar2(13)
  5    );

Table created.

SQL>
SQL>
SQL> create table emp
  2    (empno       number(4) primary key,
  3     ename       varchar2(10),
  4     job         varchar2(9),
  5     mgr         number(4) references emp,
  6     hiredate    date,
  7     sal         number(7, 2),
  8     comm        number(7, 2),
  9     deptno      number(2) references dept
 10    );

Table created.

SQL>
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  /

SQL>
SQL> create or replace type emp_tab_type as table of emp_type;
  2  /


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;


SQL>
SQL>
SQL> drop table dept_and_emp;

Table dropped.

SQL> drop table emp cascade constraints;

Table dropped.

SQL> drop table dept cascade constraints;

Table dropped.

SQL> drop type emp_tab_type;

Type dropped.

SQL> drop type emp_type;

Type dropped.

SQL>
SQL>
SQL> --

   
    
    
    
  








Related examples in the same category

1.The Object Type Column Objects
2.Format column in the object
3.Alter a table with user-defined object to upgrade including data
4.Query column with user-defined type
5.Check object table column type
6.Use varray in a table
7.Nested type Column
8.This script demonstrates column objects.
9.Reference nested data type in select statement
10.Reference type column