Oracle SQL*Plus 命令大全：環境設定、編輯緩衝區、腳本執行與格式化輸出實戰

SQL*Plus 核心概念

• SQL：ANSI 標準查詢語言，操作資料庫物件
• SQL*Plus：Oracle 專屬命令列工具，提供環境控制、報表格式化、腳本管理等擴充功能
• 關鍵差異：SQL 不可縮寫、可操作資料庫、命令存入緩衝區；SQL*Plus 可縮寫、不直接操作資料庫、命令不存入緩衝區

核心命令分類速查

環境變數設定 (SET/SHOW)

輸出重導向與報表產生

SPOOL 命令

SPOOL c:\output\report.txt        -- 開始記錄 (覆蓋)
SPOOL c:\output\report.txt APPEND -- 附加模式
SPOOL c:\output\report.html       -- 搭配 MARKUP HTML
...
SPOOL OFF                         -- 結束記錄

完整報表腳本範例

SET LINESIZE 200 PAGESIZE 50 TRIMSPOOL ON TRIMOUT ON
SET FEEDBACK OFF HEADING ON VERIFY OFF
SET COLSEP ',' MARKUP HTML ON SPOOL ON
SPOOL c:\reports\dept_sal_&_DATE..html

BREAK ON DEPARTMENT_NAME SKIP 1
COMPUTE SUM LABEL '部門總薪' OF SALARY ON DEPARTMENT_NAME
COMPUTE SUM LABEL '公司總薪' OF SALARY ON REPORT

SELECT d.DEPARTMENT_NAME, e.FIRST_NAME || ' ' || e.LAST_NAME AS EMP_NAME,
       e.SALARY
FROM   EMPLOYEES e JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY d.DEPARTMENT_NAME, e.SALARY DESC;

SPOOL OFF
SET MARKUP HTML OFF

替換變數與腳本參數化

-- 定義變數
DEFINE dept_id = 10
DEFINE start_date = '2024-01-01'

-- 腳本參數 (&1, &2... 對應呼叫時傳入)
-- 查詢腳本 query_dept.sql:
SELECT * FROM employees WHERE department_id = &1 AND hire_date > '&2';

-- 執行
@query_dept.sql 10 '2024-01-01'

-- ACCEPT 互動式輸入
ACCEPT p_dept PROMPT '請輸入部門編號: ' NUMBER
SELECT * FROM employees WHERE department_id = &p_dept;

常用格式化命令 (COLUMN / FORMAT)

COLUMN salary FORMAT 999,999.99 HEADING '月薪'
COLUMN hire_date FORMAT A12 HEADING '到職日'
COLUMN email FORMAT A30 TRUNCATE
COLUMN commission_pct FORMAT 0.99 HEADING '提成%' NULL '(無)'

-- 清除格式
CLEAR COLUMNS

實用技巧與最佳實踐

1. login.sql / glogin.sql：每次啟動自動執行，預設設定 LINESIZE、PAGESIZE、DATE FORMAT、EDITOR 等
2. SET SQLPROMPT：自訂提示符，如 SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
3. SET EDITOR：指定外部編輯器 SET EDITOR "notepad++" 或 "code --wait"
4. DESC 進階：DESC table_name PARTITION 顯示分區資訊，DESC function_name 顯示函數簽名
5. 避免 & 衝突：執行含 & 字元腳本前先 SET DEFINE OFF，結束後 SET DEFINE ON
6. 大量資料匯出：SET HEADING OFF FEEDBACK OFF TRIMSPOOL ON PAGESIZE 0 搭配 SPOOL 產生純資料檔
7. 錯誤處理：WHENEVER SQLERROR EXIT SQL.SQLCODE / WHENEVER OSERROR EXIT FAILURE 讓腳本失敗時回傳非零代碼，利於 CI/CD 整合

常見問題快速解決