前回の続き
今回はカーソルについて説明をしていきます。
カーソル
カーソルとは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を表示しています。
以上です。結構難しくなってきましたがいかがだったでしょうか。
次回はカーソルの応用です。