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
)
ここでは、一時テーブルを作っています。
- ID ・・・BusinessEntityIDを入れるものです。
- FULLNAME ・・・フルネームを入れるものです。
- NAME_LENGTH ・・・ フルネームの字数を入れるものです。
- 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
表示します。
以上、こんなかんじで自由な一時テーブルがつくれます。
さまざまなアプリケーション開発をする場合は、たまに複雑な出力が求められるので必要になることがあるかも!?
以上、今回はここまで。