物件導向二(排序)(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一樣