| SQL*Plus®ユーザーズ・ガイドおよびリファレンス リリース11.2 B56314-03 |
|
 前 |
 次 |
この章では、SQL*Plusコマンド、SQLコマンドおよびPL/SQLブロックの操作方法について説明します。内容は次のとおりです。
この章を読むときは、コンピュータで、示されている例を実際に試してみてください。始める前に、「SQL*Plus概要」で説明したサンプル・スキーマへのアクセス権限があることを確認してください。
SQL*Plusコマンドラインでは、外部エディタを@、@@またはSTARTコマンドと組み合せて使用すると、共通スクリプトの作成および実行に有効です。SQL*Plus、SQLおよびPL/SQLコマンドを含むスクリプトが作成できます。この機能は、複雑なコマンドまたは頻繁に使用されるレポートの格納に特に有効です。
オペレーティング・システムには、スクリプトの作成に使用可能な1つ以上のテキスト・エディタが含まれています。EDITコマンドを入力すると、SQL*Plusのコマンドラインを終了せずに、オペレーティング・システムのデフォルト・テキスト・エディタを実行できます。
デフォルトのテキスト・エディタの名前を保持するには、SQL*PlusのDEFINEコマンドを使用して変数_EDITORを定義します。たとえば、EDITで使用するエディタをviとして定義するには、次のコマンドを入力します。
DEFINE _EDITOR = vi
ユーザー定義は、SQL*Plusを起動すると常に有効になるように、ユーザー・プロファイルまたはサイト・プロファイルに含めることができます。詳細は、「SQL*Plusの構成」、「DEFINE」コマンドおよび「EDIT」コマンドを参照してください。
テキスト・エディタを使用してスクリプトを作成するには、EDITの後に編集または作成するファイルの名前を続けて入力します。たとえば、次のように入力します。
EDIT SALES
EDITを使用すると、ファイルの拡張子を指定しないかぎり、ファイル名拡張子.SQLが名前に追加されます。テキスト・エディタを使用してスクリプトを保存する場合、そのスクリプトは同じファイルに保存されます。EDITを使用すると、スクリプトを作成および編集できます。
各SQLコマンドの終わりにセミコロン、また、ファイル内の各PL/SQLブロックの後にスラッシュ(/)のみの行をそれぞれ挿入する必要があります。複数のSQLコマンドおよびPL/SQLブロックをスクリプトに挿入できます。
例5-1 システム・エディタでのSQLスクリプトの作成
販売担当者とそのコミッションのリストを表示するために問合せを作成したとします。その問合せは、それぞれの従業員の実績を追跡し記録するために月1回実行する計画です。
システム・エディタを使用して問合せを作成および保存するには、エディタを起動し、スクリプトを保存するファイルを作成します。
EDIT SALES
エディタで、次の各行を入力します。SQL文の終わりには、必ずセミコロンを挿入してください。
COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90 SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN';
COMMISSION_PCT列の書式モデルでは、10進値に対しては最初の0(ゼロ)が表示され、行のCOMMISSION_PCTの値が0(ゼロ)の場合は空白のかわりに0(ゼロ)が表示されます。書式モデルおよびCOLUMNコマンドの詳細は、「COLUMN」コマンドおよび『Oracle Database SQL言語リファレンス』を参照してください。
様々なSQL*Plusコマンドを使用して、現在バッファに格納されているSQLコマンドまたはPL/SQLブロックを編集できます。
表5-1「SQL*Plusの編集コマンド」に、コマンドを再入力せずにバッファ内のコマンドを検証または変更できるSQL*Plusコマンドを示します。
表5-1 SQL*Plusの編集コマンド
これらのコマンドは、入力したコマンドの修正または変更に有効です。
SQLバッファには、最新のSQLコマンドまたはPL/SQLコマンドが含まれます。LISTおよびDEL以外のすべての編集コマンドは、バッファ内の1行にのみ影響します。その行をカレント行と呼びます。現行のコマンドまたはブロックを表示した場合、カレント行にはアスタリスクが付きます。
例5-2 バッファ内容の表示
現行のコマンドを表示するとします。次のように、LISTコマンドを使用します。(例4-3「SQLコマンドの入力」の手順に従った後、SQL*Plusを終了するか、別のSQLコマンドまたはPL/SQLブロックを入力した場合は、続行する前に、その例の手順を再実行してください。)
LIST
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY 2 FROM EMP_DETAILS_VIEW 3* WHERE SALARY>12000 |
SELECTコマンドの終わりに入力したセミコロンは表示されません。このセミコロンは、入力時にはコマンドの終わりを示すために必要ですが、SQLコマンドの一部とみなされないため、SQLバッファには格納されません。
SQL*PlusのCHANGEコマンドを使用して、カレント行を編集できます。 次のように、操作によってカレント行は異なります。
バッファ内のコマンドを表示(LIST)または実行(RUN)した場合、コマンドの最終行がカレント行になります。(ただし、スラッシュ(/)コマンドを使用してバッファ内のコマンドを実行しても、カレント行は影響を受けません。)
例5-3 コマンド入力でのエラー
JOB_ID列を選択するつもりで誤ってJO_IDと入力したとします。1行目のJOB_IDを意図的に誤った綴りにして、次のようにコマンドを入力します。
SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN';
画面に次のメッセージが表示されます。
SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY
*
ERROR at line 1:
ORA-00904: invalid column name
|
エラー・メッセージには、問合せの1行目に無効な列名が存在することが示されています。アスタリスクは、エラーの位置(綴りを誤って入力した列であるJOB_ID)を示しています。
この場合、コマンド全体を再入力せずに、バッファ内のコマンドを編集して誤りを修正できます。この時点では、エラーが含まれている行がカレント行になっています。CHANGEコマンドを使用して、誤りを修正します。このコマンドは次の3つの部分で構成され、それぞれの部分はスラッシュまたはその他の英数字以外の文字で区切られます。
CHANGEという単語、またはCという文字
変更する文字列
置換文字列
CHANGEコマンドを使用すると、カレント行の中で最初に現れる変更文字列を検索し、新しい文字列に変更できます。行全体を再入力するには、CHANGEコマンドを使用する必要はありません。
例5-4 エラーの修正
JO_IDをJOB_IDに変更するには、次のようにCHANGEコマンドを使用して行を変更します。
CHANGE /JO_ID/JOB_ID
修正後の行が、次のように表示されます。
1* SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY |
エラー修正の終了後は、RUNコマンドを使用してコマンドを再実行できます。
RUN
SQL*Plusでは、問合せおよびその結果が正しく表示されます。
1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
2 FROM EMP_DETAILS_VIEW
3* WHERE JOB_ID='SA_MAN'
EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY
----------- ------------------------- ---------- --------------
145 Russell SA_MAN $14,000
146 Partners SA_MAN $13,500
147 Errazuriz SA_MAN $12,000
148 Cambrault SA_MAN $11,000
149 Zlotkey SA_MAN $10,500
|
SALARY列には、例4-4「SQL*Plusコマンドの入力」で指定した書式が保持されます。(例4-4「SQL*Plusコマンドの入力」の実行後、SQL*Plusを終了して再起動すると、列は元の書式に戻ります。)
CHANGEコマンドでの大文字と小文字の区別、およびCHANGEコマンドでワイルド・カードを使用してテキストのブロックを指定する方法については、「CHANGE」コマンドを参照してください。
バッファの行末にテキストを追加するには、次のようにAPPENDコマンドを使用します。
LISTコマンド(または行番号)を使用して、変更する行を表示します。
APPENDを入力し、その後に追加するテキストを続けて入力します。追加するテキストが空白で始まる場合は、APPENDという単語とテキストの1文字目を2つの空白で区切ります。1つはAPPENDとテキストの区切りで、もう1つはテキストとともにバッファ内に格納されます。
4* ORDER BY SALARY |
次のコマンドを入力します(APPENDとDESCの間には、必ず空白を2つ入力します)。
APPEND DESC
4* ORDER BY SALARY DESC |
RUNを入力して、問合せを検証します。
1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID='SA_MAN'
4* ORDER BY SALARY DESC
EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY
----------- ------------------------- ---------- --------------
145 Russell SA_MAN $14,000
146 Partners SA_MAN $13,500
147 Errazuriz SA_MAN $12,000
148 Cambrault SA_MAN $11,000
149 Zlotkey SA_MAN $10,500
|
カレント行の後に新しい行を挿入するには、INPUTコマンドを使用します。
行1の前に行を挿入するには、0(ゼロ)を入力し、その後にテキストを続けます。その行がバッファの先頭に挿入され、すべての行が再度1から番号付けされます。
0 SELECT EMPLOYEE_ID
例5-6 行の追加
例5-4「エラーの修正」で変更したSQLコマンドに4行目を追加するとします。すでに行3がカレント行なので、INPUTを入力し、[Return]を押します。
INPUT
次のように、新しい行の入力を求めるプロンプトが表示されます。
4 |
新しい行を入力します。次に、[Return]を押します。
4 ORDER BY SALARY
次のように新しい行の入力を求めるプロンプトが再度表示されます。
5 |
再度[Return]を押して、これ以上入力しないことを示します。その後、RUNコマンドを使用して問合せを検証し再実行します。
1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID='SA_MAN'
4* ORDER BY SALARY
EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY
----------- ------------------------- ---------- --------------
149 Zlotkey SA_MAN $10,500
148 Cambrault SA_MAN $11,000
147 Errazuriz SA_MAN $12,000
146 Partners SA_MAN $13,500
145 Russell SA_MAN $14,000
|
バッファ内の行を削除するには、DELコマンドを使用します。DELを入力し、削除する行番号を指定します。
カレント行から最終行までを削除するとします。次のように、DELコマンドを使用します。
DEL * LAST
DELを実行すると、バッファの次の行がある場合は、その行がカレント行になります。
詳細は、「DEL」を参照してください。
スクリプトにコメントを入力するには、次の3つの方法があります。
単一行のコメントに対して、SQL*PlusのREMARKコマンドを使用する方法
1行以上のコメントに対して、SQLのコメント・デリミタ(/* ...*/)を使用する方法
単一行のコメントに対して、米国規格協会(ANSI)および国際標準化機構(ISO)のコメント「- -」を使用する方法
コマンドラインで入力したコメントは、SQLバッファには格納されません。
REMARKコマンドのみの行をスクリプト内に指定し、同じ行にコメントを続けます。コメントを次の行に継続するには、追加のREMARKコマンドを入力します。REMARKコマンドは、1つのSQLコマンドの行と行の間には挿入しないでください。
REMARK Commission Report; REMARK to be run monthly.; COLUMN LAST_NAME HEADING 'LAST_NAME'; COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999; COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90; REMARK Includes only salesmen; SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN';
SQLのコメント・デリミタ(/*...*/)は、スクリプト内の個別の行に入力するか、SQLコマンドと同じ行に入力するか、またはPL/SQLブロック内の行に入力します。
コメントの初めのスラッシュとアスタリスク(/*)の後に空白を入力する必要があります。
コメントは、次のように複数の行にわたっていてもかまいませんが、コメント内にコメントをネストさせることはできません。
/* Commission Report to be run monthly. */ COLUMN LAST_NAME HEADING 'LAST_NAME'; COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999; COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90; REMARK Includes only salesmen; SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW /* Include only salesmen.*/ WHERE JOB_ID='SA_MAN';
SQL文、PL/SQLブロックまたはSQL*Plusコマンドの中で、ANSI/ISOの「- -」スタイルのコメントを使用できます。終了デリミタがないため、複数行にわたるコメントは入力できません。
PL/SQLおよびSQLの場合、次のように、ある行のコマンドの後にコメントを入力するか、コメントのみの行を入力します。
-- Commissions report to be run monthly DECLARE --block for reporting monthly sales
SQL*Plusコマンドでは、行全体をコメントに指定する場合のみ、「- -」を使用してコメントを挿入できます。たとえば、次のコメントは有効です。
-- set maximum width for LONG to 777 SET LONG 777
次のコメントは無効です。
SET LONG 777 -- set maximum width for LONG to 777
次のSQL*Plusコマンドを入力した場合、このコマンドはコメントとして解釈され、実行されません。
-- SET LONG 777
SQL*Plusには、SQLコマンドまたはPL/SQLコマンドの解析機能はありません。新しい文のそれぞれについて、先頭から数個のキーワードがスキャンされ、SQL、PL/SQLまたはSQL*Plusのいずれのコマンドであるかが判断されます。コメントの挿入箇所によっては、SQL*Plusでコマンドが正しく認識されず、予期しない結果になる場合があります。次に、SQL*Plusのコメントを効果的に使用する方法を示します。
文の先頭から数個のキーワードには、コメントを挿入しないでください。たとえば、次のように入力します。
CREATE OR REPLACE
2 /* HELLO */
3 PROCEDURE HELLO AS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('HELLO');
6 END;
7 /
Warning: Procedure created with compilation errors.
例に示す位置にコメントを挿入すると、コマンドがコマンドとして認識されません。コメントの最初にあるスラッシュ(/)が文の終了記号と解釈され、SQL*PlusからサーバーにPL/SQLブロックが送信されます。コメントを移動して、このエラーを回避します。たとえば、次のように入力します。
CREATE OR REPLACE PROCEDURE
2 /* HELLO */
3 HELLO AS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('HELLO');
6 END;
7 /
Procedure created.
文の終了記号(ピリオド、セミコロンまたはスラッシュ)の後に、コメントを挿入しないでください。たとえば、次のように入力したとします。
SELECT 'Y' FROM DUAL; -- TESTING
次のエラーが戻されます。
SELECT 'Y' FROM DUAL; -- TESTING
*
ERROR at line 1:
ORA-00911: invalid character
|
SQL*Plusでは、文の終了記号の後でテキストを記述することはできないため、コマンドは処理されません。
コメント行の終わり、またはSQL文またはPL/SQLブロック内のコメントの後に文の終了文字を挿入しないでください。たとえば、次のように入力したとします。
SELECT * -- COMMENT;
次のエラーが戻されます。
-- COMMENT
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
|
セミコロンは文の終了記号として解釈されるため、不完全なSQLコマンドがSQL*Plusからサーバーに送信されて処理されるため、エラーになります。
SQL文またはPL/SQLブロックでは、コメントにアンパサンド(&)を使用しないでください。たとえば、次のようにスクリプトを入力したとします。
SELECT REGION_NAME, CITY /* THIS & THAT */ FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
SQL*Plusでは、アンパサンド(&)の後のテキストは置換変数として解釈されるため、この例では、変数&thatの値を求めるプロンプトが表示されます。
Enter value for that: old 2: /* THIS & THAT */ new 2: /* THIS */ REGION_NAME CITY ------------------------- ------------------------------ Americas Seattle Americas Seattle Americas Seattle Europe Oxford Europe Oxford Americas Toronto 6 rows selected. |
SET DEFINE OFFを使用して、置換文字をスキャンしないように設定できます。
置換文字および終了文字の詳細は、「SET」コマンドのDEFINE、SQLTERMINATORおよびSQLBLANKLINESを参照してください。
STARTコマンドを使用すると、スクリプトが取得され、そこに含まれるコマンドが実行されます。SQLコマンド、PL/SQLブロックおよびSQL*Plusコマンドを含むスクリプトを実行するには、STARTを使用します。ファイル内に多くのコマンドを含むことができます。STARTコマンドの後に、ファイルの名前を入力します。
START file_name
デフォルトでは、ファイルに拡張子.SQLが割り当てられていると想定されています。
例5-7 スクリプトの実行
SALES.SQLに格納されているコマンドを取得して実行するには、次のように入力します。
START SALES
SQL*PlusではファイルSALES内のコマンドが実行され、コマンドの結果が画面に表示されます。その際、ファイル内のSQL*Plusコマンドに従って、問合せ結果の書式が次のように設定されます。
LAST NAME MONTHLY SALARY COMMISSION % ------------------------- -------------- ------------ Russell $14,000 0.40 Partners $13,500 0.30 Errazuriz $12,000 0.30 Cambrault $11,000 0.30 Zlotkey $10,500 0.20 |
@(アットマーク)コマンドを使用しても、スクリプトを実行できます。
@SALES
@および@@コマンドを使用すると、STARTコマンドと同様に、スクリプト内のコマンドが表示され実行されます。SET ECHOでの指定は、STARTコマンドと同様に、@および@@コマンドでも有効です。
SQL*Plusでコマンドが入力される様子を表示する場合は、SET ECHO ONに設定します。ECHOシステム変数は、START、@および@@コマンドで実行されるスクリプト内のコマンドの表示を制御します。ECHO変数をOFFに設定すると、コマンドは表示されません。
START、@および@@では、スクリプト内の最後のSQLコマンドまたはPL/SQLブロックがバッファ内に残ります。
一連のスクリプトを順に実行するには、最初に、複数のSTARTコマンドを順に指定したスクリプトを作成します。それぞれのSTARTコマンドの後には、スクリプト名を続けます。次に、STARTコマンドが含まれているスクリプトを実行します。たとえば、SALESRPTというスクリプトには次のSTARTコマンドを挿入できます。
START Q1SALES START Q2SALES START Q3SALES START Q4SALES START YRENDSLS
EXITコマンドをスクリプトに含めると、スクリプトの終了時に値を戻すことができます。詳細は、「EXIT」コマンドを参照してください。
WHENEVER SQLERRORコマンドをスクリプトに含めると、スクリプトがSQLエラーを生成した場合に、自動的にリターン・コードを戻し、SQL*Plusを終了できます。同様に、WHENEVER OSERRORコマンドを含めると、オペレーティング・システム・エラーが発生した場合に、自動的に終了することができます。iSQL*Plusでは、スクリプトが停止され、作業領域にフォーカスが戻されます。詳細は、「WHENEVER SQLERROR」コマンドおよび「WHENEVER OSERROR」コマンドを参照してください。
SQL*PlusのDEFINEコマンドを使用すると、置換変数という変数を定義して、1つのスクリプトの中で繰り返し使用できます。タイトル内で使用し、キーストロークを保存するための置換変数も(長い文字列を短い名前の変数の値として定義して)定義できます。
例5-8 置換変数の定義
置換変数L_NAMEを定義し、それに値「SMITH」を指定するには、次のコマンドを入力します。
DEFINE L_NAME = SMITH
変数定義を確認するには、次のようにDEFINEの後に変数名を指定します。
DEFINE L_NAME
DEFINE L_NAME = "SMITH" (CHAR) |
置換変数のすべての定義を表示するには、DEFINEのみを入力します。DEFINEを使用して明示的に定義する置換変数には、CHAR値のみ指定できます(ユーザーが変数に割り当てる値は、常にCHARデータ型として扱われます)。ACCEPTコマンドを使用すると、NUMBERデータ型の置換変数を暗黙的に定義できます。ACCEPTコマンドについては、後半で詳しく説明します。
SQL*Plusのインストール中に定義され、SQL*Plus情報を保持する8つの変数があります。これらの変数は他の変数と同様に、再定義、参照または削除できます。明示的に削除または再定義しないかぎり、これらの変数はどのセッションでも使用できます。
SALESでの問合せ(例5-1「システム・エディタでのSQLスクリプトの作成」を参照)と同様の問合せを作成し、職種が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に設定すると非表示にできます。
例5-9 置換変数の使用
数値列についてのサブグループ統計(最大値)の計算に使用するSTATSというスクリプトを、次のように作成します。
SELECT &GROUP_COL, MAX(&NUMBER_COL) MAXIMUM FROM &TABLE GROUP BY &GROUP_COL . SAVE STATS
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';
ファイルSTATSを拡張して、数値列の最小値、合計および平均値を含めるとします。You may have noticed that SQL*Plus prompted you twice for the value of GROUP_COL and once for the value of NUMBER_COL in 例5-9「置換変数の使用」では、値の入力を求めるプロンプトが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)されません。変数が定義済である場合、現行のセッションでは、変数の値の入力を求めるプロンプトは表示されません。
例5-10 二重アンパサンドの使用方法
二重アンパサンドを使用してスクリプトSTATSを拡張した後にそのスクリプトを実行するには、まず、次のように入力して置換の前後に各行が表示されないようにします。
SET VERIFY OFF
この時点で、次のコマンドを入力してSTATSを取得し、編集します。
GET STATS
SELECT &GROUP_COL, MAX(&NUMBER_COL) MAXIMUM FROM &TABLE GROUP BY &GROUP_COL |
2
2* MAX(&NUMBER_COL) MAXIMUM |
APPEND ,
2* MAX(&NUMBER_COL) MAXIMUM, |
CHANGE/&/&&
2* MAX(&&NUMBER_COL) MAXIMUM, |
I
3i |
MIN (&&NUMBER_COL) MINIMUM,
4i |
SUM(&&NUMBER_COL) TOTAL,
5i |
AVG(&&NUMBER_COL) AVERAGE
6i |
1
1* SELECT &GROUP_COL, |
CHANGE/&/&&
1* SELECT &&GROUP_COL, |
7
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
置換変数はバッファ編集コマンドのAPPEND、CHANGE、DELおよびINPUTには使用できません。また、置換が意味をなさない他のコマンドにも使用できません。バッファ編集コマンドのAPPEND、CHANGEおよびINPUTでは、「&」または「&&」で始まるテキストは、他のテキスト文字列と同様に単なる文字列として扱われます。
SQL*PlusのSETコマンドとともに指定するシステム変数で、置換変数に影響するものを次に示します。
システム変数の詳細は、「SET」を参照してください。
置換変数に関連する値の入力を求めるプロンプトは、STARTコマンドを使用してスクリプト内のパラメータに値を渡すと回避できます。
この操作には、置換変数のかわりにスクリプト内でアンパサンド(&)およびその後に続けて数値を指定します。このスクリプトを実行するたびに、STARTによって、ファイル内の各&1がSTARTコマンドのファイル名の後の最初の値(引数)に置換され、各&2が2番目の値に置換されます。
たとえば、MYFILEというスクリプトに次のコマンドを挿入できます。
SELECT * FROM EMP_DETAILS_VIEW WHERE JOB_ID='&1' AND SALARY='&2';
次のSTARTコマンドでは、スクリプトMYFILEの&1はPU_CLERKに置換され、&2は3100に置換されます。
START MYFILE PU_CLERK 3100
STARTコマンドに引数を使用した場合、SQL*Plusでは、スクリプト内の各パラメータが適切な引数の値でDEFINEされます。
1 COLUMN LAST_NAME HEADING 'LAST NAME' 2 COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 3 COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90 4 SELECT LAST_NAME, SALARY, COMMISSION_PCT 5 FROM EMP_DETAILS_VIEW 6* WHERE JOB_ID='SA_MAN' |
6
6* WHERE JOB_ID='SA_MAN' |
CHANGE /SA_MAN/&1
6* WHERE JOB_ID='&1' |
SAVE ONEJOB
Created file ONEJOB |
この時点で、次のようにパラメータSA_MANを使用してこのコマンドを実行します。
START ONEJOB SA_MAN
SQL*Plusでは、次のように、パラメータが含まれているSQLコマンドの行が、パラメータがその値に置換される前および置換された後に表示され、その後に結果が表示されます。
old 3: WHERE JOB_ID='&1' new 3: WHERE JOB_ID='SA_MAN' LAST NAME MONTHLY SALARY COMMISSION % ------------------------- -------------- ------------ Russell $14,000 0.40 Partners $13,500 0.30 Errazuriz $12,000 0.30 Cambrault $11,000 0.30 Zlotkey $10,500 0.20 |
スクリプト内で複数のパラメータを使用できます。また、1つのスクリプト内でそれぞれのパラメータを何回でも参照でき、複数のパラメータをどのような順序でも挿入できます。
パラメータを使用できない場合、RUNまたはスラッシュ(/)を使用してコマンドを実行すると、かわりに置換変数を使用できます。
先へ進む前に、次のコマンドを入力して、列を元のヘッダーに戻します。
CLEAR COLUMN
PROMPT、ACCEPTおよびPAUSEという3つのSQL*Plusコマンドは、エンド・ユーザーとの対話に有効です。これらのコマンドを使用すると、画面へのメッセージの送信およびユーザーからの入力([Return]キーを押すなど)の受信ができます。PROMPTおよびACCEPTを使用して、SQL*Plusで置換変数用に自動生成される値の入力を求めるプロンプトのカスタマイズもできます。
PROMPTおよびACCEPTを使用すると、エンド・ユーザーへのメッセージの送信およびエンド・ユーザーからの入力値の受信ができます。PROMPTは、指定したメッセージを画面に表示して、ユーザーに指示または情報を与えるコマンドです。ACCEPTは、ユーザーに対して値の入力を求めるプロンプトを表示し、入力された値を指定した置換変数に格納するコマンドです。値の入力を求めるプロンプトを複数行にわたって表示する場合は、PROMPTをACCEPTと組み合せて使用します。
例5-12 入力のプロンプトおよびアクセプト
ユーザーにレポートのタイトルを入力するよう指示し、その入力値を変数MYTITLEに格納してその後の問合せで使用可能にするには、まず、次のように入力してバッファを消去します。
CLEAR BUFFER
次に、スクリプトを次のとおり設定し、PROMPT1という名前で保存します。
PROMPT Enter a title of up to 30 characters ACCEPT MYTITLE PROMPT 'Title: ' TTITLE LEFT MYTITLE SKIP 2 SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' SAVE PROMPT1
Created file PROMPT1.sql |
TTITLEコマンドは、レポートの一番上のタイトルを設定します。TTITILEコマンドの詳細は、「ページおよびレポートのタイトルとサイズの定義」を参照してください。
最後に、スクリプトを実行し、タイトルの入力を求めるプロンプトに次のように応答します。
START PROMPT1
Enter a title of up to 30 characters
Title: Department Report
Department ReportEMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
145 John Russell 14000
146 Karen Partners 13500
147 Alberto Errazuriz 12000
148 Gerald Cambrault 11000
149 Eleni Zlotkey 10500
|
先へ進む前に、次のように入力して、TTITLEコマンドをOFFにします。
TTITLE OFF
置換変数値の入力を求めるプロンプトをカスタマイズする場合は、次の例に示すように、PROMPTおよびACCEPTを置換変数と組み合せて使用します。
例5-13 PROMPTおよびACCEPTを置換変数と組み合せて使用する方法
例5-12「入力のプロンプトおよびアクセプト」で示したように、SQL*Plusでは、置換変数を使用する場合、値の入力を求めるプロンプトが自動生成されます。このプロンプトを別のプロンプトに置き換えるには、置換変数を参照する問合せが含まれているスクリプトにPROMPTおよびACCEPTを挿入します。まず、次のコマンドを使用してバッファを消去します。
CLEAR BUFFER
目的のファイルを作成するには、次のコマンドを入力します。
INPUT PROMPT Enter a valid employee ID PROMPT For Example 145, 206 ACCEPT ENUMBER NUMBER PROMPT 'Employee ID. :' SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID=&ENUMBER;
PROMPT2という名前でこのファイルを保存します。次に、このスクリプトを実行します。PROMPTおよびACCEPTで指定されたテキストが使用され、ENUMBERの値の入力を求めるプロンプトが次のように表示されます。
START PROMPT2
Employee IDの入力を求めるプロンプトが、次のように表示されます。
Enter a valid employee ID For Example 145, 206 Employee ID. : |
205
old 3: WHERE EMPLOYEE_ID=&ENUMBER new 3: WHERE EMPLOYEE_ID= 205 Department Report FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Shelley Higgins 12000 |
文字ではなく数字を入力する必要があります。ACCEPTコマンド内で変数名の後にNUMBERを指定したため、数値以外の値はSQL*Plusで受け入れられません。
「Employee ID.」の入力を求めるプロンプトに、数字のかわりに文字を入力してみてください。エラー・メッセージが表示され、正しい数字の再入力を求めるプロンプトが次のように表示されます。
START PROMPT2
SQL*PlusでEmployee IDの入力を求めるプロンプトが表示されると、次のように数字のかわりにoneという単語を入力します。
Enter a valid employee ID For Example 145, 206 Employee ID. : |
one
SP2-0425: "one" is not a valid number |
バインド変数は、SQL*Plusで作成し、PL/SQLまたはSQLで参照する変数です。SQL*Plusでバインド変数を作成した場合、その変数はPL/SQLサブプログラムの中で宣言した変数と同様に使用でき、SQL*Plusからもアクセスできます。バインド変数は、リターン・コードの格納、PL/SQLサブプログラムのデバッグなどに使用できます。
バインド変数はSQL*Plusから認識できるため、SQL*Plusでのバインド変数の値の表示、およびSQL*Plusで実行するPL/SQLサブプログラムでのバインド変数の参照が可能です。
バインド変数は、SQL*PlusでVARIABLEコマンドを使用して作成します。次に例を示します。
VARIABLE ret_val NUMBER
このコマンドによって、データ型がNUMBERで、ret_valという名前のバインド変数が作成されます。詳細は、「VARIABLE」コマンドを参照してください。(あるセッションで作成したすべてのバインド変数を表示するには、引数を付けずにVARIABLEと入力します。)
PL/SQLでバインド変数を参照するには、コロン(:)を入力し、その直後に変数の名前を指定します。次に例を示します。
:ret_val := 1;
SQL*Plusでこのバインド変数を変更するには、PL/SQLブロックを入力する必要があります。たとえば、次のように入力します。
BEGIN :ret_val:=4; END; /
PL/SQL procedure successfully completed. |
このコマンドは、ret_valという名前のバインド変数に値を割り当てます。
SQL*Plusでバインド変数の値を表示するには、SQL*PlusのPRINTコマンドを使用します。たとえば、次のように入力します。
PRINT RET_VAL
RET_VAL
----------
4
|
このコマンドは、ret_valという名前のバインド変数を表示します。バインド変数の表示の詳細は、「PRINT」を参照してください。
SQL*PlusのREFCURSORバインド変数を使用すると、PL/SQLブロックに含まれるSELECT文の結果をSQL*Plusからフェッチし、その書式を設定できます。
REFCURSORバインド変数は、ストアド・プロシージャ内のPL/SQLカーソル変数の参照にも使用できます。この機能を使用して、データベース内にSELECT文を格納し、それらの文をSQL*Plusから参照できます。
REFCURSORバインド変数は、ストアド・ファンクションから戻すこともできます。
例5-14 REFCURSORバインド変数の作成、参照および表示
REFCURSORバインド変数を作成、参照および表示するには、最初にREFCURSORデータ型のローカル・バインド変数を宣言します。
VARIABLE employee_info REFCURSOR
次に、このバインド変数を使用するPL/SQLブロックをOPEN... FOR SELECT文に入力します。この文は、カーソル変数をオープンし、問合せを実行します。OPENコマンドおよびカーソル変数については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
この例では、SQL*Plusのemployee_infoバインド変数をカーソル変数にバインドします。
BEGIN OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ; END; /
PL/SQL procedure successfully completed. |
この時点で、SELECT文の結果をSQL*PlusでPRINTコマンドを使用して表示できます。
PRINT employee_info
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500
|
PRINT文は、カーソルもクローズします。結果を再出力するには、PRINTを使用する前にPL/SQLブロックを再実行する必要があります。
例5-15 ストアド・プロシージャでのREFCURSOR変数の使用方法
REFCURSORバインド変数は、パラメータとしてプロシージャに渡されます。パラメータは、REF CURSOR型です。最初に、型を定義します。
CREATE OR REPLACE PACKAGE EmpPack AS TYPE EmpInfoTyp IS REF CURSOR; PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp); END EmpPack; /
Package created. |
次に、OPEN... FOR SELECT文が含まれたストアド・プロシージャを作成します。
CREATE OR REPLACE PACKAGE BODY EmpPack AS
PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp) AS
BEGIN
OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN' ;
END;
END;
/
Procedure created. |
SQL*Plusバインド変数をパラメータとして、プロシージャを実行します。
VARIABLE cv REFCURSOR EXECUTE EmpPack.EmpInfoRpt(:cv)
PL/SQL procedure successfully completed. |
この時点でバインド変数を出力します。
PRINT cv
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500
|
このプロシージャは、同じまたは別のREFCURSORバインド変数を使用して何度でも実行できます。
VARIABLE pcv REFCURSOR EXECUTE EmpInfo_rpt(:pcv)
PL/SQL procedure successfully completed. |
PRINT pcv
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500
|
例5-16 ストアド・ファンクションでのREFCURSOR変数の使用方法
次の形式で、OPEN... FOR SELECT文を含むストアド・ファンクションを作成します。
CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN - cv_types.EmpInfo IS resultset cv_types.EmpInfoTyp; BEGIN OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN'; RETURN(resultset); END; /
Function created. |
このファンクションを実行します。
VARIABLE rc REFCURSOR EXECUTE :rc := EmpInfo_fn
PL/SQL procedure successfully completed. |
この時点でバインド変数を出力します。
PRINT rc
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500
|
このファンクションは、同じまたは別のREFCURSORバインド変数を使用して何度でも実行できます。
EXECUTE :rc := EmpInfo_fn
PL/SQL procedure successfully completed. |
