一時テーブル（２）

2015年9月29日ＩＴＣシュウちゃんコメントをどうぞ

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();

同一のセッションで、このグローバル一時テーブルの中身を確認してみます。

SELECT * FROM ##排他チェック;

これを、全く別のセッションで異なるUSERIDのメンバーが抽出してみます。

SELECT * FROM ##排他チェック;

ローカル一時テーブルとは異なり、他のセッションからもデータを確認できます。

ここで、元のグローバル一時テーブルを作成したセッションの接続を解除、もしくは実行していたウィンドウの右上の×の箇所をクリックして閉じてから、再度先ほど確認できた別のセッションからデータを確認してみます。

グローバル一時テーブルが接続の解除とともに自動的に削除されたために、データの抽出ができないことが確認できます。

非常に簡単なテストではありますが、このグローバル一時テーブルの存在チェックを行って、存在していれば排他中のため更新不可であり、存在しなければ排他中ではないため、処理を実行してよいという制御をある程度行うことができそうです。

例えば
IF OBJECT_ID(N'tempdb..##排他チェック', N'U') IS NOT NULL
print '他のユーザが使用中のため、実行できません'
ELSE
print '処理を実行します'

上記の処理を、先ほどと同様のタイミングで実行すると、最初に他のセッションでグローバル一時テーブルを作成したタイミングでは、

グローバル一時テーブルを作成したセッションの接続を解除した状態では

実際には、後から接続して処理を実行する側でも、処理の開始時点で同様に排他用のグローバル一時テーブルを作成するという処理を行うかたちになると思います。

今回は、SQL Serverについて、ローカル一時テーブルだけでなく、グローバル一時テーブルについても試してみました。

今日は以上まで

にほんブログ村

一時テーブル

2015年9月23日ＩＴＣシュウちゃんコメントをどうぞ

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;

データを確認してみます。

SELECT * FROM temp_sample2
ORDER BY CD;

今度は、COMMITしてもデータは消えず、SELECTで確認することができました。
以上、まずOracleについての一時テーブルの例について取り上げてみました。

今日は以上まで

にほんブログ村

SEQUENCE、IDENTITY

IDENTITY列

2015年8月26日ＩＴＣシュウちゃんコメントをどうぞ

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

社長がアメリカのサンフランシスコにあるニューコムの支社に行ったついでに、シリコンバレーに寄った際に撮ってきたオラクル社の写真です。誰かが言ってましたが、丸いビルと模様はハードディスクを意味しているとか！　本当ですかね？　ただ、たしかにシリンダーみたいですよね。
グーグルやアップル、インテル、ヒューレットパッカード、シスコシステムズ、ヤフー、アドビ、イーベイなど、シリコンバレーにある著名な企業を上げればきりがないほどですが、シリコンバレーっていうのは、実際にシリコンバレーという都市が存在しているわけではなく、サンノゼ、マウンテンビュー、サニーベール、サンタクララといった複数の市からなる場所だそうです。

＜本日の題材＞
IDENTITY列

前回、SEQUENCEについて取り上げましたが、今回はSQL Serverではよく使用されていたIDENTITYプロパティが、Oracle 12cから使用できるようになったという件を取り上げてみたいと思います。

SQL ServerのIDENTITY プロパティというのは、Accessの「オートナンバー型」のように、ある列の値を自動的に連番が設定されるようにしたい場合に使用します。

前回取り上げたシーケンスが、特定のテーブルとは関連づかない独立したオブジェクトであるのに対して、IDENTITYのほうは、特定のテーブルの列に関連づく機能というところが違います。

★例）SQL Serverの場合：

