Sql Query

SELECT
    o.OrderId
    ,o.OrderDate
    ,c.CustomerName
    ,SUM(d.Quantity * d.UnitPrice) AS TotalAmount
FROM Orders AS o
INNER JOIN Customers AS c
    ON o.CustomerId = c.CustomerId
INNER JOIN OrderDetails AS d
    ON o.OrderId = d.OrderId
WHERE o.OrderDate >= @StartDate
    AND o.Status = @Status
GROUP BY
    o.OrderId
    ,o.OrderDate
    ,c.CustomerName
HAVING SUM(d.Quantity * d.UnitPrice) > @MinAmount
ORDER BY o.OrderDate DESC;

資料表別名

• 必須使用 AS 關鍵字指定別名（如 Orders AS o），不使用隱式別名（Orders o）。
• 別名應簡短且有意義，通常取資料表名稱的首字母小寫（如 Orders AS o、CustomerAddresses AS ca）。

索引友善查詢（Crucial）

避免破壞索引的寫法

• 禁止對欄位套用函式後再比較，會導致索引失效：

-- ❌ 索引失效
WHERE YEAR(OrderDate) = 2024
WHERE CONVERT(VARCHAR, OrderDate, 112) = '20240101'
WHERE ISNULL(Status, 0) = 1

-- ✅ 索引友善
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE Status = 1

• 禁止對欄位進行隱式型別轉換：確保比較值的型別與欄位型別一致（如 NVARCHAR 欄位使用 N'...' 前綴）。

萬用字元查詢

• LIKE 的前綴萬用字元（LIKE '%keyword'）無法使用索引，應盡量避免。
• 若需全文搜尋，建議使用 Full-Text Search 而非 LIKE '%...%'。

SELECT 規範

• 禁止 SELECT *：必須明確列出所需欄位，減少 I/O 開銷並避免結構變更時的隱性問題。
• 例外：EXISTS (SELECT 1 FROM ...) 中的子查詢允許使用 SELECT 1。

分頁查詢

• 使用 OFFSET ... FETCH NEXT 語法（SQL Server 2012+），不使用舊式 ROW_NUMBER() 子查詢包裝。

SELECT
    ProductId
    ,ProductName
FROM Products
ORDER BY ProductId
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

NULL 處理

• 比較 NULL 使用 IS NULL / IS NOT NULL，禁止 = NULL。
• 注意 NOT IN 與 NULL 值的陷阱：若子查詢可能回傳 NULL，改用 NOT EXISTS。

-- ❌ 當子查詢含 NULL 時，NOT IN 會回傳空結果
SELECT * FROM Products
WHERE CategoryId NOT IN (SELECT CategoryId FROM ExcludedCategories);

-- ✅ NOT EXISTS 不受 NULL 影響
SELECT p.*
FROM Products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM ExcludedCategories AS e
    WHERE e.CategoryId = p.CategoryId
);

交易與鎖定

• 長時間執行的查詢應評估是否需要 WITH (NOLOCK) 或 READ UNCOMMITTED 隔離等級（僅限可容忍 Dirty Read 的報表查詢）。
• 更新操作若涉及先讀後寫，考慮使用 UPDLOCK 或適當的隔離等級防止競爭條件。

效能注意事項

• 避免巢狀子查詢過深：超過 2 層的巢狀子查詢應考慮改用 CTE (WITH) 或暫存資料表提升可讀性與效能。
• 大量資料操作：INSERT, UPDATE, DELETE 大量資料列時，應分批處理（如每批 5,000～10,000 筆），避免長時間鎖定與交易日誌暴增。
• UNION vs UNION ALL：若不需要去重，使用 UNION ALL，避免不必要的排序開銷。

Oracle 專用語法（Oracle SQL / PL/SQL）

以下規則僅適用於 Oracle 資料庫，與上方 T-SQL 章節不重疊。

SQL Injection 防範

• 動態 SQL 使用 Bind Variables（:paramName），禁止字串串接：

-- ✅ 正確
EXECUTE IMMEDIATE 'SELECT * FROM Products WHERE CategoryId = :cid'
    USING v_category_id;

-- ❌ 錯誤
EXECUTE IMMEDIATE 'SELECT * FROM Products WHERE CategoryId = ' || v_category_id;

分頁查詢

• Oracle 12c 以上使用 OFFSET ... FETCH NEXT（與 T-SQL 語法相同）：

SELECT ProductId, ProductName
FROM Products
ORDER BY ProductId
OFFSET :offset ROWS
FETCH NEXT :page_size ROWS ONLY;

• Oracle 11g 以下使用 ROWNUM 雙層包裝：

SELECT *
FROM (
    SELECT inner_q.*, ROWNUM AS rn
    FROM (
        SELECT ProductId, ProductName
        FROM Products
        ORDER BY ProductId
    ) inner_q
    WHERE ROWNUM <= :end_row
)
WHERE rn > :start_row;

自動編號（SEQUENCE）

• Oracle 沒有 IDENTITY 欄位，使用 SEQUENCE 搭配 NEXTVAL：

CREATE SEQUENCE seq_products START WITH 1 INCREMENT BY 1 NOCACHE;

INSERT INTO Products (ProductId, ProductName)
VALUES (seq_products.NEXTVAL, :product_name);

• Oracle 12c 以上可使用 GENERATED AS IDENTITY：

ProductId NUMBER GENERATED BY DEFAULT AS IDENTITY

日期與字串轉換

• 日期格式轉換使用 TO_DATE / TO_CHAR，必須明確指定格式遮罩：

-- 字串轉日期
WHERE OrderDate >= TO_DATE(:date_str, 'YYYY-MM-DD')

-- 日期轉字串
SELECT TO_CHAR(OrderDate, 'YYYY-MM-DD HH24:MI:SS') AS FormattedDate
FROM Orders;

DUAL 虛擬資料表

• Oracle 無法在 SELECT 中省略 FROM，計算純運算式時使用 DUAL：

SELECT SYSDATE FROM DUAL;
SELECT seq_products.NEXTVAL FROM DUAL;

NULL 合併

• Oracle 使用 NVL(expr, default) 或 COALESCE(expr1, expr2, ...)（標準 SQL，優先使用 COALESCE）。
• 不支援 ISNULL()（T-SQL 專屬）。

字串串接

• Oracle 使用 || 運算子串接字串，不支援 T-SQL 的 +：

SELECT FirstName || ' ' || LastName AS FullName FROM Employees;

PL/SQL 匿名區塊

• 批次操作或程序性邏輯使用匿名區塊時，以 BEGIN ... END; 包裝：

BEGIN
    UPDATE Orders SET Status = :new_status
    WHERE OrderId = :order_id;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;