プログラ生活

プログラム初学者のためのポイントを書いていこうと思います。たまに脇道それた記事もありますが、息抜きだとおもって気長にお付き合いください。

【SQL Server(連載)】トランザクションの実装 その2 -39-

www.pon-x.jp

前回の続き

前回から、トランザクションの実装をしてますが、今回はエラーを検知してロールバックしてみたいと思います。

エラー検知

エラー検知はこのような構文です。

BEGIN TRY

    -- ###############################
    -- ここでエラーが発生したらCATCHに飛ばす
    -- ###############################

END TRY

BEGIN CATCH

    -- ###############################
    -- エラーがあったらここの内容を処理する
    -- ###############################

END CATCH

こんな感じです。

前回の内容に組み込んでみる。

では、早速やってみましょう。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRY

    BEGIN TRANSACTION


        DECLARE @CNT INT

        SET @CNT = 0

        WHILE @CNT <= 30
            BEGIN
                INSERT INTO #TEMP
                VALUES(
                    POWER(2, @CNT),
                    '2^' + CONVERT(VARCHAR, @CNT)
                )

                IF @CNT = 20
                    SET @CNT = @CNT / 0

                SET @CNT += 1
            END


    COMMIT TRANSACTION
END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION

END CATCH

SELECT * FROM #TEMP

上記の内容は、WHILEのループ内で@CNTが20の時に、わざとエラーを発生させています。
エラーをキャッチしてロールバックしているので、#TEMPはからになって、最後に出力されます。
こんな感じです。
f:id:hanabusa-snow:20201226213016p:plain

ロールバックしないで、エラーが出るまで処理をする

通常、エラーがでたら処理がとまってしまうのですが、TRY~CATCHはうまくエラーをハンドリングすることができます。
では、エラーがでるまで処理するというよみましょう。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRY

    BEGIN TRANSACTION


        DECLARE @CNT INT

        SET @CNT = 0

        WHILE @CNT <= 30
            BEGIN
                INSERT INTO #TEMP
                VALUES(
                    POWER(2, @CNT),
                    '2^' + CONVERT(VARCHAR, @CNT)
                )

                IF @CNT = 20
                    SET @CNT = @CNT / 0

                SET @CNT += 1
            END


    COMMIT TRANSACTION
END TRY

BEGIN CATCH

    PRINT('##### ERROR #####')
    PRINT('@CNT -> ' + CONVERT(VARCHAR, @CNT))
    PRINT('##### ERROR #####')
    RETURN

END CATCH

SELECT * FROM #TEMP

上記の内容は、WHILEのループ内で@CNTが20の時に、わざとエラーを発生させています。
@CNTを0で割ろうとしていますが、分母が0になる数は存在しないので、エラーになります。

こんなかんじの出力です。
f:id:hanabusa-snow:20201226212032p:plain @CNTが20でおわっているのが確認できますね。

BEGIN CATCH以下の内容を確認しておこう。

BEGIN CATCH

    PRINT('##### ERROR #####')
    PRINT('@CNT -> ' + CONVERT(VARCHAR, @CNT))
    PRINT('##### ERROR #####')
    RETURN

END CATCH

SELECT * FROM #TEMP

こんなかんじですが、RETURNで処理を終了しています。
つまり、それ以下のSELECT * FROM #TEMPは処理されないということになります。
SELECT * FROM #TEMPは、もしエラーが発生されなかったら処理されるといことになります。

こんなかんじで、エラーがあったときにそこで終わるのかどうかということも明示しておく必要があります。







以上、今回はここまで。S

【SQL Server(連載)】トランザクションの実装 その1 -38-

www.pon-x.jp

前回はトランザクションの意義について説明しました。
今回は実装してみたいと思います。

コミット

では、トランザクションで処理を行いコミットしてみたいと思います。
以下例文です。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRANSACTION


    DECLARE @CNT INT

    SET @CNT = 0

    WHILE @CNT <= 30
        BEGIN
            INSERT INTO #TEMP
            VALUES(
                POWER(2, @CNT),
                '2^' + CONVERT(VARCHAR, @CNT)
            )

            SET @CNT += 1
        END


COMMIT TRANSACTION

SELECT * FROM #TEMP

