【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

作成したストアドプロシージャはオブジェクトエクスプローラーで存在を確認できます。

ちなみにここから変更ボタンをおすとALTER　PROCEDUREで開いてくれるので便利です。

実行してみる

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

EXEC [dbo].[TEST_PROCEDURE] 'EM'

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

結果はこんな感じ。

以上、今回はここまで。

【SQL Server(連載)】カーソルの応用 -32-

www.pon-x.jp

前回の続き

今回は、カーソルと一時テーブルを組み合わせた使い方を紹介します。

カーソルと一時テーブル

例えば、SELECT文で出力したいのだけれどもものすごく複雑な構造になってしまう場合、一時テーブルでイメージしていた構造をつくってあげて、それに対してカーソルを用いてデータをインポートし、最後に一時テーブルを表示してあげるという手法が考えられます。

では、やってみましょう。
前回同様に、Person.Personテーブルにおいて、フルネームの字数を数えるというカーソルにおいて、一時テーブルに情報を追加していき、最後に表示するというものです。

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

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

構文説明

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(32),
    NAME_LENGTH INT,
    MAX_LENGTH BIT
)

ここでは、一時テーブルを作っています。

1. ID ・・・BusinessEntityIDを入れるものです。
2. FULLNAME ・・・フルネームを入れるものです。
3. NAME_LENGTH　・・・　フルネームの字数を入れるものです。
4. MAX_LENGTH BIT　・・・　最大文字数のレコードは1、そうでないレコードには0を入れます。

カーソル部分は前回同様なので割愛します。

では、つづきの説明です。

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

INSERT INTOで1レコードずつ情報をいれます。
なお、MAX_LENGTHはひとまずすべて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)

@MAX_LENGTHとフルネーム文字数と比較して、フルネーム文字数が@MAX_LENGTHよりおおきければ、@MAX_LENGTHにフルネーム文字数をいれます。（なお@MAX_LENGTHは初期値を0としています）

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

@MAX_LENGTHはフルネーム文字数の最大値がとれているので、そのレコードをWHERE句で抽出し、対象のMAX_LENGTHをtrueに更新します。

SELECT * FROM #TEMP_TABLE ORDER BY 4 desc

表示します。

以上、こんなかんじで自由な一時テーブルがつくれます。
さまざまなアプリケーション開発をする場合は、たまに複雑な出力が求められるので必要になることがあるかも！？

以上、今回はここまで。

【SQL Server(連載)】カーソル -31-

www.pon-x.jp

前回の続き

今回はカーソルについて説明をしていきます。

カーソル

カーソルとは1行ずつデータを取り出してループ処理のなかでデータをハンドリングしていくことを目的とします。

では、さっそくサンプルを示してみたいと思います。 以下は、Person.PersonテーブルからBusinessEntityIDとFirstNameとMiddleNameとLastNameを1行ずつ取り出して表示し、最後にFirstNameとMiddleNameとLastNameの文字数の合計が一番多いレコードについて。BusinessEntityIDをPrint表示するという処理になります。

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

SET @MAX_LENGTH = 0
DECLARE crTest CURSOR FOR
   SELECT 
        BusinessEntityID,
        FirstName,
        MiddleName,
        LastName
   FROM Person.Person

OPEN crTest

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

WHILE @@FETCH_STATUS = 0
BEGIN

   PRINT 'BusinessEntityID : ' + CONVERT(VARCHAR,@ID)
   PRINT 'Name : ' + @F_NAME + '_' + ISNULL(@M_NAME,'') + ' ' + @L_NAME
   PRINT '#---------------------#'

   IF LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME) > @MAX_LENGTH
        BEGIN
            SET @TEMP_ID = CONVERT(VARCHAR,@ID)
            SET @MAX_LENGTH = LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME)
        END
   ELSE IF LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME) = @MAX_LENGTH
        BEGIN
            SET @TEMP_ID = @TEMP_ID + ',' + CONVERT(VARCHAR,@ID)
        END


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