CREATE TABLE dbo.受注TBL(
受注番号 INT IDENTITY(1,1)
,受注日 DATE
,顧客番号 VARCHAR(10)
,決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

上記は、受注番号に IDENTITYプロパティを設定し、最初の値を「1」、増分値を「1」としています。

データを登録する場合は、以下のように、IDENTITYプロパティを設定した項目を除いて設定します。

INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
VALUES('2015-08-24','K0001051',34000);

INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
VALUES('2015-08-24','K0001078',42000);

結果を確認してみます。
SELECT * FROM dbo.受注TBL;

たしかに、受注番号は「1」から順に作成されています。

INSERT文にこのIDENTITYプロパティを設定した項目についても含めてしまうと、下記のようにエラーとなります。

INSERT INTO dbo.受注TBL(受注番号,受注日,顧客番号,決済金額)
VALUES(3, '2015-08-24','K0001078',42000);

このIDENTITYプロパティを設定した項目に対して、どうしても自動での登録ではなく、明示的に設定して登録したい場合には、「SET IDENTITY_INSERT テーブル名 ON」という設定をした後で行う必要があります。

SET IDENTITY_INSERT dbo.受注TBL ON;
INSERT INTO dbo.受注TBL(受注番号,受注日,顧客番号,決済金額)
VALUES(10, '2015-08-24','K0001083',17000);

結果を確認してみます。
SELECT * FROM dbo.受注TBL;

再度設定を元に戻して、登録を行います。

SET IDENTITY_INSERT dbo.受注TBL OFF;
INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
values('2015-08-24','K0001091',56000);

結果を確認すると、

先ほどの受注番号の最大値「10」の後の連番で「11」の行が登録されているのが確認できます。

IDENTITY列というのは上記のようなかたちで使いますが、Oracleでは11gまではサポートされていませんでしたが、12c でこのIDENTITY列がサポートされるようになりました。

★例）Oracle 12c の場合

IDENTITYを設定したい列について、ユーザーが更新することが不可能な列として作成する場合、「GENERATED ALWAYS AS IDENTITY」をつけます。

CREATE TABLE 受注TBL(
受注番号 INT GENERATED ALWAYS AS IDENTITY
, 受注日 DATE
, 顧客番号 VARCHAR(10)
, 決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

SQL Serverのときと同様に、データを登録すると、

結果を確認すると、
SELECT * FROM 受注TBL;

受注番号は、1からの連番で作成されているのが確認できます。

ここで、受注番号を更新してみます。
UPDATE 受注TBL SET
受注番号 = 100
WHERE 受注番号 = 1;

上記のように、更新できない旨のエラーが表示されます。

次に、上記の処理をロールバックします。
ROLLBACK;

]

再度登録します。

結果を確認します。
SELECT * FROM 受注TBL;

そうすると、受注番号は「3」から始まっていて、先ほどのロールバックの処理ではこのIDENTITYの値はロールバックされないことが確認できます。（シーケンスと同様）

次に、このIDENTITYの列の値を更新可能な列として作成する場合には、以下のように「GENERATED BY DEFAULT AS IDENTITY」をつけます。また、連番の最初の値を「100」とします。

CREATE TABLE 受注TBL(
受注番号 INT GENERATED BY DEFAULT AS IDENTITY(START WITH 100)
, 受注日 DATE
, 顧客番号 VARCHAR(10)
, 決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

再度、データを登録します。

データを確認します。

今度は、受注番号は、100からの連番で作成されているのが確認できます。
受注番号を更新してみます。

UPDATE 受注TBL SET
受注番号 = 200
WHERE 受注番号 = 100;

今回は、更新できることが確認できました。
再度データを確認します。

Oracleの場合、このIDENTITY列の実体は、SEQUENCEオブジェクトとDEFAULTの組合せとのことです。「USER_OBJECTS」の内容を確認してみます。

SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE
FROM USER_OBJECTS;

SEQUENCEが作成されているのが確認できます。

今日は以上まで

にほんブログ村

SEQUENCE、IDENTITY

SEQUENCE（SQL Server）

2015年7月27日ＩＴＣシュウちゃんコメントをどうぞ

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

いよいよ夏本番という感じになってきました。
本当に暑い日が続きますね。
そういえば、高校野球の各都道府県大会も、いよいよ佳境に入り、各地で夏の甲子園の代表校が決まってきていますが、うちの子の通っている高校も、今回は上のほうまで勝ち上がっていっているので、とっても注目していました。
暑い中、一生懸命な姿に感動しますね。なかなか忙しくて球場まで応援に行けないですが、是非、どの学校も頑張ってほしいと思います。

＜本日の題材＞
SEQUENCE（SQL Server）

前回、ORACLEでのSEQUENCE（順序）について取り上げましたが、今回は、SQL Serverの環境で、SEQUENCE（シーケンス）について確認してみます。

SQL Server(2012以降)でSEQUENCEを作成する構文は、ORACLEとほぼ同じで以下のようになります。

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH value ]
[ INCREMENT BY value ]
[ { MINVALUE value } | { NO MINVALUE } ]
[ { MAXVALUE value } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE value } | { NO CACHE } ]

前回のORACLEのときと同様、1から始まり、増分値が1、特にMAX値などは設定しない「order_sequence」という順序を作成したい場合には、以下のように作成します。

CREATE SEQUENCE dbo.order_sequence
START WITH 1
INCREMENT BY 1;

次に、ORACLEの例と同様に、値を小さくしていくSEQUENCEを作成してみます。下記は、値を小さくしていき、最小値になったら元の最大値に戻す場合の例：

CREATE SEQUENCE dbo.minus_sequence
START WITH 100000
INCREMENT BY -1
MAXVALUE 100000
MINVALUE 1
CYCLE;

