SQL*Plus 進階技巧：使用 NEW_VALUE 將查詢結果存入變數

SQL*Plus 的 COLUMN ... NEW_VALUE 子參數可將查詢結果直接存入替換變數，讓腳本具備程式語言般的變數操作能力，減少資料庫存取、提升執行效率。

基本語法

COLUMN column_name NEW_VALUE variable_name [NOPRINT]
SELECT value_expr column_name FROM ...;

實戰範例 1：擷取資料庫參數

COLUMN log_mode NEW_VALUE log_mode NOPRINT
SELECT value log_mode FROM v$parameter WHERE name = 'log_archive_start';

SELECT 'The current archivelog mode is '||'&&log_mode' FROM dual;

實戰範例 2：擷取系統日期

COLUMN today NEW_VALUE today
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI') today FROM dual;

實戰範例 3：STATSPACK 報表動態計算區塊大小

COLUMN db_block_size NEW_VALUE blksz NOPRINT
SELECT value db_block_size FROM v$parameter WHERE name = 'db_block_size';

SET PAGES 999 LINES 80
SPOOL tab_rpt.lst

SELECT table_name c1,
       b.extents c2,
       b.freelists c3,
       num_rows c4,
       (num_rows * avg_row_len) * 100 / (blocks * &blksz) AS pct_used
FROM ...;

此處 &blksz 會被替換為查詢得到的 db_block_size 值，避免重複查詢。

關鍵優勢

• 變數定義與賦值合一，無需 PL/SQL 區塊
• 可用於後續 SQL、報表標題、條件判斷
• 搭配 NOPRINT 隱藏中間查詢輸出
• 支援多變數鏈式賦值

注意事項

• 查詢必須**恰好回傳一列一行**，否則變數值不可預期
• 變數名稱區分大小寫，建議統一小寫
• NEW_VALUE 在 SELECT 執行**後**生效，同一陳述式不可立即引用

善用 NEW_VALUE，能讓 SQL*Plus 腳本從「一次性查詢」進化為「可重用、參數化的自動化工具」。