Employee->Manager

emp_t

create or replace type emp_t as object(
    employee_id      number(6)   ,
    first_name       varchar2(20),
    last_name        varchar2(25),
    email            varchar2(25),
    phone_number     varchar2(20),
    hire_date        date        ,
    job_id           varchar2(10),
    salary           number(8,2) ,
    commission_pct   number(2,2) ,
    manager_id       number(6)   ,
    department_id    number(4)   ,
    
    constructor function emp_t(self in out nocopy emp_t
                              ,p_emp_id number
                              )
                               return self as result,                               
    member function manager return emp_t,
    map member function get_emp_id return number
); 
/
create or replace type body emp_t as 
    
    constructor function emp_t(self in out nocopy emp_t
                              ,p_emp_id number
                              )
                               return self as result
    is
    begin
       if p_emp_id is not null then
          
          self.employee_id:=p_emp_id;
          select
             e.employee_id       ,e.first_name    ,e.last_name      ,e.email   
            ,e.phone_number      ,e.hire_date     ,e.job_id         ,e.salary     
            ,e.commission_pct    ,e.manager_id    ,e.department_id
           into 
            self.employee_id     ,self.first_name ,self.last_name   ,self.email
           ,self.phone_number    ,self.hire_date  ,self.job_id      ,self.salary
           ,self.commission_pct  ,self.manager_id ,self.department_id
          from hr.employees e
          where e.employee_id = p_emp_id;
       end if;
       return;
    end;
  
    member function manager return emp_t is
    begin
       return emp_t(self.manager_id);
    end;

    map member function get_emp_id return number is
    begin
       return self.employee_id;
    end;

end; 
/
--test:
declare
   emp emp_t;
   mgr emp_t;
   procedure p( msg varchar2) is
   begin
      dbms_output.put(rpad(msg,30));
   end;
begin
   for r in (select emp_t(employee_id) e from hr.employees e) loop
      mgr := r.e.manager;
      p('Employee: '||r.e.first_name||' '||r.e.last_name);
      p('Manager: ' ||mgr.first_name||' '||mgr.last_name);
      dbms_output.new_line;

   end loop;
end;
/