Oracle SQL 進階查詢與函數實戰

排序與 NULL 處理

-- 多欄位排序：部門升序、薪資降序
SELECT * FROM s_emp ORDER BY dept_id, salary DESC;

-- 使用別名排序
SELECT dept_id, AVG(salary) avg_sal FROM s_emp GROUP BY dept_id ORDER BY avg_sal;

-- NULL 在升序視為最大值，降序視為最小值

字串操作函數

數值函數

ROUND(45.935, 2)  -- 45.94  四捨五入
TRUNC(45.995, 1)  -- 45.9   直接截斷

DUAL 虛擬表

Oracle 提供的單列單行虛表，用於不需查實體表的運算：

SELECT SYSDATE FROM DUAL;
SELECT ROUND(45.935,2) FROM DUAL;

彙總與分組查詢

-- 各部門平均薪資
SELECT dept_id, AVG(salary) avg_sal
FROM s_emp
GROUP BY dept_id
ORDER BY avg_sal;

-- 平均薪資 > 2000 的部門 (HAVING 過濾分組)
SELECT dept_id, AVG(salary) avg_sal
FROM s_emp
GROUP BY dept_id
HAVING AVG(salary) > 2000;

-- 效率寫法：WHERE 先過濾再分組
SELECT dept_id, AVG(salary)
FROM s_emp
WHERE dept_id != 42
GROUP BY dept_id;

關鍵差異：WHERE vs HAVING

• WHERE：針對單筆記錄過濾，可使用單列函數
• HAVING：針對分組結果過濾，需搭配彙總函數

多表聯結範例

-- 等值聯結 (舊語法)
SELECT e.first_name, d.name, r.name
FROM s_emp e, s_dept d, s_region r
WHERE e.dept_id = d.id AND d.region_id = r.id;

-- 等值聯結 (ANSI 標準語法)
SELECT e.first_name, d.name, r.name
FROM s_emp e
JOIN s_dept d ON e.dept_id = d.id
JOIN s_region r ON d.region_id = r.id;

表名查詢技巧

-- Oracle 資料字典表名儲存為大寫
SELECT table_name FROM user_tables WHERE table_name = 'S_EMP';
-- 或使用 UPPER()
SELECT table_name FROM user_tables WHERE UPPER(table_name) = 'S_EMP';

-- ESCAPE 處理底線萬用字元
SELECT * FROM user_tables WHERE table_name LIKE 's\_%' ESCAPE '\';