SQL Server 2014 Management Studio で確認すると、シーケンスが作成されているのが確認できます。

次に、実際の使い方ですが、ORACLEではNEXTVALというものを使っていましたが、SQL Serverでは NEXT VALUE FOR 関数を使用して、次の値を取得します。

例）受注情報を登録する「受注」テーブルに、order_sequence を利用して、次の受注番号を取得して登録します。

INSERT INTO dbo.受注(受注番号,受注日,顧客番号)
VALUES(NEXT VALUE FOR order_sequence, CONVERT(VARCHAR, getdate(), 111), 'K0001037');

先ほど登録した受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
FROM 受注
WHERE 顧客番号 = 'K0001037';

もう1件登録してみます。

INSERT INTO dbo.受注(受注番号,受注日,顧客番号)
VALUES(NEXT VALUE FOR order_sequence, CONVERT(VARCHAR, getdate(), 111), 'K0001038');

受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
FROM 受注
WHERE 顧客番号 = 'K0001038';

SQL Serverでは、現在のorder_sequenceの値は、SQL Server Management Studioで確認できます。
現在値を見たいシーケンスorder_sequenceにカーソルを置いて、右ボタンでのプロパティを表示すると、

現在値が2になっていることが確認できます。

もうひとつの例として、Microsoft社のホームページに上がっていたものを参考に、テーブルの項目のデフォルト値にSEQUENCEのNEXT VALUE FOR関数を使うものを上げてみます。

まず、イベントのカウンター用のSEQUENCEを作成します。

CREATE SEQUENCE dbo.EventCounter
AS int
START WITH 1
INCREMENT BY 1 ;

次に、上記のSEQUENCEをイベントIDという項目のデフォルト値に利用したテーブルを作成します。

CREATE TABLE dbo.EventLog(
イベントID int DEFAULT (NEXT VALUE FOR EventCounter)
,イベント発生時刻 datetime NOT NULL DEFAULT (getdate())
,エラーCD varchar(20)
,イベント内容 nvarchar(256)
, CONSTRAINT PK_EventLog PRIMARY KEY (イベントID)
);

このEventLog というテーブルにデータを登録します。

INSERT dbo.EventLog(エラーCD, イベント内容)
VALUES ('EC_QCTL_20', 'SQL実行時にエラーが発生しました。') ;

INSERT dbo.EventLog(エラーCD, イベント内容)
VALUES ('EC_QSEL_2', 'クエリーの出力開始セルが正しく取得できませんでした。') ;

登録したデータを確認してみます。

SELECT * FROM dbo.EventLog
ORDER BY イベントID;

項目：イベントIDには、EventCounterシーケンスで取得された、1から1つずつ増えていく番号が設定されていることが確認できました。

今日は以上まで

にほんブログ村

SEQUENCE、IDENTITY

SEQUENCE（ORACLEの順序）

2015年7月23日ＩＴＣシュウちゃんコメントをどうぞ

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

梅雨が明けて、めちゃめちゃ暑い日が続いていますね～。
外での活動は本当に大変だと思います。

さて、上記の写真については知人の方に頂いた、アメリカでの写真シリーズの最後で、「セドナ」と呼ばれる場所です。グランドキャニオンと同じアリゾナ州の中北部で、ボルテックスと呼ばれるパワースポットがあることでも有名であり、「スピリチュアル」な場所、"癒しの町"として人気があるそうです。写真はベルのかたちをした「ベルロック」という岩山です。他にも「カセドラルロック」、「コーヒーポットロック」、「スヌーピーロック」などの愛称で呼ばれる岩山があるそうです。どんな形をしているのかな？　興味がわきますね。それにパワースポットか、日々の仕事で疲れている私も癒されたい～。（行ってみたいな．．．）

＜本日の題材＞
SEQUENCE（ORACLEの順序）

今回は、SEQUENCE（シーケンス）という、複数のユーザーが一意の整数を生成するときに使用できるデータベース・オブジェクト（ORACLEでは順序という）について取り上げてみたいと思います。システムを開発する際に、受注用テーブルの受注番号など、一意に順に採番していく番号を主キーとするようなテーブルを設計することはよくあると思います。そのときに、キーの値が重複せずに簡単に採番していくことを可能にするものとして、使用されることが多いのではないかと思います。
今回は、ORACLEの環境で確認していきます。

SEQUENCEを作成する構文としては、

CREATE SEQUENCE sequence_name
[START WITH value]
[INCREMENT BY value]
[MAXVALUE value]
[MINVALUE value]
[NOCYCLE | CYCLE]
[CACHE value | NOCACHE ]

