ヘッダーをスキップ

SQL*Plus ユーザーズ・ガイドおよびリファレンス
10g リリース2(10.2)

B19277-01
目次
目次
索引
索引

戻る 次へ

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

この章では、SQL*Plusコマンド、SQLコマンドおよびPL/SQLブロックの操作方法について説明します。ここでは、次の項目について説明します。

この章を読むときは、コンピュータで、示されている例を実際に試してみてください。始める前に、「SQL*Plus概要」で説明したサンプル・スキーマへのアクセス権限があることを確認してください。

スクリプトの編集

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

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

オペレーティング・システムには、スクリプトの作成に使用可能な1つ以上のテキスト・エディタが含まれています。EDITコマンドを入力すると、SQL*PlusのコマンドラインまたはWindows GUIを終了せずに、オペレーティング・システムのデフォルト・テキスト・エディタを実行できます。

デフォルトのテキスト・エディタの名前を保持するには、SQL*PlusのDEFINEコマンドを使用して変数_EDITORを定義します。たとえば、EDITで使用するエディタをviとして定義するには、次のコマンドを入力します。

DEFINE _EDITOR = vi

ユーザー定義は、SQL*Plusを起動すると常に有効になるように、ユーザー・プロファイルまたはサイト・プロファイルに含めることができます。詳細は、「SQL*PlusおよびiSQL*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リファレンス』を参照してください。

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

SQL*Plusコマンドラインでのスクリプトの編集

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

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

表5-1    SQL*Plus 編集コマンド 
コマンド  略称  用途 
APPEND text
 
A text
 

カレント行の末尾にテキストを追加します。 

CHANGE/old/new
 
C/old/new
 

カレント行内のoldnewに変更します。 

CHANGE/text
 
C/text
 

テキストをカレント行から削除します。 

CLEAR BUFFER
 
CL BUFF
 

すべての行を消去します。 

DEL
 

(なし) 

カレント行を削除します。 

DEL n
 

(なし) 

nを削除します。 

DEL * 
 

(なし) 

カレント行を削除します。 

DEL n *
 

(なし) 

nからカレント行までを削除します。 

DEL LAST
 

(なし) 

最終行を削除します。 

DEL m n
 

(なし) 

ある範囲(mn)の行を削除します。 

DEL * n
 

(なし) 

カレント行から行nまでを削除します。 

INPUT
 
I
 

1つ以上の行を追加します。 

INPUT text
 
I text
 

textで構成されている行を追加します。 

LIST
 
;またはL
 

SQLバッファ内のすべての行を表示します。 

LIST n
 
L nまたはn
 

nを表示します。 

LIST * 
 
L *
 

カレント行を表示します。 

LIST n *
 
L n *
 

nからカレント行までを表示します。 

LIST LAST
 
L LAST
 

最終行を表示します。 

LIST m n
 
L m n
 

ある範囲(mn)の行を表示します。 

LIST * n
 
L * n
 

カレント行から行nまでを表示します。 

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

バッファ内容の表示

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コマンドを使用して、カレント行を編集できます。次のように、操作によってカレント行は異なります。

例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コマンドを使用すると、カレント行の中で最初に現れる変更文字列を検索し、新しい文字列に変更できます。行全体を再入力するには、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コマンドの入力(iSQL*Plus以外)」で指定した書式が保持されます(例4-4「SQL*Plusコマンドの入力(iSQL*Plus以外)」の実行後、SQL*Plusを終了して再起動すると、列は元の書式に戻ります)。

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

行へのテキストの追加

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

  1. LISTコマンド(または行番号)を使用して、変更する行を表示します。

  2. 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
 

新しい行の追加

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

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

0 SELECT EMPLOYEE_ID

例5-6    行の追加

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

INPUT

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

4
 

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

4 ORDER BY SALARY

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

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つの方法があります。

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

