前言

今天要講的是撰寫SQL的好習慣，要先破除一些迷思，不是寫越多行越厲害，也不是越精簡越好，而是要確定三個關鍵：效能、防呆 & 可讀性。效能差的code會隨著時間資料量變多而出現 timeout 問題，防呆沒寫好導致code可執行但輸出卻是錯誤的(見下方圖1)，可讀性則是為了後續維護著想，註解不寫好、縮排亂七八糟都是下任sponsor閱讀時的痛苦。

撰寫 Oracle 資料庫程式時，以下好習慣有助於 SQL 可讀性與效能

1. 錯誤處理：使用 EXCEPTION 處理異常，提高程序的穩定性
2. 使用索引：建立索引提高經常查詢欄位的查詢校能
   [Hint] 使用 EXPLAIN PLAN 逐行分析 SQL 執行耗費的時間 (下一篇介紹)
3. 注意資料型別：建立Table時要注意欄位資料型別 (日期時間用 DATE 或 TIMESTAMP 類型，非 VARCHAR)
4. 欄位命名精確：一致的命名方式有助於可讀性，可使用駝峰式命名法 or 底線命名 (studentID or student_id)
5. 避免hardcode：非必要時，儘量避免使用固定值的寫法，採用動態變數 (可額外建立config來做參數管控)
6. 批次儲存資料：使用 COMMIT 和 ROLLBACK 控制事務，確保資料一致性
7. 編寫清晰的註解：可以by段落撰寫SQL功能和邏輯，有助於後續維護
8. 安全性：設定不同登入使用者權限來保護資料庫
9. 防呆處理：多用 NVL、upper、lower、to_number & rownum作為防呆

    -- 1. NVL 防呆數字
    -- oracle
    select NVL(AVG(score), 0)
    from student
    ;
    -- mysql
    select ifnull(AVG(score), 0)
    from student
    ;

    -- 2. upper/lower防呆大小寫導致的比對錯誤
    -- oracle
    select *
    from student
    where upper(subject) = 'ENG'
    ;
    -- mysql
    select *
    from student
    where upper(subject) = "ENG"

    -- 3. to_number防止數字比對錯誤(可參考*圖1)
    -- oracle
    select *
    from student
    order by to_number(score) desc
    ;
    -- mysql
    select *
    from student
    order by cast(score as number) desc

【圖1】防止數字比對錯誤

    -- 4. rownum 防止子查詢太多資料 ERROR(可參考*圖2)
    -- oracle
    CREATE TABLE Student (
      ID NUMBER PRIMARY KEY,
      name VARCHAR2(15) NOT NULL,
      Subject VARCHAR2(10) NOT NULL,
      Score VARCHAR2(10) NOT NULL
    );
    INSERT INTO Student VALUES (1, 'Amy', 'Eng', 74);
    INSERT INTO Student VALUES (2, 'Peter', 'Eng', 50);

    CREATE TABLE PHONE (
      name VARCHAR2(15) NOT NULL,
      phone VARCHAR2(15) NOT NULL
    );
    INSERT INTO PHONE VALUES ('Amy', '0956258956');
    INSERT INTO PHONE VALUES ('Peter', '0974859359');
    INSERT INTO PHONE VALUES ('Amy', '02-5689574');

    select s.*
      , (select phone from Phone P where P.name = S.name and rownum<=1) phone
    from student s
    where s.subject = 'Eng'
    ;
    -- mysql 無 rownum 隱形欄位

【圖2】防止子查詢太多資料 ERROR

Reference

• ORACLE SQL 總整理