PLSQL Developer 好用的設定

※顯示行號

SQL的行號

PL SQL 的行號

※编辑用快速鍵

指的是下 SQL 時的快速鍵，Tools-->Preferences

※此時可以用如下的設定
S=SELECT * FROM
W=WHERE
GB=GROUP BY
OB=ORDER BY

=前後不能空，要使用時，只要下S空格，就會出=後面的字了

※此檔當然不能刪除

※功能快速鍵

※用滑鼠按右邊想改的地方會反白，此時按想設定的快速鍵即可
以下是個參考，設定成與 Eclipse 一樣
Edit / Undo：Ctrl+Z
Edit / Redo：Ctrl+Y
Edit / PL/SQL Beautifier：Shift+Ctrl+F
Edit / Selection / Indent：Tab
Edit / Selection / Unindent：Shift+Tab
Edit / Selection / Uppercase：Shift+Ctrl+X
Edit / Selection / Lowercase：Shift+Ctrl+Y
Editor: Delete Line：Ctrl+D

註解和反註解：
Edit / Comment：Shift+Ctrl+C
Edit / Selection / Uncomment：
這是多行註解 (/**/)，這個軟體必需要用滑鼠選完文字後，才有用，而且不能有兩個都是一樣的快速鍵

※執行一行 SQL

※大部分的人一行都是一句 SQL，一句是以「;」區分的

※假設在編輯區全部有三句 SQL，按下如上的快速鍵 (F8)，每句都會執行

※但大部分的時候，想要執行的只是游標所在的那一行(一句)，此時可以如下的設定

※此時只會執行游標當前行，但二句(含)以上會出錯

Debug PL SQL

以 PL/SQL Developer為例
1.用Command Window叫出想debug的procedure或function，然後在行數按右鍵有一些功能可以用，如下：

紅x才是debug，按Set Breakpoint就會出現，或按行數的數字也會出現
綠0是書籤，按Set Bookmark就會出現，或按行數的左邊空白的地方也會出現
Goto Bookmark會出現有設定的點


2.在procedure或function的名稱按右鍵test會出現如下的畫面

先在下面的紅框打上要測的資料，如果是procedure，out的部分不用打，然後按左上角的鑰匙就會到debug處


到debug處後，左下角有如下的圖

可以在Variable打上這個procedure或function的變數名，如果有找到，右邊的Value會自動出現內容，Variable也可以打Record的變數，例如：rec.xxx