/*...*/の使用方法

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*Plusでは、新しい文のそれぞれについて、先頭から数個のキーワードがスキャンされ、SQL、PL/SQLまたはSQL*Plusのいずれのコマンドであるかが判断されます。コメントの挿入箇所によっては、SQL*Plusでコマンドが正しく認識されず、予期しない結果になる場合があります。次に、SQL*Plusのコメントを効果的に使用する方法を示します。

  1. 文の先頭から数個のキーワードには、コメントを挿入しないでください。たとえば、次のように入力します。

    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.
    
    
  2. 文の終了記号(ピリオド、セミコロンまたはスラッシュ)の後に、コメントを挿入しないでください。たとえば、次のように入力したとします。

    SELECT 'Y' FROM DUAL; -- TESTING
    
    

    次のエラーが戻されます。

    SELECT 'Y' FROM DUAL; -- TESTING
    *
    ERROR at line 1:
    ORA-00911: invalid character
     

    SQL*Plusでは、文の終了記号の後でテキストを記述することはできないため、コマンドは処理されません。

  3. コメント行の終わり、またはSQL文またはPL/SQLブロック内のコメントの後に文の終了文字を挿入しないでください。たとえば、次のように入力したとします。

    SELECT *
    -- COMMENT;
    
    

    次のエラーが戻されます。

    -- COMMENT
    *
    ERROR at line 2:
    ORA-00923: FROM keyword not found where expected
     

    セミコロンは文の終了記号として解釈されるため、不完全なSQLコマンドがSQL*Plusからサーバーに送信されて処理されるため、エラーになります。

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

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

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

スクリプトのネスト

一連のスクリプトを順に実行するには、最初に、複数の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コマンドのUNDEFINEの後に変数名を指定します。

事前定義変数の使用

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を拡張して、数値列の最小値、合計および平均値を含めるとします。例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では、「&」または「&&」で始まるテキストは、他のテキスト文字列と同様に単なる文字列として扱われます。

システム変数およびiSQL*Plusの「プリファレンス」画面

SQL*PlusのSETコマンドとともに指定したり、iSQL*Plusの「プリファレンス」画面で指定するシステム変数で、置換変数に影響するものを次に示します。

システム変数  置換変数への影響 
SET CONCAT
 

置換変数またはパラメータの名前と、その置換変数またはパラメータの直後の文字とを区切る文字を定義します。デフォルトではピリオド(.)です。 

SET DEFINE
 

置換文字(デフォルトではアンパサンド(&))を定義し、置換をON/OFFにします。 

SET ESCAPE
 

置換文字の前で使用できるエスケープ文字を定義します。エスケープ文字が検出されると、置換文字は変数置換の要求ではなく、通常の文字として扱われます。デフォルトのエスケープ文字は円記号(¥)です。 

SET NUMFORMAT
 

数値を表示するためのデフォルトの書式を設定します(数値置換変数による表示も含む)。 

SET NUMWIDTH
 

数値を表示するためのデフォルトの幅を設定します(数値置換変数による表示も含む)。 

SET VERIFY ON
 

置換の前後にスクリプトの各行を表示します。 

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

iSQL*Plusの「置換変数」画面

「プリファレンス」画面で指定したシステム変数は、iSQL*Plusの動作に影響を与えます。「置換変数の接頭辞」、「置換変数の表示」、「置換変数の参照終了文字」および「エスケープ文字」の設定は、変数の置換動作に影響を与えます。

iSQL*Plusでは、スクリプトを「ワークスペース」から起動し、出力がブラウザに表示されている(「入力領域の下」オプションに設定されている)場合にのみ、入力を求めるプロンプトが表示されます。出力が他の3つのオプションのいずれかに設定されている場合やiSQL*Plusの動的なURL構文を使用して起動した場合は、iSQL*Plusでは値の入力を求めるプロンプトは表示されません。

iSQL*Plusでは、置換変数が検出されると、その値の入力を求めるプロンプトが別の「入力は必須です」画面に表示されます。

変数の置換を同期化するには、「置換変数の接頭辞」の設定をONにして、後続スクリプトの実行前に、置換変数の入力を求めるプロンプトが常に表示されるようにiSQL*Plusを設定します。「実行」ボタンをクリックしてコマンドを実行します。