END

CLOSE crTest
DEALLOCATE crTest


PRINT '##########################'

PRINT @TEMP_ID

PRINT '##########################'

こんなかんじに出力できました。

コードの説明

DECLARE crTest CURSOR FOR
   SELECT 
        BusinessEntityID,
        FirstName,
        MiddleName,
        LastName
   FROM Person.Person

上記の説明でカーソルを定義してあげています。
SELECT文の結果を保存しているようなイメージですね。

OPEN crTest

上記で、作成したカーソルを開きます。

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

上記で、カーソルを1行進めます。
つまり、最初の行を取り出して変数に入れるということをしています。

WHILE @@FETCH_STATUS = 0

@@FETCH_STATUS は0 のとき、フェッチが正常に実行できたということ、かみくだくと次のデータが取り出せたということになるので、その間はループを繰り返すという処理になります。

   IF LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME) > @MAX_LENGTH
        BEGIN
            SET @TEMP_ID = CONVERT(VARCHAR,@ID)
            SET @MAX_LENGTH = LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME)
        END
   ELSE IF LEN(@F_NAME) + LEN(ISNULL(@M_NAME,'')) + LEN(@L_NAME) = @MAX_LENGTH
        BEGIN
            SET @TEMP_ID = @TEMP_ID + ',' + CONVERT(VARCHAR,@ID)
        END

@MAX_LENGTHは初期値に0を入れてあります。
文字列の長さが@MAX_LENGTHよりおおきければ、@TEMP_IDにBusinessEntityIDを上書きし、さらに、@MAX_LENGTHを文字列の長さに書き換えます。
文字列の長さは@MAX_LENGTHと同じであれば、、@TEMP_IDにBusinessEntityIDを追記します。

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

上記はさらにカーソルを1行進めます。
ここで進めないとカーソルが進まないまま処理を繰り返すので一生処理が終わりません。

CLOSE crTest
DEALLOCATE crTest

上記は、カーソルを閉じて、その次にカーソルを開放します。
開放しないとメモリを消費しっぱなしになるので注意しましょう。

PRINT '##########################'

PRINT @TEMP_ID

PRINT '##########################'

上記は最大文字数であった名前のBusinessEntityIDを表示しています。

以上です。結構難しくなってきましたがいかがだったでしょうか。
次回はカーソルの応用です。

【SQL Server(連載)】一時テーブル -30-

www.pon-x.jp

前回の続き

今回は、一時テーブルの扱い方について説明します。

一時テーブル

その処理の中において、仮想にデータを保持したいときに扱うのが、一時テーブルです。
一時テーブルは実際にはテーブルを作成せず（実際は tempdbに作成される）に、その処理の中で一時的に作られるテーブルを言います。

一時テーブルは、同一セッション内ののみ有効な「ローカル一時テーブル」と、その他のセッションでも引き継がれるな「グローバル一時テーブル」があり、作り方が異なります。

ローカル一時テーブル

ローカル一時テーブルは、テーブル名の先頭に#をつけます。
例えばこんな感じです。

CREATE TABLE #TEMP_TABLE(
    NUM INT,
    TXT NVARCHAR(10)
)

では、作成した一時テーブルを参照してみましょう。
SSMSで別のタブを開いて以下のSQLを実行してみてください。

SELECT * FROM #TEMP_TABLE

以下の通りエラー表示になり出力はできません。

グローバル一時テーブル

グローバル一時テーブルは、テーブル名の先頭に##をつけます。
例えばこんな感じです。

CREATE TABLE ##TEMP_TABLE(
    NUM INT,
    TXT NVARCHAR(10)
)

では、また作成した一時テーブルを参照してみましょう。
SSMSで別のタブを開いて以下のSQLを実行してみてください。

SELECT * FROM ##TEMP_TABLE

