How to override the ORDER PL / SQL method?

Question

I need to determine the type of the Employee object that will have some attributes, then I have to define the type manager of the object that inherits the Employee type and will have the aditional attribute called nrEmp, which will contain the number of employees each manager has under his command. I also have to implement the ORDER method for the type manager, so I can order managers by the number of employees they have. First I defined this type:

CREATE OR REPLACE TYPE Departament AS OBJECT ( deptno NUMBER(2), dname CHAR(14) ); 

Next, I defined the type of Employee:

 CREATE OR REPLACE TYPE Employee AS OBJECT ( empno NUMBER(4), ename CHAR(10), dept REF Departament, sal NUMBER(7,2) ) NOT FINAL; 

So far, everything has been working fine. Then I will try to determine the type of Manager:

 CREATE OR REPLACE TYPE Manager UNDER Employee ( nrEmp INTEGER, ORDER MEMBER FUNCTION compare(m Manager) RETURN INTEGER ); 

When I do this, I get the following error:

 Error(1,1): PLS-00646: MAP or ORDER method must be defined in the root of the subtype hierarchy 

As I understand it, I need to declare a method in type Employee. But I'm not sure how to do it right. Could not find any example showing how to implement the ORDER method on inheritance. Any help would be greatly appreciated. Thanks.

Answer 1

I found a way to do this. I can’t say that this is the best solution or the most elegant, but it worked perfectly for my needs. Here is the code. Employee Type:

 CREATE OR REPLACE TYPE Employee AS OBJECT ( empno NUMBER(4), ename CHAR(10), dept REF Departament, sal NUMBER(7,2), ORDER MEMBER FUNCTION match (other IN Employee) RETURN INTEGER ) NOT FINAL; 

Manager Type:

 CREATE OR REPLACE TYPE Manager UNDER Employee ( nrEmp INTEGER ); 

Body for type Employee:

 CREATE OR REPLACE TYPE BODY Employee AS ORDER MEMBER FUNCTION match(other IN Employee) Return INTEGER IS v_mng_self Manager; v_mng_other Manager; BEGIN v_mng_self := TREAT(self AS Manager); v_mng_other := TREAT(other AS Manager); IF v_mng_self.nrEmp < v_mng_other.nrEmp THEN RETURN -1; ELSIF v_mng_self.nrEmp > v_mng_other.nrEmp THEN RETURN 1; ELSE RETURN 0; END IF; END; END; 

This is all you need to do if you want to compare 2 Manager objects. The ORDER method will drop the type from the Employee to Manager type. For example:

 DECLARE manager1 Manager; manager2 Manager; BEGIN manager1 := Manager(7823,'John',null,2000,10); manager2 := Manager(7782,'Bob',null,3000,15); IF manager1 < manager2 THEN SYS.DBMS_OUTPUT.PUT_LINE('manager1 has less employees than manager2'); END IF; END; 

Remember to set the output in front of the above code block so that you can see the displayed result.

 SET SERVEROUTPUT ON; 

Answer 2

I just had to solve the same problem and solve the following problem:

 create or replace type employee as object( empno number( 4 ), member function compare_internal( e employee ) return integer, order member function compare( e employee ) return integer ) not final; / create or replace type body employee is member function compare_internal( e employee ) return integer is begin return case when self.empno = e.empno then 0 when self.empno > e.empno then 1 when self.empno < e.empno then -1 end; end; order member function compare( e employee ) return integer is begin return compare_internal( e ); end; end; / create or replace type manager under employee( nr_emp integer, overriding member function compare_internal( e employee ) return integer ); / create or replace type body manager is overriding member function compare_internal( e employee ) return integer is m manager; r integer; begin if e is of ( manager ) then m := treat( e as manager ); r := case when self.nr_emp = m.nr_emp then 0 when self.nr_emp > m.nr_emp then 1 when self.nr_emp < m.nr_emp then -1 end; end if; return r; end; end; / 

This allows you to override the order / map functions by overriding the called functions.

 declare x employee; y employee; begin x := employee(empno => 1); y := employee(empno => 1); dbms_output.put_line( x.compare(y) ); -- gives 0, as both have same empno x := manager(empno => 1, nr_emp => 2); y := manager(empno => 1, nr_emp => 3); dbms_output.put_line( x.compare(y) ); -- gives -1 as both have different nr_emp x := employee(empno => 1); y := manager(empno => 1, nr_emp => 3); dbms_output.put_line( x.compare(y) ); -- gives 0, as both have same empno -- is that what we want? x := manager(empno => 1, nr_emp => 3); y := employee(empno => 1); dbms_output.put_line( x.compare(y) ); -- gives null, y is not a manager end;