13.55 VARIABLE
構文
VAR[IABLE] [variable [type [=value]]]
PL/SQLで参照可能なバインド変数を宣言するか、1つまたはすべての変数の現在の表示特性を表示します。
typeはデータ型を表します。これらのデータ型は、「用語」の項にリストされています。
引数なしでVARIABLE
を指定すると、セッション内で宣言されているすべての変数が表示されます。VARIABLE
の後に変数名のみを指定すると、その変数が表示されます。
CLOB
およびNCLOB
バインド変数に使用されるリソースを解放するには、次のように入力して一時LOBを手動で解放する必要があります。
EXECUTE DBMS_LOB.FREETEMPORARY(:cv)
バインド変数の詳細は、バインド変数の使用方法についてを参照してください。PL/SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
項
variable
value
NUMBER
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
設定より優先されます。
ノート:
クライアント文字NLS_LANG
環境変数が設定されておらず、データベース文字セットがマルチバイト(AL32UTF32など)である場合、宣言されたバインド変数がPL/SQLから返されたデータより小さいと、PL/SQLによってデータが切り捨てられます。詳細は、「制限事項」を参照してください。
ノート:
デフォルトでは、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)に実質的に準拠する浮動小数点数です。
BOOLEAN
VECTOR
VECTOR
バインド変数を使用してVECTOR
データ型列に値を挿入または更新するときに、バインド変数に割り当てられたベクトル・テキストがデータベース表で定義されたVECTOR
データ型列と一致しない場合、エラーが表示されます。
使用方法
バインド変数は、ストアド・プロシージャに対するパラメータとして使用されます。また、無名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 abc BOOLEAN VARIABLE var1 VECTOR VARIABLE myvar REFCURSOR
引数なしでVARIABLE
コマンドを実行して、定義された変数をリストできます:
VARIABLE
次のような出力結果が表示されます。
variable id
datatype NUMBER
variable txt
datatype CHAR(20)
variable abc
datatype BOOLEAN
variable var1
datatype VECTOR
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
---------
TRUE
BOOLEAN
列にデータを挿入できます。
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
VECTOR
列にデータを挿入できます。VARIABLE var1 VECTOR='[2,3]'
INSERT INTO my_vector1 VALUES(:var1);
1 row created
SELECT
コマンドを使用して、VECTOR
列データを表示できます。SELECT * FROM my_vector1;
INT -------------------------------------------------------------------------------- [2.0E+000,3.0E+000]
VECTOR
データ型のバインド変数を参照するには、次のように入力します。SET SERVEROUTPUT ON;
VARIABLE plsql_flt32 VECTOR='[8.4,9.6]'
BEGIN
INSERT INTO myvector_2 VALUES(:plsql_flt32);
END;
/
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SELECT
コマンドを使用して、VECTOR
列データを表示できます。SELECT * FROM my_vector2;
FLT32 -------------------------------------------------------------------------------- [8.39999962E+000,9.60000038E+000]