以下の通りセッションをまたいでも出力できることが確認できました。

一時テーブルの削除

通常のテーブル同様にDROPで削除が可能です。
ただし、通常のテーブルと違うのは一時テーブルはあくまで一時つくるもので、処理毎に作成することになるでしょうから、頻繁にテーブルの作成処理が行われることになろうと思います。
その時に、一時テーブルが存在していれば問題なく削除処理ができるのですが、存在しない場合に削除処理を行うとエラーになり以後の処理に至りません。
そこで、一時テーブルを削除する前に、テーブルの存在チェックを行うことをお勧めします。
こんな感じです。

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

CREATE TABLE #TEMP_TABLE(
    NUM INT,
    TXT NVARCHAR(10)
)

これでなんど実行してもエラー出力になることはありません。

以下、適当に一時テーブルで遊んでみました。

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

CREATE TABLE #TEMP_TABLE(
    NUM INT,
    TXT NVARCHAR(20)
)

DECLARE @NUM INT
DECLARE @VALUE BIGINT

SET @NUM = 1

WHILE @NUM < 10
    BEGIN
        SET @VALUE = POWER(10, @NUM)

        INSERT INTO #TEMP_TABLE
        VALUES
        (RAND() * @VALUE, 'UNDER ' + CONVERT(VARCHAR, @VALUE))
        SET @NUM += 1
    END


SELECT * FROM #TEMP_TABLE

0以上、かつ10の累乗のデータを追加した一時テーブルを作成します。
作成毎に値が変わるので、試してみてください。

こんなかんじ。

以上、今回はここまで

【SQL Server(連載)】テーブル関数 -29-

www.pon-x.jp

前回の続き

今回からテーブル関数について紹介していきます。

テーブル関数とは

前回は単一の値を返す、スカラ関数について説明しましたが、今回紹介するテーブル関数はその名の通りテーブルを返す関数です。
なんかイメージしにくいですよね。
ひとまず、お付き合いください。 きっと出力をみたらイメージがわくはずです。

テーブル関数の作り方

テーブル関数はこんな風に作ります。

CREATE FUNCTION [関数名] (
    [ @引数名 ] [データ型],
    ・・・
)  
RETURNS [ @戻値となるテーブル名] TABLE (
    [カラム名] [データ型] [NULL or NOT NULL],
    ・・・
)
AS
BEGIN

    --##############################--
    --　戻値となるテーブルに値をINSERTする
    --##############################--

    RETURN

END

こんな感じに定義できます。

実際に作ってみよう。

いよいよ実際に作ってみます。
今回は引数を２つつけて累乗の計算をするテーブルを返す関数を作ってみます。

CREATE FUNCTION TEST_FUN (
    @NUM INT,
    @POW INT
)  
RETURNS @TEST_TABLE TABLE(
    NUM BIGINT,
    TXT NVARCHAR(10)
)
AS
BEGIN

    DECLARE @CNT INT

    SET @CNT = 1

    WHILE @CNT <= @POW
        BEGIN
            INSERT INTO @TEST_TABLE
            VALUES(
                POWER(@NUM, @CNT),
                CONVERT(VARCHAR,@NUM) + '^' + CONVERT(VARCHAR,@CNT)
            )

            SET @CNT += 1
        END

    RETURN

END

こんな感じです。

簡単に説明しますね。
＠NUMの累乗を求めるのですが、1乗から@POW乗までを計算するというものです。
WHILE文は＠CNTが１から@POWまで繰り返すのですが、計算した結果をINSERT文でテーブルに入れてあげるのを繰り返すといったことをしてあげています。

では実行してみましょう。

SELECT * FROM TEST_FUN(2, 30)

こんな感じに出力されます。

その他

作ったテーブル関数の参照の仕方はスカラ変数と同じで、オブジェクトエクスプローラーから閲覧できます。

そのた、ALTERやDROPもスカラ関数と同じですので、説明は省略させていただきます！！





