SQLのdeclareについてまとめています。
目次
SQL declareの構文
declareはDBMSによって扱いが異なります。多くのDBMSではローカル変数の宣言用に使われますが、Oracleなどでは無名ブロックの開始を表します。
例えば、MySQLの変数の宣言は、ストアドプロシジャやストアドファンクションの定義内で、「decare 変数名 データ型」 のように記述します。以下は、MySQLでストアドプロシジャtestprocを定義し、実行した例です。
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)
【関連記事】
▶SQLのdeclareは、ストアドプロシジャ内のローカル変数宣言をおこなう
なお、MySQLの通常のSQL内ではdeclareなしで変数名の1文字目を@にすることで、変数として使用可能です。データ型の宣言も不要で、代入する値によって自動的に型が決まるようですね。変数を使った演算も可能です。
mysql> set @a="abcde"; Query OK, 0 rows affected (0.00 sec) mysql> select @a; +-------+ | @a | +-------+ | abcde | +-------+ 1 row in set (0.00 sec) mysql> set @b=100; Query OK, 0 rows affected (0.00 sec) mysql> select @a; +-------+ | @a | +-------+ | abcde | +-------+ 1 row in set (0.00 sec) mysql> select @b+100; +--------+ | @b+100 | +--------+ | 200 | +--------+ 1 row in set (0.00 sec)
SQL declareの使い方
OracleやPostgerSQLのPL/SQLの無名ブロックにdeclareを使う
PL/SQLの無名ブロックは以下のように記述します。無名ブロックとは、登録されずに実行されるプロシジャのようなものと考えてよいでしょう。declareの部分にはブロック内で使用する変数を宣言するんですね。
DECLARE <宣言部分> BEGIN <実行部分> EXCEPTION <例外処理部分> END; /
なお、ブロック内で特に変数を使用しない場合は、declareを省略することが可能です。
具体的には以下のような記述になります。以下は、１から10までループして、ループ変数が奇数か偶数化で判定してinsert処理を行うOracle用のサンプルです。
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 BEGINの使い方 トランザクション記述時にデリミタの置き換えが必要なケース
SQL ServerのTransact-SQLのローカル変数宣言にdeclareを使用
SQL ServerのT-SQLでは、ローカル変数の宣言にdeclareを使用します。変数名の1文字目は@とする必要があります。
-- Create the table.
CREATE TABLE TestTable (cola INT, colb CHAR(3));
GO
SET NOCOUNT ON;
GO
-- Declare the variable to be used.
DECLARE @MyCounter INT;
-- Initialize the variable.
SET @MyCounter = 0;
-- Test the variable to see if the loop is finished.
WHILE (@MyCounter < 26)
BEGIN;
-- Insert a row into the table.
INSERT INTO TestTable VALUES
-- Use the variable to provide the integer value
-- for cola. Also use it to generate a unique letter
-- for each row. Use the ASCII function to get the
-- integer value of 'a'. Add @MyCounter. Use CHAR to
-- convert the sum back to the character @MyCounter
-- characters after 'a'.
(@MyCounter,
CHAR( ( @MyCounter + ASCII('a') ) )
);
-- Increment the variable to count this iteration
-- of the loop.
SET @MyCounter = @MyCounter + 1;
END;
GO
SET NOCOUNT OFF;
GO
-- View the data.
SELECT cola, colb
FROM TestTable;
GO
DROP TABLE TestTable;
GO
参考）変数 (Transact-SQL) – SQL Server | Microsoft Docs
以下の宣言部分で、@MyCounterというINT型のローカル変数を定義しているんですね。
DECLARE @MyCounter INT;
宣言後のローカル変数にはSETで値を代入したり、四則演算の結果や関数の返り値をセットすることが可能です。
SET @MyCounter = 0;
また、カンマ区切りで記述することで、複数のローカル変数を一度に宣言することが可能です。
DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);
まとめ
- declareはプロシジャ内の変数宣言をおこなうが、DBMSにより扱いが異なる
- MySQLではdeclare宣言しなくても、通常SQL内でローカル変数の使用が可能
- Oracleでは、PL/SQLの無名ブロックの変数宣言にdeclareを使用する