こんな感じに出力されます。
f:id:hanabusa-snow:20201225085910p:plain
BEGIN TRANSACTIONでトランザクションを開始して、COMMIT TRANSACTIONでコミットされるということです。

ロールバック

ではもし、COMMIT TRANSACTIONではなく、ROLLBACK TRANSACTIONだとどうなるでしょうか。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRANSACTION


    DECLARE @CNT INT

    SET @CNT = 0

    WHILE @CNT <= 30
        BEGIN
            INSERT INTO #TEMP
            VALUES(
                POWER(2, @CNT),
                '2^' + CONVERT(VARCHAR, @CNT)
            )

            SET @CNT += 1
        END


ROLLBACK TRANSACTION

SELECT * FROM #TEMP

こんなかんじに、トランザクションを開始してそれ以降の処理を行いますが、ロールバックを行うので、最後はトランザクション処理の前に戻ってしまうので、SELECTで出力しても何も中身はありません。 f:id:hanabusa-snow:20201225085851p:plain







以上、次回はエラーを検知してロールバック処理を行います。

【SQL Server(連載)】トランザクション -37-

www.pon-x.jp

前回の続き

今回はトランザクションのはなしです。
ちなみに今回は100%読み物です。

トランザクション

例えば、銀行の預金を管理するデータベースがあったとします。
Aさんは10万円の預金があります。
また、キャッシュカードはAさんと、Aさんの奥さんのBさんも家族カードとして同一口座のものをもっています。
例えば、ほぼ同時に2人が6万円ずつ払い戻しをしたとします。
ほぼ同時だったので、データベースを参照したら10万円の預金があったので、結果6万円ずつ払い戻しができました・・・。となったら大変なことですよね。

こんな時に必要な処理がトランザクションです。

トランザクションは複数の処理をひとまとめにして、処理の最中は他の処理によってテーブルが更新されないようにロックしてしまうものです。
今回の預金の話は、

  1. 預金額を参照する
  2. 払い戻し金額が預金額を超えていたら、処理を終える。
  3. 払い戻し金額が預金額を超えていなければ払い戻し金額を書き込み、現金を出力する。

という処理ですね。(実際はもっとあると思いますが、ひとまず単純に考えます)

トランザクションによるテーブルロックがなければ、預金額を参照してAさん、Bさんともに10万円というデータが得られてしまえば、2人とも上記の3の処理を行ってしまうということになってしまうのです。

ですから、上記1,2,3の処理を一連のひとつの処理と考え、先にアクセスした人がテーブルロックを行い、次にアクセスした人は前の人のロックが解除するのをまっているというのがトランザクションの考え方です。

コミットとロールバック

トランザクションは複数の処理をひとつにまとめるわけですから、処理途中にひとつひとつが確定したのでは、まとめるとはいえません。
そこで、仮にデータの書き換えなどの処理を行い、最終的にそれで確定するか、もしくは処理をなかったことにするかを選択できます。
もし、処理を確定するときはコミット、処理をなかったことにするときはロールバックを行います。

また、ロールバック処理を行う際は、例外がおきたとき、つまりエラー検知を行うことがセットにされることが多いので、これも紹介をしておきます。

エラー検知

処理を大量に行うとき、必ずしもすべてうまくいくとはかぎりません。
ですので予期せぬ例外がおきたときの対応をしておくことも必要です。
エラーが起きたときにはどのように処理を行うのかを決めておく必要があります。
エラーが予期できるようなことであれば、ルールベースで回避しておくべきでしょうが、そうでないときは多くは、トランザクション内でおきていることであればロールバックさせておくことが無難です。






以上、今回はここまで。
次回は実践してみたいと思います。

【SQL Server(連載)】正規化(第3正規化) -36-

www.pon-x.jp

前回の続き

今回は、第3正規化についてのはなしです。

第3正規化

前回まで第2正規化まで行い、以下のテーブルが得られました。

TABLE: 売上 Primary Key:注文番号、商品ID

注文番号 注文年月日 商品ID 数量 顧客ID 顧客名 顧客TEL 顧客住所
1001 2020/12/20 K101 3 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K112 2 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K126 1 P501 岡氏 好男 0000-00-0000 東京都
1002 2020/12/21 Y516 2 P905 野菜 好子 1111-11-1111 埼玉県
1002 2020/12/21 Y842 3 P905 野菜 好子 1111-11-1111 埼玉県




