物件導向五(物件表一)(PL/SQL 三十二)

※新增以下五個TYPE來練習

※dept_obj宣告與實作 

CREATE OR REPLACE TYPE dept_obj IS OBJECT(
    attr_deptno NUMBER(2),
    attr_dname VARCHAR2(14),
    attr_loc VARCHAR2(13),
    
    MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;
    
CREATE OR REPLACE TYPE BODY dept_obj IS
    MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_deptno || ':' || SELF.attr_dname || ':' || SELF.attr_loc;
    END;
END;

※emp_obj的抽象父類別emp_papa宣告

CREATE OR REPLACE TYPE emp_papa IS OBJECT(
    attr_empno NUMBER(4),
    attr_ename VARCHAR2(10),
    attr_job VARCHAR2(9),
    
    NOT INSTANTIABLE MEMBER FUNCTION to_string RETURN VARCHAR2,
    NOT INSTANTIABLE MAP MEMBER FUNCTION compare RETURN NUMBER
) NOT FINAL NOT INSTANTIABLE;

※emp_obj宣告與實作

CREATE OR REPLACE TYPE emp_obj UNDER emp_papa(
    attr_sal NUMBER(7,2),
    attr_comm NUMBER(7,2),
    attr_deptno dept_obj,
    
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2,
    OVERRIDING MAP MEMBER FUNCTION compare RETURN NUMBER
) NOT FINAL;
    
CREATE OR REPLACE TYPE BODY emp_obj IS
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_empno || ':' || SELF.attr_ename || ':' || SELF.attr_sal || ':' || SELF.attr_comm;
    END;
    
    OVERRIDING MAP MEMBER FUNCTION compare RETURN NUMBER IS
    BEGIN
        RETURN SELF.attr_sal + NVL(SELF.attr_comm, 0);
    END;
END;

※emp_obj和emp_papa為父子，而dept_obj為emp_obj裡的屬性型態

※新增測試資料練習

CREATE TABLE object_table OF EMP_OBJ;
    
INSERT INTO object_table VALUES (7369,'SMITH','CLERK',800,NULL,dept_obj(20,'RESEARCH','DALLAS'));
INSERT INTO object_table VALUES (7499,'ALLEN','SALESMAN',1600,300,dept_obj(30,'SALES','CHICAGO'));
INSERT INTO object_table VALUES (7521,'WARD','SALESMAN',1250,500,dept_obj(30,'SALES','CHICAGO'));
INSERT INTO object_table VALUES (7566,'JONES','MANAGER',2975,NULL,dept_obj(20,'RESEARCH','DALLAS'));
INSERT INTO object_table VALUES (7654,'MARTIN','SALESMAN',1250,1400,dept_obj(30,'SALES','CHICAGO'));
INSERT INTO object_table VALUES (7698,'BLAKE','MANAGER',2850,NULL,dept_obj(30,'SALES','CHICAGO'));
INSERT INTO object_table VALUES (7782,'CLARK','MANAGER',2450,NULL,dept_obj(10,'ACCOUNTING','NEW YORK'));
INSERT INTO object_table VALUES (7788,'SCOTT','ANALYST',3000,NULL,dept_obj(20,'RESEARCH','DALLAS'));
INSERT INTO object_table VALUES (7839,'KING','PRESIDENT',5000,NULL,dept_obj(10,'ACCOUNTING','NEW YORK'));
INSERT INTO object_table VALUES (7844,'TURNER','SALESMAN',1500,0,dept_obj(30,'SALES','CHICAGO'));
INSERT INTO object_table VALUES (7876,'ADAMS','CLERK',1100,NULL,dept_obj(20,'RESEARCH','DALLAS'));
INSERT INTO object_table VALUES (7900,'JAMES','CLERK',950,NULL,dept_obj(30,'SALES','CHICAGO'));
INSERT INTO object_table VALUES (7902,'FORD','ANALYST',3000,NULL,dept_obj(20,'RESEARCH','DALLAS'));
INSERT INTO object_table VALUES (7934,'MILLER','CLERK',1300,NULL,dept_obj(10,'ACCOUNTING','NEW YORK'));

※排序

SELECT VALUE(ot) v, attr_empno, attr_sal, attr_comm, attr_sal + attr_comm sal_comm 
FROM object_table ot
ORDER BY v DESC;

※之前的物件導向二(排序)(PL/SQL 二十九)講到這裡

※還是可以使用INTO賦值

DECLARE
    xxx emp_obj;
BEGIN
    SELECT VALUE(ot) INTO xxx FROM object_table ot WHERE ot.ATTR_EMPNO = 7521;
    DBMS_OUTPUT.PUT_LINE(xxx.to_string());
    DBMS_OUTPUT.PUT_LINE(xxx.attr_deptno.to_string());
END;

※和游標配合使用

DECLARE
    xxx emp_obj;
    CURSOR xxx_cursor IS SELECT VALUE(ot) v FROM object_table ot;
BEGIN
    FOR i IN xxx_cursor LOOP
        xxx := i.v;
        DBMS_OUTPUT.PUT_LINE(xxx.to_string());
        DBMS_OUTPUT.PUT_LINE(xxx.attr_deptno.to_string() || CHR(10));
    END LOOP;
END;