【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

表示します。

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

以上、今回はここまで。