置換変数の使用方法

SALESでの問合せ(例5-1を参照)と同様の問合せを作成し、職種がSA_MANの従業員のみでなく、様々な職種の従業員のリストを表示するとします。この場合、コマンドの実行ごとに異なる値を編集してWHERE句に挿入する方法もありますが、さらに簡単な方法があります。

WHERE句のテキストSA_MANのかわりに置換変数を使用すると、コマンド自体に値を書き込んだ場合と同じ結果が得られます。

置換変数は、前に1つまたは2つのアンパサンド(&)を付けたものです。SQL*Plusでは、コマンド内で置換変数が検出されると、そのコマンドに置換変数自体ではなく置換変数の値が含まれている場合と同様に、コマンドを実行します。

変数SORTCOLの値がJOB_IDで、変数MYTABLEの値がEMP_DETAILS_VIEWの場合のコマンド例を次に示します。

SELECT &SORTCOL, SALARY
FROM &MYTABLE
WHERE SALARY>12000;

このコマンドは、次のコマンドと同様に実行されます。

SELECT JOB_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;

置換変数を使用する位置および方法

置換変数は、SQLコマンドおよびSQL*Plusコマンド内の任意の位置で使用できますが、最初に入力する単語としては使用できません。SQL*Plusでは、コマンド内で未定義の置換変数が検出された場合、値の入力を求めるプロンプトが表示されます。

このプロンプトには、任意の文字列を入力できます。空白および句読点を含む文字列も入力できます。参照を含むSQLコマンドで、置換変数を引用符で囲む必要がある場所に引用符が挿入されていない場合は、プロンプトが表示されたときに引用符を挿入する必要があります。

SQL*Plusでは、キーボードからの応答が読み込まれます。これは、端末の入力または出力をファイルへリダイレクトした場合でも同様です。端末が使用できない場合(たとえば、バッチ・モードでスクリプトを実行した場合)、SQL*Plusではリダイレクトされたファイルが使用されます。

プロンプトで値を入力すると、置換変数が含まれている行が2回表示されます。1回は入力した値に置換される前、もう1回は置換後です。この表示は、SETコマンドのVERIFY変数をOFFに設定すると非表示にできます。

Created file STATS

この時点で、次のようにスクリプトSTATSを実行します。

@STATS

値の入力を求めるプロンプトに次のように応答します。

Enter value for group_col: JOB_ID
old   1: SELECT   &GROUP_COL,
new   1: SELECT   JOB_ID,
Enter value for number_col: SALARY
old   2:          MAX(&NUMBER_COL) MAXIMUM
new   2:          MAX(SALARY) MAXIMUM
Enter value for table: EMP_DETAILS_VIEW
old   3: FROM     &TABLE
new   3: FROM     EMP_DETAILS_VIEW
Enter value for group_col: JOB_ID
old   4: GROUP BY &GROUP_COL
new   4: GROUP BY JOB_ID

次の出力が表示されます。

JOB_ID        MAXIMUM
---------- ----------
AC_ACCOUNT       8300
AC_MGR          12000
AD_ASST          4400
AD_PRES         24000
AD_VP           17000
FI_ACCOUNT       9000
FI_MGR          12000
HR_REP           6500
IT_PROG          9000
MK_MAN          13000
MK_REP           6000

JOB_ID        MAXIMUM
---------- ----------
PR_REP          10000
PU_CLERK         3100
PU_MAN          11000
SA_MAN          14000
SA_REP          11500
SH_CLERK         4200
ST_CLERK         3600
ST_MAN           8200

19 rows selected.

置換変数の直後に文字を追加する場合は、変数と文字の区切りにピリオドを使用します。次に例を示します。

SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='&X.5';
Enter value for X:  20

これは、次のように解釈されます。

SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='205';

例5-9 置換変数の使用

数値列についてのサブグループ統計(最大値)の計算に使用するSTATSというスクリプトを、次のように作成します。

SELECT &GROUP_COL, MAX(&NUMBER_COL) MAXIMUM
FROM &TABLE
GROUP BY &GROUP_COL
.
SAVE STATS

値の入力を求める不要なプロンプトの回避

ファイルSTATSを拡張して、数値列の最小値、合計および平均値を含めるとします。例5-9では、値の入力を求めるSQL*PlusプロンプトがGROUP_COLに対して2回、NUMBER_COLに対して1回表示されます。それぞれのGROUP_COLまたはNUMBER_COLの前にはアンパサンドが1つ付いています。さらに3つのファンクションを、それぞれの前にアンパサンドを1つ付けてスクリプトに追加した場合は、合計で4回、数値列の値の入力を求めるプロンプトが表示されます。

グループ列および数値列の入力を求めるプロンプトが再度表示されないようにするには、STATS内でそれぞれのGROUP_COLおよびNUMBER_COLの前にもう1つのアンパサンドを追加します。SQL*Plusでは、前に2つのアンパサンドが付いた置換変数が自動的に定義(DEFINE)され、前に1つのみのアンパサンドが付いた置換変数は定義(DEFINE)されません。変数が定義済である場合、現行のセッションでは、変数の値の入力を求めるプロンプトは表示されません。

