SQLのdeclareについてまとめています。
SQLのdeclareのサンプルSQL
declareはストアドプログラムやストアドプロシジャのローカル変数や受け渡し用のパラメータ定義に使われます。
例えば、MySQLでは以下のように使います。
mysql> delimiter //
mysql> create procedure testproc( param1 INT )
-> begin
-> declare name1 varchar(10);
-> set name1='Saniya';
-> select * from employees where emp_no = param1 and first_name = name1;
-> end//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call testproc( 10008 ) ; +--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
簡単なストアドプロシジャを登録し、callで実行するSQLです。delimiterは、区切り文字を変更するための設定です。ストアドプロシジャ内のセミコロンでSQLが実行されないよう、一時的に別の区切り文字//（スラッシュを２つ）に設定しています。
【関連記事】
▶SQL BEGINの使い方 トランザクション記述時にデリミタの置き換えが必要なケース
delimiterを設定しておかないと、以下のようにsyntax errorが発生します。
mysql> create procedure testproc( param1 INT )
-> begin
-> declare name1 varchar(10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right s
yntax to use near '' at line 3
エラーはdeclareの行でのsyntax errorですが、書式等は間違っていません。発生原因は改行文字の設定忘れとなります。
短期集中でWebエンジニアになれるスクールはこちら
カーソル定義のサンプルコード
MySQLでは、ローカル変数の定義以外に、指定したSQLの結果を一件ずつ取得するための「カーソル」の定義にもdeclareが使われます。
以下は、二つの異なるSQLの結果をループしながら読み出し、別テーブルに書き出すストアドプロシジャを作るサンプルです。
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.6.6 カーソル
データベースごとのdeclareの注意点
Oracleは、サブプログラムの実行にもdeclareを使用する
Oracleでは、ストアドプロシジャのローカル変数のほか、PL/SQLサブプログラムの実行にDECLAREが使用できます。
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD (i, 2) = 0 THEN --i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
END;
.
/
SQL Serverは、バッチの中でdeclareによる変数の宣言が可能
SQL Serverでは、バッチやプロシージャ中でDeclareを使って変数の宣言をおこなうことができます。
USE AdventureWorks2012;
GO
DECLARE @mycol nvarchar(20) ;
SET @mycol = 'Name';
EXECUTE ('SELECT ' + @mycol + ' FROM Production.Product';) ;
PostgreSQLは、カーソル定義にdeclareが使われる
PostgreSQLでは、create function内でのローカル変数の宣言や、cursorの定義用にdeclareが使われます。
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$ LANGUAGE plpgsql;
PostgreSQLもMySQLと同様、カーソル定義にdeclareが使われます。
まとめ
- declareは、ストアドプロシジャなどでローカル変数を宣言する命令
- ストアドプロシジャ定義時に、declareでsyntax errorが出たら、改行文字の設定し忘れの可能性あり
- データベースによっては、カーソルの宣言にも使われる
SQLを学んでWebエンジニアを目指そう
Webエンジニアは、Webサービスを作るエンジニアで非常に人気の高い職種です。
スタートアップやベンチャー企業が中心なので柔軟性のある雇用形態、魅力的な作業環境、面白いプロジェクト、高い報酬など非常に魅力的な求人が多いです。
Ruby on RailsやGo言語を用いたプロジェクトが多く、SQLも重要なスキルとなります。
このブログを運営するプログラミングスクールのポテパンキャンプでは、実践的なカリキュラムと現役エンジニアからのレビュー、そしてポートフォリオ添削や模擬面談などの面談転職サポートにより、最短距離でWebエンジニアを目指すことができます。
Webエンジニアへの転職を考えている方は、是非一度無料カウンセリングへお申込みください。
