Oracle SQLにおける「置換変数」の完全解説～SCOTTスキーマで実践的に学ぶ～

Oracle SQLの「置換変数」は、クエリを動的に実行するための強力な機能です。本記事では、初心者でも直感的に理解できるよう、SCOTTスキーマを活用した実践的な例や、応用技術までを詳しく解説します。

リンク

1. 置換変数とは
1. 利用シーン
2. 一時的な置換変数（&）の使い方
1. 基本例
3. 永続的な置換変数（&&）の活用方法
1. 基本例
4. 動的クエリの実践例（複数条件指定）
1. 条件付き検索例
5. 管理コマンド
1. 置換変数の確認（DEFINE）
2. 置換変数の削除（UNDEFINE）
6. よくあるトラブルと解決策
7. 注意点とベストプラクティス
8. まとめ

1. 置換変数とは

置換変数とは、SQLクエリの中で動的な値を指定するための仕組みです。クエリ実行時に値を入力することで、同じクエリを異なる条件で柔軟に利用できます。

利用シーン

レポート作成時の条件指定
簡易的なインタラクティブクエリ
再利用可能なスクリプト作成

以下、SCOTTスキーマを例に解説を進めます。SCOTTスキーマは、Oracleに標準で提供される学習用のデータセットです。

2. 一時的な置換変数（&）の使い方

一時的な置換変数は、クエリ実行時に値を入力します。毎回異なる値を指定できる点が特徴です。

基本例

以下のクエリは、DEPTNO（部門番号）を指定して従業員を検索します。

SELECT * 
FROM EMP 
WHERE DEPTNO = &deptno;

実行手順

プロンプトで値を入力：mathematicaコードをコピーするEnter value for deptno: 10
指定した部門番号（例：10）でクエリが実行されます。

結果例

EMPNO ENAME      JOB       SAL   DEPTNO
----- ---------- --------- ----  ------
 7839 KING       PRESIDENT 5000     10
 7782 CLARK      MANAGER   2450     10
 7934 MILLER     CLERK     1300     10

3. 永続的な置換変数（&&）の活用方法

永続的な置換変数は、一度入力した値をセッション内で保持します。これにより、同じ条件で繰り返し実行する場合に便利です。

基本例

SELECT * 
FROM EMP 
WHERE DEPTNO = &&deptno;

実行手順

初回実行時に値を入力：mathematicaコードをコピーするEnter value for deptno: 20
セッション内では、再入力なしで同じ値が使用されます。

再入力が必要な場合 以下のコマンドで変数をリセットします。

UNDEFINE deptno;

4. 動的クエリの実践例（複数条件指定）

複数の置換変数を組み合わせることで、より柔軟な検索が可能です。

条件付き検索例

部門番号と最低給与を指定して、該当する従業員を検索します。

COLUMN ENAME FORMAT A15
COLUMN SAL FORMAT 99999

SELECT ENAME, JOB, SAL 
FROM EMP 
WHERE DEPTNO = &deptno
  AND SAL > &min_salary;

実行手順

DEPTNO と MIN_SALARY を入力：yamlコードをコピーするEnter value for deptno: 30 Enter value for min_salary: 1500
条件に一致するデータが表示されます。

結果例

ENAME           JOB       SAL  
--------------- --------- ------
ALLEN           SALESMAN  1600
BLAKE           MANAGER   2850
TURNER          SALESMAN  1500

5. 管理コマンド

置換変数の確認（DEFINE）

現在設定されている置換変数を確認します。

DEFINE deptno;

置換変数の削除（UNDEFINE）

変数を削除し、再入力を促します。

UNDEFINE deptno;

6. よくあるトラブルと解決策

トラブル	原因	解決方法
値が保持されて変更できない	`&&` を使用している	`UNDEFINE` を使用して変数をリセットする
プロンプトが表示されない	`SET DEFINE OFF` が有効になっている	`SET DEFINE ON` を実行する
複数回プロンプトが表示される	同じ変数名を`&`で繰り返し使用している	永続変数（`&&`）を使用して入力を省略する

7. 注意点とベストプラクティス

セキュリティリスク ユーザー入力を直接SQLに挿入する場合、SQLインジェクションのリスクがあります。特に本番環境では、入力値の検証が必須です。
変数名のわかりやすさ 短すぎず、意味のある変数名を使用しましょう（例：deptno や min_salary）。
再利用可能なスクリプト 変数を使ったスクリプトは再利用性が高く、日々の業務を効率化します。

8. まとめ

本記事では、「置換変数」を利用した動的なクエリ作成の基本と応用を、SCOTTスキーマを用いて解説しました。初心者でもすぐに活用できる実例を挙げ、実践的なSQLスキル向上をサポートします。

ぜひ、この記事を参考にしてご自身の環境で試してみてください！質問やフィードバックがあればお気軽にお寄せください。

[参考]
Oracle Database SQL言語リファレンス 19c