仮に、1から始まり、増分値が1、特にMAX値などは設定しない「order_sequence」という順序を作成したい場合には、以下のようにして作成します。

CREATE SEQUENCE order_sequence
START WITH 1
INCREMENT BY 1;

※順序を作成するためには、CREATE SEQUENCE権限が必要です。また、値を小さくしていくことも可能で、そのときは、INCREMENT BY の値をマイナスにします。
値を大きく、または小さくしていく場合に、最大値や最小値を指定して、それを超えるとまた最小値や最大値に戻すようにする場合には、MAXVALUE、MINVALUEの値を設定し、かつ、CYCLE という指定を行うことになります。

下記は、値を小さくしていき、最小値になったら元の最大値に戻す場合の例：

次に、実際の使い方ですが、INSERT文のVALUES句、UPDATE文のSET句などの中で、NEXTVALというものを使って次の値を取得します。

例）受注情報を登録する「受注」テーブルに、順序を利用して、次の受注番号を取得して登録します。

INSERT INTO 受注(受注番号, 受注日, 顧客番号) VALUES(order_sequence.NEXTVAL, TO_DATE(sysdate), ‘K0001037’);

受注番号はSEQUENCEを使って、データが登録されました。この登録した受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
FROM 受注
WHERE 顧客番号 = 'K0001037';

現在のorder_sequenceの値をCURRVALを使用して確認します。
SELECT order_sequence.CURRVAL FROM dual;

たしかに、先ほどNEXTVALを使用して登録した受注番号と、CURRVALを使用して確認したシーケンスの番号が一致していることが確認できました。

次に、注意点ですが、sqlplusをもう一つ立ち上げて、先ほどとは別のセッションでログインしてみます。この後、現在のシーケンスの値を確認しようと思い、CURRVALを使用して確認してみます。

先ほど別のセッションでは、CURRVALで「7」という値が返ってきたので、こちらでも同じ用に結果が帰って来ると思いきや、「ORA-08002：順序ORDER_SEQUENCE.CURRVALはこのセッションではまだ定義されていません」というエラーが返ってきました。

ここで、エラーメッセージをよく見ると、「このセッションでは」という記述がありますように、CURRVALというのはそのセッションの中で最後に生成した順序の値を表示するものであるため、まだこのセッションの中で一度もその順序を生成していない場合はエラーになってしまうということです。
この状態で、NEXTVALを使って順序を生成してみると、

SELECT order_sequence.NEXTVAL FROM dual;

CURRVALではエラーになった新しいセッションでも、NEXTVALでの順序の生成は可能であることがわかります。

ここで、再度、先ほどエラーになったCURRVALで現在の順序の値を取得してみると、

今度は確認できました。

それでは、ということで、もう一つ立ち上げていた元のsqlplusのほうで、現在の順序の値を確認してみると、

SELECT order_sequence.CURRVAL FROM dual;

先ほど作成した、もう片方での最終の値「8」ではなく、そのセッション内で最後に生成した順序の値「7」が返ってくることが注意点です。

この状態で、受注データを作成してみると、

INSERT INTO 受注(受注番号, 受注日, 顧客番号) VALUES(order_sequence.NEXTVAL, TO_DATE(sysdate), ‘K0001038’);

登録した受注データを確認します。

SELECT 受注番号, 受注日, 顧客番号
FROM 受注
WHERE 顧客番号 = 'K0001038';

受注番号は「9」となり、別のセッションで最後にNEXTVALで取得した「8」の次の番号が取られていることが確認できます。ですので、NEXTVALで取得する番号は、複数のセッションからのアクセスで重複せずに次の番号が生成されることがわかりました。

ちなみに、SQL ServerではSQL Server 2012から、SEQUENCEがサポートされています。次回は、SQL Serverで確認したいと思います。

今日は以上まで

にほんブログ村

UPDATE

UPDATE ～ FROM（その２）

2015年6月27日ＩＴＣシュウちゃんコメントをどうぞ

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

前回に続いて、アメリカに行ってきた知人の方から頂いた写真で、フーバーダムというラスベガスのそばにある有名なダムの写真です（走っている車の中から撮ったということ）。

ダムによってコロラド川がせき止められたことでできた湖（ダム湖）はミード湖と呼ばれ、貯水量は400億トン、日本にある2,500基のダムの貯水量の合計が250億トンであり、琵琶湖の貯水量が280億トン程度ということなので、如何に大きいかということがわかりますね。
このダムは、1931年世界恐慌のときに、ニューディール政策の一環として着工されて1936年に竣工し、当時は世界一の水力発電だったとのこと。その後、世界にはもっと大きなダムもできているということですが、よくこんな大きなダムを作れるものだと、人間の力は本当にすごいと思います。