TABLE: 商品 Primary Key:商品ID

商品ID 商品名 単価
K101 うまい棒 10
K112 がりがりくん 60
K126 チップスター 150
Y516 とまと 90
Y842 きゅうり 30

前回、関数従属性の説明をしましたね。
そのことから、商品テーブルにカラムを分離させてあげたということです。

第2正規化は主キーに対いて従属しているカラムを分離させましたが、主キー以外の項目についても従属関係があるカラムがあるとき、これを推移的関数従属といい、別テーブルに切り分ける必要があります。
これを第3正規化といい、第3正規化した結果を第3正規形といいます。

推移的関数従属

上述しましたが、推移的関数従属は主キー以外のカラムで従属関係にあるカラムをテーブル分離することですが、今回のケースは売上テーブルの、顧客IDに対する、顧客名と顧客TELと顧客住所がこれにあたります。
なぜなら、顧客IDはユニークなはずなので、顧客名と顧客TELと顧客住所は一意にきまるだめです。

では、第3正規化をしてみましょう。

TABLE: 売上 Primary Key:注文番号、商品ID

注文番号 注文年月日 商品ID 数量 顧客ID
1001 2020/12/20 K101 3 P501
1001 2020/12/20 K112 2 P501
1001 2020/12/20 K126 1 P501
1002 2020/12/21 Y516 2 P905
1002 2020/12/21 Y842 3 P905




TABLE: 商品 Primary Key:商品ID

商品ID 商品名 単価
K101 うまい棒 10
K112 がりがりくん 60
K126 チップスター 150
Y516 とまと 90
Y842 きゅうり 30




TABLE: 顧客 Primary Key:顧客ID

顧客ID 顧客名 顧客TEL 顧客住所
P501 岡氏 好男 0000-00-0000 東京都
P501 岡氏 好男 0000-00-0000 東京都
P501 岡氏 好男 0000-00-0000 東京都
P905 野菜 好子 1111-11-1111 埼玉県
P905 野菜 好子 1111-11-1111 埼玉県

これで第3正規形まで完成です。







以上、3回にわたり正規形の説明でした。

【SQL Server(連載)】正規化(第2正規化) -35-

www.pon-x.jp

前回の続き

今回は、第2正規化についてのはなしです。

第2正規化

前回、第1正規化を行い以下のようにテーブルを設計してきました。

注文番号 注文年月日 商品ID 商品名 単価 数量 顧客ID 顧客名 顧客TEL 顧客住所
1001 2020/12/20 K101 うまい棒 10 3 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K112 がりがりくん 60 2 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K126 チップスター 150 1 P501 岡氏 好男 0000-00-0000 東京都
1002 2020/12/21 Y516 とまと 90 2 P905 野菜 好子 1111-11-1111 埼玉県
1002 2020/12/21 Y842 きゅうり 30 3 P905 野菜 好子 1111-11-1111 埼玉県

上記の形式で、リレーショナルデータベースとしては扱えるのですが、まだこれではメンテナンス効率の悪い構造ですので、引き続き正規化を進めていきます。

第2正規化では、主キーに従属するカラムを別のテーブルに分離してあげることを目的とします。

関数従属性

一方の値が決まるともう一方の値も決まる関係を関数従属性といいます。
その関係のなかで主キー全てのカラムを用いて値が決まる関係を完全従属、主キーの一部のカラムによって値が決まる関係を部分従属といいます。
では、関数従属性の考え方を用いて、カラムを分離してみます。

TABLE: 売上 Primary Key:注文番号、商品ID

注文番号 注文年月日 商品ID 数量 顧客ID 顧客名 顧客TEL 顧客住所
1001 2020/12/20 K101 3 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K112 2 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K126 1 P501 岡氏 好男 0000-00-0000 東京都
1002 2020/12/21 Y516 2 P905 野菜 好子 1111-11-1111 埼玉県
1002 2020/12/21 Y842 3 P905 野菜 好子 1111-11-1111 埼玉県

TABLE: 商品 Primary Key:商品ID

