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