変数の接頭辞として「&」および「&&」を使用し、スクリプトを入力します。「実行」ボタンをクリックしてスクリプトを実行します。iSQL*Plusでは、スクリプト内の置換変数に対して値の入力を求めるプロンプトが表示されます。スクリプトの実行の終了時、スクリプト内の二重アンパサンド置換変数(&&)は定義されたままです。したがって、これらの変数値の定義が解除されるか、またはiSQL*Plusをログオフするまで、変数値の再入力を求めるプロンプトは表示されません。このプロンプト表示が必要な場合は、スクリプトで1つのアンパサンド置換変数(&)を使用します。これによって、1つのアンパサンドを使用した置換変数が出現するびに、値の置換を求めるプロンプトが表示されます。DEFINEを使用して、このモードでスクリプトに変数値を定義すると、「入力は必須です」画面で入力した値がこれらの値で上書きされます。

また、置換変数には、iSQL*Plusの動的レポートのURL構文を使用して、パラメータとして受け渡された値を指定することもできます。これらの値は、ユーザーが記述したHTMLフォームからPOST動作中のiSQL*Plusに送信することができます。これによって、すべての入力を1つのフォームに収集するアプリケーションを記述できます。また、JavaScriptを使用したフィールド・レベルの検証も行うことができます。

iSQL*Plusの「入力は必須です」画面

置換変数が含まれたスクリプトをiSQL*Plusで実行すると、各置換変数に対して「入力は必須です」画面が表示されます。たとえば、次のように入力したとします。

BREAK ON &&SORTCOL
SELECT &SORTCOL, SALARY
FROM &MYTABLE
WHERE SALARY > 12000
ORDER BY &SORTCOL;

iSQL*Plusに次のように表示されます。


画像の説明

sortcolに値を入力してください

sortcol変数の値を入力します。たとえば、LAST_NAMEと入力します。1つのアンパサンドを使用して置換変数を定義した場合は、この変数が出現するたびに、この値の入力を求めるプロンプトが表示されます。二重アンパサンドを使用して変数を定義した場合、この値はセッションで定義され、値の入力を求めるプロンプトは1回のみ表示されます。

プロンプトが表示されたら、mytable変数の値を入力します。たとえば、EMP_DETAILS_VIEWと入力します。

続行

入力した値を使用して入力領域のスクリプトを実行するには、「続行」ボタンをクリックします。

取消

スクリプトの実行を取り消して「ワークスペース」画面に戻るには、「取消」ボタンをクリックします。

STARTコマンドを使用したパラメータの受渡し方法

置換変数に関連する値の入力を求めるプロンプトは、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されます。

例5-11    STARTを使用したパラメータの受渡し方法

表示する職種をパラメータで指定する新しいスクリプトをSALESに基づいて作成するには、次のように入力します。

GET SALES

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 Report

EMPLOYEE_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

SQL*Plus 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
 

メッセージの送信および入力としての[Return]のアクセプト

ユーザーの画面にメッセージを表示し、ユーザーがそのメッセージを読んだ後、[Return]を押すように指示するには、SQL*PlusコマンドのPAUSEを使用します。たとえば、スクリプトに次のような行を挿入します。

PROMPT Before continuing, make sure you have your account card.
PAUSE Press RETURN to continue.

iSQL*Plusでは、PAUSEによって「次のページ」ボタンが表示されます。続行するには「次のページ」をクリックする必要があります。

画面の消去

レポートを表示する前に(または任意の時点で)画面を消去する場合は、スクリプト内の適切な場所に、次の書式でSQL*PlusのCLEARコマンドにSCREEN句を付けて挿入します。

CLEAR SCREEN

iSQL*Plusでは、「消去」ボタンをクリックします。

次の項へ進む前に、次のコマンドを入力して、すべての列を元の書式およびヘッダーにリセットします。

CLEAR COLUMNS

バインド変数の使用方法

バインド変数は、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」を参照してください。

REFCURSORバインド変数の使用方法

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.
 


戻る 次へ
Oracle
Copyright © 2005 Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引