それから、先日の父の日は、子供たちから一品ずつ、チョコとかちょっとしたものでしたが、プレゼントを頂きました。一番下の息子はカードを作ってくれて、とても素直な表現がうれしかったです。妻もいつもよりは少しだけ豪華な食事を用意してくれて、うれしい1日となりました。

＜本日の題材＞
UPDATE ～ FROM（その２）

前回、SQL ServerでのUPDATE文で、他のテーブルのデータに基づくデータの更新をUPDATE ～ FROM テーブルというかたちで実現できるということを説明しました。
そのときに、Oracleでの対応の仕方として、例では EXISTS句を使ったものを取り上げましたが、今回はインラインビュー（ビューでもよい）を使ったかたちでの更新を取り上げてみたいと思います。

構文としては、
UPDATE (
SELECT A.col2 Acol2, B.col2 Bcol2
FROM tbl_A A
JOIN tbl_B B
ON A.col1 = B.col1
)
SET Acol2 = Bcol2;

ただし、UPDATEを行うのは更新可能なビュー（インラインビューも含む）でなければならず、また、テーブルの結合が1：n の場合には、1の親表の列のデータは更新できないということです。
（前回の例である、「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理では、テーブルの結合が１：nの場合に該当するため、このビューの更新の方法ではできません）

ちなみに、以下のような場合は更新できないビューになります。
　・GROUP BY句を使用したビュー
　・式により定義したビュー
　・DISTINCT句を使用したビュー
　・グループ関数を使用したビュー
　・擬似列ROWNUMを使用したビュー

例を以下に示します。
例）
社員マスタの入社日のデータが何らかの問題で消えてしまったとします。対応として、以前バックアップしていたテーブルの日付に戻したいという場合があるとします。当然、現在の社員マスタと、以前バックアップしていた社員マスタはDBのレイアウトは同じなので、データは1対１の関係です。
この場合、Oracleでの上記のやり方で更新するかたちでは以下になります。

UPDATE (
SELECT A.入社日, B.入社日 BK_入社日
FROM 社員マスタ A
JOIN 社員マスタ_BAK B
ON A.社員番号 = B.社員番号
)
SET 入社日 = BK_入社日;

まず、社員マスタのデータを確認します。

SELECT 社員番号, 社員名, 入社日
FROM 社員マスタ
ORDER BY 社員番号;

入社日の値がNULLになっています。
次に、同様にして社員マスタ_BAKのデータ（入社日）を確認します。

上記で示したように、社員マスタ_BAKの入社日のデータで社員マスタの入社日の値を更新します。

更新後の社員マスタの入社日のデータを確認します。

入社日がセットされているのが確認できます。
念のため、社員マスタの入社日と社員マスタ_BAKの入社日に差がないことを確認します。
上記から、社員マスタと社員マスタ_BAKがともに12件で同じ件数であることを利用して、下記の結果が同じく12件であれば、入社日に差がないことが確認できます。

上記の入社日の更新を、SQL Serverで実施する場合は、前回説明したように、

UPDATE dbo.社員マスタ SET
入社日 = dbo.社員マスタ_BAK.入社日
FROM dbo.社員マスタ
INNER JOIN dbo.社員マスタ_BAK
ON dbo.社員マスタ.社員ID = dbo.社員マスタ_BAK.社員ID;

とするか、JOINを使わずに

UPDATE dbo.社員マスタ SET
入社日 = SB.入社日
FROM dbo.社員マスタ_BAK SB
WHERE dbo.社員マスタ.社員ID = SB.社員ID;

とすることもできます。
以下は実際に実行したところです。

念のため結果を確認すると、

件数が12件で同じため、社員マスタの入社日は社員マスタ_BAKの入社日と同じものに更新されたことがわかります。

※ちなみに、前回の例としての「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理に、今回のやり方で更新する方法で実行してみると、以下のようにエラーが出ますね。

UPDATE (
SELECT A.累計購入金額 A_累計購入金額, B.購入金額 B_購入金額
FROM 顧客購買集計 A
JOIN 顧客日次購買 B
ON A.顧客ID = B.顧客ID
WHERE B.購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD')
) C
SET C.A_累計購入金額 = C.A_累計購入金額 + C.B_購入金額;

エラーは「ORA-01779：キー保存されていない表にマップする列は変更できません」となります。この「キー保存されていない表」というのが結合の１：ｎとなるときの親表のことです。

今日は以上まで

にほんブログ村

UPDATE

UPDATE ～ FROM（他のテーブルのデータを基に更新）

2015年6月14日ＩＴＣシュウちゃん 1件のコメント

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

