この記事について
第3章後編では、SQL*Plus や SQL Developer で使える 置換変数 を解説します。置換変数を活用すると、SQL実行時に値を動的に切り替えられるため、同じSQLを再利用したり、対話的に条件を変えて実行できます。
置換変数とは
置換変数とは、SQLの中に &変数名 の形式で埋め込んでおき、SQL実行前にその部分を別の値に書き換える仕組みです。
- SQL*Plus と SQL Developer の両方で使用可能です
- 列名・テーブル名・WHERE句の値・ORDER BYの列名など、SQL文の任意の場所に埋め込めます
- 変数名はOracleの予約語であっても、SQL*Plusや SQL Developerで変数名として使用できます
置換変数の書き方：& と && の違い
置換変数は &変数名（1つ）と &&変数名（2つ）の2種類があり、挙動が異なります。
| 種類 | 書き方 | 値の保持 | 主な用途 |
|---|---|---|---|
| 一時変数 | &変数名 |
実行後に破棄される | 毎回値を変えて実行したい場合 |
| 永続変数 | &&変数名 |
次回実行でも保持される（DEFINEと同様） | 同じ値を繰り返し使いたい場合 |
&変数名（毎回入力を促す）
-- SQL実行のたびに department_id の値を入力するプロンプトが出る
SELECT employee_id, last_name, department_id
FROM test_employees_subst
WHERE department_id = &dept_id;
-- 文字列の場合は '' で囲む
SELECT employee_id, last_name
FROM test_employees_subst
WHERE last_name = '&name';
動作確認
■テストデータ作成DROP TABLE test_employees_subst PURGE;
CREATE TABLE test_employees_subst (
employee_id NUMBER(6) PRIMARY KEY,
last_name VARCHAR2(30),
department_id NUMBER(4),
salary NUMBER(8,2)
);
INSERT INTO test_employees_subst (
employee_id, last_name, department_id, salary
) VALUES (
1, 'King', 10, 8000
);
INSERT INTO test_employees_subst (
employee_id, last_name, department_id, salary
) VALUES (
2, 'Scott', 10, 5000
);
INSERT INTO test_employees_subst (
employee_id, last_name, department_id, salary
) VALUES (
3, 'Allen', 20, 3000
);
INSERT INTO test_employees_subst (
employee_id, last_name, department_id, salary
) VALUES (
4, 'Brown', 20, 7000
);
INSERT INTO test_employees_subst (
employee_id, last_name, department_id, salary
) VALUES (
5, 'Smith', 30, 2000
);
INSERT INTO test_employees_subst (
employee_id, last_name, department_id, salary
) VALUES (
6, 'Sato', 30, 1000
);
COMMIT;
文字列の場合
実行するたびに次のようなプロンプトが表示されます。
dept_idに値を入力してください: 10
旧 3: WHERE department_id = &dept_id
新 3: WHERE department_id = 10
&&変数名（最初の1回だけ入力）
SELECT employee_id, last_name, &&dept_var
FROM test_employees_subst
ORDER BY &dept_var;
動作確認
&&変数名
- 最初の実行時にのみ入力を求められ、入力した値はそのセッション内で保持されます
- 次回同じ変数を参照するときは、再入力不要で保持された値が使われます
- 値を消すには
UNDEFINEコマンドを実行するか、SQL*Plus を終了します
活用パターン
パターン① 実行時に対話的に値を入力する（&）
-- 部署を動的に絞り込む
SELECT employee_id, last_name, salary, department_id
FROM test_employees_subst
WHERE department_id = &dept_id
ORDER BY salary DESC;
動作確認
実行のたびに異なる部署番号を入力できるため、同じSQLを何度も書き直さずに使い回せます。
パターン② DEFINEコマンドで事前に値をセットする
DEFINE コマンドを使うと、プロンプトを出さずにあらかじめ置換変数の値を設定できます。
-- 事前に変数をセット
DEFINE dept_id = 10
-- SQL実行時に入力プロンプトが出ない（DEFINEの値が自動的に使われる）
SELECT employee_id, last_name, department_id
FROM test_employees_subst
WHERE department_id = &dept_id;
-- 現在定義されている置換変数をすべて確認
DEFINE
-- 特定の変数の値を確認
DEFINE dept_id
-- 変数を削除（値を解放する）
UNDEFINE dept_id
動作確認
DEFINE で設定した値は、SQL*Plus を終了するか UNDEFINE で解放するまで保持されます。複数のSQLに同じ値を使い回したいときに便利です。
& と DEFINE の比較
| 観点 | &変数名 |
DEFINE 変数名 = 値 |
|---|---|---|
| 値のセット方法 | 実行時にプロンプトで入力 | 事前にコマンドで設定 |
| 入力プロンプト | 毎回出る（&）/ 最初のみ（&&） |
出ない |
| 値の保持期間 | 実行後に破棄（&）/ 保持（&&） |
終了または UNDEFINE まで保持 |
| 主な用途 | 毎回条件を変えて実行したい | 同じ値を複数のSQLで使い回したい |
VERIFY システム変数
置換変数を使うと、デフォルトで置換前後のSQL文が次のように2行表示されます。
旧 3: WHERE department_id = &dept_id
新 3: WHERE department_id = 10
VERIFY は、この置換前後の表示を制御するSQL*Plusのシステム変数です。
| コマンド | 効果 |
|---|---|
SET VERIFY ON |
置換前後のSQL文を表示する（デフォルト） |
SET VERIFY OFF |
置換前後のSQL文を表示しない |
SET VER ON / SET VER OFF
|
短縮形 |
-- 表示をOFF
SET VERIFY OFF
SELECT employee_id, last_name
FROM test_employees_subst
WHERE department_id = &dept_id;
-- 入力プロンプトは出るが、置換前後の行は表示されない
動作確認
SET VERIFY OFF
🔑 試験ポイント：
VERIFYのデフォルトはON（表示する）です。SET VERIFY OFFでスッキリした出力にできます。
コマンドまとめ
| コマンド | 内容 |
|---|---|
&変数名 |
実行時に毎回入力を促す。値は実行後破棄 |
&&変数名 |
最初の実行時のみ入力。値はセッション内で保持 |
DEFINE 変数名 = 値 |
事前に変数の値をセットする |
DEFINE |
現在定義されている置換変数をすべて表示 |
UNDEFINE 変数名 |
指定した変数を削除（値を解放） |
SET VERIFY ON |
置換前後のSQL文を表示する（デフォルト） |
SET VERIFY OFF |
置換前後のSQL文を表示しない |
💡 これらは SQL*PlusおよびSQL Developerの専用コマンドです。SQL文ではないため末尾に
;（セミコロン）は不要です。