Create instance of nested user-defined type : Type Combination « Object Oriented Database « Oracle PL / SQL

1. Oracle PL / SQL
2. Object Oriented Database
3. Type Combination

Create instance of nested user-defined type

 
SQL>
SQL> create or replace
  2  type person as object(
  3   first_name varchar2(100),
  4   last_name varchar2(100) )
  5  /

Type created.

SQL>
SQL>
SQL>
SQL> alter type person
  2  add attribute dob date
  3  cascade not including table data
  4  /

Type altered.

SQL>
SQL> create or replace
  2  type employee as object(
  3   name person,
  4   empno number,
  5   hiredate date,
  6   sal number,
  7   commission number,
  8   order member function match ( p_employee employee ) return integer )
  9  /

Type created.

SQL>
SQL> create or replace
  2  type body employee as
  3   order member function match ( p_employee employee ) return integer is
  4   begin
  5    if self.empno > p_employee.empno then
  6      return 1;
  7    elsif self.empno < p_employee.empno then
  8      return -1;
  9    else
 10      return 0;
 11    end if;
 12   end;
 13  end;
 14  /

Type body created.

SQL>
SQL> declare
  2   l_emp employee;
  3  begin
  4   l_emp := employee( person( 'Tom', 'K', sysdate ), 12345, '01-JAN-01',1,1);
  5   dbms_output.put_line( 'Empno: ' || l_emp.empno );
  6   dbms_output.put_line( 'First Name: ' || l_emp.name.first_name );
  7   end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop type employee;

Type dropped.

SQL>
SQL> drop type person;

Type dropped.

SQL>
SQL>

 

Related examples in the same category