【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を表示しています。

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