SQL*Plusでのスクリプトの使用

5.1 スクリプトの編集について

SQL*Plusコマンドラインでは、外部エディタを@、@@またはSTARTコマンドと組み合せて使用すると、共通スクリプトの作成および実行に有効です。SQL*Plus、SQLおよびPL/SQLコマンドを含むスクリプトが作成できます。この機能は、複雑なコマンドまたは頻繁に使用されるレポートの格納に特に有効です。

5.1.1 システム・エディタでのスクリプトの作成

オペレーティング・システムには、スクリプトの作成に使用可能な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」コマンドおよび「書式モデル」を参照してください。

最後に、エディタの保存コマンドを使用して、SALES.SQLというファイルに問合せを格納します。

5.2 SQL*Plusコマンドラインでのスクリプトの編集について

様々なSQL*Plusコマンドを使用して、現在バッファに格納されているSQLコマンドまたはPL/SQLブロックを編集できます。

表5-1に、コマンドを再入力せずにバッファ内のコマンドを検証または変更できるSQL*Plusコマンドを示します。

表5-1 SQL*Plusの編集コマンド

コマンド	略称	用途
APPEND text	A text	カレント行の末尾にテキストを追加します。
CHANGE/old/new	C/old/new	カレント行内のoldをnewに変更します。
CHANGE/text	C/text	テキストをカレント行から削除します。
CLEAR BUFFER	CL BUFF	すべての行を削除します。
DEL	(なし)	カレント行を削除します。
DEL n	(なし)	行nを削除します。
DEL *	(なし)	カレント行を削除します。
DEL n *	(なし)	行nからカレント行までを削除します。
DEL LAST	(なし)	最終行を削除します。
DEL m n	(なし)	ある範囲(mからn)の行を削除します。
DEL * n	(なし)	カレント行から行nまでを削除します。
INPUT	I	1つ以上の行を追加します。
INPUT text	I text	textで構成されている行を追加します。
LIST	; or L	SQLバッファ内のすべての行を表示します。
LIST n	L n or n	行nを表示します。
LIST *	L *	カレント行を表示します。
LIST n *	L n *	行nからカレント行までを表示します。
LIST LAST	L LAST	最終行を表示します。
LIST m n	L m n	ある範囲(mからn)の行を表示します。
LIST * n	L * n	カレント行から行nまでを表示します。

これらのコマンドは、入力したコマンドの修正または変更に有効です。

5.2.1 バッファ内容の表示

SQLバッファには、最新のSQLコマンドまたはPL/SQLコマンドが含まれます。LISTおよびDEL以外のすべての編集コマンドは、バッファ内の1行にのみ影響します。その行をカレント行と呼びます。現行のコマンドまたはブロックを表示した場合、カレント行にはアスタリスクが付きます。

SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMP_DETAILS_VIEW
  3* WHERE SALARY>12000

SELECTコマンドの終わりに入力したセミコロンは表示されません。このセミコロンは、入力時にはコマンドの終わりを示すために必要ですが、SQLコマンドの一部とみなされないため、SQLバッファには格納されません。

例5-2 バッファ内容の表示

現行のコマンドを表示するとします。次のように、LISTコマンドを使用します。(例4-3のステップに従った後、SQL*Plusを終了するか、別のSQLコマンドまたはPL/SQLブロックを入力した場合は、続行する前に、その例のステップを再実行してください。)

LIST

5.2.2 カレント行の編集

SQL*PlusのCHANGEコマンドを使用して、カレント行を編集できます。次のように、操作によってカレント行は異なります。

LISTコマンドで特定の行を表示すると、その行がカレント行になります。
バッファ内のコマンドを表示(LIST)または実行(RUN)した場合、コマンドの最終行がカレント行になります。(ただし、スラッシュ(/)コマンドを使用してバッファ内のコマンドを実行しても、カレント行は影響を受けません。)
エラーが発生した場合は、エラーが含まれている行が自動的にカレント行になります。

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コマンドを使用する必要はありません。

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で指定した書式が保持されます。(例4-4の実行後、SQL*Plusを終了して再起動すると、列は元の書式に戻ります。)

CHANGEコマンドでの大文字と小文字の区別、およびCHANGEコマンドでワイルド・カードを使用してテキストのブロックを指定する方法については、「CHANGE」コマンドを参照してください。

例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';

画面に次のメッセージが表示されます。

例5-4 エラーの修正

JO_IDをJOB_IDに変更するには、次のようにCHANGEコマンドを使用して行を変更します。

CHANGE /JO_ID/JOB_ID

修正後の行が、次のように表示されます。

5.2.3 行へのテキストの追加

バッファの行末にテキストを追加するには、次のようにAPPENDコマンドを使用します。

LISTコマンド(または行番号)を使用して、変更する行を表示します。
APPENDを入力し、その後に追加するテキストを続けて入力します。追加するテキストが空白で始まる場合は、APPENDという単語とテキストの1文字目を2つの空白で区切ります。1つはAPPENDとテキストの区切りで、もう1つはテキストとともにバッファ内に格納されます。