SELECT   &GROUP_COL,
MAX(&NUMBER_COL) MAXIMUM
FROM     &TABLE
GROUP BY &GROUP_COL

2* MAX(&NUMBER_COL) MAXIMUM

APPEND ,

2* MAX(&NUMBER_COL) MAXIMUM,

CHANGE/&/&&

2* MAX(&&NUMBER_COL) MAXIMUM,

3i

MIN (&&NUMBER_COL) MINIMUM,

4i

SUM(&&NUMBER_COL)  TOTAL,

5i

AVG(&&NUMBER_COL)  AVERAGE

6i

1* SELECT   &GROUP_COL,

CHANGE/&/&&

1* SELECT   &&GROUP_COL,

7* GROUP BY &GROUP_COL

CHANGE/&/&&/

7* GROUP BY &&GROUP_COL

SAVE STATS2

Created file STATS2

最後に、スクリプトSTATS2を実行し、プロンプトに次のように応答します。

START STATS2
Enter value for group_col: JOB_ID
Enter value for number_col: SALARY
Enter value for table: EMP_DETAILS_VIEW

次の出力が表示されます。

JOB_ID        MAXIMUM    MINIMUM      TOTAL    AVERAGE
---------- ---------- ---------- ---------- ----------
AC_ACCOUNT       8300       8300       8300       8300
AC_MGR          12000      12000      12000      12000
AD_ASST          4400       4400       4400       4400
AD_PRES         24000      24000      24000      24000
AD_VP           17000      17000      34000      17000
FI_ACCOUNT       9000       6900      39600       7920
FI_MGR          12000      12000      12000      12000
HR_REP           6500       6500       6500       6500
IT_PROG          9000       4200      28800       5760
MK_MAN          13000      13000      13000      13000
MK_REP           6000       6000       6000       6000

JOB_ID        MAXIMUM    MINIMUM      TOTAL    AVERAGE
---------- ---------- ---------- ---------- ----------
PR_REP          10000      10000      10000      10000
PU_CLERK         3100       2500      13900       2780
PU_MAN          11000      11000      11000      11000
SA_MAN          14000      10500      61000      12200
SA_REP          11500       6100     250500       8350
SH_CLERK         4200       2500      64300       3215
ST_CLERK         3600       2100      55700       2785
ST_MAN           8200       5800      36400       7280

19 rows selected.

NUMBER_COLおよびGROUP_COLの値の入力を求めるプロンプトは、1回のみ表示されます。現行セッションでSTATS2を再実行する場合、TABLEの入力を求めるプロンプトは表示されます(この変数は、名前に1つのアンパサンドが付いていて定義(DEFINE)されないため)が、GROUP_COLまたはNUMBER_COLの入力を求めるプロンプトは表示されません(これらの変数は、名前に二重アンパサンドが付いていて定義(DEFINE)されるため)。

先へ進む前に、次のように入力してシステム変数VERIFYの設定をONに戻します。

SET VERIFY ON

例5-10 二重アンパサンドの使用方法

二重アンパサンドを使用してスクリプトSTATSを拡張した後にそのスクリプトを実行するには、まず、次のように入力して置換の前後に各行が表示されないようにします。

SET VERIFY OFF

この時点で、次のコマンドを入力してSTATSを取得し、編集します。

GET STATS

制限事項

置換変数はバッファ編集コマンドのAPPEND、CHANGE、DELおよびINPUTには使用できません。また、置換が意味をなさない他のコマンドにも使用できません。バッファ編集コマンドのAPPEND、CHANGEおよびINPUTでは、「&」または「&&」で始まるテキストは、他のテキスト文字列と同様に単なる文字列として扱われます。

システム変数

SQL*PlusのSETコマンドとともに指定するシステム変数で、置換変数に影響するものを次に示します。

システム変数	置換変数への影響
SET CONCAT	置換変数またはパラメータの名前と、その置換変数またはパラメータの直後の文字とを区切る文字を定義します。デフォルトではピリオド(.)です。
SET DEFINE	置換文字(デフォルトではアンパサンド(&))を定義し、置換をON/OFFにします。
SET ESCAPE	置換文字の前で使用できるエスケープ文字を定義します。エスケープ文字が検出されると、置換文字は変数置換の要求ではなく、通常の文字として扱われます。デフォルトのエスケープ文字は円記号(\)です。
SET NUMFORMAT	数値を表示するためのデフォルトの書式を設定します(数値置換変数による表示も含む)。
SET NUMWIDTH	数値を表示するためのデフォルトの幅を設定します(数値置換変数による表示も含む)。
SET VERIFY ON	置換の前後にスクリプトの各行を表示します。

システム変数の詳細は、「SET」を参照してください。