物件導向六(物件表二) (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()

物件導向七(物件view) (PL/SQL 三十四)

※新增類別

CREATE OR REPLACE TYPE v_emp IS OBJECT(
    v_empno NUMBER(4),
    v_ename VARCHAR2(10),
    v_job VARCHAR2(9),
    v_mgr NUMBER(4),
    v_hiredate DATE,
    v_sal NUMBER(7,2),
    v_comm NUMBER(7,2),
    v_deptno NUMBER(2),
    
    MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;

※屬性的類型和順序必需和EMP表的類型和順序一模一樣

※BODY

CREATE OR REPLACE TYPE BODY v_emp IS
    MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.v_empno || ':' || SELF.v_ename;
    END;
END;

※隨便寫一個測試

※物件view語法

CREATE OR REPLACE VIEW view_test OF v_emp
    WITH OBJECT IDENTIFIER(v_empno) AS
SELECT * FROM emp;

※如果權限不夠要用有權限的登入，下「GRANT CREATE VIEW TO 使用者;」

※測試

DECLARE
    v v_emp;
BEGIN
    SELECT VALUE(vt) INTO v FROM view_test vt WHERE v_empno=7499;
    DBMS_OUTPUT.PUT_LINE(v.to_string);
END;

物件導向五(物件表一)(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;

物件導向四(多型、Final、抽象)(PL/SQL 三十一)

※多型

※父類別的宣告與實作

CREATE OR REPLACE TYPE father_obj IS OBJECT(
    attr_empno NUMBER(4),
    attr_ename VARCHAR2(10),
    attr_job VARCHAR2(9),
    
    MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;
    
    
    
CREATE OR REPLACE TYPE BODY father_obj IS
    MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_empno || ':' || SELF.attr_ename || ':' || SELF.attr_job;
    END;
END;

※第一個子類別的宣告與實作

CREATE OR REPLACE TYPE son_obj UNDER father_obj(
    attr_sal NUMBER(7,2),
    attr_comm NUMBER(7,2),
    
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;
    
    
    
CREATE OR REPLACE TYPE BODY son_obj IS
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_ename || ':' || SELF.attr_sal;
    END;
END;

※第二個子類別的宣告與實作

CREATE OR REPLACE TYPE son2_obj UNDER father_obj(
    attr_hiredate DATE,
    
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;
    
    
    
CREATE OR REPLACE TYPE BODY son2_obj IS
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_hiredate || ':' || SELF.attr_empno;
    END;
END;

※測試

DECLARE
    s1 father_obj;
    s2 father_obj;
BEGIN
    s1 := son_obj(7698, 'BLAKE', 'MANAGER', 2850, NULL);
    s2 := son2_obj(7698, 'BLAKE', 'MANAGER', TO_DATE('17-12-1980','DD-MM-YYYY'));
    DBMS_OUTPUT.PUT_LINE(s1.to_string);
    DBMS_OUTPUT.PUT_LINE(s2.to_string);
END;

※s1和s2都可以向上轉型，而 to_string 為自己的實作方法

※Final

※宣告Final類別

CREATE OR REPLACE TYPE final_obj IS OBJECT(
    attr_empno NUMBER(4)
) FINAL;
    
CREATE OR REPLACE TYPE xxx UNDER final_obj();

※預設就是FINAL，所以不寫也是一樣

※編譯時會出「PLS-00590: 嘗試在 FINAL 類型之下建立子類型」的錯

※宣告Final方法

CREATE OR REPLACE TYPE final_obj IS OBJECT(
    attr_empno NUMBER(4),
    FINAL MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;
    
CREATE OR REPLACE TYPE xxx UNDER final_obj(
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
);

※編譯時會出「PLS-00637: 無法覆寫或隱藏 FINAL 方法」的錯

※抽象

※宣告抽象類別

CREATE OR REPLACE TYPE abstract_obj IS OBJECT(
    attr_empno NUMBER(4),
    NOT INSTANTIABLE MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT INSTANTIABLE NOT FINAL;

※和java一樣，有抽象方法的一定是抽象類別，在Oracle裡，NOT INSTANTIABLE兩邊都要寫，但是抽象類別不一定是抽象方法，所以拿掉方法的抽象，編譯也是會過

※NOT INSTANTIABLE 和 NOT FINAL誰在前面都可以

※繼承與實作

CREATE OR REPLACE TYPE instance_abs UNDER abstract_obj(
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
);
    
    
    
CREATE OR REPLACE TYPE BODY instance_abs IS
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN 'success';
    END;
END;

※和之前都一樣

※測試

DECLARE
    abs abstract_obj;
BEGIN
    abs := instance_abs(1111);
    DBMS_OUTPUT.PUT_LINE(abs.to_string());
END;

※由子類別為父類別進行實例化，最後使用覆寫的 to_string 方法

物件導向三(一對多、繼承、覆寫)(PL/SQL 三十)

類似Hibernate的東西，可以一對多

※一對多

※宣告與實作 DEPT

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

CREATE OR REPLACE TYPE emp_obj IS OBJECT(
    attr_empno NUMBER(4),
    attr_ename VARCHAR2(10),
    attr_job VARCHAR2(9),
    attr_mgr NUMBER(4),
    attr_hiredate DATE,
    attr_sal NUMBER(7,2),
    attr_comm NUMBER(7,2),
    attr_deptno dept_obj,
    
    MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;
    
    
    
CREATE OR REPLACE TYPE BODY emp_obj IS
    MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_empno || ':' || SELF.attr_ename || ':' || SELF.attr_sal || ':' || SELF.attr_comm;
    END;
END;

※注意最後一個屬性的型態

※測試

DECLARE
    o_dept dept_obj;
    o_emp emp_obj;
BEGIN
    o_dept := dept_obj(30, 'SALES', 'CHICAGO');
    o_emp := emp_obj(7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981', 'dd-mm-yyyy'), 2850, NULL, o_dept);
    
    DBMS_OUTPUT.PUT_LINE(o_emp.to_string());
    DBMS_OUTPUT.PUT_LINE(o_emp.attr_sal);
    DBMS_OUTPUT.PUT_LINE(o_emp.attr_deptno.to_string());
END;

※注意o_emp的最後一個參數型態

※結果：
7698:BLAKE:2850:
2850
30:SALES:CHICAGO

※繼承

※父類別的宣告與實作

CREATE OR REPLACE TYPE father_obj IS OBJECT(
    attr_empno NUMBER(4),
    attr_ename VARCHAR2(10),
    attr_job VARCHAR2(9),
    
    MEMBER FUNCTION f_tostring RETURN VARCHAR2
) NOT FINAL;
    
    
    
CREATE OR REPLACE TYPE BODY father_obj IS
    MEMBER FUNCTION f_tostring RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_empno || ':' || SELF.attr_ename || ':' || SELF.attr_job;
    END;
END;

※和之前沒什麼不同

※子類別的宣告與實作

CREATE OR REPLACE TYPE son_obj UNDER father_obj(
    attr_sal NUMBER(7,2),
    attr_comm NUMBER(7,2),
    
    MEMBER FUNCTION s_tostring RETURN VARCHAR2
) NOT FINAL;
    
    
    
CREATE OR REPLACE TYPE BODY son_obj IS
    MEMBER FUNCTION s_tostring RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_ename || ':' || SELF.attr_sal;
    END;
END;

※要使用UNDER關鍵字，相當於java的extend，「IS OBJECT」就不需要了

※測試

DECLARE
    son son_obj;
BEGIN
    son := son_obj(7698, 'BLAKE', 'MANAGER', 2850, NULL);
    DBMS_OUTPUT.PUT_LINE(son.f_tostring);
    DBMS_OUTPUT.PUT_LINE(son.s_tostring);
END;

※要使用son時，要先從父類別的建構子開使塞值，塞完換子類別

※覆寫

就是要覆寫父類別的方法，將上面的父類別拿來改(必需先刪除子類別才行)
我只將父類別的方法改成to_string，就不貼出來了

※子類別的宣告與實作

CREATE OR REPLACE TYPE son_obj UNDER father_obj(
    attr_sal NUMBER(7,2),
    attr_comm NUMBER(7,2),
    
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
) NOT FINAL;
    
    
    
CREATE OR REPLACE TYPE BODY son_obj IS
    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
    BEGIN
        RETURN SELF.attr_ename || ':' || SELF.attr_sal;
    END;
END;

※一定要加OVERRIDING關鍵字；如果不加，編譯時，錯誤訊息也有提示，如「PLS-00636: 覆寫方法必須有 OVERRIDING 關鍵字」

※測試

DECLARE
    son son_obj;
    papa father_obj;
BEGIN
    son := son_obj(7698, 'BLAKE', 'MANAGER', 2850, NULL);
    DBMS_OUTPUT.PUT_LINE(son.to_string);
    
    papa := father_obj(7698, 'BLAKE', 'MANAGER');
    DBMS_OUTPUT.PUT_LINE(papa.to_string);
END;

※測試和繼承一樣，也可以只呼叫父類別

物件導向二(排序)(PL/SQL 二十九)

排序使用MAP或ORDER，MAP效能較好、但ORDER還可比較兩個物件的大小，兩者只能選其一

※MAP

※宣告

CREATE OR REPLACE TYPE emp_order_by IS OBJECT(
    xxx_empno NUMBER(4),
    xxx_sal NUMBER(7,2),
    xxx_comm NUMBER(7,2),
    xxx_deptno NUMBER(2),
    MAP MEMBER FUNCTION order_by RETURN NUMBER
) NOT FINAL;

※在MEMBER前面多個MAP即可

※沒有MAP MEMBER PROCEDURE 這種語法

※實作

CREATE OR REPLACE TYPE BODY emp_order_by IS
    MAP MEMBER FUNCTION order_by RETURN NUMBER IS
    BEGIN
        RETURN SELF.xxx_sal + NVL(SELF.xxx_comm, 0);
    END;
END;

※寫排序規則

※物件表

CREATE TABLE order_by_map OF emp_order_by;
    
INSERT INTO order_by_map VALUES (7369,800,NULL,20);
INSERT INTO order_by_map VALUES (7499,1600,300,30);
INSERT INTO order_by_map VALUES (7521,1250,500,30);
INSERT INTO order_by_map VALUES (7566,2975,NULL,20);
INSERT INTO order_by_map VALUES (7654,1250,1400,30);
INSERT INTO order_by_map VALUES (7698,2850,NULL,30);
INSERT INTO order_by_map VALUES (7782,2450,NULL,10);
INSERT INTO order_by_map VALUES (7788,3000,NULL,20);
INSERT INTO order_by_map VALUES (7839,5000,NULL,10);
INSERT INTO order_by_map VALUES (7844,1500,0,30);
INSERT INTO order_by_map VALUES (7876,1100,NULL,20);
INSERT INTO order_by_map VALUES (7900,950,NULL,30);
INSERT INTO order_by_map VALUES (7902,3000,NULL,20);
INSERT INTO order_by_map VALUES (7934,1300,NULL,10);
COMMIT;

※OF 剛剛建立的TYPE，就會將裡面的4個屬性變成欄位了

※新增一些資料來測試

※測試

SELECT VALUE(obm) v, xxx_empno, xxx_sal, xxx_comm, xxx_sal + NVL(xxx_comm, 0) sal_comm
FROM order_by_map obm
ORDER BY v ASC, xxx_empno DESC;

※VALUE()可將新表取出，然後用ORDER BY就會自動排序了

※ORDER BY 還是和以前一樣，可以有多個欄位

※結果：

※ORDER

※宣告

CREATE OR REPLACE TYPE emp_order IS OBJECT(
    xxx_empno NUMBER(4),
    xxx_sal NUMBER(7,2),
    xxx_comm NUMBER(7,2),
    xxx_deptno NUMBER(2),
    ORDER MEMBER FUNCTION compare(this emp_order) RETURN NUMBER 
) NOT FINAL;

※在MEMBER前面多個ORDER即可

※沒有ORDER MEMBER PROCEDURE 這種語法

※實作

CREATE OR REPLACE TYPE BODY emp_order IS
    ORDER MEMBER FUNCTION compare(this emp_order) RETURN NUMBER IS
        self_sal_comm NUMBER;
        this_sal_comm NUMBER;
    BEGIN
        self_sal_comm := SELF.xxx_sal + NVL(SELF.xxx_comm, 0);
        this_sal_comm := this.xxx_sal + NVL(this.xxx_comm, 0);
    
        IF self_sal_comm > this_sal_comm THEN
            RETURN 1;
        ELSIF self_sal_comm < this_sal_comm THEN
            RETURN -1;
        ELSE
            RETURN 0;
        END IF;
    END;
END;

※寫排序規則

※compare名稱、this都不是關鍵字，我學java的

※測試1

DECLARE
    e1 emp_order;
    e2 emp_order;
BEGIN
    /*
        e1 := emp_order(7499, 1600, 300, 30);
        e2 := emp_order(7521, 1250, 500, 30);
    */
    
    /*
        e1 := emp_order(7521, 1250, 500, 30);
        e2 := emp_order(7499, 1600, 300, 30);
    */
    
    e1 := emp_order(7788, 3000, NULL, 20);
    e2 := emp_order(7902, 3000, NULL, 20);
    
    IF e1 > e2 THEN
        DBMS_OUTPUT.PUT_LINE('e1的薪水大於e2');
    ELSIF e1 < e2 THEN
        DBMS_OUTPUT.PUT_LINE('e1的薪水小於e2');
    ELSE
        DBMS_OUTPUT.PUT_LINE('e1的薪水等於e2');
    END IF;
END;

※我的7788和7902薪水一樣，這裡要和資料庫對照了

※物件表

※ORDER也可以和MAP一樣有物件表

CREATE TABLE order_by_order OF emp_order;
    
INSERT INTO order_by_order VALUES (7369,800,NULL,20);
INSERT INTO order_by_order VALUES (7499,1600,300,30);
INSERT INTO order_by_order VALUES (7521,1250,500,30);
INSERT INTO order_by_order VALUES (7566,2975,NULL,20);
INSERT INTO order_by_order VALUES (7654,1250,1400,30);
INSERT INTO order_by_order VALUES (7698,2850,NULL,30);
INSERT INTO order_by_order VALUES (7782,2450,NULL,10);
INSERT INTO order_by_order VALUES (7788,3000,NULL,20);
INSERT INTO order_by_order VALUES (7839,5000,NULL,10);
INSERT INTO order_by_order VALUES (7844,1500,0,30);
INSERT INTO order_by_order VALUES (7876,1100,NULL,20);
INSERT INTO order_by_order VALUES (7900,950,NULL,30);
INSERT INTO order_by_order VALUES (7902,3000,NULL,20);
INSERT INTO order_by_order VALUES (7934,1300,NULL,10);
COMMIT;

※OF 剛剛建立的TYPE，就會將裡面的4個屬性變成欄位了

※新增一些資料來測試

※測試2

SELECT VALUE(obo) v, xxx_empno, xxx_sal, xxx_comm, xxx_sal + NVL(xxx_comm, 0) sal_comm
FROM order_by_order obo
ORDER BY v ASC, xxx_empno DESC;

※VALUE()可將新表取出，然後用ORDER BY就會自動排序了，結果和MAP一樣

物件導向一(屬性、方法、建構子) (PL/SQL 二十八)

※屬性、方法

※宣告

CREATE OR REPLACE TYPE xxx_emp IS OBJECT(
    xxx_empno NUMBER(4),
    xxx_sal NUMBER(7,2),
    xxx_deptno NUMBER(2),
    MEMBER PROCEDURE update_sal(v_percent NUMBER),
    STATIC PROCEDURE update_sal(v_deptno NUMBER, v_percent NUMBER),
    MEMBER FUNCTION get_sal RETURN NUMBER,
    STATIC FUNCTION get_sal(v_empno NUMBER) RETURN NUMBER
) NOT FINAL;

※一定要宣告屬性，但可以沒有方法

※宣告了3個屬性和4個方法；方法又分成兩個靜態方法和兩個一般方法

※TYPE的型態不能使用%TYPE之類的語法

※MEMBER的NUMBER不能用NUMBER(2)，可能括號會誤判吧

※實作

CREATE OR REPLACE TYPE BODY xxx_emp IS
    MEMBER PROCEDURE update_sal(v_percent NUMBER) IS
    BEGIN
        UPDATE EMP SET SAL = SAL * (1 + v_percent)
        WHERE EMPNO = SELF.xxx_empno;
    END;
    
    STATIC PROCEDURE update_sal(v_deptno NUMBER, v_percent NUMBER) IS
    BEGIN
        UPDATE EMP SET SAL = SAL * (1 + v_percent)
        WHERE DEPTNO = v_deptno;
    END;
    
    MEMBER FUNCTION get_sal RETURN NUMBER IS
        ooo_sal_comm NUMBER;
    BEGIN
        SELECT SAL + NVL(COMM, 0) INTO ooo_sal_comm FROM EMP
        WHERE EMPNO = SELF.xxx_empno;
        RETURN ooo_sal_comm;
    END;
    
    STATIC FUNCTION get_sal(v_empno NUMBER) RETURN NUMBER IS
        ooo_sal_comm NUMBER;
    BEGIN
        SELECT SAL + NVL(COMM, 0) INTO ooo_sal_comm FROM EMP
        WHERE EMPNO = v_empno;
        RETURN ooo_sal_comm;
    END;
END;

※實作時，將整個方法複製過來，將TYPE改成TYPE BODY，然後Object()不要，最後變END，而且只要實作方法即可，屬性不用

※靜態的方法不能使用SELF關鍵字，相當於java的this

※最後的END不能寫END xxx_update_emp

※可以在圖中看到TYPE和TYPE BODY

※測試

DECLARE
    ooo_emp xxx_emp;
BEGIN
    ooo_emp := xxx_emp(7521, 1250, 30);
    ooo_emp.xxx_sal := 2000;
    DBMS_OUTPUT.PUT_LINE(ooo_emp.xxx_sal);
    
    DBMS_OUTPUT.PUT_LINE('修改前：' || ooo_emp.get_sal());
    DBMS_OUTPUT.PUT_LINE('靜態修改前：' || xxx_emp.get_sal(7499));
    
    ooo_emp.update_sal(0.2);
    DBMS_OUTPUT.PUT_LINE('修改後：' || ooo_emp.get_sal());
    
    xxx_emp.update_sal(30, 0.2);
    DBMS_OUTPUT.PUT_LINE('靜態修改後：' || xxx_emp.get_sal(7499));
END;

※和java一樣，要呼叫靜態方法時，要用「類別.方法」

※2000那行只有在顯示有用，並沒有真的存到資料庫

※如果要刪除類別，指令要下「DROP TYPE xxx_emp;」

※建構子

※宣告

CREATE OR REPLACE TYPE xxx_constructor IS OBJECT(
    xxx_empno NUMBER(4),
    xxx_sal NUMBER(7,2),
    xxx_deptno NUMBER(2),
    
    CONSTRUCTOR FUNCTION xxx_constructor(p_empno NUMBER) 
    RETURN SELF AS RESULT,
    
    CONSTRUCTOR FUNCTION xxx_constructor(p_empno NUMBER, p_deptno NUMBER) 
    RETURN SELF AS RESULT
) NOT FINAL;

※寫了兩個建構子，但其實還有一個是傳3個參數的，也就是宣告的3個屬性，這也是一個建構子

※沒有CONSTRUCTOR PROCEDURE這種語法

※實作

CREATE OR REPLACE TYPE BODY xxx_constructor IS
    CONSTRUCTOR FUNCTION xxx_constructor(p_empno NUMBER) 
    RETURN SELF AS RESULT IS
    BEGIN
        SELF.xxx_empno := p_empno;
        SELECT SAL INTO SELF.xxx_sal FROM EMP
        WHERE EMPNO = p_empno;
        RETURN;
    END;
    
    CONSTRUCTOR FUNCTION xxx_constructor(p_empno NUMBER, p_deptno NUMBER) 
    RETURN SELF AS RESULT IS
    BEGIN
        SELF.xxx_empno := p_empno;
        SELF.xxx_sal := 1500;
        SELF.xxx_deptno := p_deptno;
        RETURN;
    END;
END;

※一定要寫RETURN，雖然編譯會過，但呼叫時，會出「ORA-06503: PL/SQL: 函數沒有傳回任何值」的錯

※測試

DECLARE
    ooo_cons1 xxx_constructor;
    ooo_cons2 xxx_constructor;
    ooo_cons3 xxx_constructor;
BEGIN
    ooo_cons1 := xxx_constructor(7521, 1250, 30);
    ooo_cons2 := xxx_constructor(7499);
    ooo_cons3 := xxx_constructor(7654, 30);
    
    DBMS_OUTPUT.PUT_LINE(ooo_cons1.xxx_sal);
    DBMS_OUTPUT.PUT_LINE(ooo_cons2.xxx_sal);
    DBMS_OUTPUT.PUT_LINE(ooo_cons3.xxx_sal);
END;

動態SQL四(DBMS.SQL) (PL/SQL 二十七)

官網連結
在Oracle 9i之前(不包含9i)都是用這個，現在都用上一篇的寫法，這裡稍微寫一下
官網的Execution Flow寫了8步，我將我的理解寫下來：
1.OPEN_CURSOR：打開游標
2.PARSE：解析SQL
3.BIND_VARIABLE or BIND_ARRAY：參數挷定
4.DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY：設定游標的欄位
5.EXECUTE：執行游標
6.FETCH_ROWS or EXECUTE_AND_FETCH：取得游標的一條記錄
7.VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG：按第4步的順序取出欄位
8.CLOSE_CURSOR：關閉游標

※查詢

DECLARE
    dynamic_sql VARCHAR2(200);
    save_cur NUMBER;
    xxx_emp_ename EMP.ENAME%TYPE;
    xxx_emp_job EMP.JOB%TYPE;
    xxx_emp_sal EMP.SAL%TYPE;
    xxx_state NUMBER;
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    save_cur := DBMS_SQL.OPEN_CURSOR;
    dynamic_sql := 'SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = :p1';
    DBMS_SQL.PARSE(save_cur, dynamic_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(save_cur, 'p1', xxx_emp_deptno);
    DBMS_SQL.DEFINE_COLUMN(save_cur, 1, xxx_emp_ename, 10);
    DBMS_SQL.DEFINE_COLUMN(save_cur, 2, xxx_emp_job, 9);
    DBMS_SQL.DEFINE_COLUMN(save_cur, 3, xxx_emp_sal);
    xxx_state := DBMS_SQL.EXECUTE(save_cur);
    
    LOOP
        EXIT WHEN DBMS_SQL.FETCH_ROWS(save_cur) = 0;
        DBMS_SQL.COLUMN_VALUE(save_cur, 1, xxx_emp_ename);
        DBMS_SQL.COLUMN_VALUE(save_cur, 2, xxx_emp_job);
        DBMS_SQL.COLUMN_VALUE(save_cur, 3, xxx_emp_sal);
    
        DBMS_OUTPUT.PUT_LINE(xxx_emp_ename);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_job);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_sal || CHR(10));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(save_cur);
END;

※SQL裡的參數一定要「:」開頭，而BIND_VARIABLE第二個參數看要不要「:」都可以

※PARSE的DBMS_SQL.NATIVE是解析用的版本，8i以後(包含)都用這個，官網有寫v6、v7，應該就是舊版本用的

※EXECUTE後用xxx_state接，指的是改變的行數，因為是SELECT，所以一定是0

※修改

DECLARE
    dynamic_sql VARCHAR2(200);
    save_cur NUMBER;
    xxx_emp_empno EMP.EMPNO%TYPE := 7499;
    xxx_emp_comm EMP.COMM%TYPE := 700;
    xxx_state NUMBER;
BEGIN
    save_cur := DBMS_SQL.OPEN_CURSOR;
    dynamic_sql := 'UPDATE EMP SET COMM = :p1 WHERE EMPNO = :p2';
    DBMS_SQL.PARSE(save_cur, dynamic_sql, DBMS_SQL.NATIVE);
    
    DBMS_SQL.BIND_VARIABLE(save_cur, 'p1', xxx_emp_comm);
    DBMS_SQL.BIND_VARIABLE(save_cur, 'p2', xxx_emp_empno);
    xxx_state := DBMS_SQL.EXECUTE(save_cur);
    
    DBMS_OUTPUT.PUT_LINE(xxx_state);
    DBMS_SQL.CLOSE_CURSOR(save_cur);
    -- COMMIT;
END;

※因為是修改，所以第4、7步就省了

動態SQL三(處理批次、和游標配合) (PL/SQL 二十六)

※處理批次

※修改、查詢

DECLARE
    TYPE ooo_ename IS TABLE OF EMP.ENAME%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_job IS TABLE OF EMP.JOB%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_sal IS TABLE OF EMP.SAL%TYPE INDEX BY PLS_INTEGER;
    xxx_emp_ename ooo_ename;
    xxx_emp_job ooo_job;
    xxx_emp_sal ooo_sal;
    
    dynamic_sql VARCHAR2(200);
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    dynamic_sql := '
        UPDATE EMP SET SAL = SAL * 1.2 WHERE DEPTNO = :p1
        RETURN ENAME, JOB, SAL INTO :p2, :p3, :p4
    ';
    -- SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = :p1
    
    EXECUTE IMMEDIATE dynamic_sql USING xxx_emp_deptno
    RETURN BULK COLLECT INTO xxx_emp_ename, xxx_emp_job, xxx_emp_sal;
    
    /* 
    EXECUTE IMMEDIATE dynamic_sql 
    BULK COLLECT INTO xxx_emp_ename, xxx_emp_job, xxx_emp_sal
    USING xxx_emp_deptno;
    */
    
    FOR i IN 1..xxx_emp_ename.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_emp_ename(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_job(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_sal(i) || CHR(10));
    END LOOP;
END;

※註解部分是查詢

※FORALL

上一個例子的xxx_emp_deptno只是一個值，如果是多個值就要用FORALL

DECLARE
    TYPE xxx_nested IS TABLE OF EMP.EMPNO%TYPE;
    xxx_empnos xxx_nested := xxx_nested(7369, 7499, 7521);
    
    TYPE ooo_ename IS TABLE OF EMP.ENAME%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_job IS TABLE OF EMP.JOB%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_sal IS TABLE OF EMP.SAL%TYPE INDEX BY PLS_INTEGER;
    xxx_emp_ename ooo_ename;
    xxx_emp_job ooo_job;
    xxx_emp_sal ooo_sal;
    
    dynamic_sql VARCHAR2(200);
BEGIN
    dynamic_sql := '
        DELETE FROM EMP WHERE EMPNO = :p1
        RETURN ENAME, JOB, SAL INTO :p2, :p3, :p4
    ';
    
    FORALL j IN 1..xxx_empnos.COUNT
        EXECUTE IMMEDIATE dynamic_sql USING xxx_empnos(j)
        RETURN BULK COLLECT INTO xxx_emp_ename, xxx_emp_job, xxx_emp_sal;
    
    FOR i IN 1..xxx_emp_ename.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_emp_ename(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_job(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_sal(i) || CHR(10));
    END LOOP;
END;

※可以一次性的刪除

※和游標配合

※不使用陣列

DECLARE
    xxx_cur SYS_REFCURSOR;
    xxx_emp_row EMP%ROWTYPE;
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    OPEN xxx_cur FOR 'SELECT * FROM EMP WHERE DEPTNO = :p1' USING xxx_emp_deptno;
    
    LOOP
        FETCH xxx_cur INTO xxx_emp_row;
        EXIT WHEN xxx_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row.EMPNO);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row.ENAME);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row.JOB || CHR(10));
    END LOOP;
    CLOSE xxx_cur;
END;

※所以如果sql有in時，可以如下使用

※IN

IF xxx = 'Y' THEN
    ooo := ' AND emp_id in(1,2,3)';
ELSIF xxx = 'N' THEN
    ooo := ' AND emp_id in(4,5)';
END IF;
    
OPEN v_cur FOR 'SELECT * FROM EMP
    WHERE xxx = :aaa || ooo
USING x;
    
LOOP
    FETCH v_cur INTO datas;
    EXIT WHEN v_cur%NOTFOUND;
    -- v_cur%ROWCOUNT;
    -- do something
END LOOP;
    
CLOSE v_cur;

※ooo必需要用||連起來才行，否則會執行錯誤，因為連起來它認為是字串

※使用陣列

DECLARE
    xxx_cur SYS_REFCURSOR;
    TYPE xxx_emp IS TABLE OF EMP%ROWTYPE INDEX BY PLS_INTEGER;
    xxx_emp_row xxx_emp;
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    OPEN xxx_cur FOR 'SELECT * FROM EMP WHERE DEPTNO = :p1' USING xxx_emp_deptno;
    FETCH xxx_cur BULK COLLECT INTO xxx_emp_row;
    CLOSE xxx_cur;
    
    FOR i IN 1..xxx_emp_row.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row(i).EMPNO);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row(i).ENAME);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row(i).JOB || CHR(10));
    END LOOP;
END;

動態SQL二(RETURN、配合PROCEDURE) (PL/SQL 二十五)

※RETURN／RETURNING

兩個都一樣，看要用哪個都可以，回傳用的

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_eno EMP.EMPNO%TYPE := 7499;
    xxx_sal EMP.SAL%TYPE;
    xxx_job EMP.JOB%TYPE;
BEGIN
    dynamic_sql := '
        UPDATE EMP SET SAL = SAL * 1.2, JOB = ''管理''
        WHERE EMPNO = :p1 RETURN SAL, JOB INTO :p2, :p3
    ';
    -- DELETE FROM EMP WHERE EMPNO = :p1 RETURN SAL, JOB INTO :p2, :p3
    
    EXECUTE IMMEDIATE dynamic_sql USING xxx_eno RETURN INTO xxx_sal, xxx_job;
    DBMS_OUTPUT.PUT_LINE(xxx_sal || ' ' || xxx_job);
    -- COMMIT;
END;

※配合PROCEDURE

CREATE OR REPLACE PROCEDURE xxx_emp_proc(
    xxx_eno IN OUT EMP.EMPNO%TYPE,
    xxx_ena EMP.ENAME%TYPE,
    xxx_sal EMP.SAL%TYPE
) IS
BEGIN
    SELECT MAX(EMPNO) INTO xxx_eno FROM EMP;
    xxx_eno := xxx_eno + 1000;
    INSERT INTO EMP(EMPNO, ENAME, SAL)VALUES(xxx_eno, xxx_ena, xxx_sal);
    -- COMMIT;
END;

※呼叫端

DECLARE
    dynamic_sql VARCHAR2(200);
    in_out_eno EMP.EMPNO%TYPE;
    in_ena EMP.ENAME%TYPE := 'abc';
    in_sal EMP.SAL%TYPE := 1800;
BEGIN
    dynamic_sql := '
        BEGIN
            xxx_emp_proc(:p1, :p2, :p3);
        END;
    ';
    EXECUTE IMMEDIATE dynamic_sql USING IN OUT in_out_eno, IN in_ena, in_sal;
    DBMS_OUTPUT.PUT_LINE(in_out_eno);
END;

※呼叫時如果是IN，可以省略不寫

※如果xxx_emp_proc的xxx_eno宣告成OUT，那USING就用OUT、IN OUT都可以；
可是如果宣告成IN OUT，那USING就只能用IN OUT，如果使用OUT，就會出「ORA-06537: OUT 連結變數已連結到 IN 位置」的錯

動態SQL一(EXECUTE IMMEDIATE、USING) (PL/SQL 二十四)

所謂動態SQL，簡單來說就是SQL是組合起來的

※錯誤的例子

CREATE OR REPLACE FUNCTION dynamic_sql(xxx_table VARCHAR2) RETURN NUMBER IS
    xxx_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM USER_TABLES WHERE TABLE_NAME = UPPER(xxx_table);
    IF xxx_count = 0 THEN
        CREATE TABLE xxx_table(
            ID NUMBER,
            NAME VARCHAR2(10),
            CONSTRAINT XXX_PK_' || xxx_table || ' PRIMARY KEY(ID)
        );
    END IF;
    
    SELECT COUNT(*) INTO xxx_count FROM xxx_table;
    RETURN xxx_count;
END;
    
    
    
BEGIN
    DBMS_OUTPUT.PUT_LINE(dynamic_sql('xxx'));
END;

※執行時，出現「PLS-00103: 發現了符號 "CREATE" 當您等待下列事項之一發生時:~~」，也就是沒辦法直接在裡面寫CREATE，所以修改如下：

※EXECUTE IMMEDIATE

※範例一

CREATE OR REPLACE FUNCTION dynamic_sql(xxx_table VARCHAR2) RETURN NUMBER IS
    xxx_count NUMBER;
    dynamic_create VARCHAR2(100);
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM USER_TABLES WHERE TABLE_NAME = UPPER(xxx_table);
    IF xxx_count = 0 THEN
    dynamic_create := 'CREATE TABLE ' || xxx_table || -- 注意CREATE TABLE後要加空格
    '(
        ID NUMBER,
        NAME VARCHAR2(10),
        CONSTRAINT XXX_PK_' || xxx_table || ' PRIMARY KEY(ID)
    )';
    EXECUTE IMMEDIATE dynamic_create;
    END IF;
    
    dynamic_create := 'SELECT COUNT(*) FROM ' || xxx_table; -- 注意FROM後要加空格
    EXECUTE IMMEDIATE dynamic_create INTO xxx_count;
    RETURN xxx_count;
END;

※執行時如果出現權限不足，使用sys登入
CONN sys as SYSDBA-->密碼
GRANT CREATE ANY TABLE TO 帳號名稱

※CONSTRAINT一定要加xxx_table，不然執行第二次會發現名字重覆

※範例二

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM USER_TABLES WHERE TABLE_NAME='XXX_TABLE';
    IF xxx_count = 0 THEN
        dynamic_sql := '
            CREATE TABLE XXX_TABLE(
                id NUMBER PRIMARY KEY,
                name VARCHAR2(10)
            )';
        EXECUTE IMMEDIATE dynamic_sql;
    ELSE
        dynamic_sql := 'TRUNCATE TABLE XXX_TABLE';
        EXECUTE IMMEDIATE dynamic_sql;
    END IF;
    
    dynamic_sql := '
        BEGIN
            FOR i IN 1..10 LOOP
                INSERT INTO XXX_TABLE VALUES(i, ''xxx'' || i);
            END LOOP;
        END;';
    EXECUTE IMMEDIATE dynamic_sql;
    COMMIT;
END;

※XXX_TABLE一定要大寫

※BEGIN~END字串裡最後一定要有「;」

※因為已經是在「'」裡面，所以裡面要用時，只好用跳脫字元，兩個「'」就是一個「'」

※dynamic_sql我本來設100，但出「ORA-06502: PL/SQL: 數字或值錯誤: 字元字串緩衝區太小」這個錯，所以加到200就ok了

※USING

USING只能用在DML，可以依照順序對應到自定的參數

※用法

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_deptno DEPT.DEPTNO%TYPE := 80;
    xxx_dname DEPT.DNAME%TYPE := 'aaa';
    xxx_loc DEPT.LOC%TYPE := 'bbb';
BEGIN
    dynamic_sql := '
        INSERT INTO DEPT VALUES(:p1, :p2, :p3)
    ';
    EXECUTE IMMEDIATE dynamic_sql USING xxx_deptno, xxx_dname, xxx_loc;
    COMMIT;
END;

※USING xxx_deptno, xxx_dname, xxx_loc，可以直接將值寫在裡面，如
USING 80, 'aaa', 'bbb'，如果有null，要用空，如USING 80, 'aaa', ''，
不能顯示的寫NULL，如USING 80, 'aaa', NULL

※和巢狀表配合使用

DECLARE
    dynamic_sql VARCHAR2(200);
    TYPE dno IS TABLE OF DEPT.DEPTNO%TYPE NOT NULL;
    TYPE dna IS TABLE OF DEPT.DNAME%TYPE NOT NULL;
    xxx_nested dno := dno(10, 20, 30);
    ooo_nested dna := dna('apple', 'banana', 'strawberry');
BEGIN
    dynamic_sql := '
        UPDATE DEPT SET dname = :p1 WHERE deptno = :p2
    ';
    FOR i IN 1..xxx_nested.LAST LOOP
        EXECUTE IMMEDIATE dynamic_sql USING ooo_nested(i), xxx_nested(i);
    END LOOP;
    -- COMMIT;
END;

※COMMIT我先註解掉，不然會蓋掉原有的值

※查詢

DECLARE
    dynamic_sql VARCHAR2(200);
    xxx_eno EMP.EMPNO%TYPE := 7499;
    xxx_emp EMP%ROWTYPE;
BEGIN
    dynamic_sql := '
        SELECT * FROM EMP WHERE EMPNO = :p1
    ';
    EXECUTE IMMEDIATE dynamic_sql INTO xxx_emp USING xxx_eno;
    DBMS_OUTPUT.PUT_LINE(xxx_emp.empno || ' ' || xxx_emp.ename);
END;

Trigger六(調用子程序) (PL/SQL 二十三)

尤於trigger不能超過32KB，所以要寫很多的時候，可以提出來成為procedure或function

CREATE OR REPLACE PROCEDURE xxx IS 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('procedure');
END;
    
    
    
CREATE OR REPLACE FUNCTION ooo RETURN VARCHAR2 IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('function');
    RETURN 'success';
END;
    
    
    
CREATE OR REPLACE TRIGGER test_ddl
    BEFORE DDL ON SCHEMA ENABLE
BEGIN
    xxx;
    IF ooo() = 'success' THEN
        RAISE_APPLICATION_ERROR(-20000, '不能使用DDL');
    END IF;
END test_ddl;

※這時隨便create(假設create sequence)，就OK了

※刪除時，必須先刪除trigger，如果先刪除procedure或function會出錯

Trigger五(系統 trigger DDL、登入/登出、開啟/關閉、錯誤訊息、啟用/禁用) (PL/SQL 二十二)

官網連結

※禁止使用DDL

CREATE OR REPLACE TRIGGER test_ddl
    BEFORE DDL ON SCHEMA 
BEGIN
    RAISE_APPLICATION_ERROR(-20000, '不能使用DDL');
END test_ddl;

※執行「CREATE SEQUENCE xxx;」就會出現訊息

※以下的練習還是要用trigger，所以先將trigger刪除，「DROP TRIGGER test_ddl;」

※ON SCHEMA一般用戶就可以CREATE； 但ON DATABASE預設沒權限

※官網 的 Table 9-5 Database Event Triggers 和 Table 9-6 Client Event Triggers可以參考

※系統定義的事件屬性

CREATE OR REPLACE TRIGGER test_ddl
    AFTER CREATE OR DROP OR ALTER ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE('ORA_LOGIN_USER=' || ORA_LOGIN_USER);
    DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_TYPE=' || ORA_DICT_OBJ_TYPE);
    DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_OWNER=' || ORA_DICT_OBJ_OWNER);
    BMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_NAME=' || ORA_DICT_OBJ_NAME);
    BMS_OUTPUT.PUT_LINE('ORA_SYSEVENT=' || ORA_SYSEVENT);
END test_ddl;

※執行「DROP SEQUENCE xxx;」會出現以下結果：
ORA_LOGIN_USER=SYS
ORA_DICT_OBJ_TYPE=SEQUENCE
ORA_DICT_OBJ_OWNER=SYS
ORA_DICT_OBJ_NAME=XXX
ORA_SYSEVENT=DROP

ORA_DICT_OBJ_OWNER是SYS是因為我用SYS用戶進來的，才有權限，只要是ON DATABASE，預設一般用戶沒有權限

※還有很多屬性，可參考官網 的Table 9-4 System-Defined Event Attributes

※控制特定欄位不能修改和刪除

CREATE OR REPLACE trigger test_ddl
    BEFORE ALTER ON SCHEMA
DECLARE
    CURSOR xxx_emp(
        table_owner all_tab_columns.OWNER%TYPE,
        table_table_name all_tab_columns.TABLE_NAME%TYPE
    ) IS
    SELECT column_name FROM all_tab_columns
    WHERE owner = table_owner
    AND table_name = table_table_name;
BEGIN
    IF ora_dict_obj_type = 'TABLE' THEN
        FOR emp_row IN xxx_emp(ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME) LOOP
    
            IF ora_is_alter_column(emp_row.column_name) THEN
                IF emp_row.column_name IN('EMPNO', 'DEPTNO') THEN
                    raise_application_error(-20000, '不能修改EMPNO和DEPTNO欄位');
                END IF;
            END IF;
    
            IF ora_is_drop_column(emp_row.column_name) THEN
                IF emp_row.column_name IN('EMPNO', 'DEPTNO') THEN
                    raise_application_error(-20001, '不能刪除EMPNO和DEPTNO欄位');
                END IF;
            END IF;
    
        END LOOP;
    END IF;
END test_ddl;

※ALTER TABLE emp MODIFY(deptno);
ALTER TABLE emp DROP COLUMN empno;

※登入/登出記錄表

DROP TABLE login_logout_log PURGE;
CREATE TABLE login_logout_log(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    username VARCHAR2(20),
    login_date DATE,
    logout_date DATE,
    ip VARCHAR2(30)
);
    
SELECT * FROM login_logout_log;

※因為以下都是ON DATABASE，所以都用有權限的使用者來創建

※登入/登出、開啟/關閉 Trigger

CREATE OR REPLACE TRIGGER XXX_LOGIN
    AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO LOGIN_LOGOUT_LOG(username, login_date, ip)
    VALUES(ORA_LOGIN_USER, SYSDATE, ORA_CLIENT_IP_ADDRESS);
END XXX_LOGIN;
    
CREATE OR REPLACE TRIGGER XXX_LOGOUT
    BEFORE LOGOFF ON DATABASE
BEGIN
    INSERT INTO LOGIN_LOGOUT_LOG(username, logout_date, ip)
    VALUES(ORA_LOGIN_USER, SYSDATE, ORA_CLIENT_IP_ADDRESS);
END XXX_LOGOUT;

※登入/登出就會紀錄在表裡了

※開啟/關閉大同小異，只要改成AFTER STARTUP和BEFORE SHUTDOWN，然後執行SHUTDOWN IMMEDIATE和STARTUP就會記錄在表裡了，不過要注意PL/SQL Developer不支援這兩個命令，會出現「ORA-00900: invalid SQL statement」

※錯誤訊息 Trigger

CREATE OR REPLACE TRIGGER XXX_ERROR
    AFTER SERVERERROR ON DATABASE
BEGIN
    INSERT INTO ERR_LOG(dbname, err_date, err_msg)
    VALUES(ORA_DATABASE_NAME, SYSDATE, DBMS_UTILITY.FORMAT_ERROR_STACK);
END XXX_ERROR;

※SELECT * FROM III;給一個沒有的table名稱就會產生錯誤

※啟用/禁用Trigger

．預設在編譯完trigger後，就可以用了，可以在ON table 加上DISABLE或ENABLE(無D)，也就是預設在ON table後面就有ENABLE了，所以可以不打

．user_triggers、all_triggers、dba_triggers(sys權限)這三張表可以看全部的trigger，如看狀態可以用SELECT status FROM user_triggers就可以看到ENABLED或DISABLED(有D)

也可以使用ALTER TRIGGER trigger_name DISABLE/ENABLE，針對特定的trigger啟/禁用

．還可以使用ALTER TABLE [schema.]table_name ENABLE/DISABLE ALL TRIGGERS，如ALTER TABLE emp ENABLE ALL TRIGGERS;

Trigger四(DML trigger四 view) (PL/SQL 二十一)

針對view的trigger，主要是因為view有多表的時候不能增刪改，所以使用view trigger可以達到，但如果有彙總函數、CASE、DECODE、GROUP BY、DISTINCT還是不行，稍微想一下就知道為什麼不行了
官網連結

※新增一個view來練習

CREATE OR REPLACE VIEW xxx_view AS
SELECT
    e.EMPNO, e.ENAME, d.DEPTNO, d.DNAME
FROM EMP e, DEPT d
WHERE
    e.DEPTNO = d.DEPTNO
AND d.DEPTNO = 30;

※Oracle11g和12c預設是沒有權限可以新增view的，用有權限的帳號進入後，打上GRANT CREATE VIEW TO 使用者名稱;才有權限

※如果記不起來可以用視窗的方式來新增

 使用畫面可以輕鬆完成，完成後編譯，如下圖可以看見

※一執行「INSERT INTO xxx_view VALUES(7777, 'aaa', 50, 'xxx');」
ORA-01776: cannot modify more than one base table through a join view就會出錯，所以才要有view trigger

※新增

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF INSERT ON xxx_view 
    FOR EACH ROW
DECLARE
    xxx_emp_count NUMBER;
    xxx_dept_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_emp_count FROM EMP
    WHERE empno = :new.empno;
    
    SELECT COUNT(*) INTO xxx_dept_count FROM DEPT
    WHERE deptno = :new.deptno;
    
    IF xxx_emp_count = 0 THEN
        INSERT INTO emp(empno, ename) VALUES(:new.empno, :new.ename);
    END IF;
    
    IF xxx_dept_count = 0 THEN
        INSERT INTO dept(deptno, dname) VALUES(:new.deptno, :new.dname);
    END IF;
END update_view_trigger;

※再次執行「INSERT INTO xxx_view VALUES(7777, 'aaa', 50, 'xxx');」，看xxx_view、emp、dept可以發現成功了

※其實很多都有畫面操作，在Trigger按右鍵新增，如下

※修改

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF UPDATE ON xxx_view 
    FOR EACH ROW
BEGIN
    UPDATE emp SET ename = :new.ename WHERE empno = :new.empno;
    UPDATE dept SET dname = :new.dname WHERE deptno = :new.deptno;
END update_view_trigger;

※執行「UPDATE xxx_view SET ename = 'bbb', dname='ccc' WHERE empno = 7844;」

※刪除

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF DELETE ON xxx_view 
    FOR EACH ROW
DECLARE
    xxx_emp_count NUMBER;
BEGIN
    DELETE FROM emp WHERE empno = :old.empno;
    
    SELECT COUNT(*) INTO xxx_emp_count FROM EMP
    WHERE deptno = :old.deptno;
    
    IF xxx_emp_count = 0 THEN
        DELETE FROM dept WHERE deptno = :old.deptno;
    END IF;
END update_view_trigger;

※執行「DELETE FROM xxx_view WHERE deptno = 30;」

※增刪改

也就是將剛剛的新增、修改、刪除合併成一支

CREATE OR REPLACE TRIGGER update_view_trigger
    INSTEAD OF INSERT OR UPDATE OR DELETE ON xxx_view 
    FOR EACH ROW
DECLARE
    xxx_emp_count NUMBER;
    xxx_dept_count NUMBER;
BEGIN
    IF INSERTING THEN
        SELECT COUNT(*) INTO xxx_emp_count FROM EMP
        WHERE empno = :new.empno;
    
        SELECT COUNT(*) INTO xxx_dept_count FROM DEPT
        WHERE deptno = :new.deptno;
    
        IF xxx_dept_count = 0 THEN
            INSERT INTO dept(deptno, dname) VALUES(:new.deptno, :new.dname);
        END IF;
    
        IF xxx_emp_count = 0 THEN
            INSERT INTO emp(empno, ename) VALUES(:new.empno, :new.ename);
        END IF;
    ELSIF UPDATING THEN
        UPDATE emp SET ename = :new.ename WHERE empno = :new.empno;
        UPDATE dept SET dname = :new.dname WHERE deptno = :new.deptno;
    ELSIF DELETING THEN
        DELETE FROM emp WHERE empno = :old.empno;
    
        SELECT COUNT(*) INTO xxx_emp_count FROM EMP
        WHERE deptno = :old.deptno;
    
        IF xxx_emp_count = 0 THEN
            DELETE FROM dept WHERE deptno = :old.deptno;
        END IF;
    END IF;
END update_view_trigger;

※view和巢狀表

※事前準備

DROP TYPE xxx_nested;
DROP TYPE xxx_type;
CREATE OR REPLACE TYPE xxx_type IS OBJECT(
    pid NUMBER,
    pname VARCHAR2(20),
    pdate DATE
);
    
-- 新增巢狀表，型態是自定的，叫xxx_type
CREATE OR REPLACE TYPE xxx_nested IS TABLE OF xxx_type NOT NULL;
    
DROP TABLE xxx_table PURGE;
CREATE TABLE xxx_table(
    xid NUMBER,
    xname VARCHAR(20) NOT NULL,
    ooo_nested xxx_nested,
    CONSTRAINT vt_pk PRIMARY KEY(xid)
) NESTED TABLE ooo_nested STORE AS PROJECTS_NESTED_TABLE;
    
-- 新增資料來練習
INSERT INTO xxx_table VALUES(1, 'xxx',
    xxx_nested(
        xxx_type(10, 'aaa', TO_DATE('2000-01-01', 'YYYY-MM-DD')),
        xxx_type(20, 'bbb', TO_DATE('2011-11-11', 'YYYY-MM-DD')),
        xxx_type(30, 'ccc', TO_DATE('1988-01-11', 'YYYY-MM-DD'))
    )
);
    
INSERT INTO xxx_table VALUES(2, 'ooo',
    xxx_nested(
        xxx_type(11, 'ddd', TO_DATE('1997-02-22', 'YYYY-MM-DD')),
        xxx_type(22, 'eee', TO_DATE('1996-10-11', 'YYYY-MM-DD'))
    )
);
    
-- 看結果有沒有在裡面
SELECT * FROM xxx_table;
    
-- 新增view
CREATE OR REPLACE VIEW v1 AS
SELECT xid, xname, ooo_nested
FROM xxx_table
WHERE xid = 1;

※增刪改

INSERT INTO TABLE(
    SELECT ooo_nested FROM v1
)VALUES(3, 'fff', TO_DATE('1999-09-09', 'YYYY-MM-DD'));
    
UPDATE TABLE(SELECT ooo_nested FROM v1)xxx
set value(xxx) = xxx_type(
    10, 'fff', TO_DATE('2000-01-01', 'YYYY-MM-DD')
)WHERE xxx.pid = 10;
    
DELETE FROM TABLE(
    SELECT ooo_nested FROM v1
)xxx
WHERE xxx.pid=10;

※執行就會出現「ORA-25015: cannot perform DML on this nested table view column」的錯，雖然和前面的錯誤訊息不同，但反正就是會錯

※view trigger

CREATE OR REPLACE TRIGGER nested_trigger
    INSTEAD OF INSERT OR UPDATE OR DELETE
    ON NESTED TABLE ooo_nested OF v1
-- DECLARE
BEGIN
    IF INSERTING THEN
        INSERT INTO TABLE(
            SELECT ooo_nested FROM xxx_table WHERE xid = :parent.xid
        )VALUES(:new.pid, :new.pname, :new.pdate);
    
    ELSIF UPDATING THEN
        UPDATE TABLE(
            SELECT ooo_nested FROM xxx_table WHERE xid = :parent.xid
        )xxx
        SET VALUE(xxx) = xxx_type(
            :new.pid, :new.pname, :new.pdate
        )WHERE xxx.pid = :old.pid;
    
    ELSIF DELETING THEN
        DELETE FROM TABLE(
            SELECT ooo_nested FROM xxx_table WHERE xid = :parent.xid
        )xxx
        WHERE xxx.pid = :old.pid;
    END IF;
END;

※:new和:old控制的是自定型態的表，也就是xxx_type； :parent控制的是有巢狀欄位的表，也就是xxx_table，可以想成在insert時給的是pid、pname、pdate，而它的爸爸自然就是xxx_table了，所以控制的是xid、xname，對照下面幾行比較清楚明白
xxx_type-->pid、pname、pdate==>:new、:old
xxx_nested -->xxx_type
xxx_table(v1)-->xid、xname、ooo_nested(xxx_nested)==>:parent

※再次執行增刪改，然後再看v1或xxx_table就會發現成功了

Trigger三(DML trigger三 表、行級trigger) (PL/SQL 二十)

也就是將行級和表級合成一個trigger，之前的做法要寫四支trigger(before、after+行、表級)
分成四種，可看官網的解釋，稍為往下一點的Table 9-1

※基本語法

CREATE OR REPLACE TRIGGER LINE_TABLE_TRIGGER
    FOR INSERT OR UPDATE OR DELETE ON DEPT
    COMPOUND TRIGGER
    
    BEFORE STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('表級之前');
    END BEFORE STATEMENT;
    
    AFTER STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('表級之後');
    END AFTER STATEMENT;
    
    BEFORE EACH ROW IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('行級之前');
    END BEFORE EACH ROW;
    
    AFTER EACH ROW IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('行級之後');
    END AFTER EACH ROW;
END;

※執行「INSERT INTO DEPT VALUES (50, 'a', 'b');」可以出現結果

※結果：
表級之前
行級之前
行級之後
表級之後

※使用:new、:old

CREATE OR REPLACE TRIGGER LINE_TABLE_TRIGGER
    FOR INSERT OR UPDATE OR DELETE ON DEPT
    COMPOUND TRIGGER
    
    BEFORE EACH ROW IS
    BEGIN
        IF INSERTING THEN
            IF :new.dname IS NULL THEN
                :new.dname := 'default';
            END IF;
    
            IF :new.loc IS NULL THEN 
                :new.loc := '芝加哥';
            END IF;
        END IF;
    END BEFORE EACH ROW;
END;

※INSERT INTO DEPT(DEPTNO) VALUES(50);

※當然還是只有行級才能用

※宣告變數

CREATE OR REPLACE TRIGGER xxx
    FOR INSERT OR UPDATE OR DELETE ON EMP
    COMPOUND TRIGGER
    
    BEFORE EACH ROW IS
        xxx_sal emp.sal%TYPE;
    BEGIN
        IF INSERTING OR UPDATING THEN
            :new.ename := UPPER(:new.ename);
        END IF;
    
        IF INSERTING THEN
            SELECT AVG(sal) INTO xxx_sal FROM EMP;
            IF :new.sal > xxx_sal THEN
                &RAISE_APPLICATION_ERROR(-20000, '不可高於平均工資');
            END IF;
        END IF;
    END BEFORE EACH ROW;
END;

※INSERT INTO EMP(EMPNO, ENAME, SAL) VALUES(9999, 'test', 8000);

※寫在IS和BEGIN之間即可

Trigger二(DML trigger二 REFERENCING、WHEN、增刪改ING、FOLLOWS) (PL/SQL 十九)

※REFERENCING

預設的:new和:old可以改成自己喜歡的，可以只改一個

CREATE OR REPLACE TRIGGER line_trig_update
    BEFORE UPDATE OF SAL ON EMP
    REFERENCING
        new AS ooo
        old AS xxx
    FOR EACH ROW
DECLARE
BEGIN
    IF ABS(:ooo.sal - :xxx.sal) >= 1000 THEN
        RAISE_APPLICATION_ERROR(-20000, '加薪太過！');
    END IF;
END;

※如上new改成ooo；old改成xxx，所以new和old就不能用了，AS可省略

※WHEN

trigger被觸發之後會判斷這裡設定的條件

CREATE OR REPLACE TRIGGER line_trig_update
    BEFORE UPDATE OF SAL ON EMP FOR EACH ROW
    WHEN (new.sal <= old.sal)
BEGIN
    RAISE_APPLICATION_ERROR(-20000, '新的薪水<=舊的薪水');
END;

※要注意使用WHEN時，new和old，不能加前面的「:」

※增刪改ING

Oracle提供三個內鍵的變數，可以判斷增刪改，分別是INSERTING、UPDATING、DELETING

CREATE OR REPLACE TRIGGER line_trig_update
    BEFORE INSERT OR UPDATE OR DELETE ON DEPT
    -- FOR EACH ROW
BEGIN
    IF INSERTING THEN
        RAISE_APPLICATION_ERROR(-20000, '新增');
    ELSIF UPDATING THEN
        RAISE_APPLICATION_ERROR(-20001, '修改');
    ELSIF DELETING THEN
        RAISE_APPLICATION_ERROR(-20002, '刪除');
    END IF;
END;

※行、表級都可以用，BEFORE後面的INSERT等還是要寫

※FOLLOWS

多個觸發器的條件都一樣時，順序不一定會按照我們想的去做，所以可以用FOLLOWS

CREATE OR REPLACE TRIGGER dept1
    BEFORE INSERT ON DEPT FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('111');
END;
    
CREATE OR REPLACE TRIGGER dept2
    BEFORE INSERT ON DEPT FOR EACH ROW
    -- FOLLOWS dept1
BEGIN
    DBMS_OUTPUT.PUT_LINE('222');
END;
    
CREATE OR REPLACE TRIGGER dept3
    BEFORE INSERT ON DEPT FOR EACH ROW
    -- FOLLOWS dept2
BEGIN
    DBMS_OUTPUT.PUT_LINE('333');
END;

※如上有三個觸發器，我新增時的順序是321，但我想要123，所以將上面的註解打開即可

※FOLLOWS後面接trigger名稱，後面接的trigger觸發完後就會換自己了

※如果2和3都接1不會錯，只是就和沒設定FOLLOWS一樣，順序不確定，沒意義了

Trigger一(DML trigger一 增刪改、子事務、自增主鍵) (PL/SQL 十八)

※Trigger就是執行了某件事後，會自動做某件事的動作

※DML trigger針對增刪改的之前或之後，自動執行一些程式，分成表級和行級

※表級DML trigger

※針對表的trigger

CREATE OR REPLACE TRIGGER trigger_test
    BEFORE INSERT OR DELETE ON DEPT
DECLARE
    xxx_min NUMBER;
    xxx_week NUMBER;
BEGIN
    SELECT 
        TO_NUMBER(TRIM(TO_CHAR(SYSDATE, 'D'))), 
        TO_NUMBER(TRIM(TO_CHAR(SYSDATE, 'HH24MI')))
    INTO xxx_week, xxx_min FROM DUAL;
    
    IF xxx_week = 1 OR xxx_week = 7 THEN
        RAISE_APPLICATION_ERROR(-20000, '星期六日不允許更動DEPT表');
    ELSIF xxx_min < 900 OR xxx_min > 1830 THEN
        RAISE_APPLICATION_ERROR(-20001, '非上班時間不允許更動DEPT表');
    END IF;
END;

※執行「INSERT INTO DEPT VALUES(80, 'a', 'b');」因為是BEFORE，所以會在執行insert之前就去判斷能不能新增

※1代表星期日，7代表星期六

※針對表裡的欄位trigger

CREATE OR REPLACE TRIGGER trigger_test
BEFORE UPDATE OF LOC ON DEPT
DECLARE
    xxx_hour NUMBER;
BEGIN
    SELECT TO_NUMBER(TRIM(TO_CHAR(SYSDATE, 'HH24')))
    INTO xxx_hour FROM DUAL;
    
    IF xxx_hour >= 12 THEN
        RAISE_APPLICATION_ERROR(-20001, '12點之後不允許修改DEPT表的LOC欄位');
    END IF;
END;

※因為是針對DEPT表的LOC欄位，所以「UPDATE DEPT SET DNAME = 'xxx' WHERE DEPTNO = 80;」可以成功修改，但「UPDATE DEPT SET LOC = 'ooo' WHERE DEPTNO = 80;」如果過12點就會跳出trigger

※子事務

※trigger裡也可以有事務

※新增一張表做練習

CREATE TABLE after_emp_sal(
    EMPNO NUMBER(4),
    ENMAE VARCHAR2(10),
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    AFTER_SAL NUMBER(7,2),
    CONSTRAINT AFTER_PK PRIMARY KEY(EMPNO)
);

※這張表是在做EMP的SAL(薪水)和COMM(佣金)欄位，加總後判斷多少錢，然後依加薪的程度有所不同，最後新增到這張表

※先不開啟子事務

CREATE OR REPLACE TRIGGER add_sal
    AFTER INSERT OR UPDATE OF SAL, COMM ON EMP
DECLARE
    -- PRAGMA AUTONOMOUS_TRANSACTION;
    CURSOR xxx_cur IS SELECT * FROM EMP;
    xxx_emp EMP%ROWTYPE;
    xxx_sal EMP.SAL%TYPE;
    xxx_after_sal after_emp_sal.after_sal%type;
BEGIN
    FOR xxx_emp IN xxx_cur LOOP
        xxx_sal := NVL(xxx_emp.sal, 0) + NVL(xxx_emp.comm, 0);
    
        IF xxx_sal < 2000 THEN
            xxx_after_sal := xxx_sal * 1.3;
        ELSIF xxx_sal <= 3000 THEN
            xxx_after_sal := xxx_sal * 1.2;
        ELSIF xxx_sal <= 4000 THEN
            xxx_after_sal := xxx_sal * 1.1;
        END IF;
    
        INSERT INTO after_emp_sal values(
            xxx_emp.empno,
            xxx_emp.ename,
            xxx_emp.sal,
            xxx_emp.comm,
            xxx_after_sal
        );
    END LOOP;
    -- COMMIT;
END;

※執行「INSERT INTO EMP (empno, ename, sal, comm) VALUES(8888, 'xxx', 2400, 200);」之後就會發現after_emp_sal已經新增了，也包含了8888這筆，但如果開啟另外一個session，就會發現是空的，因為沒有COMMIT，所以COMMIT或ROLLBACK後，就會全看的見或全看不見

※如果只開啟trigger的COMMIT，就會出ORA-04092:cannot COMMIT in a trigger錯誤訊息，說trigger裡，不能有COMMIT，這時再開啟PRAGMA那行的子事務就可以了

※兩者最大的差異在8888新增的這一筆，如果是用子事務，就不會包括

※如果有子事務，然後把INSERT那筆rollback後，after_emp_sal還是有資料(當然沒有8888，要等下次再做一次才有)

※行級DML trigger

只有加上FOR EACH ROW才表示行級DML trigger，也才可以使用:new、:old

官網有寫:new和:old的表，新增的:old和刪除的:new都是null

※新增測試

CREATE OR REPLACE TRIGGER line_trig_add
    BEFORE INSERT ON DEPT FOR EACH ROW
DECLARE
    xxx_count NUMBER;
    xxx_deptno NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM DEPT 
    WHERE :new.loc IN(
        SELECT LOC FROM DEPT
    );
    
    IF xxx_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20000, '公司沒有這個地點！');
    ELSE
        xxx_deptno := :new.deptno;
        IF SUBSTR(xxx_deptno, LENGTH(xxx_deptno), 1) != 0 OR xxx_deptno <= 0 THEN
            RAISE_APPLICATION_ERROR(-20001, '部門號必需是10的倍數！');
        END IF;
    END IF;
END;

※INSERT INTO DEPT VALUES(80, 'xxx', 'ooo');
INSERT INTO DEPT VALUES(62, 'xxx', 'CHICAGO');
以上兩條分別出現不一樣的trigger

※修改測試

CREATE OR REPLACE TRIGGER line_trig_update
    BEFORE UPDATE OF SAL ON EMP FOR EACH ROW
DECLARE
BEGIN
    IF ABS(:new.sal - :old.sal) >= 1000 THEN
        RAISE_APPLICATION_ERROR(-20000, '加薪太過！');
    END IF;
END;

※如果加薪幅度超過1000就會跳出trigger

※刪除測試

CREATE OR REPLACE TRIGGER line_trig_delete
    BEFORE DELETE ON DEPT FOR EACH ROW
DECLARE
BEGIN
    IF :old.deptno = 10 THEN
        RAISE_APPLICATION_ERROR(-20000, '無法刪除10部門');
    END IF;
END;

※只有刪除10部門才會跳出trigger

※注意事項

1.宣告ddd DEPT%ROWTYPE;
然後使用ddd := :new;是不可以的，雖然:new或:old很像%ROWTYPE，但是是不可以的

2.宣告ooo NUMBER;
然後使用ooo := :old.sal;這也是不行的，原始值是不能被修改的，但:new是可以的，如上面的新增就有用到

3.上面的程式碼是我自己run成功的結果貼上去的，有一次我自己打開PL/SQL Developer，然後打開一個新的Test Window，然後複製我成功的程式碼，居然出現「ORA-01036: illegal variable name/number」，debug後發現是:new、:old的錯，也就是它認為我不能有「:」開頭的變數，見鬼了，後來我try了好久，發現有這種「:」開頭的，可以先把它刪除，變成new和old然後編譯就會過了，但當然是不能run，所以我去trigger按右鍵Edit或在command輸入edit trigger名稱就會跳出來，然後再將「:」加上去，最後編譯就可以了

4.編譯完trigger後，就不能更改別的表了，如ON DEPT，就不能換成別的，否則會出現「ORA-04095:trigger 'trigger名稱' already exists on another table, cannot replace it」的錯，只能先Drop這個trigger或重新寫一支trigger，名稱不一樣就ok

※自增主鍵

Oracle12c有提供和其他家資料庫一樣，不用再做Sequence，以下先說不是12c時的做法

※建立兩張表做練習

CREATE SEQUENCE ttt_sequence;
    
CREATE TABLE trig_test_table(
    id NUMBER,
    name VARCHAR2(10),
    CONSTRAINT ttt_pk PRIMARY KEY(id)
);
    
CREATE TABLE trig_test_table_copy AS SELECT * FROM trig_test_table WHERE 1=0;

※where 1=0是因為假設trig_test_table有10筆資料，而因為where條件不成立，所以只會複製表的結構而已

※trigger

CREATE OR REPLACE TRIGGER trigger_test
    BEFORE INSERT ON trig_test_table_copy FOR EACH ROW
DECLARE
    xxx_ttt trig_test_table%ROWTYPE;
BEGIN
    DELETE FROM trig_test_table_copy;
    SELECT ttt_sequence.NEXTVAL INTO :new.id FROM DUAL;
    
    xxx_ttt.id := :new.id;
    xxx_ttt.name := :new.name;
    
    INSERT INTO trig_test_table VALUES xxx_ttt;
END;

※也可以不用%ROWTYPE，直接用一般的INSERT，如下：

CREATE OR REPLACE TRIGGER trigger_test
    BEFORE INSERT ON trig_test_table_copy FOR EACH ROW
BEGIN
    DELETE FROM trig_test_table_copy;
    INSERT INTO VALUES(ttt_sequence.NEXTVAL, :new.name);
END;

※因為不用宣告變數，所以連DECLARE也不要了，比較簡潔

※insert into trig_test_table_copy(name) values('xxx');就可以看到trig_test_table新增進去了

※多一張表是因為如果沒有copy那張表，那第一次insert時觸發了Trigger，而Trigger裡面因為沒有copy那張表，是自己的表，那又觸發了Trigger，所以會變成無限迴圈

※Oracle12c的自增主鍵

CREATE TABLE trig_test_table(
    -- id NUMBER,
    id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR2(10),
    CONSTRAINT ttt_pk PRIMARY KEY(id)
);

※註解的部分變成下面那一行，其實也就是將原本的SEQUENCE寫在裡面，和table挷起來而已，圓括號裡的參數可以不打，參數用法都和SEQUENCE一樣

※這時連trigger都不用了

包三(DBMS_JOB、DBMS_ASSERT) (PL/SQL 十七)

※DBMS_JOB

官方手冊在這
其實就是排程，看要自動做什麼事情的功能



※我試過每一秒就執行一次，使用DBMS_OUT.PUT_LINE但看不出效果，所以就用個測試表來練習，才看得出來

DROP TABLE dbms_job_test_table PURGE;
DROP SEQUENCE dbms_job_test;
    
CREATE TABLE dbms_job_test_table(
    jid NUMBER,
    jname VARCHAR2(20),
    jdate DATE,
    CONSTRAINT jid_pk PRIMARY KEY(jid)
);
    
CREATE SEQUENCE dbms_job_test;

※使用procedure做一個新增的功能

CREATE OR REPLACE PROCEDURE
    save_dbms_job_test_table_proc(p_name dbms_job_test_table.jname%TYPE) IS 
BEGIN
    INSERT INTO dbms_job_test_table VALUES (dbms_job_test.NEXTVAL, p_name, SYSDATE);
END;

※使用排程，參數的用途請看官網的解釋

DECLARE
    num NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(
        num,
        'save_dbms_job_test_table_proc(''xxx'');',
        SYSDATE,
        'SYSDATE + (1/(60*60*24))'
    );
    
    DBMS_OUTPUT.PUT_LINE('num=' || num);
    COMMIT;
END;

※結果：num=24

※要注意第二個參數後面有個「;」，而結果的24視個人情況而定

※使用「SELECT * FROM dbms_job_test_table;」過個幾秒執行一次就會看出效果

※這裡也看得到排程的程式

※刪除排程

DECLARE
BEGIN
    DBMS_JOB.REMOVE(24);
    DBMS_OUTPUT.PUT_LINE('刪除job成功');
    COMMIT;
END;

※執行了以後就不會再新增了

※但有時候可能會忘記24這個數字，所以有一張表「user_jobs」，可以看到JOB這個欄位，如果還沒REMOVE可以看得到24這個數字，REMOVE完就沒了，也就證明刪除了

※DBMS_ASSERT

這個包可以將字串做一些驗證或轉換，可參考官網

※ENQUOTE_LITERAL、ENQUOTE_NAME

SELECT
    DBMS_ASSERT.ENQUOTE_LITERAL('xxx') as A,
    DBMS_ASSERT.ENQUOTE_NAME('xxx') as B
FROM DUAL;

※結果：
'xxx'
"XXX"

※ENQUOTE_LITERAL會在前後加上「'」

※ENQUOTE_NAME會將字串變成大寫，然後會在前後加上「"」

※SQL_OBJECT_NAME、QUALIFIED_SQL_NAME、SIMPLE_SQL_NAME

SELECT
    DBMS_ASSERT.SQL_OBJECT_NAME('xxx') as C,
    DBMS_ASSERT.QUALIFIED_SQL_NAME('xxx') as D,
    DBMS_ASSERT.SIMPLE_SQL_NAME('xxx') as E
FROM DUAL;

※這三個方法如果都正確，都會回傳字串

※SQL_OBJECT_NAME是找有沒有符合的物件，例如叫xxx的function或procedure

※QUALIFIED_SQL_NAME、SIMPLE_SQL_NAME是指xxx是合法的SQL名稱嗎？所以如果輸入123xxx，因為是數字開頭，所以會錯，就不能用123xxx當變數，兩個我分不太清楚差在哪裡，只知道SIMPLE_SQL_NAME不檢查長度

※SCHEMA_NAME、NOOP

SELECT
    DBMS_ASSERT.SCHEMA_NAME('DIP') as F,
    DBMS_ASSERT.NOOP('xxx') as G
FROM DUAL;

※NOOP輸入什麼就返回什麼，類似dos的echo

※SCHEMA_NAME裡的字串一定是大寫，驗證是不是有效的SCHEMA，也可用滑鼠看，但Oracle SQL Developer看不到自己的