继承／覆盖／重载
----------------------------------------------------------对象的继承,注意：UNDER DROP TYPE HUMANCREATE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), -- M :MALE F:FEMALE BIRTHDAY DATE) NOT FINAL;
CREATE TYPE EMPLOYEE_TYPE UNDER HUMAN( EMP_NO VARCHAR2(20), DEPT_NO VARCHAR2(20), JOB_TITLE VARCHAR2(60))------------------------------------------------------------下面体现 NOT FINAL 的作用： DROP TYPE HUMANDROP TYPE EMPLOYEE_TYPECREATE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE)
CREATE TYPE EMPLOYEE_TYPE UNDER HUMAN( EMP_NO VARCHAR2(20), DEPT_NO VARCHAR2(20), JOB_TITLE VARCHAR2(60))
/* 对象类型默认为 FINAL ,即不能派生子类．由于HUMAN 没有加：NOT FINAL ，所以，它不能作为另一个类型的超类，在创建EMPLOYEE_TYPE 的时候，产生一个错误：PLS-00590: 正在尝试创建一个最终类型的子类型 */
------------------------------------------------------------ 对象类型的方法也是可以继承的．DROP TYPE EMPLOYEE_TYPEDROP TYPE HUMAN
CREATE OR REPLACE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE, MEMBER FUNCTION GET_AGE RETURN NUMBER) NOT FINAL;
CREATE OR REPLACE TYPE BODY HUMAN AS MEMBER FUNCTION GET_AGE RETURN NUMBER AS V_NUM NUMBER; BEGIN SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,TO_DATE('20060601','YYYYMMDD'))) INTO V_NUM FROM DUAL; RETURN V_NUM; END;END;
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('20060601','YYYYMMDD')) FROM DUAL;
CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN( EMP_NO VARCHAR2(20), DEPT_NO VARCHAR2(20))
--存储过程 SET SERVEROUT ONDECLARE EMP1 EMPLOYEE_TYPE := EMPLOYEE_TYPE('xling','M',TO_DATE('20060601','YYYYMMDD'),'2060006','DPT0201');BEGIN DBMS_OUTPUT.PUT_LINE(EMP1.GET_AGE());END;
--表DROP TABLE EMPLOYEE
CREATE TABLE EMPLOYEE( GUID NUMBER NOT NULL, EMP EMPLOYEE_TYPE)
CREATE OR REPLACE TRIGGER EMPLOYEE_T_I1BEFORE INSERT ON EMPLOYEE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROWBEGIN :NEW.GUID := XLING_PKG_TOOLS.F_GET_NEXTVAL('EMPLOYEE');END;
CREATE OR REPLACE TRIGGER EMPLOYEE_T_D1BEFORE DELETE ON EMPLOYEE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROWBEGIN XLING_PKG_TOOLS.P_DELETE_GUID(:OLD.GUID);END;
INSERT INTO EMPLOYEE (EMP) VALUES (EMPLOYEE_TYPE('xling','M',TO_DATE('20060601','YYYYMMDD'),'2060006','DPT0201'));INSERT INTO EMPLOYEE (EMP) VALUES (EMPLOYEE_TYPE('snow','F',TO_DATE('20000601','YYYYMMDD'),'2060001','DPT0301'));INSERT INTO EMPLOYEE (EMP) VALUES (EMPLOYEE_TYPE('werewi','M',TO_DATE('20050601','YYYYMMDD'),'2060007','DPT0401'));
--提示错误：ORA-22905: 无法从非嵌套表项访问行-- TABLE 子句只能用在嵌套表？意思上有点像SELECT GUID, I.NAME,I.SEX,I.BIRTHDAY,I.GET_AGE(),I.DEPT_NO,I.EMP_NO FROM EMPLOYEE E, TABLE(E.EMP)
--SELECT GUID, E.EMP.NAME,E.EMP.SEX,E.EMP.BIRTHDAY,E.EMP.GET_AGE(),E.EMP.EMP_NO,E.EMP.DEPT_NOFROMEMPLOYEE E------------------------------------------------------------------------------------------抽象类型，注意：NOT FINAL NOT INSTANTIABLE DROP TABLE EMPLOYEEDROP TYPE EMPLOYEE_TYPE
CREATE OR REPLACE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE, MEMBER FUNCTION GET_AGE RETURN NUMBER) NOT FINAL NOT INSTANTIABLE
CREATE OR REPLACE TYPE BODY HUMAN AS MEMBER FUNCTION GET_AGE RETURN NUMBER AS V_NUM NUMBER; BEGIN SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,TO_DATE('20060601','YYYYMMDD'))) INTO V_NUM FROM DUAL; RETURN V_NUM; END;END;
/*现在试图声明一个抽象类的实例，产生如下错误．PLS-00713: 正在试图实例化一种 NOT INSTANTIABLE 的类型 */DECLARE AMAN HUMAN := HUMAN('xling','M',TO_DATE('20060601','YYYYMMMDD'));BEGIN DBMS_OUTPUT.PUT_LINE(AMAN.GET_AGE());END;
---------------------------------------------------------抽象方法：拥有抽象方法的类型必须为抽象类型，抽象方法不能有主体
/*这样声明是不对的，因为具有抽象方法的对象类型必须是抽象类型*/CREATE OR REPLACE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE, NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER) NOT FINAL NOT INSTANTIABLE
--DROP TYPE HUMAN
CREATE OR REPLACE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE, NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER) NOT FINAL NOT INSTANTIABLE
/*抽象类型也不能有主体，如下会提示：PLS-00632: NOT INSTANTIABLE 方法不能具有主体*/CREATE OR REPLACE TYPE BODY HUMAN AS NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER AS BEGIN NULL; END; END;DROP TYPE BODY HUMAN
/*只能为非 NOT INSTANTIABLE 的方法创建方法体*/DROP TYPE HUMAN
CREATE OR REPLACE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE, NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER, MEMBER FUNCTION TT RETURN NUMBER) NOT FINAL NOT INSTANTIABLE
CREATE OR REPLACE TYPE BODY HUMAN AS MEMBER FUNCTION TT RETURN NUMBER AS V_NUM NUMBER; BEGIN SELECT MONTHS_BETWEEN(SYSDATE,BIRTHDAY) INTO V_NUM FROM DUAL; RETURN V_NUM; END;END;
/*在子类里实现超类的抽象方法，注意：OVERRIDING */
CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN( EMP_NO VARCHAR2(20), DEPT_NO VARCHAR2(20), OVERRIDING MEMBER FUNCTION GET_AGE RETURN NUMBER )
CREATE OR REPLACE TYPE BODY EMPLOYEE_TYPE AS OVERRIDING MEMBER FUNCTION GET_AGE RETURN NUMBER AS V_NUM NUMBER; BEGIN SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL; RETURN V_NUM; END;END;
SET SERVEROUT ONDECLARE EMP1 EMPLOYEE_TYPE:= EMPLOYEE_TYPE('xling','M',TO_DATE('19840601','YYYYMMDD'),'2060006','DPT0201');BEGIN DBMS_OUTPUT.PUT_LINE(EMP1.GET_AGE());END;
/*覆盖并不限于 NOT INSTANTIABLE 方法，子类可以覆盖所有超类的方法，除非在超类的方法中用 FINAL 限制 */
DROP TYPE EMPLOYEE_TYPEDROP TYPE HUMAN
CREATE OR REPLACE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE, FINAL MEMBER FUNCTION GET_AGE RETURN NUMBER) NOT FINAL
CREATE OR REPLACE TYPE BODY HUMAN AS FINAL MEMBER FUNCTION GET_AGE RETURN NUMBER AS V_NUM NUMBER; BEGIN SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL; RETURN V_NUM; END;END;
/*这里试图覆盖掉超类里的 FINAL 方法,提示错误：PLS-00637:无法覆盖FINAL 方法*/
CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN( EMP_NO VARCHAR2(20), DEPT_NO VARCHAR2(20), OVERRIDING MEMBER FUNCTION GET_AGE RETURN NUMBER)
-----------------------------------------------------------------------------方法的重载DROP TYPE EMPLOYEE_TYPEDROP TYPE HUMAN
CREATE OR REPLACE TYPE HUMAN AS OBJECT( NAME VARCHAR2(20), SEX VARCHAR2(1), BIRTHDAY DATE, MEMBER FUNCTION GET_AGE RETURN NUMBER, MEMBER FUNCTION GET_AGE(I_END_DATE IN DATE) RETURN NUMBER)
CREATE OR REPLACE TYPE BODY HUMAN AS MEMBER FUNCTION GET_AGE RETURN NUMBER AS V_NUM NUMBER; BEGIN SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL; RETURN V_NUM; END; MEMBER FUNCTION GET_AGE(I_END_DATE IN DATE) RETURN NUMBER AS V_NUM NUMBER; BEGIN SELECT FLOOR(MONTHS_BETWEEN(I_END_DATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL; RETURN V_NUM; END;END;
SET SERVEROUT ONDECLARE V_HUMAN HUMAN := HUMAN('xling','M',TO_DATE('19830714','YYYYMMDD'));BEGIN DBMS_OUTPUT.PUT_LINE('Method 1:' || V_HUMAN.GET_AGE()); DBMS_OUTPUT.PUT_LINE('Method 2:' || V_HUMAN.GET_AGE(TO_DATE('20000101','YYYYMMDD')));END;
----------------------------------------------------------------------------------------------将FINAL 对象对型改为可继承,CASCADE 选项指定应该如何处理从属对象及其数据，ALTER TYPE HUMAN NOT FINAL CASCADE
CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN( EMP_NO VARCHAR2(20), DEPT_NO VARCHAR2(20))
SET SERVEROUT ONDECLARE V_EMP EMPLOYEE_TYPE := EMPLOYEE_TYPE('xling','M',TO_DATE('18900714','YYYYMMDD'),'2060006','DPT0201');BEGIN DBMS_OUTPUT.PUT_LINE('Method 1:' || V_EMP.GET_AGE()); DBMS_OUTPUT.PUT_LINE('Method 2:' || V_EMP.GET_AGE(TO_DATE('20000101','YYYYMMDD')));END;