Oracle PL/SQL - Create table from object

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Object types

Introduction

You can use objects in SQL.

You have two options. First, you can use object types as attributes in a traditional relational way: 

create or replace type emp_oty is object (
      empNo    NUMBER,
      eName    VARCHAR2(10),
      job      VARCHAR2(9),
      mgr      NUMBER,
      hireDate DATE,
      sal      NUMBER,
      comm     NUMBER,
      deptNo   NUMBER,
      member procedure p_changeName (i_newName_tx VARCHAR2),
      member function       f_getIncome_nr  return VARCHAR2
);
/
create or replace type body emp_oty as
      member function f_getIncome_nr return VARCHAR2
      is
      begin
            return sal+comm;
      end f_getIncome_nr;
      member procedure p_changeName
           (i_newName_tx VARCHAR2)
      is
      begin
            eName:=i_newName_tx;
      end p_changeName;
end;
/

create table t_emp
      (employee emp_oty,
      remarks_tx VARCHAR2(2000));

Alternatively, you can create an object table, where each attribute becomes a column and each row contains a unique object identifier: 

create table t_emp of emp_oty;

Related Topic