12.51 VARIABLE
構文
VAR[IABLE] [variable [type [=value]]]
PL/SQLで参照可能なバインド変数を宣言するか、1つまたはすべての変数の現在の表示特性を表示します。
typeには、次のいずれかを指定します。
NUMBER CHAR CHAR (n [CHAR | BYTE]) NCHAR NCHAR (n) VARCHAR2 (n [CHAR | BYTE]) NVARCHAR2 (n) CLOB NCLOB REFCURSOR BINARY_FLOAT BINARY_DOUBLE
引数なしでVARIABLEを指定すると、セッション内で宣言されているすべての変数が表示されます。VARIABLEの後に変数名のみを指定すると、その変数が表示されます。
CLOBおよびNCLOBバインド変数に使用されるリソースを解放するには、次のように入力して一時LOBを手動で解放する必要があります。
EXECUTE DBMS_LOB.FREETEMPORARY(:cv)
バインド変数の詳細は、バインド変数の使用方法についてを参照してください。PL/SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
項
variablevalueNUMBER
CHAR
CHAR (n[CHAR | BYTE])CHAR型で長さがnバイトまたはn文字の変数が作成されます。nの最大値は2000バイトで、最小値は1バイトまたは1文字です。キャラクタ・セマンティクスを持つCHAR変数の最大値nは、選択したキャラクタ・セットで各文字を格納するために必要なバイト数で決定されますが、最大2000バイトです。長さセマンティクスは、長さ修飾子CHARまたはBYTEで決定されます。明示的に記述しない場合は、環境変数NLS_LENGTH_SEMANTICSの値がバインド変数に適用されます。変数定義の段階で長さセマンティクスを明示的に記述すると、NLS_LENGTH_SEMANTICS設定より常に優先されます。
NCHAR
NCHAR(各国語キャラクタ)型で長さ1の変数が作成されます。
NCHAR (n)NCHAR型で長さがn文字の変数が作成されます。nの最大値は、選択したキャラクタ・セットで各文字を格納するために必要なバイト数で決定されますが、最大2000バイトです。SQL*PlusセッションがOracle8i以下のサーバーに接続している場合、またはSQLPLUSCOMPATIBILITYシステム変数が9.0.0未満に設定されている場合は例外となります。この場合、長さnは、選択した各国語キャラクタ・セットによってバイト単位または文字単位になりますが、いずれの場合も最大値は2000バイトです。
VARCHAR2 (n[CHAR | BYTE])VARCHAR2型で長さがnバイトまたはn文字までの変数が作成されます。nの最大値は32KB (注意を参照)で、最小値は1バイトまたは1文字です。キャラクタ・セマンティクスを持つVARCHAR2変数nの最大値は、選択したキャラクタ・セットで各文字を格納するために必要なバイト数で決定されますが、最大32KBです。長さセマンティクスは、長さ修飾子CHARまたはBYTEで決定されます。明示的に記述しない場合は、環境変数NLS_LENGTH_SEMANTICSの値がバインド変数に適用されます。変数定義の段階で長さセマンティクスを明示的に記述すると、NLS_LENGTH_SEMANTICS設定より常に優先されます。
注意:
デフォルトでは、VARCHAR2の最大長は4000バイトです。4000バイトを超える最大長の使用を試みると、ORA-01460 : 「リクエストされた変換はできません。」が発生します。
32Kの最大長を有効にするには、init.oraファイルにMAX_STRING_SIZE=extendedパラメータを追加する必要があります。
NVARCHAR2 (n)NVARCHAR2型で長さがn文字までの変数が作成されます。nの最大値は、選択したキャラクタ・セットで各文字を格納するために必要なバイト数で決定されますが、最大32KB (注意を参照)です。SQL*PlusセッションがOracle8i以下のサーバーに接続している場合、またはSQLPLUSCOMPATIBILITYシステム変数が9.0.0未満に設定されている場合は例外となります。この場合、長さnは、選択した各国語キャラクタ・セットによってバイト単位または文字単位になりますが、いずれの場合も最大値は32KBです。
注意:
デフォルトでは、NVARCHAR2の最大長は4000バイトです。4000バイトを超える最大長の使用を試みると、ORA-01460 : 「リクエストされた変換はできません。」が発生します。
32Kの最大長を有効にするには、init.oraファイルにMAX_STRING_SIZE=extendedパラメータを追加する必要があります。
CLOB
NCLOB
REFCURSOR
BINARY_FLOAT
BINARY_FLOAT型の変数が作成されます。BINARY_FLOATは、米国電気電子技術者協会(IEEE)の2進浮動小数点演算についてのIEEE規格(IEEE規格754-1985)に実質的に準拠する浮動小数点数です。
BINARY_DOUBLE
BINARY_DOUBLE型の変数が作成されます。BINARY_DOUBLEは、米国電気電子技術者協会(IEEE)の2進浮動小数点演算についてのIEEE規格(IEEE規格754-1985)に実質的に準拠する浮動小数点数です。
使用方法
バインド変数は、ストアド・プロシージャに対するパラメータとして使用されます。また、無名PL/SQLブロックの中で直接参照できます。
VARIABLEで作成したバインド変数の値を表示するには、PRINTコマンドを使用します。詳細は、「PRINT」を参照してください。
VARIABLEで作成したバインド変数の値を自動的に表示するには、SET AUTOPRINTコマンドを使用します。詳細は、SET AUTOP[RINT] {ON | OFF}を参照してください。
バインド変数は、PL/SQLブロックに入れる場合を除いて、COPYコマンドまたはSQL文の中で使用できません。かわりに、置換変数を使用します。
VARIABLE ... CLOBまたはNCLOBコマンドを実行すると、SQL*Plusは、LOBロケータをバインド変数に対応付けます。PL/SQLブロック内のSELECT clob_column INTO :cv文を実行すると、LOBロケータには自動的にデータが挿入されます。SQL*Plusを終了すると、SQL*PlusはLOBロケータをクローズします。
CLOBおよびNCLOBバインド変数に使用されるリソースを解放するには、次のように入力して一時LOBを手動で解放する必要があります。
EXECUTE DBMS_LOB.FREETEMPORARY(:cv)
すべての一時LOBは、SQL*Plusの終了時に解放されます。
CLOBまたはNCLOBバインド変数を出力するときに、SQL*PlusのSETコマンド(SET LONG、SET LONGCHUNKSIZE、SET LOBOFFSETなど)を使用して、バッファのサイズを制御できます。
SQL*PlusのREFCURSORのバインド変数を使用すると、PL/SQL 2.3以上のカーソル変数を参照できます。また、PL/SQL出力をSQL*Plusで書式設定できます。PL/SQLのカーソル変数の詳細は、「カーソル変数」を参照してください。
VARIABLE ... REFCURSORコマンドを実行する場合、SQL*Plusはカーソル・バインド変数を作成します。PL/SQLブロック内のバインド変数を参照するOPEN ... FOR SELECT文によって、カーソルが自動的にオープンされます。SQL*Plusは、バインド変数用のPRINT文の処理が完了した後、または終了時に、カーソルをクローズします。
SQL*Plusの書式設定コマンド(BREAK、COLUMN、COMPUTE、SETなど)を使用して、REFCURSORの印刷出力を書式設定できます。
REFCURSORバインド変数を2回以上印刷するには、PL/SQL OPEN ... FOR文を再実行する必要があります。
例
次の例では、バインド変数を作成し、その値を変更して、現在の値を表示する方法を示します。
バインド変数を作成するには、次のように入力します。
VARIABLE ret_val NUMBER
SQL*Plusでこのバインド変数を変更するには、PL/SQLブロックを使用する必要があります。
BEGIN :ret_val:=4; END; /
PL/SQL procedure successfully completed.SQL*Plusのバインド変数の値を表示するには、次のように入力します。
PRINT ret_val
RET_VAL
----------
4次の例では、バインド変数を作成し、その変数を関数から戻される値に設定する方法を示します。
VARIABLE id NUMBER
BEGIN
:id := EMP_MANAGEMENT.HIRE
('BLAKE','MANAGER','KING',2990,'SALES');
END;
/ストアド・プロシージャから戻された値がバインド変数:idに設定されています。この変数は、PRINTコマンドを使用して表示したり、後続のPL/SQLサブプログラムで使用できます。
次の例では、バインド変数を自動的に表示する方法を示します。
SET AUTOPRINT ON VARIABLE a REFCURSOR BEGIN OPEN :a FOR SELECT LAST_NAME, CITY, DEPARTMENT_ID FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_ID; END; /
PL/SQL procedure successfully completed.
LAST_NAME CITY DEPARTMENT_ID
------------------------- ------------------------------ -------------
Hartstein Toronto 20
Russell Oxford 80
Partners Oxford 80
King Seattle 90
Kochhar Seattle 90
De Haan Seattle 90
6 rows selected.前述の例では、変数を表示するためにPRINTコマンドを実行する必要はありません。
次の例では、いくつかの変数を作成します。
VARIABLE id NUMBER VARIABLE txt CHAR (20) VARIABLE myvar REFCURSOR
定義済変数を表示するには、引数を指定せずにVARIABLEを入力します。
VARIABLE
variable id
datatype NUMBER
variable txt
datatype CHAR(20)
variable myvar
datatype REFCURSOR次の例では、1つの変数を表示します。
VARIABLE txt
variable txt
datatype CHAR(20)次の例では、入力バインディングの変数に対する値の割当てを示します。
VARIABLE tmp_var VARCHAR2(10)=Smith
次の例では、前の例と同じ結果を得るための代替方法を示します。
VARIABLE tmp_var VARCHAR2(10) VARIABLE tmp_var=Smith EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)
次の例では、個人別に給与を表示し、1か月の給与が$12,000を超える従業員の部門別の給与を計算するレポートを作成する方法を示します。
VARIABLE rc REFCURSOR BEGIN OPEN :rc FOR SELECT DEPARTMENT_NAME, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY DEPARTMENT_NAME, LAST_NAME; END; /
PL/SQL procedure successfully completed.SET PAGESIZE 100 FEEDBACK OFF TTITLE LEFT '*** Departmental Salary Bill ***' SKIP 2 COLUMN SALARY FORMAT $999,990.99 HEADING 'Salary' COLUMN DEPARTMENT_NAME HEADING 'Department' COLUMN LAST_NAME HEADING 'Employee' COMPUTE SUM LABEL 'Subtotal:' OF SALARY ON DEPARTMENT_NAME COMPUTE SUM LABEL 'Total:' OF SALARY ON REPORT BREAK ON DEPARTMENT_NAME SKIP 1 ON REPORT SKIP 1 PRINT rc
*** Departmental Salary Bill ***
DEPARTMENT_NAME Employee Salary
------------------------------ ------------------------- ------------
Executive De Haan $17,000.00
King $24,000.00
Kochhar $17,000.00
****************************** ------------
Subtotal: $58,000.00
Marketing Hartstein $13,000.00
****************************** ------------
Subtotal: $13,000.00
Sales Partners $13,500.00
Russell $14,000.00
****************************** ------------
Subtotal: $27,500.00
------------
Total: $98,500.00次の例では、入力バインドを作成してCLOBデータをCLOB列に挿入する方法を示します。
SQL> create table xyz (col1 clob); Table created. SQL> var abc varchar2(100)="This is a clob input" SQL> insert into xyz values(:abc); 1 row created.
次の例では、CLOB列を含むレポートを作成し、SET LOBOFFSETコマンドを使用してそのレポートを表示する方法を示します。
CLOB型のclob_colという名前の列を含むclob_tabという名前の表を、すでに作成してあるとします。clob_colには、次のデータが含まれています。
Remember to run the Departmental Salary Bill report each month. This report contains confidential information.
col_clob列にデータを表示するレポートを作成するには、次のように入力します。
VARIABLE T CLOB BEGIN SELECT CLOB_COL INTO :T FROM CLOB_TAB; END; /
PL/SQL PROCEDURE SUCCESSFULLY COMPLETEDclob_col列から200文字を出力するには、次のように入力します。
SET LINESIZE 70 SET LONG 200 PRINT T
T
----------------------------------------------------------------------
Remember to run the Departmental Salary Bill report each month This r
eport contains confidential information.出力位置を21番目の文字に設定するには、次のように入力します。
SET LOBOFFSET 21 PRINT T
T
----------------------------------------------------------------------
Departmental Salary Bill report each month This report contains confi
dential information.