13.54 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 BOOLEAN
引数なしで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 (n)NCHAR型で長さがn文字の変数が作成されます。nの最大値は、選択した文字セットで各文字を格納するために必要なバイト数で決定されますが、最大2000バイトです。SQL*PlusセッションがOracle9i以降のサーバーに接続している場合、または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の最大長を有効にするには、MAX_STRING_SIZE=extendedパラメータをinit.oraファイルに追加する必要があります。
NVARCHAR2 (n)NVARCHAR2型で長さがn文字までの変数が作成されます。nの最大値は、選択した文字セットで各文字を格納するために必要なバイト数で決定されますが、最大32KB (ノートを参照)です。SQL*PlusセッションがOracle9i以降のサーバーに接続している場合、またはSQLPLUSCOMPATIBILITYシステム変数が9.0.0未満に設定されている場合は例外となります。この場合、長さnは、選択した各国語文字セットによってバイト単位または文字単位になりますが、いずれの場合も最大値は32KBです。
ノート:
デフォルトでは、NVARCHAR2の最大長は4000バイトです。4000バイトを超える最大長の使用を試みると、ORA-01460 : 「リクエストされた変換はできません。」が発生します。
32Kの最大長を有効にするには、MAX_STRING_SIZE=extendedパラメータをinit.oraファイルに追加する必要があります。
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)に実質的に準拠する浮動小数点数です。
ノート:
リリース23c以降、VARIABLEコマンドはSQL*PLUSでBOOLEANデータ型をサポートします。
BOOLEAN
使用方法
バインド変数は、ストアド・プロシージャに対するパラメータとして使用されます。また、無名PL/SQLブロックの中で直接参照できます。
VARIABLEで作成したバインド変数の値を表示するには、PRINTコマンドを使用します。詳細は、「PRINT」を参照してください。
VARIABLEで作成したバインド変数の値を自動的に表示するには、SET AUTOPRINTコマンドを使用します。詳細は、SET AUTOP[RINT] {ON | OFF}を参照してください。
バインド変数は、PL/SQLブロックに入れる場合を除いて、COPYコマンドまたはSQL文の中で使用できません。かわりに、置換変数を使用します。
VARIABLE ... CLOBまたはNCLOBコマンドを実行すると、SQL*Plusは、LOBロケータをバインド変数に対応付けます。LOBロケータは、PL/SQLブロック内のSELECT clob_column INTO :cv文を実行すると、自動的にデータが移入されます。SQL*Plusを終了すると、SQL*PlusはLOBロケータをクローズします。
CLOBおよびNCLOBバインド変数に使用されるリソースを解放するには、次のように入力して一時LOBを手動で解放する必要があります。
EXECUTE DBMS_LOB.FREETEMPORARY(:cv)
すべての一時LOBは、SQL*Plusの終了時に解放されます。
SQL*PlusのSETコマンド(SET LONG、SET LONGCHUNKSIZEおよびSET LOBOFFSETなど)を使用すると、CLOBまたはNCLOBバインド変数をPRINTするときに、バッファのサイズを制御できます。
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のPRINT出力を書式設定できます。
REFCURSORバインド変数を2回以上PRINTするには、PL/SQL OPEN ... FOR文を再実行する必要があります。
例
次の例では、バインド変数を作成し、その値を変更して、現在の値を表示する方法を示します。
バインド変数を作成するには、VARIABLEコマンドを使用します。
VARIABLE ret_val NUMBER
SQL*Plusでこのバインド変数を変更するには、PL/SQLブロックを使用する必要があります。
BEGIN :ret_val:=4; END; /
PL/SQL procedure successfully completed.SQL*Plusでバインド変数の値を表示するには、SQL*PlusのPRINTコマンドを使用します。
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コマンドを使用してバインド変数を自動的に表示する方法を示しています。
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; /
変数を表示するためにPRINTコマンドを実行する必要はありません。
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.次の例は、いくつかの変数の作成を示しています。
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> VARIABLE 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 COMPLETED
次のコマンドを使用して、列clob_colから200文字を出力できます。
SET LINESIZE 70 SET LONG 200 PRINT T T ---------------------------------------------------------------------- Remember to run the Departmental Salary Bill report each month This report contains confidential information.
次のコマンドを使用して、出力位置を21番目の文字に設定できます。
SET LOBOFFSET 21 PRINT T T ---------------------------------------------------------------------- Departmental Salary Bill report each month This report contains confidential information.
BOOLEANデータ型のバインド変数を参照するには、次のように入力します。VARIABLE abc BOOLEAN;
BEGIN
:abc := 'TRUE';
END;
/SELECTまたはPRINTコマンドを使用して参照されるバインド変数を表示するには、次のように入力します。
SELECT :abc FROM DUAL;
:ABC
---------
TRUE
PRINT :abc
:ABC
---------
TRUEBOOLEAN列にデータを挿入できます。
VARIABLE var1 BOOLEAN='TRUE' VARIABLE var2 BOOLEAN='FALSE' INSERT INTO my_table Values (:var1, :var2);
SELECTコマンドを使用して、BOOLEAN列データを表示できます。
SELECT * FROM my_table;次のような出力結果が表示されます。
COL1 COL2
------ ------
TRUE FALSE