CREATE TYPE Address_Type AS OBJECT (country VARCHAR2(20), town VARCHAR2(20), street VARCHAR2(20));
/
CREATE TYPE Telephone_List_Type AS VARRAY(3) OF VARCHAR2(20);
/
CREATE TYPE Person_Type AS OBJECT(name VARCHAR2(20), address Address_Type, tel Telephone_List_Type) NOT FINAL;
/
CREATE TYPE Student_Type UNDER Person_Type ();
/
CREATE TYPE Employee_Type UNDER Person_Type (
 salary NUMBER(10),
 office_number VARCHAR2(10),
 boss REF Employee_Type,
 MEMBER FUNCTION yearly_salary RETURN NUMBER
 );
/
CREATE TYPE BODY Employee_Type IS
	MEMBER FUNCTION yearly_salary RETURN NUMBER IS
	BEGIN
		RETURN 12*salary;
	END;
END;
/	

CREATE TABLE Persons OF Person_Type;
CREATE TABLE Students OF Student_Type;
CREATE TABLE Employees OF Employee_Type (
 boss SCOPE IS Employees
);


INSERT INTO Students VALUES('Mueller',Address_Type('DE','MD','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Students VALUES('Knueller',Address_Type('DE','HD','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Persons VALUES('Fueller',Address_Type('DE','Berlin','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Persons VALUES('Brueller',Address_Type('DE','MD','Hauptstrasse'),Telephone_List_Type('039111','017211'));
INSERT INTO Employees VALUES('John',Address_Type('DE','MD','Hauptstrasse'),Telephone_List_Type('039111','017211'),'1100','067', null);
INSERT INTO Employees VALUES('Marie',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', (SELECT REF(e) FROM Employees e WHERE e.name='John'));
INSERT INTO Employees VALUES('Peter',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', (SELECT REF(e) FROM Employees e WHERE e.name='John'));
INSERT INTO Employees VALUES('Felix',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', (SELECT REF(e) FROM Employees e WHERE e.name='Peter'));
INSERT INTO Employees VALUES('Evelyn',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', (SELECT REF(e) FROM Employees e WHERE e.name='Peter'));
INSERT INTO Employees VALUES('Charles',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', (SELECT REF(e) FROM Employees e WHERE e.name='Marie'));

INSERT INTO Employees VALUES('Paul',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', null);
INSERT INTO Employees VALUES('George',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', (SELECT REF(e) FROM Employees e WHERE e.name='Paul'));
INSERT INTO Employees VALUES('Keith',Address_Type('DE','Koeln','Hauptstrasse'),Telephone_List_Type('039111','017211'),'2000','100', (SELECT REF(e) FROM Employees e WHERE e.name='Paul'));


CREATE VIEW PersonsView OF Person_Type AS SELECT * FROM Persons p WHERE p.address.town='MD';
CREATE VIEW StudentsView OF Student_Type UNDER PersonsView AS SELECT * FROM Students s WHERE s.address.town='MD';
CREATE VIEW EmployeesView OF Employee_Type UNDER PersonsView AS SELECT * FROM Employees e WHERE e.address.town='MD';

SELECT e.name, e.salary, e.yearly_salary() FROM Employees e;

SELECT e.name, e.boss.name FROM Employees e;

SELECT e.name, e.boss.name, LEVEL FROM Employees e START WITH e.name='John' CONNECT BY PRIOR e.name = e.boss.name ORDER BY LEVEL;

-- That doesn't work :-(
-- SELECT e.name, e.boss.name, LEVEL FROM Employees e START WITH e.name='John' CONNECT BY PRIOR REF(e) = e.boss ORDER BY LEVEL;

SELECT * FROM PersonsView;



DROP VIEW EmployeesView;
DROP VIEW PersonsView;
DROP VIEW StudentsView;

DROP TABLE Employees;
DROP TABLE Students;
DROP TABLE Persons;

DROP TYPE BODY Employee_Type;
DROP TYPE Employee_Type;
DROP TYPE Student_Type;
DROP TYPE Person_Type;
DROP TYPE Telephone_List_Type;
DROP TYPE Address_Type;