一時テーブル（２）

ITコーディネータのシュウです。

今回も、ITC関連のネタです。ITコーディネータ協会では、毎年この時期にITCカンファレンスというものを開催しており、今年が14回目です。今年は10月30日(金)～31日(土)で行われます。
今年の大会テーマは「IoT新時代におけるＩＴコーディネータのエボリューション～クラウド・Industrie4.0の可能性～」。ドイツが取り組んでいるIndustrie4.0（第4次産業革命）というものを取り上げつつ、日本版Industrie 4.0と言われる「つながる工場」の動きやITコーディネータ協会としての取り組みを紹介するということです。
HPは以下：
http://www.itc.or.jp/activity/seminar/itc_conf2015/index.html

近年、スマートフォンに代表されるようなコンピュータを内蔵した機器がインターネットに接続され、さらには様々なモノにセンサーなどを取り付け通信ができ、それらがつながる世界というような内容でIOT（Internet of Things）「モノのインターネット」という言葉をよく耳にするようになりました。スマートハウスやスマート家電などのスマート～という名前もよく使われますね。「つながる工場」、今、ものづくりの世界で大きな変革のときを迎えつつあるようです。それらを駆使することで、大きな飛躍のチャンスの可能性があるかも知れません。

＜本日の題材＞
一時テーブル(2)

前回、一時テーブルの例としてOracleの場合を取り上げましたので、今回はSQL Serverについて例を上げて確認してみたいと思います。

SQL Serverの場合のローカル一時テーブル作成の構文：

CREATE TABLE #表名(
列名データ型
, 列名データ型
, …
);

SQL Serverの場合のグローバル一時テーブル作成の構文：

CREATE TABLE ##表名(
列名データ型
, 列名データ型
, …
);

一時テーブルの使用例として、売上テーブルから顧客の初回購入日や購買回数などの購買履歴の情報を抽出して表示するような単純な処理を、ストアド・プロシージャで実行する簡単な例を作成してみます。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
@顧客ID         VARCHAR(8)
AS
BEGIN
-- 一時テーブルの作成
CREATE TABLE #顧客初回購入(
顧客ID                  VARCHAR(8),
初回購入日              DATETIME
);

CREATE TABLE #顧客購入履歴(
顧客ID                  VARCHAR(8),
累計購入回数    decimal(7),
累計購入金額    decimal(9)
);

-- 初回購入日の抽出結果を一時テーブルに登録
INSERT INTO #顧客初回購入
SELECT 顧客ID, MIN(出荷日)
FROM dbo.売上
WHERE 顧客ID = @顧客ID
GROUP BY 顧客ID;

-- 累計購入情報の抽出結果を一時テーブルに登録
INSERT INTO #顧客購入履歴
SELECT 顧客ID, COUNT(*), SUM(売上金額)
FROM dbo.売上
WHERE 顧客ID = @顧客ID
GROUP BY 顧客ID;

-- 顧客購買履歴情報の表示
SELECT
A.顧客ID
, FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
, B.累計購入回数
, B.累計購入金額
FROM #顧客初回購入 A
JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

Oracleとは違って、プロシージャの中で一時テーブルを作成し、それらをジョインして抽出結果を表示するサンプルになります。

上記のプロシージャに顧客IDのパラメータを渡して実行してみます。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
@顧客ID

別の顧客IDをパラメータで渡して、再度実行結果を確認すると、

DECLARE @顧客ID varchar(8)=' K0000054'
EXEC dbo.顧客購買履歴情報抽出
@顧客ID

さて、SQLServerの場合のグローバル一時テーブルについては、あまり使ってみたことがないですが、どういうときに使うかを調べてみたところ、排他処理で使うケースがあるようです。

悲観的排他制御を行いたいときに、アプリケーションでコントロールする場合には、最初に排他用のフラグを立てて、そのフラグが立っている場合は、他のユーザは更新などの処理が実施できないというようなことを行うことがあると思います。
ただ、このフラグの制御を実テーブルで行うと、予期せぬかたちで処理が強制的に終了してしまった場合に、フラグが立ったままになってしまい、排他の状態が続いてしまって更新ができなくなってしまうなどというケースが出てきてしまうため、何らかのかたちで解除する方法を検討しなければなりません。

このときに、このフラグの制御を実テーブルではなく、グローバル一時テーブルを作成するというかたちで行うと、強制的に終了した場合でも、グローバルテーブルが自動的に削除されることで排他状態ではないと容易に判断できるということになるということです。

簡単に試してみたいと思います。

★排他をかけたいとき

CREATE TABLE ##排他チェック(
USERID VARCHAR(20),
処理時刻 DATETIME
);

INSERT INTO ##排他チェック
SELECT '00001', GETDATE();