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

延續上一張的內容

※新增第二個兒子

CREATE OR REPLACE TYPE emp_obj2 UNDER emp_papa(
    attr_sal NUMBER(7,2),
    attr_comm NUMBER(7,2),
    attr_deptno REF dept_obj,
    
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2,
    OVERRIDING MAP MEMBER FUNCTION compare RETURN NUMBER
) NOT FINAL;

※多個REF

※BODY內容和第一個兒子一樣

※增加DEPT_OBJ測試資料

CREATE TABLE t_dept OF DEPT_OBJ;
    
INSERT INTO t_dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO t_dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO t_dept VALUES (30,'SALES','CHICAGO');
INSERT INTO t_dept VALUES (40,'OPERATIONS','BOSTON');

※增加EMP_OBJ2測試資料

CREATE TABLE t_emp OF EMP_OBJ2;
    
INSERT INTO t_emp VALUES (7369,'SMITH','CLERK',800,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 20 )
);
INSERT INTO t_emp VALUES (7499,'ALLEN','SALESMAN',1600,300,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 30 )
);
INSERT INTO t_emp VALUES (7521,'WARD','SALESMAN',1250,500,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 30 )
);
INSERT INTO t_emp VALUES (7566,'JONES','MANAGER',2975,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 20 )
);
INSERT INTO t_emp VALUES (7654,'MARTIN','SALESMAN',1250,1400,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 30 )
);
INSERT INTO t_emp VALUES (7698,'BLAKE','MANAGER',2850,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 30 )
);
INSERT INTO t_emp VALUES (7782,'CLARK','MANAGER',2450,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 10 )
);
INSERT INTO t_emp VALUES (7788,'SCOTT','ANALYST',3000,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 20 )
);
INSERT INTO t_emp VALUES (7839,'KING','PRESIDENT',5000,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 10 )
);
INSERT INTO t_emp VALUES (7844,'TURNER','SALESMAN',1500,0,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 30 )
);
INSERT INTO t_emp VALUES (7876,'ADAMS','CLERK',1100,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 20 )
);
INSERT INTO t_emp VALUES (7900,'JAMES','CLERK',950,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 30 )
);
INSERT INTO t_emp VALUES (7902,'FORD','ANALYST',3000,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 20 )
);
INSERT INTO t_emp VALUES (7934,'MILLER','CLERK',1300,NULL,
    (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 10 )
);

※注意是使用REF()

※查詢

SELECT * FROM T_DEPT;
SELECT attr_empno, DEREF(attr_deptno) FROM T_EMP;

※原本的sql下法在sqlplus看到的畫面

※使用DEREF看到的畫面

※在SQL Developer看到的都一樣，滑鼠按下去能看到裡面的內容

※修改

UPDATE T_EMP SET 
    ATTR_SAL = ATTR_SAL * 1.2,
    ATTR_DEPTNO = dept_obj(30,'RESEARCH','芝加哥')
WHERE ATTR_JOB = UPPER('clerk');

※要修改dept_obj的內容用一般的寫法會出「ORA-00932: 不一致的資料類型: 應該是 REF ooo.DEPT_OBJ」的錯，一定要使用REF的寫法：

UPDATE T_EMP SET 
    ATTR_SAL = ATTR_SAL * 1.2,
    ATTR_DEPTNO = (SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 10 )
WHERE ATTR_JOB = UPPER('clerk');

※刪除

DELETE FROM T_EMP
WHERE ATTR_DEPTNO = (
    SELECT REF(d) FROM t_dept d WHERE ATTR_DEPTNO = 10
);

※如果where條件是DEPT_OBJ還是要用REF()