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

上の写真は、11/4に日本三大花火大会の一つといわれる土浦の花火（正式名は土浦全国花火競技大会）に家族で見に行った時の写真になります。家族で車で行ったのは初めてで、ある程度余裕をもって家を出発したつもりでしたが、土浦市内に入ると、めちゃくちゃ車が混んでいて、止められるかなと思っていたモールのほうも、当日は駐車禁止ということになっていたり、通行止めがあったりして、全く車が進まない状況に。それでも何とか駐車場を見つけようと、市内をうろうろしていたところ、あるおじさんが声をかけてくれて、自宅の駐車場の空いているスペースを貸してくれるという奇跡のような出来事がありました。妻と娘も車に乗っていたこともあり、心配して声をかけてくださったのだと思いますが、本当に感謝でした。

＜本日の題材＞
OUTPUT句（SQL Server）

今回は、SQL Serverの機能で、OUTPUT句について取り上げてみたいと思います。これは、INSERT/UPDATE/DELETE/MERGEで追加/更新/削除 された行の情報を取得・出力できるというもので、とても便利な機能になります。最近の開発の案件の中で、この処理を使うことで、開発の要件を満たすことができました。

例)
テーブルにデータを登録/更新/削除するときに、通常は登録/更新/削除したデータをその時点では確認できず、結果のデータと処理前のデータを比較することで確認できるというかたちになるかと思いますが、OUTPUT句を使うことで、その時点でそれらのデータを出力することができるので、それを確認したいと思います。

まず、今回確認するテーブルを作成し、初期のデータを登録します。

CREATE TABLE トランA(
NO    INT NOT NULL,
名称  NVARCHAR(20),
数量  INT,
CONSTRAINT PK_トランA PRIMARY KEY(NO));

INSERT INTO トランA VALUES(1, '名称１', 100);
INSERT INTO トランA VALUES(2, '名称２', 200);

それでは、INSERT/UPDATE/DELETE の処理を実行するとともに、OUTPUT句によって出力してみます。

まず、INSERT から
INSERT INTO トランA OUTPUT inserted.*
VALUES(3, '名称３', 300);

通常、INSERT文を実行すると、
(1 行処理されました)
というようなメッセージのみが出力されますが、OUTPUT句を使用することで、INSERTしたデータを出力することができます。

次に、UPDATEを試します。
UPDATE トランA SET
数量 = 400
OUTPUT deleted.*, inserted.*
WHERE NO = 3;

上記結果のように、deleted.* で更新前の(削除される)データ、inserted.* で更新後の(追加される)データを出力しています。

次に、DELETEを試してみます。

DELETE FROM トランA
OUTPUT deleted.*
WHERE NO = 3;

上記のように、DELETEされるデータを出力しているのが確認できます。

次に、MERGE処理でOUTPUT句を使用するために、トランAと同様の定義を持つワークテーブルを作成し、そこにもデータを登録します。

CREATE TABLE ワークA(
NO   INT NOT NULL,
名称 NVARCHAR(20),
数量 INT,
CONSTRAINT PK_ワークA PRIMARY KEY(NO));

INSERT INTO ワークA VALUES(1 '修正名称１', 1000);
INSERT INTO ワークA VALUES(3 '名称３', 3000);
INSERT INTO ワークA VALUES(4 '名称４', 4000);

それでは、MERGEを実行する前に、再度トランAテーブルのデータを確認します。

SELECT * FROM トランA;

トランAのテーブルに対して、ワークAのデータを参照して、キーがマッチするものは更新し、マッチしないもので、トランにはなくワークに存在するものは登録し、トランにはあるがワークには存在しないものは削除するMERGE処理を実行してみますが、このときに、OUTPUT句によって、更新・登録・削除のデータを出力してみたいと思います。

MERGE INTO トランA
USING ワークA
ON (トランA.NO = ワークA.NO)
WHEN MATCHED THEN
UPDATE SET
  名称 = ワークA.名称
 ,数量 = ワークA.数量
WHEN NOT MATCHED THEN
INSERT (NO, 名称, 数量)
VALUES(ワークA.NO, ワークA.名称, ワークA.数量)
WHEN NOT MATCHED BY SOURCE
DELETE
OUTPUT inserted.*,  deleted.*, $action;

$action という項目に、UPDATE/INSERT/DELETE のどの処理が行われたのかが出力されます。
したがって、このOUTPUTの結果を一時テーブルなどに登録すれば、MERGE文などの処理の後に、登録・削除・更新されたデータをその後の処理で活用することが可能になります。
最終的なトランAの結果を確認してみます。

SELECT * FROM トランA;

今日は以上まで

にほんブログ村

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

お久しぶりです。写真は妻が買ってきて家に置いていたハイビスカスという花が綺麗に咲いたところを撮ったものです。ハイビスカスは花の色によって花言葉が違うようですね。白は「艶美」、ピンクは「華やか」、黄色は「輝き」、赤は「勇敢」とのこと。
家に花があるのはいいものですね。あまり花とかに興味がなかった私でしたが、妻のおかげで自然の美しさを意識することが多くなったと感謝しています。
一生懸命に咲いて美を表わそうとする花のように、私たちも情熱をもって一生懸命に生きていきたいものです。

それから、dbSheetClientのブログに、はるパパさんのdbSheetClient千夜一夜というブログができています。是非そちらもご覧ください。
https://www.newcom07.jp/dbsheetclient/dbsc-blog/

＜本日の題材＞
CASE式をWHERE句で使う（SQL Server）

CASE WHENの式を、以前、SELECT文で使用するサンプルは何度かブログで取り上げましたが、WHERE句の中で、ケースによって条件文自体を変更したいという場合のやり方を今回試してみたいと思います。

例)
検索用の項目を設定し、そこに値を設定すれば、その項目に対しては指定した値で検索するが、何も設定しなければ、その項目に対しては条件を設定せずに抽出を行いたいというケースはよくあると思います。

今回は簡単なケースとして、社員マスタの検索の際に、「社員番号」「社員名」「拠点」の検索項目に対して、設定した内容があればその値で検索し、なければ条件としては加えないかたちの例を取り上げてみたいと思います。

上記の3項目をパラメータとして渡し、値が NULL でなければその値を条件とするが、NULLの場合には条件に加えないというかたちにします。

例えば、社員番号、社員名は何も値を設定せず、拠点を「ニューヨーク」として検索したい場合、以下のように設定できます。

-- 変数を定義
DECLARE
            @社員番号     DECIMAL(5),
            @社員名       NVARCHAR(20),
            @拠点         NVARCHAR(20);

--変数設定：画面で選択した値がセットされるようにします
SET @社員番号 = NULL;
SET @社員名 = NULL;
SET @拠点 = 'ニューヨーク' ;

--社員マスタ検索
SELECT * FROM dbo.社員マスタ
WHERE 1=1
AND 社員番号 = CASE WHEN @社員番号 IS NULL THEN 社員番号 ELSE @社員番号 END
AND 社員名 = CASE WHEN @社員名 IS NULL THEN 社員名 ELSE @社員名 END
AND 拠点 = CASE WHEN @拠点 IS NULL THEN 拠点 ELSE @拠点 END;

上記は、拠点が「ニューヨーク」の社員を全て抽出してきます。
もちろん、社員名にも値を設定すれば２つの条件で抽出することになります。

 結果は、2つの条件を満たすデータのみが抽出されることを確認できました。

今日は以上まで

にほんブログ村