知人の方が最近アメリカのラスベガスやグランドキャニオンなどに行ってきたということで、写真をいろいろと見せてもらったのですが、上の写真はグランドキャニオンで撮ってきたものです。

グランドキャニオンは、表層に近いほうで2億5千万年前、深層部では20億年前の地層と言われていて、それだけの長い期間の地層を見れるのは、世界的にもここだけらしいです。
谷までの深さは、1600m というところもあるとのことで、崖から落ちたら一巻の終わりですね。
奇跡とも言えるあまりの自然の壮大なスケールに、人生観も変わってしまうほどだと話に聞きますが、私も一生に一度は是非訪れてみたいものです。
写真は、観光スポットでガードレールのような柵があるところですが、下はまさに断崖絶壁になっていますね。ただ、この景色を直接見たら、本当にすごいでしょうね！

＜本日の題材＞
UPDATE ～ FROM（他のテーブルのデータを基に更新）

SQL Serverでの仕事をしているときに、こういうSQLの書き方もあるんだと気が付いた内容について上げてみたいと思います。
SQL Serverでは他のテーブルのデータに基づくデータの更新をUPDATE ～ FROM テーブルというかたちで実現できるということを、わりと最近になって知りました。Oracleでは上記の構文はUPDATE句にはなかったので、なかなか気が付かなかったのですが、今回はそれについて取り上げてみたいと思います。

構文は、

UPDATE tbl_A SET
tbl_A.col1 = tbl_B.col1
FROM tbl_A
INNER JOIN tbl_B
ON tbl_A.key_column = tbl_B.key_column;

例）
仮に、顧客の毎日の購買履歴を記録するテーブルが「顧客日次購買」として存在する場合に、顧客の過去の購入に関する集計用のテーブル「顧客購買集計」に対して、累計購入金額を前日の購入金額をプラスして更新することを、上記の構文を元に実行してみます。

「顧客日次購買」テーブルの定義：
顧客ID            VARCHAR(10)
購入日              DATE
購入金額              DECIMAL(10)
※主キーは、顧客ID, 購入日

「顧客購買集計」テーブルの定義：
顧客ID VARCHAR(10)
累計購入金額 DECIMAL(12)
※主キーは、顧客ID

一昨日までの顧客毎の累計金額を登録した「顧客購買集計」テーブルに、昨日分の購入金額をプラスするということで、累計購入金額を更新する処理を行うとします。

まず、一昨日までの累計購入金額を確認します。

SELECT * FROM dbo.顧客購買集計
ORDER BY 顧客ID;

昨日の購入があった顧客の、「顧客日次購買」テーブルのデータを確認します。

SELECT * FROM dbo.顧客日次購買
WHERE 購入日 = CONVERT(VARCHAR, getdate()-1, 111)
ORDER BY 顧客ID;

「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理を行います。

UPDATE dbo.顧客購買集計 SET
累計購入金額 =累計購入金額 + dbo.顧客日次購買.購入金額
FROM dbo.顧客購買集計
INNER JOIN dbo.顧客日次購買
ON dbo.顧客購買集計.顧客ID = dbo.顧客日次購買.顧客ID
AND dbo.顧客日次購買.購入日 = CONVERT(VARCHAR, getdate()-1, 111);

更新後の「顧客購買集計」テーブルの結果を確認します。

SELECT * FROM dbo.顧客購買集計
ORDER BY 顧客ID;

4人の顧客(K0000011,K0000021,K0000043,K0000076)については、昨日の購入金額分が累計購入金額に足されていることが確認できました。

同様の処理をOracleで行う場合、同じ設定のSQL文ではエラーになります。

UPDATE 顧客購買集計 SET
累計購入金額 = 累計購入金額 + 顧客日次購買.購入金額
FROM 顧客購買集計
INNER JOIN 顧客日次購買
ON 顧客購買集計.顧客ID = 顧客日次購買.顧客ID
AND 顧客日次購買.購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD');

Oracleでは、このケースでは以下のようなかたちになるかと思います。

