Examples of PL/SQL Type Inheritance

-- Create a supertype from which several subtypes will be derived.

CREATE TYPE Person_typ AS OBJECT ( ssn NUMBER, name VARCHAR2(30),
Defining Object Types
address VARCHAR2(100)) NOT FINAL;

-- Derive a subtype that has all the attributes of the supertype,
-- plus some additional attributes.

CREATE TYPE Student_typ UNDER Person_typ ( deptid NUMBER, major
VARCHAR2(30)) NOT FINAL;
-- Because Student_typ is declared NOT FINAL, you can derive
-- further subtypes from it.
CREATE TYPE PartTimeStudent_typ UNDER Student_typ( numhours NUMBER);

-- Derive another subtype. Because it has the default attribute
-- FINAL, you cannot use Employee_typ as a supertype and derive
-- subtypes from it.
CREATE TYPE Employee_typ UNDER Person_typ( empid NUMBER, mgr
VARCHAR2(30));

-- Define an object type that can be a supertype. Because the
-- member function is FINAL, it cannot be overridden in any
-- subtypes.
CREATE TYPE T AS OBJECT (..., MEMBER PROCEDURE Print(), FINAL MEMBER
FUNCTION foo(x NUMBER)...) NOT FINAL;

-- We never want to create an object of this supertype. By using
-- NOT INSTANTIABLE, we force all objects to use one of the subtypes
-- instead, with specific implementations for the member functions.
CREATE TYPE Address_typ AS OBJECT(...) NOT INSTANTIABLE NOT FINAL;

-- These subtypes can provide their own implementations of
-- member functions, such as for validating phone numbers and
-- postal codes. Because there is no "generic" way of doing these
-- things, only objects of these subtypes can be instantiated.
CREATE TYPE USAddress_typ UNDER Address_typ(...);
CREATE TYPE IntlAddress_typ UNDER Address_typ(...);

-- Return REFs for those Person_typ objects that are instances of
-- the Student_typ subtype, and NULL REFs otherwise.
SELECT TREAT(REF(p) AS REF Student_typ) FROM Person_v p;

-- Example of using TREAT for assignment...
-- Return REFs for those Person_type objects that are instances of
-- Employee_type or Student_typ, or any of their subtypes.
SELECT REF(p) FROM Person_v P WHERE VALUE(p) IS OF (Employee_typ,
Student_typ);

-- Similar to above, but do not allow any subtypes of Student_typ.
SELECT REF(p) FROM Person_v p WHERE VALUE(p) IS OF(ONLY
Student_typ);

-- The results of REF and DEREF can include objects of Person_typ
-- and its subtypes such as Employee_typ and Student_typ.
SELECT REF(p) FROM Person_v p;
SELECT DEREF(REF(p)) FROM Person_v p;