Oracle PL/SQL - PL SQL Data Type 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),-- from  www .  ja v a 2  s.c om
  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--  w ww  .  j a  v a 2s . c  o  m
  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