Oracle databaseでは、SQLの中で変数が使用可能で、これをバインド変数と呼びます。また、他にも同様の機能を持つデータベース管理ソフトがあります。
今回は、ここではOracle databaseを例に、バインド変数の基本について解説します。
目次
Oracle dataseのバインド変数の基本
データベース管理ソフトでは、SQLの中で変数を宣言することで、まるでプログラム言語のように、その変数に値を代入し、SQLを実行する際にそのデータを適用される機能が使えます。
そして、Oralce dataseではその変数をバインド変数と呼び、Javaなどのプログラムなどからよく使われる機能です。次から、このバインド変数の基本について解説します。
バインド変数の宣言
バインド変数を利用するためには、VARIABLEコマンドを使い、変数を宣言しなければなりません。
ただし、VARIABLEコマンドで宣言できる変数は、数値型か文字列型のいずれかです。間違えると、SQLの実行中にエラーが発生するので注意してください。
VARIABLEコマンドの構文は次のとおりで。
VARIABLE 変数名 変数の型
（VARIABLEコマンドで宣言できる変数の型は、SQLで使える数値型、または、文字列型に限られる）
バインド変数の宣言例
variable val NUMBER; variable str VARCHAR2(20);
バインド変数に値を格納する
先ほど宣言したバインド変数は、SQLの中でコロン「:」を付けて参照します。また、バインド変数に数値または文字列を格納する場合は、EXECUTEコマンドを使用し、値の代入に「:=」演算子を使用します。EXECUTEコマンドの使い方を次に紹介します。
EXECUTE :変数名 := 数値または文字列
バインド変数への値を代入する例
execute :val := 16384; execute :str := 'potepan style';
バインド変数への代入にはSQL関数も使える
バインド変数に値を入力する際、SQLで使える関数の結果を代入させることも可能です。例えば、今日の日付を文字列で格納する場合、次のようなSQLを記述します。
なお、この例では、日付を文字列に変換して格納するため、to_date関数を使用します。
バインド変数に今日の日付を格納する例
variable today VARCHAR2(20); execute :today := to_date(SYSDATE, 'YYYY-MM-DD');
また、値が1つだけ出力される場合に限られますが、SELECT文の結果をバインド変数への代入に使うことも可能です。次のSQLは、MAX関数を使い、ある列の最大値をバインド変数に代入する例です。
variable today VARCHAR2(20); execute :today := SELECT MAX(price) FROM price_table;
バインド変数をSQLで使う
SQLのSELECT句などでバインド変数を利用する場合、バインド変数に値を格納するのと同じく、コロン「:」を付けた変数を使います。
例えば、SELECT句で検索条件としてバインド変数を使う場合、次のように使います。
バインド変数を使ったSQLの例
variable val NUMBER; variable str VARCHAR2(20); execute :val := 16384; execute :str := 'potepan style'; SELECT * FROM table_one WHERE price = :val AND name = :str AND date = :today;
バインド変数が使えるのはOracle databaseだけではない
Oracle databaseのバインド変数の使い方を解説しましたが、変数が使えるデータベース管理ソフトは、Oracle databseだけではありません。
IBM DB2など、昔から使われているエンタープライズ用途のデータベース管理ソフトでもバインド変数が使えます。
そして、このようなのデータベース管理ソフトでは、Oracle databaseと同じく、変数名にコロン「:」を付けて、変数を参照するもの同じです。
なお、OracleやDB2等ではバインド変数と呼ばれていますが、他のデータベース管理ソフトでは別の名称で呼ばれています。次から、それらも合わせて解説します。
SQL Serevrの変数ではアットマークを使う
マイクロソフト製のSQL Serverは、SQLが使えるデータベース管理ソフトの中でも特殊な仕様が多いアプリケーションですが、これもSQLの中で変数が使えます。ただし、SQL Serverでは、変数にアットマーク「@」を付けます。
バインド変数の宣言
SQL Serverで変数を利用する場合、プログラミング言語のように変数を宣言しなければなりません。そして、変数の宣言に使われるコマンドがDECLAREです。DECLAREの基本的な構文は次のとおりです。
DECLARE @変数名 データタイプ;
なお、このDECLARコマンドは、Oracle databaseのvariableコマンドと違い、機能がたくさんあります。そして、下記の例のように、変数の初期値を入れることも可能です。
DECLAREコマンドの例 DECLARE @keyword varchar(30); DECLARE @findstr varchar(30) = 'Mc%';
バインド変数の使い方
SQL Serverでは、変数に値を代入する場合、SETコマンドを使います。また、代入した値は、アットマーク「@」付きの変数名を使い、SELECT句などのSQLで利用できます。
変数に値を代入し、SQLで使う例 DECLARE @keyword varchar(30); SET @keyword = 'Mc%'; SELECT p.LastName, p.FirstName From PersonTbl as p WHERE LastName LIKE @keyword;
バインド変数をSELECT句の中で代入
SQL Serverでは、SELECT句の中でバインド変数に数字や文字列などを代入できます。例えば、次のように、DECLARE文で変数を宣言し、代入のためのSELECT文を記述できます。
バインド変数をSELECT句の中で代入する例
DECLARE @ThisDate DATE; DECLARE @NextDate DATE; SELECT @ThisDate = GETDATE(), @NextDate = DATEADD(DAY,1,@ThisDate) ;
上記の例は、下記のSET文を使った例と同じです。
DECLARE @ThisDate DATE; DECLARE @NextDate DATE; SET @ThisDate = GETDATE(); SET @NextDate = DATEADD(DAY,1,@ThisDate);
MySQLでも変数が使える
オープンソースのデータベース管理ソフトとして多くのシステムで利用されている、MySQLおよび互換性のあるmariadbでは、「ユーザー定義変数」がバインド変数と同じように使えます。
なお、MySQLでは変数名の前にアットマーク「@」を付けて利用し、使い方はSQL Serverの変数と同じです。
短期集中でWebエンジニアになれるスクールはこちら
変数の宣言なしでも使える
MySQLのバインド変数は、特に宣言しなくても、SQLの中で使えます。例えば、SETコマンドは変数に値を代入するコマンドですが、変数を宣言せずに、SETコマンドで利用できます。
なお、SETでは、=演算子と:=演算子のどちらでも使えますが、=演算子は比較演算子として使われることが多いので、:=演算子を使う方が良いでしょう。
MySQLの変数を使ったSQLの例 SET @keyword ;= 'Mc%'; SELECT LastName, FirstName From PersonTbl WHERE LastNmae LIKE @keyword;
なお、MySQLのバインド変数は、SQL Serverと同じように変数名に「@」を付けて参照しますが、SQL Serverと同じSQLが動作する訳ではありません。使えない機能もあります。もし、移植する機会があれば、バインド変数の扱いに注意してください。
まとめ
Oracle databaseのバインド変数を中心に、SQLでの変数の使い方を簡単に解説しました。
なお、SQLで使える変数には、コロン「:」を付ける方法と、アットマーク「@」を付ける方法があり、データベース管理ソフトによっても使い方が違います。
SQLの中で変数を利用する場合は、移植が面倒になるケースもあるので、そのことを理解したうえで利用してください。
SQLを学んでWebエンジニアを目指そう
Webエンジニアは、Webサービスを作るエンジニアで非常に人気の高い職種です。
スタートアップやベンチャー企業が中心なので柔軟性のある雇用形態、魅力的な作業環境、面白いプロジェクト、高い報酬など非常に魅力的な求人が多いです。
Ruby on RailsやGo言語を用いたプロジェクトが多く、SQLも重要なスキルとなります。
このブログを運営するプログラミングスクールのポテパンキャンプでは、実践的なカリキュラムと現役エンジニアからのレビュー、そしてポートフォリオ添削や模擬面談などの面談転職サポートにより、最短距離でWebエンジニアを目指すことができます。
Webエンジニアへの転職を考えている方は、是非一度無料カウンセリングへお申込みください。
