SqlServer、Azure SQL Database、Azure Synapse 上でストアドプロシージャを作成する場合、Transact-SQL と呼ばれる言語を使用します。
今回は、Transact-SQLを使ったストアドプロシージャの書き方について、サンプルプログラムを交えて解説します。
Transact-SQLは、他のデータベースと比べて少し特殊な書き方をするため、OracleやPostgreSqlの経験者にとっても役立つ記事になっています。
これから Transact-SQL を始めようという方は、是非ご一読ください。
ストアドプロシージャの概要
ストアドプロシージャとは、あらかじめデータベース内に登録しておき、必要に応じて呼び出して使うプログラムの事です。
通常は、アプリケーションからデータベースに対してSQLを実行する場合、実行したいSQLの数だけデータベースとの通信が発生します。
実行したいSQLをあらかじめデータベースに登録しておき、アプリケーションから呼び出すことで、データベースとの通信が１回で済みます。
数件程度のSQL実行であれば問題ありませんが、数千～数万件のSQLを発行するような場合には威力を発揮します。
また、ストアドプロシージャのメリットはこれ以外にも、
- 複雑な処理を共通化／部品化できる
- あらかじめコンパイルされるため、処理が速くなる
などのメリットもあります。
ストアドプロシージャの種類
ストアドプロシージャには、「結果を返す」ものと、「結果を返さない」ものが存在します。また、「結果を返す」場合において、「複数の結果を返すもの」と「単一の結果を返すもの」が存在します。
「結果を返すもの」はストアドプロファンクション（stored function）と呼んでおり、「結果を返さない」ものはストアドプロシージャ（stored procedure）と呼んで区別します。
最初に知っておきたい記述上のルール
SQL Server や Azure SQL Databaseでストアドプロシージャ（以降はプロシージャと呼びます）を作成する場合、Transact-SQL と呼ばれる文法を使用します。
Transact-SQL は、Oracle や PostgreSQLなど、他のリレーショナルデータベースとは少し違った書き方をするため、最初にポイントを整理しておきます。
- 引数、変数は 必ず@から始める
- 変数に配列は使えない
- ループ処理はWHILEのみで、FOR文は存在しない
- プロシージャから任意の値を返すことが出来る
- ファンクションの中にSQLは記述できない
- 行末はセミコロンをつけることが推奨されているが、無くても動く
- カラム名に特殊文字や予約語を含む場合、[ ] でくくる
上記のことを軽く念頭に置いて、以降の記事を読み進めてください。
事前準備
本記事では次のテーブルがあると仮定して説明しています。掲載しているソースコードの動作検証を行う場合、あらかじめ下記のSQLを実行し、テーブルを作成しておいて下さい。
CREATE TABLE Sales(
StoreNumber INT NOT NULL, -- 店舗番号
SalesDateTime DATETIME NOT NULL, -- 売上日時
ProductID INT NOT NULL, -- 商品ID
ProductName NVARCHAR(100), -- 商品名
Quantity INT NOT NULL, -- 個数
UnitPrice DECIMAL(10,2) NOT NULL, -- 単価
CONSTRAINT PK_Sales PRIMARY KEY (StoreNumber, SalesDateTime, ProductID)
);
INSERT INTO Sales VALUES (1000,'2023-04-01 12:10:00',10001,'ショートケーキ',10,300);
INSERT INTO Sales VALUES (1000,'2023-04-01 12:20:00',10002,'モンブラン',20,400);
INSERT INTO Sales VALUES (1000,'2023-04-01 12:30:00',10003,'ショコラケーキ',30,500);
INSERT INTO Sales VALUES (1000,'2023-04-02 12:10:00',10001,'ショートケーキ',10,300);
INSERT INTO Sales VALUES (1000,'2023-04-02 12:20:00',10002,'モンブラン',20,400);
INSERT INTO Sales VALUES (1000,'2023-04-02 12:30:00',10003,'ショコラケーキ',30,500);
INSERT INTO Sales VALUES (2000,'2023-04-01 12:10:00',10001,'ショートケーキ',15,300);
INSERT INTO Sales VALUES (2000,'2023-04-01 12:20:00',10002,'モンブラン',25,400);
INSERT INTO Sales VALUES (2000,'2023-04-01 12:30:00',10003,'ショコラケーキ',35,500);
INSERT INTO Sales VALUES (2000,'2023-04-02 12:10:00',10001,'ショートケーキ',15,300);
INSERT INTO Sales VALUES (2000,'2023-04-02 12:20:00',10002,'モンブラン',25,400);
INSERT INTO Sales VALUES (2000,'2023-04-02 12:30:00',10003,'ショコラケーキ',35,500);プロシージャの書き方
最初に、本記事の注意事項について述べさせていただきます。
IT業界においては、ストアドプロシージャの呼称が人によって異なり、ストアド、ストプロ、プロシージャなどの呼び方をします。
本記事ではプロシージャという呼称で統一していますが、もし上記の記述があった場合は全て同じものであるとご理解ください。
では、Tracsact-SQLの基本構文について解説したいと思います。
値を返さない場合
値を返さないプロシージャの基本フォーマットは次の通りです。下記の例では引数を２つ指定していますが、引数が不要な場合はもちろん省略が可能です。
ここで他のデータベースと異なる点について簡単に触れておきたいと思います。
- 引数名、変数名は必ず＠から始める必要があります。
- OracleやPostgreSQLでは引数部分は()で括る必要がありましたが、Transact-SQLの場合は不要です。逆に付けるとエラーになります。
- プロシージャ内でSELECT文で値を取得し、変数に格納して使うケースが多々ありますが、Transact-SQLの場合はSELECT内で直接変数に値を代入することが可能です。
OracleやPostgreSQLでは、SELECTの値を取得する場合に INTO 変数1,・・・ を記述する必要があったので、Transact-SQLにおけるこの記述は、他のデータベースから比べると斬新です。
ちなみに、引数にはデフォルト値を指定することが可能で、デフォルト値を指定した引数は省略可能になります。
以上の内容を踏まえたサンプルは次のようになります。
CREATE PROCEDURE dbo.MyProcedure
-- 引数の定義
@ProductID INT,
@StoreNumber INT = 1000
AS
BEGIN
--変数の定義
DECLARE @avg DECIMAL(10,2);
--処理（指定した店舗番号における全商品単価の平均を求める）
SELECT @avg = AVG(UnitPrice)
FROM Sales
WHERE StoreNumber = @StoreNumber;
--指定した店舗番号、商品IDの単価を上記で求めた平均値の2倍で書き換える
UPDATE Sales
SET UnitPrice = @avg * 2
WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;
ENDプロシージャの実行方法
プロシージャの実行は EXEC を使います。具体的にはEXECの後にスキーマ名.ストアド名を指定します。
この時、引数の渡し方には２通りがあり、１つはプロシージャの引数の定義順に値を渡す方法、もう１つは「引数名＝値」の形式で指定する方法です。
「引数名＝値」の方法であれば、引数の順番は定義順である必要はありません。
--引数を全て指定する場合
EXEC dbo.MyProcedure 1000,10002
--EXEC dbo.MyProcedure @StoreNumber = 1000,@ProductID = 10002
--第2引数を省略する場合
EXEC dbo.MyProcedure 1000
--EXEC dbo.MyProcedure @ProductID = 10002
任意の値を返したい場合
プロシージャから任意の値を返す場合は、受取り専用の引数を定義して使います。
Oracle や PostgreSQL ではストアドファンクションを作成し、その戻り値を受けとりますが、SQL Serverの場合は戻り値ではなく引数を使うところが大きな違いです。
具体的には、プロシージャの引数として、値を受け取る変数を引数に指定し、プロシージャ内でその変数に値を代入します。その際、値を受け取る変数であることを明示するため、変数名の後にOUT句（OUT又はOUTPUT）を記述します。
そして、プロシージャの中からOUT句を指定した変数（＝OUT変数）に返したい値を代入します。この時、代入の仕方にはSETを使う方法とSELECTを使う方法の２通りがあります。
方法１．SET文を使って値を返す
下記はSETを使って呼び出し元に値を返す場合の書き方です。OUTの引数それぞれにSETを使って値を代入しています。
CREATE PROCEDURE dbo.MyProcedure
-- 引数の定義
@ProductName nvarchar(100),
@StoreNumber INT,
@Res1 DECIMAL(10,2) OUT
@Res2 DECIMAL(10,2) OUT
AS
BEGIN
--変数の定義
DECLARE @count INT;
DECLARE @sum DECIMAL(10,2);
--処理
SELECT @count = SUM(Quantity),
@sum = SUM(UnitPrice * Quantity)
FROM Sales
WHERE StoreNumber = @StoreNumber AND ProductName = @ProductName;
-- 引数に値を返す
SET @Res1 = @sum;
SET @Res2 = @sum * 2;
ENDSELECT で返す値が１個の場合は、変数に直接代入できます。引数で値を受け取るためには、変数の後に OUT を記述します。
方法２．SELECT文を使って値を返す
下記はSELECTを使って呼び出し元に値を返す場合の書き方です。引数で指定したOUT変数には、SELECTで指定した順番に代入されていきます。
CREATE PROCEDURE dbo.MyProcedure
-- 引数の定義
@ProductName nvarchar(100),
@StoreNumber INT,
@Res1 DECIMAL(10,2) OUT
@Res2 DECIMAL(10,2) OUT
AS
BEGIN
--変数の定義
DECLARE @count INT;
DECLARE @sum DECIMAL(10,2);
--処理
SELECT @count = SUM(Quantity),
@sum = SUM(UnitPrice * Quantity)
FROM Sales
WHERE StoreNumber = @StoreNumber AND ProductName = @ProductName;
-- 引数に値を返す
SELECT @sum,@sum * 2
END任意の値を返すプロシージャの実行方法と値の受け取り方
値を返すプロシージャであっても EXEC を使います。先ほどと異なるのは、受け取るためのOUT変数を記述する点です。
DECLARE @res1 DECIMAL(10,2);
DECLARE @res2 DECIMAL(10,2);
--EXEC dbo.MyProcedure 'ショートケーキ',1000,@res1 OUT,@res2 OUT
EXEC dbo.MyProcedure @ProductName='ショートケーキ', @StoreNumber=1000,@res1 OUT,@res2 OUT表形式で値を返したい場合
表形式とは、SELECT ＊ FROM テーブル名 で取得できる行列データのことです。これを使うことであたかも物理的なテーブルに対してSELECT文を実行したのと同じ結果が得られます。
下記のサンプルは、プロシージャの引数をOUT変数 で呼び出し元に返していた値を、表形式で返すよう修正したものです。
このサンプルではSELECT文が１回実行されるだけなので、２カラム１レコードのデータが返されることになります。
CREATE PROCEDURE dbo.MyProcedure
-- 引数の定義
@ProductName nvarchar(100),
@StoreNumber INT
AS
BEGIN
--変数の定義
DECLARE @count INT;
DECLARE @sum DECIMAL(10,2);
--処理
SELECT @count = SUM(Quantity),
@sum = SUM(UnitPrice * Quantity)
FROM Sales
WHERE StoreNumber = @StoreNumber AND ProductName = @ProductName;
-- 戻り値として値を返す
SELECT @count,@sum ;
END先ほど紹介したOUT変数を使った場合との相違点は以下の通りです。
- プロシージャの引数としてOUT変数は記述しない
- SELECTを連続実行することで、複数のレコードとして返すことが出来る（SELECT文と同じ）
表形式で値を返すプロシージャの実行方法
実行方法も今までのプロシージャと同じですが、値の受け取り方が異なります。
EXEC dbo.MyProcedure 'ショートケーキ',1000
表形式で返された値の受け取り方
表形式の場合、 TABLE型と呼ばれるデータタイプ（データ型）を作成し、 INSERT INTO 変数名～ と記述する必要があります。
以下の通り CREATE TYPE で TABLE 型の変数を定義し、INSERT INTO 変数名 EXEC ストアド名 ～ を実行することで、戻り値を変数に格納できます。
TABLE 型 は名前が示す通り表形式のデータを保持するデータ型です。SELECTで2個の値を指定するということは、「２個のカラムを持つテーブルから、1行だけ値が返ってきた」という扱いになります。
CREATE TYPE SALES_TYPE AS TABLE(
SalesCount INT,
Total DECIMAL(10,2)
);
DECLARE @sales SALES_TYPE;
INSERT INTO @sales EXEC dbo.MyProcedure 'ショートケーキ',1000では、複数レコードを受け取りたい場合はどうすればよいでしょう？
次は、SELECTを使ってテーブル丸ごと返すプロシージャを考えてみます。
例えば、複数のテーブルを結合し、かつ任意の項目を加工してテーブル形式で返す場合は、次の様に記述できます。
CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
SELECT '[' + t1.name+ ']' AS name,t1.age,t1.address FROM MY_DATA t1
LEFT JOIN MY_MASTER t2 ON t1.id = t2.id;
ENDプロシージャの中でCreate Tableにより一時テーブルを作成すれば、 SELECT 文でその中身を丸ごと返すことも可能です。
一時テーブルの先頭に # を付けていますが、これを付けるとセッション内でのみ有効な（セッションが終わると自動で削除される）一時テーブルを作成することができます。
CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
-- 一時テーブルの作成
CREATE TABLE #Temp(
name NVARCHAR(50),
age INT,
address NVARCHAR(200)
)
-- 一時テーブルにデータを登録
INSERT INTO #Temp
SELECT '[' + t1.name+ ']' AS name,t1.age,t1.address FROM MY_DATA t1
LEFT JOIN MY_MASTER t2 ON t1.id = t2.id;
WHERE t1.id < 10000
-- 一時テーブルを削除
DROP TABLE #Temp
END通常はBIGIN～ENDで明示的なトランザクション制御が掛かるので、DROP TABLE ＃TEMPをわざわざ記述しなくても勝手に消えてくれますが、念のため最後に実行しています。
理由は、＃TEMPはセッション中はずっと存在し続けるため、別のストアド内で同じ名前（＃TEMP）でCREATEするとエラーになるからです。単純なテーブル名で作成する場合は、使い終わった時点でDROPしておく方が安心です。
RETURNで整数値を返す
OracleやPostgreSQLのプロシージャでは、RETURN文を使って呼び出し元に値を返すことができません。
一方、SQLServer（Transact-SQL）では値を返すことが可能です。
ただし、返せる値は整数値が1個だけです。
浮動小数点を記述することは可能ですが、INT型にキャストされます。
この方法は、プロシージャの実行結果（成功、失敗、エラーコードなど）を呼び出し側に返したいときによく利用されます。
CREATE PROCEDURE dbo.MyProcedure
-- 引数の定義
@ProductID INT,
@StoreNumber INT
AS
BEGIN
--処理
--指定した店舗番号、商品IDの単価を0で書き換える
UPDATE Sales
SET UnitPrice = 0
WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;
RETURN 1
END呼び出し方は次の通りです。
DECLARE @status INT;
EXEC @status = dbo.MyProcedure 1000,10002
--EXEC @status = dbo.MyProcedure @StoreNumber = 1000,@ProductID = 10002SELECT の直後に RETURN を記述した場合、SELECTの値が優先されます。
RETURNの後にSELECTを記述した場合、RETURNの値が返され、SELECTは実行されません。
制御構文
ここからは、制御構文について解説していきます。
条件分岐
条件分岐は次のように記述します。
下記は引数で渡された商品IDの値によって、文字列をPRINTするサンプルです。
CREATE PROCEDURE MyProcedure
@productID INT
AS
BEGIN
IF @productID = 10001
BEGIN
PRINT '売れ筋'
END
ELSE IF @productID = 10002
BEGIN
PRINT '格安'
END
ELSE IF @productID = 10003
BEGIN
PRINT '絶品'
END
ELSE
BEGIN
PRINT 'その他'
END
END実行すると次の様になります。
EXEC dbo.MyProcedure 10001
ループ処理
FOR文が用意されていないため全てのループはWHILEを使うことになります。
下記は引数で指定された件数だけループして、SalesテーブルにダミーデータをINSERTするサンプルです。
CREATE PROCEDURE dbo.MyProcedure
@Count INT
AS
BEGIN
DECLARE @counter INT = 1
DECLARE @storeId INT = 3000
WHILE @counter <= @Count
BEGIN
-- ダミーデータの生成
DECLARE @salesDateTime DATETIME = GETDATE()
DECLARE @productId INT = @counter
DECLARE @productName NVARCHAR(100) = 'DUMMY_' + CAST(@counter AS NVARCHAR(10))
DECLARE @quantity INT = 10
DECLARE @unitPrice DECIMAL(10, 2) = 100.00
-- データの挿入
INSERT INTO Sales
VALUES (@storeId, @salesDateTime, @productId, @productName, @quantity, @unitPrice)
-- カウンターと店舗IDの更新
SET @counter += 1
SET @storeId = @storeId + 1000
END
END実行した結果、次の様になります。
EXEC dbo.InsertDummySalesData 20
FETCH 処理
テーブル（TABLE型含む）から値を順番に取り出すには FETCH 文を使います。
事前にカーソル型の変数を用意し、そこにSELECT文を登録しておきます。そして、OPEN文でカーソルを開き、FETCH NEXT～でレコードを順番に取り出します。
@@FETCH_STATUS は取り出すレコードが無くなると0以外になるため、ループ条件に使います。＠が２つ連続しているのはTransact-SQL の予約変数だからです。
処理が終わったら、CLOSEでカーソルを閉じ、DEALLOCATEでカーソルが使っていたメモリを解放します。
下記はSalseテーブルから1レコードづつ取り出し、PRINT文でコンソールに出力するサンプルです。
CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
-- カーソルの定義
DECLARE @cursor CURSOR;
-- SELECT文の実行とカーソルの設定
SET @cursor = CURSOR FOR
SELECT StoreNumber, SalesDateTime, ProductID, ProductName, Quantity, UnitPrice
FROM Sales;
-- 変数の宣言
DECLARE @StoreNumber INT;
DECLARE @SalesDateTime DATETIME;
DECLARE @ProductID INT;
DECLARE @ProductName NVARCHAR(100);
DECLARE @Quantity INT;
DECLARE @UnitPrice DECIMAL(10, 2);
-- FETCHのループ
OPEN @cursor
FETCH NEXT FROM @cursor
INTO @StoreNumber, @SalesDateTime, @ProductID, @ProductName, @Quantity, @UnitPrice
WHILE @@FETCH_STATUS = 0
BEGIN
-- 取得したデータの処理
-- ここで取得したデータを使って何か処理を行うことができます
PRINT 'StoreNumber: ' + CONVERT(VARCHAR(10), @StoreNumber)
PRINT 'SalesDateTime: ' + CONVERT(VARCHAR(20), @SalesDateTime, 120)
PRINT 'ProductID: ' + CONVERT(VARCHAR(10), @ProductID)
PRINT 'ProductName: ' + ISNULL(@ProductName, '')
PRINT 'Quantity: ' + CONVERT(VARCHAR(10), @Quantity)
PRINT 'UnitPrice: ' + CONVERT(VARCHAR(20), @UnitPrice, 2)
PRINT '----------------------------------------'
-- 次の行をFETCH
FETCH NEXT FROM @cursor
INTO @StoreNumber, @SalesDateTime, @ProductID, @ProductName, @Quantity, @UnitPrice
END
-- カーソルをクローズして解放
CLOSE @cursor
DEALLOCATE @cursor
END実行結果は次のようになります。
EXEC dbo.MyProcedure
トランザクションと例外処理
例外を補足し、ロールバックを実行するには次のように記述します。
正常終了すると自動でコミットされますので、明示的にCOMMITを書く必要はありません。
XACT＿STATE() 関数はトランザクションの状態を取得する関数で、トランザクションが完了している場合は0が返されます。従って、0以外の場合、ロールバックするようにします。
以下は、例外処理とロールバックを使ったサンプルです。
CREATE PROCEDURE dbo.MyProcedure
-- 引数の定義
@ProductID INT,
@StoreNumber INT = 1000
AS
BEGIN
--変数の定義
DECLARE @avg DECIMAL(10,2);
--処理
--指定した店舗番号における全商品単価の平均を求める
SELECT @avg = AVG(UnitPrice)
FROM Sales
WHERE StoreNumber = @StoreNumber;
--指定した店舗番号、商品IDの単価を上記で求めた平均値の2倍で書き換える
BEGIN TRY
UPDATE Sales
SET UnitPrice = @avg * 2
WHERE StoreNumber = @StoreNumber AND ProductID = @ProductID;
END TRY
--例外処理
BEGIN CATCH
IF XACT_STATE() <> COMMITTED
BEGIN
ROLLBACK TRANSACTION;
RETURN 1;
END
END CATCH
RETURN 0;
END動的SQL
動的SQLとは、SQL文を文字列として定義したもので、EXEC文によりSQLとして実行できます。
EXCELはプロシージャを呼ぶときにも使いましたが、動的SQLを実行する場合は ( ) で括る必要があります。
下記は、引数で指定された、カンマ区切りのカラム名を使ってSalesテーブルから値を取得するサンプルです。
CREATE PROCEDURE dbo.MyProcedure
-- 引数の定義
@ProductID INT,
@condition NVARCHAR(MAX)
AS
BEGIN
--変数の定義
DECLARE @sql NVARCHAR(MAX) = 'SELECT ' + @condition + ' FROM SALES';
EXEC (@sql);
RETURN 0;
END結果は次の通りです。第2引数で指定された ProductID と ProductName のみが抽出されています。
EXEC dbo.MyProcedure 1000,'ProductID,ProductName'
今回の様に短い動的SQLであれば、EXECの中に直接書くことも可能です。
EXEC ('SELECT ' + @condition + ' FROM SALES');ファンクションについて
Transact-SQL でいうファンクションは、OracleやPostgreSQLなどで使われるファンクションと少々異なります。
一般的なデータベースでは、値を返さないものをプロシージャ、値を返すものをファンクションと呼んで区別しますが、値を返す部分以外は両者に違いはありません。
一方、Transact-SQL のプロシージャは、プロシージャとファンクションを併せ持った存在です。
では、Transact-SQL で用意されているプロシージャは何かというと、「単純な処理を関数化したもの」という位置づけで、処理にSQL文（SELECT、INSERT、DELETE、UPDATE、MERGE）が記述できません。
従って、下記のような短い処理を関数化する用途で使用します。
CREATE FUNCTION AddDaysToDate
(
@daysToAdd INT
)
RETURNS DATE
AS
BEGIN
DECLARE @resultDate DATE
-- 現在時刻に引数で指定された日数を加算
SET @resultDate = DATEADD(DAY, @daysToAdd, GETDATE())
RETURN @resultDate
END呼び出し方は、組み込み関数であるlen()やreplace()と同じように直接変数で受け取ったり、SELECTの中で使うことが可能です。
-- 変数で受ける
DECLARE @today DATETIME;
SET @today = dbo.AddDaysToDate(5)
--SELECTの中で使う
SELECT dbo.AddDaysToDate(5,0)
まとめ
今回はマイクロソフト系のデータベースエンジン（SQL Sever、SQL Database、Azure Synapse）で利用可能な Transact-SQL について、ストアドとファンクションの書き方について解説しました。
OracleやPostgreSQLなど、他のデータベースでは戻り値を返さないプロシージャと、戻り値を返すプロシージャが区別されており、後者はファンクションと呼ばれています。
一方、Transact-SQLのプロシージャは、プロシージャとファンクションの両方を兼ね備えた存在です。
そして、Transact-SQLで用意されているファンクションは、組み込み関数である len()やreplace()のような単一機能の自作関数を作るために用意されており、関数内部でSQL文が記述できません。
これ以外にも、FORが用意されていない、配列変数が使えない、変数や引数は必ず@から始まる、SELECTの中で直接イコールで変数に代入できるなど、他のデータベースとは少し異なる点が特徴です。
他のデータベース経験者であれば、最初は戸惑うかもしれませんが、慣れると結構便利で書きやすいと思います。
もしTransact-SQLで何か作る機会があれば、是非この記事を参考にしてください。
コメント