6 置換変数の使用
この章では、SQL*Plusの置換変数の仕組みおよび使用できる場所について説明します。SQL*Plusで使用される3種類の変数(置換、バインドおよびシステム)の関係を示します。
このトピックの内容は次のとおりです。
6.1 置換変数の定義
SQL*PlusのDEFINEコマンドを使用すると、置換変数という変数を定義して、1つのスクリプトの中で繰り返し使用できます。タイトル内で使用し、キーストロークを保存するための置換変数も(長い文字列を短い名前の変数の値として定義して)定義できます。
DEFINE L_NAME = "SMITH" (CHAR)置換変数のすべての定義を表示するには、DEFINEのみを入力します。DEFINEを使用して明示的に定義する置換変数には、CHAR値のみ指定できます(ユーザーが変数に割り当てる値は、常にCHARデータ型として扱われます)。ACCEPTコマンドを使用すると、NUMBERデータ型の置換変数を暗黙的に定義できます。ACCEPTコマンドについては、後半でさらに学習します。
6.2 事前定義変数の使用について
SQL*Plusのインストール中に定義され、SQL*Plus情報を保持する9つの変数があります。これらの変数は他の変数と同様に、再定義、参照または削除できます。明示的に削除または再定義しないかぎり、これらの変数はどのセッションでも使用できます。
関連項目:
事前定義変数の表示およびその使用例については、「事前定義変数」を参照してください。
6.3 置換変数の参照
SALESと同様の問合せを作成し、職種がSA_MANだけでなく、様々な職種の従業員のリストを表示するとします。この場合、コマンドの実行ごとに異なる値を編集してWHERE句に挿入する方法もありますが、さらに簡単な方法があります。
WHERE句のテキストSA_MANのかわりに置換変数を使用すると、コマンド自体に値を書き込んだ場合と同じ結果が得られます。
置換変数は、前に1つまたは2つのアンパサンド(&)を付けたものです。SQL*Plusでは、コマンド内で置換変数が検出されると、そのコマンドに置換変数自体ではなく置換変数の値が含まれている場合と同様に、コマンドを実行します。
たとえば、変数SORTCOLの値がJOB_IDで、変数MYTABLEの値がEMP_DETAILS_VIEWの場合、SQL*Plusはコマンドを実行します
SELECT &SORTCOL, SALARY FROM &MYTABLE WHERE SALARY>12000;
このコマンドは、次のコマンドと同様に実行されます。
SELECT JOB_ID, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
6.3.1 置換変数を使用する位置および方法
置換変数は、SQLコマンドおよびSQL*Plusコマンド内の任意の位置で使用できますが、最初に入力する単語としては使用できません。SQL*Plusでは、コマンド内で未定義の置換変数が検出された場合、値の入力を求めるプロンプトが表示されます。
このプロンプトには、任意の文字列を入力できます。空白および句読点を含む文字列も入力できます。参照を含むSQLコマンドで、置換変数を引用符で囲む必要がある場所に引用符が挿入されていない場合は、プロンプトが表示されたときに引用符を挿入する必要があります。
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.置換変数のより実用的な使用方法は、変数を参照する前に値の入力を求めることです。
SQL> accept myv char prompt 'Enter a last name: '
SQL> select employee_id from employees where last_name = '&myv'; これらの2つのコマンドをSQL*Plusのスクリプトに格納すると、スクリプトが実行されるたびに異なる姓を入力できます。
置換変数の直後に文字を追加する場合は、変数と文字の区切りにピリオドを使用します。たとえば:
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';
置換変数名の直後にピリオドを付加する場合は、2つのピリオドを使用します。たとえば、「myfile」が「reports」として定義されている場合は、次のようなコマンドを使用します。
SQL> spool &myfile..logこれは、次のように解釈されます。
SQL> spool reports.log置換変数のように見えるANSIの「/* */」または「--」によるコメント内のテキストは、1つとして扱うことができます。たとえば:
SQL> select department_id, location_id /* get dept & loc */ from departments;
Enter value for loc: _ここで、コメント内のテキスト「& loc」は変数参照として解釈されます。SQL*Plusによって、変数「loc」の値を入力するように求められます。
例6-2 置換変数の使用
数値列についてのサブグループ統計(最大値)の計算に使用するSTATSというスクリプトを、次のように作成します。
SELECT &GROUP_COL, MAX(&NUMBER_COL) MAXIMUM FROM &TABLE GROUP BY &GROUP_COL . SAVE STATS
6.3.2 「&」および「&&」接頭辞の相違点
1つのアンパサンド(&)と2つのアンパサンド(&&)のどちらも、文の置換変数名の前に付けることができます。SQL*Plusは文の前処理を行い、変数の値を置き換えます。その後、文が実行されます。変数が事前に定義されていない場合は、置換を行う前にSQL*Plusによって値の入力を求められます。
1つのアンパサンドの接頭辞を未定義の変数とともに使用すると、プロンプトで入力した値は格納されません。文で値が置換された直後に変数が破棄され、未定義のままになります。変数が2回参照される場合は、同じ文であっても2回入力を求められます。各プロンプトで異なる値を入力できます。
SQL> prompt Querying table &mytable
Enter value for mytable: employees
Querying table employees
SQL> select employee_id from &mytable where last_name = 'Jones';
Enter value for mytable: employees
EMPLOYEE_ID
-----------
1952つのアンパサンドの参照によってSQL*Plusが値の入力を求めた場合、SQL*Plusは変数をその値として定義します(つまり、値は終了するまで格納されます)。「&」または「& &」を使用した、変数への後続の参照では(同じコマンド内でも)、新しく定義された値に置換されます。SQL*Plusによって再度入力を求められることはありません。
SQL> prompt Querying table &&mytable
Enter value for mytable: employees
Querying table employees
SQL> select employee_id from &mytable where last_name = 'Jones';
EMPLOYEE_ID
-----------
1956.3.3 置換変数への問合せ列値の格納
データベースに格納されているデータを置換変数に設定できます。
SQL> column last_name new_value mynv
SQL> select last_name from employees where employee_id = 100;COLUMNコマンドのNEW_VALUEオプションによって、mynvという置換変数が暗黙的に作成されます。この変数は、問合せがLAST_NAME列を参照するまで物理的には作成されません。問合せが終了すると、変数mynvは、LAST_NAME列から最後に取得した値を保持しています。
SQL> define mynv
DEFINE mynv = "King" (CHAR)6.3.5 SQL*Plusで置換変数が処理される仕組み
置換変数の参照は、コマンドの解析および実行の前に、前処理されて置換されます。各文について、SQL*Plusでは次の処理が行われます。
1. Loop for each "&" and "&&" variable reference:
If the variable already has a value defined (i.e. stored)
Replace the variable reference with the value
else
Prompt for a value
Replace the variable reference with the value
If the variable is prefixed with "&&" then
define (i.e. store) the variable for future use
2. Execute the statementステップ1は、SQL*Plusクライアント・ツール内で行われます。その後、SQL*Plusにより、ステップ2が発生したデータベース・エンジンに最終的な文が送信されます。
PL/SQLのループで繰り返し入力を求めることはできません。次の例では、入力が1回だけ求められ、入力された値がスクリプト・テキスト内で置換されます。生成されたスクリプトが、実行のためにデータベース・エンジンに送信されます。入力された同じ値が表に5回格納されます。
begin
for i in 1 .. 5 loop
insert into mytable values (&myv);
end loop;
end;
/置換変数は再帰的に展開されません。参照される変数の値にアンパサンドが含まれている場合、そのアンパサンドは文字どおりに使用され、変数の2番目の接頭辞としては扱われません。
SQL> set escape \
SQL> define myv = \&mytext
SQL> prompt &myv
&mytextコマンドの最初のトークンとして置換変数を使用することはできません。各コマンド名はハードコードされたテキストである必要があり、そうでない場合はエラーが表示されます。たとえば:
SQL> &myv * from dual;
SP2-0734: unknown command beginning "&myv * fro..." - rest of line ignored.6.3.6 置換変数コマンド
置換変数は、ほぼすべてのSQL*Plusのコマンドでオプションと値を置換するために使用できます。一部のコマンドには、置換変数として特殊な意味を持つものがあります。
| コマンド | 説明 |
|---|---|
ACCEPT |
入力された行を読み込み、指定された置換変数に行の内容を格納します。 |
COLUMN |
特定の列について表示属性を指定します。 |
DEFINE |
ユーザー変数または事前定義変数を指定し、その変数にCHAR値を割り当てたり、1つまたはすべての変数の値および変数型を表示します。 |
EDIT |
指定したファイルの内容またはバッファの内容を対象として、オペレーティング・システムのテキスト・エディタを起動します。 |
EXIT |
保留中のすべての変更をコミットまたはロールバックし、Oracle DatabaseをログアウトしてSQL*Plusを終了し、オペレーティング・システムに制御を戻します。 |
HOST |
SQL*Plusを終了せずに、オペレーティング・システムのコマンドを実行します。 |
TTITLE、BTITLE、REPHEADER、REPFOOTER |
|
UNDEFINE |
明示的に( |
WHENEVER |
|
これらの置換変数コマンドの詳細は、SQL*Plusコマンド一覧を参照してください。
6.3.6.1 タイトル変数での「&」接頭辞の使用
タイトル・コマンド(TTITLE、BTITLE、REPHEADERおよびREPFOOTER)は、他のほとんどのコマンドとは異なる方法で変数を置換します。(EXITコマンドとSET SQLPROMPTコマンドは例外で、タイトル・コマンドに似ています)。
-
すべてのページに変数を同じ値で出力する場合は、接頭辞「&」を使用し、引用符付き文字列内に変数を記述します。
accept mycustomer char prompt 'Enter your company name: ' ttitle left 'Report generated for company &mycustomer' select last_name, job_id from employees order by job_id; -
各レポート・ページで一意の問合せからのデータを各タイトルに表示する場合は、変数に接頭辞「&」を使用せず、引用符内に変数を記述しないでください。
column job_id new_value ji_nv noprint break on job_id skip page ttitle left 'Employees in job: ' ji_nv select last_name, job_id from employees order by job_id;
SQL*Plusの置換変数は、各コマンドの実行前に展開されます。タイトル・コマンドでこれが行われると、結果の文字列がタイトル・テキストとして格納されます。タイトルの変数が特別であるのは、各ページの問合せ結果で再置換する必要があるということです。現在のCOLUMN NEW_VALUEおよびOLD_VALUE置換変数の値が各ページに表示され、ページに表示される各タイトルがカスタマイズされます。誤って「&」を使用してタイトル変数に接頭辞を付けた場合は、二重置換が行われることがあります。この場合は変数の値に依存することになり、スクリプトの作成時に見落としがちな誤りです。
タイトル内の引用符で囲まれていないキーワード以外の単語は、ページの出力時にチェックされ、変数であるかどうかが確認されます。変数である場合は、その値が出力されます。変数ではない場合、単語はそのまま出力されます。これは、タイトル・コマンドに「&myvar」を使用し、それが置き換えられたテキストを別の変数名として解釈できる場合に、変数置換が二重に行われることを意味します。たとえば、次のスクリプトでは、
define myvar = scottsvar
ttitle left &myvar
define scottsvar = Hello
select * from dual;テキスト「left scottsvar」がタイトルとして格納されます。タイトルを問合せの各ページに出力する場合、この文字列は再評価されます。タイトルの「scottsvar」という単語は、それ自体が変数参照として扱われて置換されます。問合せ出力は、次のとおりです。
Hello
D
-
deXタイトルに通常の「&」を使用すると、SQL*Plusスクリプト・パラメータの数値変数名で問題が発生します。接頭辞「&」が付けられた任意のタイトル変数の値がスクリプトのパラメータの変数名と同じである場合は、二重置換が行われます。
タイトルに「&」を表示するには、SET ESCAPEの文字を前に付けます。アンパサンド(&)はタイトルのテキストとして格納され、ページ・タイトルを出力するときに置換されません。
6.3.7 置換変数のネームスペース、型、フォーマットおよび制限
置換変数のネームスペース
SQL*Plusセッションでは、置換変数のグローバルなネームスペースは1つのみです。CONNECTを使用して再接続する場合、または「@」を使用してサブスクリプトを実行する場合は、定義されているすべての変数を使用でき、上書きしたり、未定義にすることができます。
子スクリプトが終了すると、定義または変更されたすべての置換変数をコール元のスクリプトから参照できます。これは、「@」またはSTARTを使用して実行されるサブスクリプトにスクリプト・パラメータが渡される場合に特に顕著です。パラメータ「&1」などは再定義され、親スクリプトは新しい値を参照します。
define myuser = '&1'
@myscript.sql King
select first_name from employees where last_name = '&myuser';myscript.sqlをコールすると、「&1」の値が「King」に変更されます。「myuser」に「&1」の元の値を保存し、SELECTで「&1」のかわりに「&myuser」を使用すると、問合せは正しく実行されます。
置換変数の型
SQL*Plusによって格納される置換変数の型は次のとおりです。
- CHAR
- NUMBER
- BINARY_FLOAT
- BINARY_DOUBLE
CHAR型は、データベース表のVARCHAR2列型と同様の汎用テキスト形式です。次のものから作成されるすべての変数はCHAR型です。
- DEFINEを使用
- 「&」変数のプロンプトから
- スクリプト・パラメータから
これにより、入力された値は変換による損失なしでそのまま置換されます。
Oracle数値形式の列のCOLUMN NEW_VALUEまたはOLD_VALUEで作成された変数は、NUMBER型になります。これらの置換変数は、データベース内ではOracleの内部的な数値表現で格納されます。これにより、内部値を失うことなく表示形式を変更できます。BINARY_FLOATおよびBINARY_DOUBLE型の置換変数は、OracleのBINARY_FLOAT列およびBINARY_DOUBLE列から同様に作成されます。これらの変数は、ネイティブなマシンの表現で格納されます。他のすべての列型のNEW_VALUE変数およびOLD_VALUE変数には、CHAR型が使用されます。
明示的なDATE型はありません。ACCEPTコマンドのDATEキーワードは、日付書式に対して正しい書式の検証を行うためにのみ使用されます。ACCEPT... DATE、または日付列に対するCOLUMN NEW_VALUEで作成された置換変数は、CHAR型として格納されます。たとえば:
SQL> accept mydvar date format 'DD-MON-YYYY'
prompt 'Enter a date: '
Enter a date: 03-APR-2003
SQL> define mydvar
DEFINE MYDVAR = "03-APR-2003" (CHAR)変数がすでに存在していて再定義されている場合は、その古い型が破棄されて新しい型が使用されます。
通常、置換変数の型は透過的です。置換変数には弱い型定義が使用されます。たとえば、COLUMN NEW_VALUEで作成される変数は、新しい各問合せの名前付きの列の特定の型になります。問合せの実行中に型が変更されることもあります。たとえば、NUMBER列に使用される置換変数の型は、NULL値がフェッチされた場合、NUMBERからCHARに変更されます。次に数値がフェッチされると、NUMBERに変更されます。
変数同士は直接比較されないため、型比較のセマンティクスはいずれの型に対しても定義されていません。比較が実行される可能性のあるSQL文またはPL/SQL文の前に、すべての変数がテキストに置換されます。
置換変数のフォーマット
変数が置換されるか、その値がDEFINEコマンドで表示される場合、その変数を参照するコマンドが最終的に実行される前に、テキストとしてフォーマットされます。
CHARの変数はそのまま置換されます。
NUMBERの変数は、SET NUMWIDTH (デフォルト)またはSET NUMFORMAT (明示的に設定した場合)に従ってフォーマットされます。
変数の作成後でも、数値の表示フォーマットを変更できます。これを説明するために、まずNUMBERの変数を作成します。これを行うためにDEFINEを使用することはできません。すべての新しい変数の型がCHARになってしまうからです。かわりに、NUMBER列からNUMBER型を継承するCOLUMN NEW_VALUEコマンドを使用します。
SQL> column c2 new_val m
SQL> select 1.1 c2 from dual C2;
----------
1.1
SQL> define m
DEFINE M = 1.1 (NUMBER)フォーマットを変更すると、格納されている値ではなく数値の表示に影響します。
SQL> set numformat 99.990
SQL> define m
DEFINE M = 1.100 (NUMBER)置換変数の制限
使用できる置換変数の最大数は2048個です。さらに作成しようとすると、SQL*Plusでエラーが表示されます。制限には事前定義された変数が含まれますが、必要に応じて、未定義にすることができます。変数の参照は低速であるため、不必要に定義された変数が多数残っていると、SQL*Plusのパフォーマンスが低下することがあります。
文字の置換変数の長さは最大240バイトです。
数値の置換変数には、Oracleの数値の完全な範囲が保持されます。
コマンドラインで変数置換が行われた場合、結果の行の長さは次の値以下になります。
- 3000バイト(SQL (SELECT、INSERTなど)またはPL/SQLテキスト(BEGIN、CREATE PROCEDUREなど)の行の場合)
- 2499バイト(SQL*Plusコマンド(TTITLE、COLUMNなど)の行の場合)
そうでない場合は、エラーが表示されます。
これらの制限は、古いバージョンのSQL*Plusではさらに低い場合があります。
6.3.8 バインド変数への置換変数の割当て
置換変数をバインド変数に割り当てることができます。
SQL> define mysubv = 123
SQL> variable mybndv number
SQL> execute :mybndv := &mysubv;SQL*Plusは、「mysubv」の値を置換した後にPL/SQLの代入文を実行します。「mysubv」がまだ定義されていない場合は、値の入力を求められます。
バインド変数は、後続のSQLまたはPL/SQLコマンドで使用できます。
6.3.9 置換変数へのバインド変数の割当て
TTITLE、SPOOLなどのSQL*Plusコマンドでバインド変数の値を使用できるようにすると便利なことがあります。たとえば、文字列を返すPL/SQLファンクションをコールし、SQL*Plusのスプール・ファイル名にその値を使用します。SPOOLコマンドはバインド変数の構文を認識しないため、最初にバインド変数値を置換変数に割り当てる必要があります。
これは、COLUMN NEW_VALUEおよびSELECTコマンドを使用して行います。たとえば、SQL*Plusでバインド変数を宣言し、PL/SQLブロックでインスタンス化します。その値は、PL/SQLファンクションから返されるか、次に示すように直接割り当てることによって設定できます。
SQL> variable mybv varchar2(14)
SQL> begin
2 /* ... */
3 :mybv := 'report.log';
4 end;
5 /問合せを使用して、バインド変数の値を新しい置換変数「nv」に渡します。
SQL> column mybvcol new_value nv noprint
SQL> select :mybv mybvcol from dual;SPOOLコマンドで置換変数を使用できるようになりました。
SQL> spool &nvSPOOLコマンドは、ユーザーが入力した場合と同じように実行されます。
SQL> spool report.log6.3.10 置換変数の例
次の例では、置換変数の使用方法を示します。
6.3.10.1 置換変数値の設定
置換変数はいくつかの方法で設定できます。一般的な方法を次に示します。
-
DEFINEコマンドは、明示的な値を設定します。
define myv = 'King' -
ACCEPTコマンドは、
accept myv char prompt 'Enter a last name: '値の入力を求め、入力されたテキストに設定された文字変数「myv」を作成します。
-
未定義の変数の前に「&&」を付けると値の入力を求められ、その値が文で使用されます。
select first_name from employees where last_name = '&&myuser';置換変数「myuser」がまだ定義されていない場合、この文は「myuser」を作成し、入力された値を設定します。
-
COLUMN NEW_VALUEを使用して、データベースに格納されている値を置換変数に設定します。
column last_name new_value mynv select last_name from employees where employee_id = 100;これによって、「last_name」列の値が設定された置換変数「mynv」が作成されます。
6.3.10.2 置換変数の使用
置換変数に値を設定したら、変数名にアンパサンド(&)を接頭辞として付けることによって参照できます。
変数「myv」がすでに定義されている場合は、次のように使用できます。
select employee_id from employees where last_name = '&myv';6.3.10.3 すべての定義済置換変数の検索
defineこのコマンドでは、次のような結果が表示されます。
DEFINE MYV = "King" (CHAR)
...6.3.10.4 入力を求めずに「&」を含むデータを挿入する
set define off
create table mytable (c1 varchar2(20));
insert into mytable (c1) values ('thick & thin');このINSERT文では、テキスト「thick & thin」が表に格納されます。
2番目の方法は、特定の「&」を無視して、他の「&」は置換変数の接頭辞として認識されるようにする場合に便利です。
set escape \
create table mytable (c1 varchar2(20));
insert into mytable (c1) values ('thick \& thin');
insert into mytable (c1) values ('&mysubvar');この方法の最初のINSERT文では、テキスト「thick & thin」が表に格納されます。2番目のINSERTでは、SQL*Plusによって値の入力が求められ、その値が格納されます。
6.3.10.5 スプール・ファイル名への現在の日付の付加
SYSDATEを使用して現在の日付を問い合せ、置換変数に設定できます。その後、その置換変数をSPOOLコマンドで使用できます。
column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;
spool &mydate.report.txt
-- my report goes here
select last_name from employees;
spool offこの例では、最初の問合せによって、置換変数「mydate」に日付が設定されます。COLUMNコマンドにNOPRINTオプションが指定されているため、この問合せでは出力は表示されません。SPOOLコマンドでは、最初のピリオド(.)は変数名の終わりを示し、結果の文字列には含められません。最初の問合せで「mydate」に「20030120」が含まれていた場合、スプール・ファイル名は「20030120report.txt」になります。
この方法を使用して、ファイル名の任意の文字列を作成できます。
ピリオドはSET CONCATのデフォルト値です。別の文字を割り当てた場合は、ピリオドのかわりにその文字を使用して、置換変数名の終わりを示します。
6.3.10.6 置換変数の直後への英数字の付加
define mycity = Melbourne
spool &mycity.Australia.txtこのコマンドでは、「MelbourneAustralia a.txt」という名前のファイルが作成されます。
6.3.10.7 置換変数の後ろへのピリオドの付加
define mycity = Melbourne
spool &mycity..logこれは、次のように解釈されます。
spool Melbourne.log6.3.10.8 TTITLE、BTITLE、REPHEADERまたはREPFOOTERでの固定値変数の使用
define dept = '60'
ttitle left 'Salaries for department &dept'
select last_name, salary from employees where department_id = &dept;6.3.10.9 TTITLE、BTITLE、REPHEADERまたはREPFOOTERでの値が変化する変数の使用
column department_id new_value dv noprint
ttitle left 'Members of department ' dv
break on department_id skip page
select department_id, last_name from employees order by department_id, last_name;BTITLEコマンドまたはREPFOOTERコマンドでは、COLUMN NEW_VALUE変数ではなくCOLUMN OLD_VALUE変数を使用します。
6.3.10.10 SPOOLなどのSQL*Plusコマンドでのバインド変数の値の使用
SQL*Plusコマンドでバインド変数の値を使用する場合は、最初にそれを置換変数にコピーする必要があります。
SPOOL、SET、TTITLEなどのSQL*Plusコマンドは、SQL*Plusプログラム内で実行され、実行のためにデータベースに渡されません。このため、これらのコマンドはバインド変数を認識しません。
スプール・ファイルの名前としてバインド変数の値を使用するには:
-- Set a bind variable to a text string
variable mybindvar varchar2(20)
begin
:mybindvar := 'myspoolfilename';
end;
-- Transfer the value from the bind variable to the substitution variable
column mc new_value mysubvar noprint
select :mybindvar mc from dual;
-- Use the substitution variable
spool &mysubvar..txt
select * from employees;
spool off6.3.10.11 SQL*Plusの置換変数へのパラメータの受渡し
コマンドラインのパラメータをSQL*Plusのスクリプトに渡すことができます。
sqlplus hr/my_password @myscript.html employees "De Haan"これらは「&1」および「&2」を使用してスクリプトで参照できます。たとえば、myscript.sqlの内容を次のようにすることができます。
set verify off
select employee_id from &1 where last_name = '&2';ここで、SET VERIFY OFFコマンドは、変数の置換の前後にSQL*PlusでSQL文がエコーされないようにします。この問合せは、employees表から従業員「De Haan」の従業員IDを返します。
パラメータは、SQL*Plus内でコールされるスクリプトにも渡すことができます。
SQL> @myscript.sql employees "De Haan"6.3.10.12 SQL*Plusへのオペレーティング・システム変数の受渡し
sqlplus hr/my_password @myscript.sql $USERまたは、Windowsのコマンド・ウィンドウの場合:
sqlplus hr/my_password @myscript.sql %USERNAME%スクリプトmyscript.sqlでは、置換変数「&1」を参照することによって、渡された名前を参照できます。
6.3.10.13 コマンドラインからのPL/SQLプロシージャへの値の受渡し
create or replace procedure myproc (p1 in number) as
begin
dbms_output.put_line('The number is '||p1);
end;
/myscript.sqlが次のような内容の場合、
begin
myproc(&1);
end;
/次のコマンドを実行すると、
sqlplus hr/my_password @myscript.sql 88スクリプトは次のような内容である場合と同様に実行されます。
begin
myproc(88);
end;
/myprocのパラメータp1がIN OUTの場合、この方法は機能しません。変数参照は前処理され、実際にはハードコードされた値であり、OUT値を設定することはできません。この問題を回避するには、置換変数をバインド変数に割り当てることができます。スクリプトmyscript.sqlは次のようになります。
variable mybindvar number
begin
:mybindvar := &1;
myproc(:mybindvar);
end;
/6.3.10.14 スクリプトのパラメータをオプション化してデフォルト値を設定する
-- Name: myscript.sql
prompt Enter a value for PAGESIZE
set termout off
define mypar = &1
set termout on
prompt Setting PAGESIZE to &mypar
set pagesize &mypar
select last_name from employees where rownum < 20;
exitパラメータを使用する場合も使用しない場合もこのスクリプトをコールできます。プロンプトで「12」と入力した場合、画面は次のようになります。
$ sqlplus hr/my_password @myscript.sql
SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:19:40 2003
. . .
Enter a value for PAGESIZE 12
Setting PAGESIZE to 12
LAST_NAME
-------------------------
King
Kochhar
De Haan
. . .または、パラメータに「8」を指定してコールした場合は、次のようになります。
$ sqlplus hr/my_password @myscript.sql 8
SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:20:38 2003
. . .
Enter a value for PAGESIZE
Setting PAGESIZE to 8
LAST_NAME
-------------------------
King
Kochhar
De Haan
. . .パラメータを渡した場合、PROMPTのテキストは表示されますが値は入力しません。このPROMPTコマンドは、SQL*Plusのecho文またはprint文です。(入力を読み取りません)。「&1」は「mypar」を定義する場所でのみ使用する必要があります。パラメータへの他のすべての参照では、「&mypar」または「&&mypar」を使用する必要があります。
6.3.10.15 SQL*Plusの戻りステータスへの変数の使用
EXIT myv置換変数名の前にアンパサンド(&)の接頭辞は必要ありません。
数値のバインド変数には、コロン(:)の接頭辞が必要となります。
EXIT :mybv6.3.10.16 プロンプトへのユーザー名とデータベースの設定
set sqlprompt "_user'@'_connect_identifier:SQL> "データベースに問い合せるカスタマイズされたプロンプトを設定するには、参照される置換変数を明示的に定義(DEFINE)します。glogin.sqlおよびlogin.sqlは、データベース接続がない場合に実行できます。変数を定義すると、問合せが失敗して変数が定義されない場合に、ユーザーが値の入力を求められることがありません。
set termout off
define myv = 'Not connected'
column myc new_value myv
select user||'@'||global_name myc from global_name;
set sqlprompt '&myv:SQL> '
set termout onSQL*Plus 9.2以前では、CONNECTコマンドの後にglogin.sqlおよびlogin.sqlは再実行されません。また、SQLPROMPTの変数は動的に置換されません。前述の問合せスクリプトを使用できますが、元の接続のプロンプトのみが有効になります。
6.4 置換変数に影響するシステム変数
システム変数の詳細は、「SET」を参照してください。
6.4.1 タイトルおよびEXITのシステム変数
TTITLE、BTITLE、REPHEADER、REPFOOTERおよびEXITコマンドでシステム変数を参照するための特殊な構文があります。特殊な各変数の名前は、「SQL」という接頭辞が付いたSHOWオプションと同じです。
参照可能な特殊変数には次のものがあります。
-
SQL.PNO - ページ番号
-
SQL.LNO - 行番号
-
SQL.USER - 現在のユーザー名
-
SQL.RELEASE - SQL*Plusのバージョン
-
SQL.SQLCODE - 最後のOracleのORAエラー番号
たとえば:
SQL> ttitle left 'Salary Report. Page: ' sql.pno
SQL> select salary from employees;
SQL> exit sql.sqlcode数値型のシステム変数(SQL.SQLCODEなど)は、数値の置換変数と同じルールを使用してフォーマットされます。
変数の先頭に「&」を付けることはできません。
これらの変数は置換変数ではありません。DEFINEコマンドでは、これらは表示されません。一般のコマンドでは参照できません。同じ名前の置換変数を作成しても、システム変数は影響されません。たとえば、USERという置換変数を作成しても、SQL.USERは影響されません。事前定義済の置換変数_O_RELEASEを変更しても、システム変数SQL.RELEASEは影響されません。
6.5 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されます。
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
例6-3 STARTを使用したパラメータの受渡し方法
表示する職種をパラメータで指定する新しいスクリプトをSALESに基づいて作成するには、次のように入力します。
GET SALES
6.5.1 スクリプトのパラメータ
SQL*Plusのスクリプトにパラメータを渡すことができます。たとえば、コマンドラインから次のように渡します。
sqlplus hr/my_password @myscript.sql KingSQL*Plusセッション内からSQL*Plusのスクリプトをコールするときに、次のようにパラメータを渡すこともできます。
SQL> @myscript.sql Kingスクリプトのパラメータは定義済の置換変数になります。最初のパラメータの変数名は「1」、2番目のパラメータは「2」のようになります。結果はSQL*Plusを開始して次のコマンドを入力した場合と同じです。
SQL> define 1 = King
SQL> @myscript.sqlmyscript.sql内のコマンドは、「&1」を参照することによって値「King」を取得できます。DEFINEコマンドはパラメータの変数を表示します。
SQL> define 1
DEFINE 1 = "King" (CHAR)スクリプトのパラメータの変数は、DEFINEで明示的に作成した変数と同様にCHAR型になります。
パラメータを一重引用符または二重引用符で囲むことができます。これによって、パラメータ内で空白文字を使用できます。SQL*Plusをコールするオペレーティング・システムおよびスクリプト言語は、異なる方法で引用符を処理します。SQL*Plus実行可能ファイルに引用符を渡す場合と渡さない場合があります。たとえば、UNIXの標準のBourneシェルでは、パラメータを囲む引用符を取り除いてからパラメータをSQL*Plusに渡すため、SQL*Plusは引用符があったことを認識しません。
使用しているオペレーティング・システムおよびSQL*Plusのパッチ・レベルで、引用符で囲まれたパラメータが処理される方法を確認することをお薦めします。UNIX環境とWindows環境の間の移植性のために、空白文字を含むパラメータは二重引用符で囲みます。
SQL*Plusリリース8.1.7、9.2.0.3 (およびバグ2471872がパッチ適用されているその他の9.xバージョン)および10.1以降では、SQL*Plusのコマンドラインに渡されたパラメータから外側の一重引用符および二重引用符の組が削除されます。これにより、引用符を取り除かないオペレーティング・システムでも、オペレーティング・システムが引用符を取り除いてからSQL*Plusを呼び出す場合と同様に、SQL*Plusが動作します。
パラメータの引渡しの例として、UNIXシェル・スクリプトでSQL*Plus 10.1を呼び出す例を示します。
#! /bin/sh
sqlplus hr/<i>my_password</i> @myscript.sql "Jack and Jill"定義されるプログラム・パラメータは1つのみです。myscript.sql内での「&1」への参照は「Jack and Jill」(引用符なし。シェルスクリプトが引用符をSQL*Plusに渡さないため)に置換されます。
6.6 ユーザーとの対話について
PROMPT、ACCEPTおよびPAUSEという3つのSQL*Plusコマンドは、エンド・ユーザーとの対話に有効です。これらのコマンドを使用すると、画面へのメッセージの送信およびユーザーからの入力([Return]キーを押すなど)の受信ができます。PROMPTおよびACCEPTを使用して、SQL*Plusで置換変数用に自動生成される値の入力を求めるプロンプトのカスタマイズもできます。
6.6.1 置換変数値の受信
PROMPTおよびACCEPTを使用すると、エンド・ユーザーへのメッセージの送信およびエンド・ユーザーからの入力値の受信ができます。PROMPTは、指定したメッセージを画面に表示して、ユーザーに指示または情報を与えるコマンドです。ACCEPTは、ユーザーに対して値の入力を求めるプロンプトを表示し、入力された値を指定した置換変数に格納するコマンドです。値の入力を求めるプロンプトを複数行にわたって表示する場合は、PROMPTをACCEPTと組み合せて使用します。
Created file PROMPT1.sqlTTITLEコマンドは、レポートの一番上のタイトルを設定します。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
例6-4 入力のプロンプトおよびアクセプト
ユーザーにレポートのタイトルを入力するよう指示し、その入力値を変数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
6.6.2 置換変数の入力を求めるプロンプトのカスタマイズ
置換変数値の入力を求めるプロンプトをカスタマイズする場合は、次の例に示すように、PROMPTおよびACCEPTを置換変数と組み合せて使用します。
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例6-5 PROMPTおよびACCEPTを置換変数と組み合せて使用する方法
例6-4で示したように、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の入力を求めるプロンプトが、次のように表示されます。
6.7 バインド変数の使用方法について
バインド変数は、SQL*Plusで作成し、PL/SQLまたはSQLで参照する変数です。SQL*Plusでバインド変数を作成した場合、その変数はPL/SQLサブプログラムの中で宣言した変数と同様に使用でき、SQL*Plusからもアクセスできます。データを保持する入力バインド変数としてバインド変数を使用し、後でPL/SQL文またはSQL文で使用してデータをデータベースに挿入できます。新規に定義した変数に値を割り当てることができます。この変数に割り当てられた値は、後で文に使用できます。
バインド変数はSQL*Plusから認識できるため、SQL*Plusでのバインド変数の値の表示、およびSQL*Plusで実行するPL/SQLサブプログラムでのバインド変数の参照が可能です。
6.7.1 バインド変数の作成
バインド変数は、SQL*PlusでVARIABLEコマンドを使用して作成します。たとえば
VARIABLE ret_val NUMBER
このコマンドによって、データ型がNUMBERで、ret_valという名前のバインド変数が作成されます。詳細は、「VARIABLE」コマンドを参照してください。(あるセッションで作成したすべてのバインド変数を表示するには、引数を付けずにVARIABLEと入力します。)
6.7.2 バインド変数の参照
PL/SQLでバインド変数を参照するには、コロン(:)を入力し、その直後に変数の名前を指定します。たとえば
:ret_val := 1;
SQL*Plusでこのバインド変数を変更するには、PL/SQLブロックを入力する必要があります。たとえば:
BEGIN :ret_val:=4; END; /
PL/SQL procedure successfully completed.このコマンドは、ret_valという名前のバインド変数に値を割り当てます。
6.7.3 バインド変数の表示
SQL*Plusでバインド変数の値を表示するには、SQL*PlusのPRINTコマンドを使用します。たとえば:
PRINT RET_VAL
RET_VAL
----------
4このコマンドは、ret_valという名前のバインド変数を表示します。バインド変数の表示の詳細は、「PRINT」を参照してください。
6.7.4 入力バインドの実行
入力バインディングの変数に値を割り当てることができます。
SQL> variable abc number=123 SQL> select :abc from dual; :ABC ---------- 123 SQL>
SQL> create table mytab (col1 number, col2 varchar2(10));
Table created.
SQL> var abc number=123
SQL> var xyz varchar2(10)='test'
SQL> insert into mytab values(:abc,:xyz);
1 row created.
SQL> select * from mytab;
COL1 COL2
---------- ----------
123 test
SQL>詳細は、「VARIABLE」コマンドを参照してください。
6.8 REFCURSORバインド変数の使用方法
SQL*PlusのREFCURSORバインド変数を使用すると、PL/SQLブロックに含まれるSELECT文の結果をSQL*Plusからフェッチし、その書式を設定できます。
REFCURSORバインド変数は、ストアド・プロシージャ内のPL/SQLカーソル変数の参照にも使用できます。この機能を使用して、データベース内にSELECT文を格納し、それらの文をSQL*Plusから参照できます。
REFCURSORバインド変数は、ストアド・ファンクションから戻すこともできます。
PL/SQL procedure successfully completed.この時点で、SELECT文の結果をSQL*PlusでPRINTコマンドを使用して表示できます。
PRINT employee_info
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500PRINT文は、カーソルもクローズします。結果を再出力するには、PRINTを使用する前にPL/SQLブロックを再実行する必要があります。
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 10500Function 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.例6-6 REFCURSORバインド変数の作成、参照および表示
REFCURSORバインド変数を作成、参照および表示するには、最初にREFCURSORデータ型のローカル・バインド変数を宣言します。
create procedure p4 as c1 sys_refcursor; c2 sys_refcursor; begin open c1 for SELECT * FROM DEPT; dbms_sql.return_result(c1); open c2 for SELECT * FROM EMP; dbms_sql.return_result(c2); end; /
次に、このバインド変数を使用するPL/SQLブロックをOPEN... FOR SELECT文に入力します。この文は、カーソル変数をオープンし、問合せを実行します。OPENコマンドおよびカーソル変数の詳細は、「OPEN文」を参照してください。
この例では、SQL*Plusのemployee_infoバインド変数をカーソル変数にバインドします。
BEGIN OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ; END; /
例6-7 ストアド・プロシージャでのREFCURSOR変数の使用方法
REFCURSORバインド変数は、パラメータとしてプロシージャに渡されます。パラメータは、REF CURSOR型です。最初に、型を定義します。
CREATE OR REPLACE PACKAGE EmpPack AS TYPE EmpInfoTyp IS REF CURSOR; PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp); END EmpPack; /
例6-8 ストアド・ファンクションでの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; /
6.9 PL/SQLブロックに含まれるSELECT文の結果の反復的なフェッチ
SQL*Plusでは、PL/SQLブロックまたはストアド・プロシージャに含まれるSELECT文の結果を反復的にフェッチし、その書式を設定できます。ローカルのREFCURSOR変数を定義する必要はありません。
SELECT文の結果が表示されます。
ResultSet #1
DEPTNO DNAME LOC
------ ---------- ---------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selectedResultSet #2
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ --------- ---- --------- ---- ---- --
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 05-APR-11 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 09-MAY-11 1100
14 rows selected例6-9 PL/SQLプロシージャの作成
2つの文をコールするPL/SQLプロシージャP4を作成します。
create procedure p4 as
c1 sys_refcursor;
c2 sys_refcursor;
begin
open c1 for SELECT * FROM DEBT;
dbms_sql.return_result(c1);
open c2 for SELECT * FROM EMP;
dbms_sql.return_result(c2);
end;
/
Procedure created.次に、このプロシージャを実行して、プロシージャ内のSELECT文の結果を反復的に取得します。
exec p4 PL/SQL procedure successfully completed.