以上、今回はここまで。

【SQL Server(連載)】WHILE文 -28-

www.pon-x.jp

前回の続き

今回は繰り返し処理を行うためのWHILE文を紹介します。

WHILE文

繰り返し処理はWHILEの後に条件式をかいて、条件式がTRUEな限り繰り返し処理を行います。

こんな感じです。

WHILE [条件式]
BEGIN

   -- ##################
   -- 何かしらの処理
   -- ##################

END

具体的に書いてみましょう。
1から10までをPRINTでコメント出力してみます。

DECLARE @CNT INT

SET @CNT = 1

WHILE @CNT <= 10
    BEGIN
        PRINT @CNT

        SET @CNT += 1
    END

こんな感じに出力されました。

CONTINUE

繰り返し処理の中で特定の処理をスキップすることができます。
この時、CONTINUEを使います。

WHILE [条件式]
BEGIN

   IF 条件式
         CONTINUE

   -- ##################
   -- 何かしらの処理
   -- ##################

END

試してみます。
1から10のうち5を抜かしてコメント出力します。

DECLARE @CNT INT

SET @CNT = 1

WHILE @CNT <= 10
    BEGIN
        IF @CNT = 5
            BEGIN
                SET @CNT += 1

                CONTINUE
            END

        PRINT @CNT

        SET @CNT += 1
    END

こんな感じに出力できます。

@CNTが5の時にPRINT @CNTが処理されていないことがわかりますね。

BREAK

繰り返し処理において、途中で処理を終了させてあげたいときにBREAKを使うことになります。

WHILE [条件式]
BEGIN

   IF 条件式
         BREAK

   -- ##################
   -- 
何かしらの処理
   -- ##################

END

では試してみます。
1から10までの繰り返し処理において、5までで終了させてみます。

DECLARE @CNT INT

SET @CNT = 1

WHILE @CNT <= 10
    BEGIN
        PRINT @CNT

        IF @CNT = 5
            BREAK

        SET @CNT += 1
    END

こんな感じに出力されます。

@CNTが5の時に、処理が終了されたのがわかると思います。

以上、今回はここまで。
次回は繰り返し処理を使った例も扱っていきたいと思います。

【SQL Server(連載)】スカラ関数の活用 -27-

www.pon-x.jp

前回の続き

前回はスカラ―関数をつくりました。
今回はこれの応用です。

スカラ―関数の定義変更

まず、定義したスカラ―関数をどこで確認すればよいかということですが、
オブジェクトエクスプローラーから、
[プログラミング]→[関数]→[スカラー値関数]を展開すると確認できます。

では、前回作ったJP_YEARを開いて確認してみます。
[右クリック]→[変更]を押してください。

こんな感じで展開されます。

ビューでやった時と同じで、すでにALTER定義がされているので、それ以下を変更して実行するだけで定義変更が可能です。

使い方の応用

スカラ―変数の使い方の応用です。
例えば、SELECT句で使うにはこんなことが考えられます。

SELECT 
    ModifiedDate,
    dbo.JP_YEAR(ModifiedDate) AS NENDO 
FROM Person.Person

こんな感じに、カラムの値に対してすべてを演算してくれます。

こんなこともできます。

SELECT 
    ModifiedDate
FROM Person.Person
WHERE dbo.JP_YEAR(ModifiedDate) > 2010

カラムごとの演算結果をWHERE句で比較してあげることができるのです。

さらにこんなことも

SELECT 
    ModifiedDate,
    dbo.JP_YEAR(ModifiedDate)
FROM Person.Person
ORDER BY dbo.JP_YEAR(ModifiedDate)

カラムごとの演算結果でソートしてあげることも可能です。

もちろんSELECT文以外でもよくて例えば、 UPDATE文やINSERT文に使うことも考えられます。

こんなかんじにSQLを単純にかくために関数って便利ですよね。
以上、今回はここまで。