Oracle PL/SQL - PL SQL Data Type Object types

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

Introduction

In an object-oriented environment, you can describe the whole thing as an object called EMP:

Demo

SQL>
SQL> create type emp_oty is object (
  2        empNo    NUMBER,
  3        eName    VARCHAR2(10),-- w w  w .  j  av a2s  .  co  m
  4        job      VARCHAR2(9),
  5        mgr      NUMBER,
  6        hireDate DATE,
  7        sal      NUMBER,
  8        comm     NUMBER,
  9        deptNo   NUMBER,
 10        member procedure p_changeName (i_newName_tx VARCHAR2),
 11        member function       f_getIncome_nr  return VARCHAR2
 12  );
 13  /
create type emp_oty is object (
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object


Elapsed: 00:00:00.02
SQL> create or replace type body emp_oty as
  2        member function f_getIncome_nr return VARCHAR2
  3        is
  4        begin
  5              return sal+comm;
  6        end f_getIncome_nr;
  7        member procedure p_changeName
  8             (i_newName_tx VARCHAR2)
  9        is
 10        begin
 11              eName:=i_newName_tx;
 12        end p_changeName;
 13  end;
 14  /

Type body created.
SQL>

You can prefix it with CREATE or REPLACE and execute it in the same way as procedures or functions.

You can manipulate objects with the same standard DDL commands: 

drop type emp_oty; -- drop type
alter type emp_oty add attribute birthdate_dt DATE; -- add attribute
alter type emp_oty drop attribute birthdate_dt DATE; -- drop attribute

Demo

SQL>
SQL> declare-- from  w  w w  .  j a v a  2 s.c  om
  2        v_emp_oty emp_oty;
  3  begin
  4        v_emp_oty:=emp_oty (100,
  5                            'TestEmp',
  6                            null,
  7                            null,
  8                            sysdate,
  9                            1000,
 10                            500,
 11                            10);
 12         v_emp_oty.sal:=v_emp_oty.sal+500;
 13         DBMS_OUTPUT.put_line('Employee:'||v_emp_oty.eName||' has income '||v_emp_oty.f_getIncome_nr());
 14
 15  end;
 16  /
Employee:TestEmp has income 2000

PL/SQL procedure successfully completed.
SQL>

Related Topics