商品ID 商品名 単価
K101 うまい棒 10
K112 がりがりくん 60
K126 チップスター 150
Y516 とまと 90
Y842 きゅうり 30







以上、今回はここまで。
次回は第3正規化をやります。

【SQL Server(連載)】正規化(第1正規化) -34-

www.pon-x.jp

前回の続き

今回からテーブル設計についての話になります。

正規化と正規形

リレーショナルデータベースをここまでやってきて、おおよそテーブル構造についても想像できるようになったかとおもいます。
一方で、テーブルを作るということもやってきたわけですが、どのような手順でテーブルを設計していくべきかということをしっかりと確認していきたいと思います。

例えば、リレーショナルデータベースを知らない人がEXCELなどでデータを書き並べていった場合、あたりまえですがリレーショナルデータベースにふさわしくない形式になっているかと思います。
この形式を非正規形といいます。
データベース設計者はこの非正規形のデータベースをリレーショナルデータベースとして扱える形式である正規形に設計しなおす必要があります。
この正規形にリメイクする処理を、正規化というわけです。

正規化には、第1正規化から第5正規化までありますが、実務においてはほぼ第3正規化までが必須となっているので、本ブログでも第3正規化まで紹介することにいたします。

今回の内容はだ1正規化です。

第1正規化

上記でも触れましたが、非正規形のデータベースを第1正規形に変換することを第1正規化といいます。

第1正規化とは、以下の処理を行うものをいいます。

  1. 繰り返しのカラムを別テーブルに分離
  2. キーを設定
  3. 導出項目を削除

では、ひとつひとつ見ていきましょう。

繰り返しのカラムを別テーブルに分離

まず、非正規形のデータを確認してみましょう。 例えば、こんなデータを例とします。

注文番号 注文年月日 商品名 単価 数量 合計金額 商品名 単価 数量 合計金額 商品名 単価 数量 合計金額 顧客ID 顧客名 顧客TEL 顧客住所
1001 2020/12/20 うまい棒 10 3 30 がりがりくん 60 2 120 チップスター 150 1 150 P501 岡氏 好男 0000-00-0000 東京都
1002 2020/12/21 とまと 90 2 180 きゅうり 21 30 60 P905 野菜 好子 1111-11-1111 埼玉県

上記の形式だと商品名、単価、数量が繰り返し項目になっています。
なんで、これだとリレーショナルデータベースにとって好ましくないかというと、顧客が1回の買い物で商品を最大いくつかうかということは想像つかないからです。
リレーショナルデータベースは、テーブルごとのカラムを事前に用意しておいて、データとしてレコードを増やしていくとい構造です。
なので、上記は非正規形であるといえるのです。
上記を改善するとこんな感じです。

注文番号 注文年月日 商品ID 商品名 単価 数量 合計金額 顧客ID 顧客名 顧客TEL 顧客住所
1001 2020/12/20 K101 うまい棒 10 3 30 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K112 がりがりくん 60 2 120 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K126 チップスター 150 1 150 P501 岡氏 好男 0000-00-0000 東京都
1002 2020/12/21 Y516 とまと 90 2 180 P905 野菜 好子 1111-11-1111 埼玉県
1002 2020/12/21 Y842 きゅうり 30 3 90 P905 野菜 好子 1111-11-1111 埼玉県

繰り返しカラムをまとめることができました。

キーの設定

上記の正規化過程で、商品IDカラムを追加しました。
非正規形のときは注文番号だけでユニークになったが、繰り返しカラムを除外することで、注文番号ともうひとつの2カラムでユニークにする必要があるためです。
※商品名のような名詞は必ずユニークになるとは言えないので、基本的にはキーに指定しません。

今回の場合、注文番号と商品IDでプライマリーキーに指定するのがよいでしょう。

導出項目を削除する。

導出項目とは、他のカラム(他のテーブルのカラムでもよい)を用いて算出できる値です。
つまり、ここでは単価と数量の積で求められる合計金額をさします。
わざわざカラムをつくらなくても、必要な時に演算させてあげればよいということです。

削除するとこんな感じになります。