例5-5 行へのテキストの追加

現行の問合せの行4に空白およびDESC句を追加するには、最初に行4を次のように表示します。

LIST 4

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

5.2.4 新しい行の追加

カレント行の後に新しい行を挿入するには、INPUTコマンドを使用します。

行1の前に行を挿入するには、0(ゼロ)を入力し、その後にテキストを続けます。その行がバッファの先頭に挿入され、すべての行が再度1から番号付けされます。

0 SELECT EMPLOYEE_ID

新しい行を入力します。次に、[Return]を押します。

4 ORDER BY SALARY

次のように新しい行の入力を求めるプロンプトが再度表示されます。

再度[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

例5-6 行の追加

例5-4で変更したSQLコマンドに4行目を追加するとします。すでに行3がカレント行なので、INPUTを入力し、[Return]を押します。

INPUT

次のように、新しい行の入力を求めるプロンプトが表示されます。

5.2.5 行の削除

バッファ内の行を削除するには、DELコマンドを使用します。DELを入力し、削除する行番号を指定します。

カレント行から最終行までを削除するとします。次のように、DELコマンドを使用します。

DEL * LAST

DELを実行すると、バッファの次の行がある場合は、その行がカレント行になります。

詳細は、「DEL」を参照してください。

5.3 スクリプトへのコメントの挿入について

スクリプトにコメントを入力するには、次の3つの方法があります。

単一行のコメントに対して、SQL*PlusのREMARKコマンドを使用する方法
1行以上のコメントに対して、SQLのコメント・デリミタ(/* ...*/)を使用する方法
単一行のコメントに対して、米国規格協会(ANSI)および国際標準化機構(ISO)のコメント「- -」を使用する方法

コマンドラインで入力したコメントは、SQLバッファには格納されません。

5.3.1 REMARKコマンドの使用

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';

5.3.2 /**/の使用

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';

5.3.3 - -の使用

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

5.3.4 コメント挿入時のノート

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を参照してください。

5.4 スクリプトの実行

STARTコマンドを使用すると、スクリプトが取得され、そこに含まれるコマンドが実行されます。SQLコマンド、PL/SQLブロックおよびSQL*Plusコマンドを含むスクリプトを実行するには、STARTを使用します。ファイル内に多くのコマンドを含むことができます。STARTコマンドの後に、ファイルの名前を入力します。

START file_name

デフォルトでは、ファイルに拡張子.SQLが割り当てられていると想定されています。

ノート:

Oracle Databaseリリース19c、バージョン19.3以降、$ (ドル)記号を含むスクリプトを実行すると、Windowsでエラーになります。これは、$記号はLinuxおよびUnixの環境変数を示しているためです。

たとえば:

SQL>@C:\User\my$script.sql

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ブロックがバッファ内に残ります。

例5-7 スクリプトの実行

SALES.SQLに格納されているコマンドを取得して実行するには、次のように入力します。

START SALES

SQL*PlusではファイルSALES内のコマンドが実行され、コマンドの結果が画面に表示されます。その際、ファイル内のSQL*Plusコマンドに従って、問合せ結果の書式が次のように設定されます。

5.4.1 SQL*Plus起動時のスクリプトの実行

SQL*Plus起動時にスクリプトを実行するには、次のいずれかのオプションを使用します。

SQLPLUSコマンドの後にユーザー名、スラッシュ、空白、@およびファイル名を指定します。
```
SQLPLUS HR @SALES
```
SQL*Plusが起動し、パスワードの入力が求められ、スクリプトが実行されます。
ファイルの1行目にユーザー名を指定します。SQLPLUSコマンドの後に@およびファイル名を指定します。SQL*Plusが起動し、パスワードの入力が求められ、ファイルが実行されます。

5.5 スクリプトのネスト

一連のスクリプトを順に実行するには、最初に、複数のSTARTコマンドを順に指定したスクリプトを作成します。それぞれのSTARTコマンドの後には、スクリプト名を続けます。次に、STARTコマンドが含まれているスクリプトを実行します。たとえば、SALESRPTというスクリプトには次のSTARTコマンドを挿入できます。

START Q1SALES
START Q2SALES
START Q3SALES
START Q4SALES
START YRENDSLS

ノート:

この例では、@@コマンドが有効な場合があります。詳細は、「@@(二重アットマーク)」コマンドを参照してください。

5.6 リターン・コードを伴うスクリプトの終了について

EXITコマンドをスクリプトに含めると、スクリプトの終了時に値を戻すことができます。詳細は、「EXIT」コマンドを参照してください。

WHENEVER SQLERRORコマンドをスクリプトに含めると、スクリプトがSQLエラーを生成した場合に、自動的にリターン・コードを戻し、SQL*Plusを終了できます。同様に、WHENEVER OSERRORコマンドを含めると、オペレーティング・システム・エラーが発生した場合に、自動的に終了することができます。詳細は、「WHENEVER SQLERROR」コマンドおよび「WHENEVER OSERROR」コマンドを参照してください。