一時テーブル

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

ラグビーのワールドカップでは、日本が24年ぶりの勝利、それも世界ランク３位の南アフリカを倒すという歴史的大金星を上げて、世界中が驚いたということがありました。開催地イギリスの地元の新聞も、「UNBELIEVABLE」」という題で大きく日本の勝利を取り上げ、会場やその近辺では日本人というだけで、「素晴らしい試合だった。おめでとう！」という祝福を受けたということです。
本当にスポーツは素晴らしいですね。

さて、マイナンバー制度の施行もいよいよ間近に迫ってきました。会社などでもいろいろと教育を受けられた方も多いと思います。ITコーディネータ協会でも、ITコーディネータ協会が進めるマイナンバーへの取組みという内容でHPで取り上げています。
http://www.itc.or.jp/mynumber/
興味のある方は、是非ご確認ください。

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

ストアドプロシージャなどで複雑な処理を行うような場合に、1つのSQLで一度に結果を導き出すことは難しいため、第一段階としての抽出の結果を一時的に登録する一時テーブルを用意し、その結果を元に次のステップとしての必要な処理を行い、最終的に目的のデータを登録するとか、結果を抽出するということがよくあると思います。

この一時テーブルですが、Oracleの場合とSQL Serverの場合で多少使い方が異なります。
Oracleの場合は、セッション（デフォルトではトランザクション）が終了するとセッションの中で作成したデータは消えますが、テーブル自体は永続的なものであり、セッションが切れてもテーブルは残ります。そのため、プロシージャなどとは別に、あらかじめテーブルを作成しておく必要があります。
※デフォルト(ON COMMIT DELETE ROWS)ではトランザクションがコミットされると一時表のデータは削除されますが、設定によって(ON COMMIT PRESERVE ROWS)は、トランザクションが終了後もデータは残り、セッション終了時にデータが切り捨てられます。

これに対して、SQL Serverの場合、プロシージャなどの中で一時テーブル自体も作成し、データを登録して使うかたちで、セッションが終了するとテーブルも自動的に削除されます。
ただし、SQL Serverでは、ローカル一時テーブル（テーブル名が#で始まる）とグローバル一時テーブル（テーブル名が##で始まる）があり、一時テーブルを作成したセッションのみから参照できるローカル一時テーブルと、別セッションからも参照でき、全ての参照がなくなったときに自動的に削除されるグローバル一時テーブルという違いがあります。

Oracleの場合の一時表作成の構文：

CREATE GLOBAL TEMPORARY TABLE 表名(
列名データ型
, 列名データ型
, …
[ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]
);

一時テーブルの作成例：
CREATE GLOBAL TEMPORARY TABLE temp_sample1(
CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7)
);

以前、ブログで使用した商品マスタ、及び売上テーブルから、2014年11月に購入した商品と数量について、一時テーブルに登録します。

INSERT INTO temp_sample1
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
FROM 売上テーブル U
JOIN 商品マスタ S ON U.商品CD = S.商品CD
WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
GROUP BY U.商品CD, S.商品名;

そのままデータを確認します。

SELECT * FROM temp_sample1
ORDER BY CD;

このとき、別のユーザが異なるセッションでこの一時テーブルのデータを確認すると、

SELECT * FROM temp_sample1
ORDER BY CD;

一時テーブルのデータは、他のセッションからは参照できないことがわかります。

また、データを登録したほうのセッションで、COMMITを実行してからSELECTしてみます。

COMMIT;

SELECT * FROM temp_sample1
ORDER BY CD;

COMITすると、一時テーブルのデータは削除されることがわかります。

それでは、トランザクション終了後もデータが残る設定（ON COMMIT PRESERVE ROWS）の設定で一時テーブルを作成してみます。

CREATE GLOBAL TEMPORARY TABLE temp_sample2(
CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7))
ON COMMIT PRESERVE ROWS
;

同様にデータを登録し、COMMITします。

INSERT INTO temp_sample2
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
FROM 売上テーブル U
JOIN 商品マスタ S ON U.商品CD = S.商品CD
WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
GROUP BY U.商品CD, S.商品名;

COMMIT;