バインド変数を宣言する
VARIABLE は PL/SQL と SQL*Plus の両方から参照できるバインド変数の宣言を行なうためのコマンド
バインド変数には ユーザー変数 のように変数を削除するコマンドは存在しない。
クライアントで使用しているメモリの消費量が気になるような環境では変数に NULL を代入して初期化する。
バインド変数の内容を SQL*Plus から表示させるには PRINT コマンド を使用する。
VARIABLE コマンド
VARIABLE コマンドの構文
VARIABLE [変数名 [データ型]]
短縮形
VAR [変数名 [データ型]]
使用できるデータ型に以下のとおり
- NUMBER
- CHAR [ (n [ CHAR | BYTE ]) ]
- NCHAR [(n)]
- VARCHAR2 (n [ CHAR | BYTE ])
- NVARCHAR2 (n)
- CLOB
- NCLOB
- BINARY_FLOAT
- BINARY_DOUBLE
- REFCURSOR
CHAR、NCHAR はサイズを省略することができ、省略した場合は '1' となる。
SQL*Plus で使用できるバインド変数のデータ長の制限は PL/SQL の上限値とは異なりデータベースに格納できる データ型 の制限と同じ。
バインド変数の参照と代入
代入や参照には変数名の前にコロン(:) をつける。バインド変数は直接代入できず(※1) 、PL/SQL ブロック内で代入する必要がある。
SQL> set null '<null>'
SQL> set head off
SQL> variable var_at_sqlplus number
SQL> print :var_at_sqlplus
<null>
SQL> begin
2 :var_at_sqlplus := 1;
3 end;
4 /
PL/SQLプロシージャが正常に完了しました。
SQL> print :var_at_sqlplus
1
SQL> execute :var_at_sqlplus := 2
SQL> -- BEGIN :var_at_sqlplus := 2; END; に変換される。
PL/SQLプロシージャが正常に完了しました。
SQL> print :var_at_sqlplus
2
(※1) バインド変数に対する初期値と代入 
Oracle 12c R2 から variable の定義時に代入を行なうことができる
Oracle 12c R2 ～
SQL> variable var_at_sqlplus number=1
SQL> print :var_at_sqlplus
VAR_AT_SQLPLUS
--------------
1
もしくは定義と代入を分ける表記でも可能
SQL> variable var_at_sqlplus number
SQL> variable var_at_sqlplus=1
SQL> print :var_at_sqlplus
VAR_AT_SQLPLUS
--------------
1
Oracle 12c R1 以前だと…
その１
SQL> variable var_at_sqlplus number=1
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
その２
SQL> variable var_at_sqlplus number
SQL> variable var_at_sqlplus=1
SP2-0553: Illegal variable name "var_at_sqlplus=1"
バインド変数と PL/SQL 変数
SQL> set serveroutput on
SQL> -- SQL*Plus のバインド変数
SQL> variable var_at_sqlplus number
SQL> declare
2 var_at_dbserver number; -- PL/SQL だけの変数
3 begin
4 :var_at_sqlplus := 0;
5 var_at_dbserver := 0;
6 for i in 1..10
7 loop
8 :var_at_sqlplus := :var_at_sqlplus + 1;
9 var_at_dbserver := var_at_dbserver + 10;
10 end loop;
11 dbms_output.put_line('PLSQL block (var): ' || :var_at_sqlplus);
12 dbms_output.put_line('PLSQL block (db) : ' || var_at_dbserver);
13 end;
14 /
PLSQL block (var): 10
PLSQL block (db) : 100
PL/SQLプロシージャが正常に完了しました。
SQL> -- 変数のプリント
SQL> print :var_at_sqlplus
10
SQL> print var_at_dbserver
SP2-0552: バインド変数"VAR_AT_DBSERVER"が宣言されていません。
SQL>
（注意）バインド変数は SQL*Plus 上のメモリを使用する変数であるためバインド変数を変更するたびにデータベースサーバーと SQL*Plus 間でデータの送受信が発生する。このため上記のサンプルのようにバインド変数の代入を繰り返すような処理は効率が悪い。（特に Oracle Net サービスを経由する場合）
ホスト変数
バインド変数にユーザー定義変数を代入する方法
動的SQLとバインド変数