注文番号 注文年月日 商品ID 商品名 単価 数量 顧客ID 顧客名 顧客TEL 顧客住所
1001 2020/12/20 K101 うまい棒 10 3 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K112 がりがりくん 60 2 P501 岡氏 好男 0000-00-0000 東京都
1001 2020/12/20 K126 チップスター 150 1 P501 岡氏 好男 0000-00-0000 東京都
1002 2020/12/21 Y516 とまと 90 2 P905 野菜 好子 1111-11-1111 埼玉県
1002 2020/12/21 Y842 きゅうり 30 3 P905 野菜 好子 1111-11-1111 埼玉県







以上、今回はここまで。
次回は第2正規化をやります。

【SQL Server(連載)】ストアドプロシージャ -33-

www.pon-x.jp

前回の続き

今回はストアドプロシージャの説明です。

ストアドプロシージャ

ストアドプロシージャとは、処理をまとめてひとつにして保存するものです。
前回まで、複数の処理を重ねて処理する例をたくさんやってきましたが、それらを実行するたびにコードにおこしていたのでは大変ですよね。
そんなときに解決してくれるものです。

使い方は処理の前に以下を記述するだけです。

CREATE PROCEDURE [ストアドプロシージャの名称]

    [引数][データ型],
    [引数][データ型],
・・・

AS

あとは上記いかに必要な処理を書いていくだけです。

ちなみに、作成済みストアドプロシージャを変更する際は、CREATEをALTERにするだけです。
こんなかんじ

ALTER PROCEDURE [ストアドプロシージャの名称]

    [引数][データ型],
    [引数][データ型],
・・・

AS

実際に試してみる

前回の処理に対して1点だけ変更を加えてをストアドプロシージャに保存してみます。 (引数に@PersonTypeを指定して、カーソルに格納するためのSELECT文のWHERE句に指定してあげることにしました。)
こんなかんじ


CREATE PROCEDURE TEST_PROCEDURE

    @PersonType NVARCHAR(2)

AS

IF OBJECT_ID(N'tempdb..#TEMP_TABLE', N'U') IS NOT NULL
    DROP TABLE #TEMP_TABLE

CREATE TABLE #TEMP_TABLE(
    ID INT Primary Key,
    FULLNAME NVARCHAR(128),
    NAME_LENGTH INT,
    MAX_LENGTH BIT
)

DECLARE @ID INT
DECLARE @F_NAME NVARCHAR(32)
DECLARE @M_NAME NVARCHAR(32)
DECLARE @L_NAME NVARCHAR(32)
DECLARE @MAX_LENGTH INT

SET @MAX_LENGTH = 0

DECLARE crTest CURSOR FOR
   SELECT 
        BusinessEntityID,
        FirstName,
        MiddleName,
        LastName
   FROM Person.Person
   WHERE PersonType = @PersonType

OPEN crTest

FETCH NEXT FROM crTest
INTO 
    @ID,
    @F_NAME,
    @M_NAME,
    @L_NAME

WHILE @@FETCH_STATUS = 0
BEGIN

   INSERT INTO #TEMP_TABLE
   VALUES(
        @ID,
        @F_NAME + ' ' + ISNULL(@M_NAME,'') + ' ' + @L_NAME,
        LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME),
        'false'
   )

   IF LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME) > @MAX_LENGTH
        SET @MAX_LENGTH = LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME)

   FETCH NEXT FROM crTest
   INTO 
        @ID,
        @F_NAME,
        @M_NAME,
        @L_NAME

END

CLOSE crTest
DEALLOCATE crTest



UPDATE #TEMP_TABLE
SET MAX_LENGTH = 'true'
WHERE NAME_LENGTH = @MAX_LENGTH


SELECT * FROM #TEMP_TABLE ORDER BY 4 desc

作成したストアドプロシージャはオブジェクトエクスプローラーで存在を確認できます。
f:id:hanabusa-snow:20201219084215p:plain

ちなみにここから変更ボタンをおすとALTER PROCEDUREで開いてくれるので便利です。
f:id:hanabusa-snow:20201219084329p:plain

実行してみる

保存されたストアドプロシージャは以下のように実行できます。

EXEC [dbo].[TEST_PROCEDURE] 'EM'

最後の’EM’は引数に対して、値を指定してあげています。
今回はWHERE句に代入される値ということになりますね。

結果はこんな感じ。
f:id:hanabusa-snow:20201219084517p:plain







以上、今回はここまで。