UPDATE 顧客購買集計 SET
累計購入金額 = 累計購入金額 +
(SELECT 購入金額
FROM 顧客日次購買
WHERE 顧客ID = 顧客購買集計.顧客ID
AND 購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'))
WHERE EXISTS (SELECT 'x' FROM 顧客日次購買
WHERE 顧客ID = 顧客購買集計.顧客ID
AND 購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'));

※上記はSQL Serverでも同様の処理が可能です。

一応、更新後の結果を確認すると

SELECT * FROM 顧客購買集計
ORDER BY 顧客ID;

先ほどのSQL Serverと同様の結果が得られました。

今日は以上まで

にほんブログ村

集合演算子

UNION、UNION ALL演算子

2015年6月1日ＩＴＣシュウちゃんコメントをどうぞ

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

筑波山に行ったときに、頂上付近で撮った写真です。私ははじめてだったのですが、何度も行ったことのある知人数人と一緒に、子供たちを連れて行ってきました。
筑波山は関東地方に人が住むようになったころから、信仰の対象として仰がれてきたということで、男体山（標高871m）と女体山（標高877m）という2つの山をいざなぎ、いざなみの神として仰いできたということです。山の中腹に筑波山神社の拝殿があり、頂上付近にも写真のような本殿があります。
頂上からはまわりが一望できて、晴れていれば素晴らしい景色になります。ロープウェイもありましたが、頑張って子供たちと登りました。ただし、結構急な坂もあり、久しぶりの運動で、登るのが大変でした。あらためて年を感じさせられました。

＜本日の題材＞
UNION、UNION ALL演算子

前回、前々回と集合演算の差、積を行う演算子について見ました。順番が逆になってしまった気がしますが、今日は複数の検索結果を統合する和集合を求める演算子について見てみます。

複数のSELECT文での問い合わせの結果に対して、和集合を抽出する演算子として、UNION、UNION ALLがあります。この演算子は、Oracle、SQL Serverともに使用できます。

前回の抽出で使用したtbl_A、tbl_Bを利用して、少なくともどちらかのテーブルに存在するレコードを抽出してみます。このとき、重複するレコードは1行にまとめて表示します。
（SQL Serverで確認）

SELECT * FROM tbl_A
UNION
SELECT * FROM tbl_B;

前々回、tbl_A、tbl_B のデータを確認していますが、たしかに、tbl_A、tbl_B のどちらかに存在するレコードが全て抽出されました。

ここで、重複する行を1つにまとめずに、そのまま抽出する場合には、「UNION ALL」句を使用します。上記の例を、UNION ALLを使用して実行すると、

SELECT * FROM tbl_A
UNION ALL
SELECT * FROM tbl_B;

tbl_A、tbl_B のデータが重複データをまとめずに全て抽出されているのが確認できます。

※4パフォーマンスの観点から言うと、重複データを1行にまとめる処理（DISTINCT、UNION、INTERSECT、MINUSなど）は暗黙的にソート処理を行うため、その分処理が重くなり、レスポンスとしては遅くなる傾向があります。重複するデータがないということがあらかじめ分かっている場合には、和集合の場合はUNIONではなくUNION ALLを使用するようにしたほうがよいと言えます。

前々回で、同じ構造の2つのテーブルA、Bがあったとして、その2つのテーブルが完全に一致しているかどうかを確認するために、MINUS演算子を使用する例を上げましたが、一致するかどうかだけを確認したいならば、UNIONを使って確認するという方法もあります。

A, Bどちらも同じ行数のレコードが存在しているという状況がわかっていれば、下記のSQLを実行します。

SELECT COUNT(*)
FROM
(
SELECT * FROM tbl_A
UNION
SELECT * FROM tbl_B
) AS tbl_C;

この結果が、A, B のテーブルのレコード数と一致していれば、A、Bは一致しているということができることになります。

ちなみに、上のサンプルデータで試した場合、以下のようになり、tbl_A、tbl_Bは一致していないことがわかります。

※Oracleでも同様の処理が可能です。

今日は以上まで

にほんブログ村

集合演算子

INTERSECT演算子

2015年5月16日ＩＴＣシュウちゃんコメントをどうぞ

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

久しぶりに新宿のほうに仕事で行く用事があり、昼休みに新宿中央公園から都庁を撮った写真です。

話は変わりますが、先日の母の日に、子供たちが一人ひとり、妻にプレゼントをしていました。娘の一人はイヤリングのようなものをプレゼントしていたようです。まだ中学に入ったばかりの長男は、少し立派なチョコレートをあげてました。妻もまんざらではないようです。
私は妻と一緒にちょっとしたお買いものにお付き合い。妻の頑張りは大きいですよね。感謝しないと！
父の日は、何かあるかな～！

＜本日の題材＞
INTERSECT演算子

前回、集合演算の差を行う関数としての、MINUS、EXCEPT関数について見ましたが、今日はINTERSECTという、積集合（ただし、重複するデータについては1行にまとめる）を取得する演算子について見てみます。
積集合とは、与えられた集合の集まり全てに共通に含まれている要素を集めることにより得られる集合のこと。論理演算子ANDに相当し、共通集合もしくは「交わり」とも呼ばれます。

複数のSELECT文での問い合わせの結果に対して、共通の集合を重複しないかたちで抽出します。
なお、この演算子は、Oracle、SQL Serverともで使用できます。

例１）
前回の抽出で使用したtbl_A、tbl_Bを利用して、どちらのテーブルにも存在する全く同じ値のレコードを抽出してみます。
（SQL Serverで確認）

SELECT * FROM tbl_A
INTERSECT
SELECT * FROM tbl_B;

前回、tbl_A、tbl_B のデータを確認していますが、たしかに、tbl_A、tbl_B のどちらにも存在するレコードが抽出されました。

例２）
あるメーカーの3月に行ったキャンペーンに応募した顧客のデータを、「CP_応募」テーブルに登録したとします。
データは以下：
①
SELECT * FROM dbo.CP_応募
ORDER BY 顧客ID;

次に、上記のキャンペーンに応募した顧客も含めて、4月の売上金額の多い顧客について、売上金額が1万円以上の顧客を金額の高い順に抽出した結果が以下とします。
②
SELECT
ROW_NUMBER() OVER (ORDER BY SUM(売上金額) DESC) AS 売上順位
, 顧客ID
, SUM(売上金額) AS 売上合計
FROM dbo.売上
WHERE FORMAT(出荷日, 'yyyyMM') = '201504'
GROUP BY 顧客ID
HAVING SUM(売上金額) >= 10000
ORDER BY SUM(売上金額) DESC;

上記の①と②の結果で、どちらにも存在する顧客IDを抽出したいというときに、INTERSECT関数を使用してみます。

SELECT 顧客ID
FROM dbo.CP_応募
INTERSECT
SELECT 顧客ID
FROM dbo.売上
WHERE FORMAT(出荷日, 'yyyyMM') = '201504'
GROUP BY 顧客ID
HAVING SUM(売上金額) >= 10000
ORDER BY 顧客ID;

3月のCPに応募した顧客が、4月の売上の上位（1万円以上）に5人含まれていることが確認できました。

ただ、これは「CP_応募」テーブルと「売上」テーブルを、下記SQLのように「顧客ID」でジョインして抽出するということでも同様の結果が出ます。というか、どちらかというと、ジョインを使うのが一般的なやり方かとは思われますが。

SELECT A.顧客ID
FROM dbo.売上 A
JOIN dbo.CP_応募 B ON A.顧客ID = B.顧客ID
WHERE FORMAT(A.出荷日, 'yyyyMM') = '201504'
GROUP BY A.顧客ID
HAVING SUM(A.売上金額) >= 10000
ORDER BY A.顧客ID;

このINTERSECT演算子は、Oracleでも同様に使用することができます。
例１）について、Oracle環境で実施した結果は、以下になります。

ちなみに、MySQLではやはりINTERSECT演算子は対応していないようなので、EXISTS句を利用するなどして、全ての項目が一致するレコードを抽出することになります。

今日は以上まで

にほんブログ村

集合演算子

MINUS、EXCEPT演算子

2015年5月9日ＩＴＣシュウちゃんコメントをどうぞ

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

ゴールデンウィーク(GW)も過ぎちゃいましたね。今年は、カレンダー通り休みがとれたのですが、今の時期、どことなく憂鬱に感じるのは私だけでしょうか？

今年は、GWの期間に、埼玉県の加須市騎西(きさい)にある、玉敷(たましき)神社の藤の花を家族で見に行って来ました。この藤の木は、県指定天然記念物であり、なんと樹齢400年、幹回り4.8メートル、枝張り約700平方メートルにも及ぶそうです。すごいですね。4月下旬くらいから5月上旬くらいまでが見ごろだとか。一応、一番見ごろな時に、見れたわけです。

ところで、出店の大判焼きもうまかったですよ。家族みんなで食べるからうまいのかな？
さて、頭を切り替え、仕事、仕事。頑張るぞ~！トホホホ・・・

＜本日の題材＞
MINUS、EXCEPT演算子

今回は、集合演算の差を行う演算子としての、MINUS、EXCEPTについて見てみます。同じ構造の2つのテーブルA,Bがあったとして、2つのテーブルのデータが完全に一致しているのかを確認したくなるときがありますね。そのときに、一致していないデータを抽出したいときに使用できます。元々Oracleを使用していた方はMINUS 演算子がすぐに連想されるかと思いますが、標準SQLではEXCEPT（SQL-92から）が採用されているんですね。ですので、SQLServerではEXCEPT 演算子を使用します（SQL Server2005からサポート）。

例）データベースがOracleの場合：

項目数、及びデータ型が一致する2つのテーブルtbl_A、tbl_Bの定義がともに以下のようであるとします。

テーブル定義：tbl_A
　CD_A INT
Name_A VARCHAR2(20)

テーブル定義：tbl_B
　CD_B INT
Name_B VARCHAR2(20)

データは、
SELECT * FROM tbl_A;