Transact-SQLを使用したデータベース開発に役立つ13のヒント

T-SQLによる業務用アプリケーション開発のケーススタディ

この記事では、現場で実際にデータベースを使用するときに直面するさまざまな状況を取り上げ、それらの問題にTransact-SQLとSQL-92を使って対処する方法を紹介します。

はじめに

　アプリケーション開発者の多くは、SQL Server 2000を使用する中で、厄介な問題に直面します。たとえば、大量のデータの取得とグループ化、結果セットの作成、データ変更の追跡などです。いずれも、無限に近い選択肢の中から、絶妙かつ妥当な戦略をとる必要があります。その際に、ヒントがあれば助けとなるはずです。ここでは、初級開発者と熟練開発者の両方に役立つT-SQLのヒントをいくつか紹介します。

目的

　本稿は「The Baker's Dozen」シリーズの一記事です。「The Baker's Dozen」シリーズの主目的の1つは、特定の技術についてもっと上達したいと考えている人に対してヒントを提供することです。経験豊富な方にも、何かの役に立つヒントが1つくらいあるのではないかと思います。本シリーズの名前はVan Amsterdamというパン屋の話に由来しますが、私もこの数か月間、開発者の皆さんのお口に合うようなヒント集を焼き上げるためにいろいろ努力してきました。

　大部分のヒントの解説では、まず業務アプリケーションの要件を1つ提示し、それを解決するためのTransact-SQLのコードを紹介するという形を取ります。今回紹介するヒントは次のとおりです。

• 単一のストアドプロシージャから複数の結果セットを返す［1］
• ユーザー定義関数（UDF：User Defined Function）を作成して、スカラー値を返す［2］
• 選択キーのコンマ区切りリストを解析するユーザー定義関数を作成し、それ以降のJOINステートメントで使用できるテーブルを返す［3］
• LIKEを使用してテキスト検索ルーチンを作成する［4］
• CASE構文を理解し、これを使用して実行時に条件を評価する［5］
• サブクエリと派生テーブルを使用して要件に対処する方法を示し、これらの手法を比較検討する［6］
• テーブル変数を使用し、テーブル変数と一時テーブルの違いを理解する［7］
• DATEPART関数を使用して、週の末日に基づいて日別データを集計する［8］
• CASTやCONVERTなどの関数を使用して、異なるデータ型を併用する［9］
• トリガを使用して、データベースの行が変更されたときに行う固有のアクションを定義する［10］
• トリガを拡張して、基本的な監査証跡機能を実装する［11］
• ダイナミックSQLを使用して、実行時までキー条件が不明なクエリを作成する［12］
• 特定のデータベースから、テーブルおよび列のリストを取得する［13］

　では、実際に13のヒントを見ていきましょう。

ヒント1：複数の結果セットを返す

　要件：ある顧客のすべての注文情報を返す簡単なストアドプロシージャを作成する必要があります。結果セットには、「注文ヘッダー」「注文詳細」「注文メモ」という3つのテーブルを含めなくてはなりません。

　SQL Server 2000の単一のストアドプロシージャで複数の結果セットを返すのは簡単です。ストアドプロシージャのSQL SELECTステートメントでは、明示的な出力宣言（テーブルや変数などに対する出力）がない場合には、結果は呼び出し元のルーチンに返るというのが一般的な規則です。

　リスト1に示すのは、1つの引数（顧客のアカウントに対応する整数キー）を持つ、基本的なストアドプロシージャです。このプロシージャでは、「注文ヘッダー」「注文詳細」「注文メモ」の各テーブルに対してクエリを行っています。このプロシージャでは、table型の変数を作成し、3つのクエリすべてで使用する注文キーのリストを格納しています。テーブル変数の詳細については、ヒント7で説明します。

リスト1：複数の結果セットを返す単純なストアドプロシージャ

CREATE PROCEDURE GetOrdersByAcct (@iAcctKey integer) AS
   -- Retrieve Orders for a single customer
      -- Since we're querying the order table three times,
      -- let's create a table variable (@tOrderList) of all
   -- order keys for the specific customer. That way, we
   -- can JOIN off the temporary order list 
   DECLARE @tOrderList TABLE (OrderKey integer)
      INSERT INTO @tOrderList 
         SELECT OrderKey FROM OrderHdr 
            WHERE AcctKey = @iAcctKey
      SELECT OH.*, AM.AcctName 
         FROM OrderHdr OH
         JOIN AcctMast    AM ON AM.AcctKey = OH.AcctKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OH.OrderKey
      SELECT OD.*, PM.ProdName
         FROM OrderDtl OD
         JOIN ProdMast PM ON PM.ProdKey = OD.ProdKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OD.OrderKey
      SELECT * FROM OrderNotes ON
         JOIN @tOrderList TMP ON TMP.OrderKey = ON.OrderKey
   GO

　これら3つのクエリには出力先が指定されていないので、結果は呼び出し元のプロシージャに返ります。このストアドプロシージャはクエリアナライザのスクリプトから実行でき、3つの結果セットが表示されます。このストアドプロシージャをC#から呼び出す場合には、データアダプタを使用して、結果セット（データセット）に対して3つのデータテーブルを次のように格納します。

SqlDataAdapter oDa =
   new SqlDataAdapter(cSQLString, oMyConn);
oDa.Fill(DsReturn,"MyResults");
// Table 0 will contain the order headers
// Table 1 will contain the order details
// Table 2 will contain the order notes

　ストアドプロシージャの呼び出しでは、何らかの種類のデータアクセス手法を用いるのが普通です。「The Baker's Dozen」シリーズでも、分散コンピューティング環境におけるこれらの種類の開発手法についていずれ解説します。

ヒント2：ユーザー定義関数で値を返す

　要件：締め日に基づいて顧客の借方と貸方の和を求め、顧客残高として返す必要があります。この計算を、複数のクエリで使用したいと考えています。

　SQL Server 2000では、開発者がユーザー定義関数を作成して呼び出すことができます。ユーザー定義関数を使用すると、ロジックと機能をカプセル化して、スカラー値を返すことができ、その値を複数のクエリで使用できます。リスト2は、顧客と締め日に応じて借方と貸方のテーブルから和を求めて結果を返す簡単なユーザー定義関数です。

リスト2：顧客残高を返すユーザー定義関数

CREATE FUNCTION dbo.GetCustomerBalance  (@iAcctKey integer, 
                        @dCutOffDate DateTime)
   -- Calculate and return balance for a Customer, 
   -- for a given point in time
   RETURNS Decimal AS  
   BEGIN
      DECLARE @nCreditAmount Decimal
      DECLARE @nDebitAmount  Decimal
      SET @nCreditAmount = (SELECT SUM(Amount) FROM Credits 
                  WHERE AcctKey = @iAcctKey
                  AND CompDate <= @dCutOffDate)
      SET @nDebitAmount = (SELECT SUM(Amount) FROM Debits
                  WHERE AcctKey = @iAcctKey
                  AND CompDate <= @dCutOffDate)
   -- depending on how data is stored, you may want to 
      -- handle the signs differently
      RETURN (ISNULL(@nCreditAmount,0) - ISNULL(@nDebitAmount,0))
   END

　この関数はクエリ内で次のように使用できます。

DECLARE @dCutOffDate DATETIME
SET @dCutOffDate = CAST('11/30/2004' AS DateTime)
SELECT CustID, dbo.GetCustomerBalance(CustID, @dCutOffDate)
AS CustBalance
FROM...

　顧客残高を必要とするクエリがアプリケーション内に5つある場合には、それぞれの場所でこのユーザー定義関数を使用すれば済みます。同じコードを各クエリに含める必要はありません。

　もちろんこれは、ユーザー定義関数の基本的な使用例に過ぎません。次は少しひねりを加えてみましょう。今回の残高計算ユーザー定義関数では、COMPDATE列に基づいて、「Debits」テーブルと「Credits」テーブルをクエリしています。ここでもし、「Debits」テーブルと「Credits」テーブルに、レコードの各段階を示す3つの日付、つまり、振り出し段階の日付を表すDRDATE、進行中の日付を表すWIPDATE、完了日付を表すCOMPDATEが格納されているとしたらどうでしょうか。残高計算ユーザー定義関数は、レコードのステータスを調べたうえで、どの日付と締め日を比較すべきかを判断しなくてはなりません。

　この残高計算ユーザー定義関数で、一連のIFステートメントまたはCASEステートメントを使用してステータスを読み取り、対応する日付列を使用するという方法も不可能ではありません。しかし、同じロジックが他のプロセスでも必要になる可能性があります。リスト3は、別のユーザー定義関数の例です。顧客のレコードを取得して、顧客のステータスに応じた適切な日付を返します。

リスト3：ステータスに基づいて日付を使用するユーザー定義関数

CREATE FUNCTION dbo.GetDate (@nStatus integer, @dDrDate DateTime
               @dWIPDate DateTime, @dCompDate DateTime) 
   RETURNS DateTime
   AS
   BEGIN
      -- This returns one of three dates, based on a status
      DECLARE @dReturnDate DateTime
      SET @dReturnDate = (SELECT CASE
                  WHEN @nStatus = 1 THEN @dDrDate
                  WHEN @nStatus = 2 THEN @dWIPDate
                  WHEN @nStatus = 3 THEN @dCompDate
                  ELSE @dDrDate
               END)
      RETURN (ISNULL(@dReturnDate,CAST('01-01-1901' AS DateTime))) 
   END

　これなら、次のように記述する代わりに、

AND CompDate <= @dCutOffDate

　次のように記述することができます。

AND dbo.GetDate(Status,DrDate,WIPDate,CompDate)
   <= @dCutOffDate

　もちろん、この場合には、元々のクエリがユーザー定義関数を呼び出し、そこからさらに別のユーザー定義関数を呼び出すことになります。コードを切り分ける方がよいかどうかは、パフォーマンスに照らしたうえで考察する必要があります。このロジックを使用して日付を判断するクエリや関数が他にない場合には、この2番目のユーザー定義関数は必要ないかもしれません。この判断は、アプリケーションのすべての技術要件をはっきりさせたうえで行う方がよいでしょう。

　SQL Server 2000のユーザー定義関数にはいくつかの制約があるという点は頭に入れておいてください。既存のテーブルに含まれているデータをユーザー定義関数の中で変更することや、ユーザー定義関数の中で一時テーブルを作成することはできません。ユーザー定義関数の一般的な目的は、コードを利用して値を返すことであり、データを変更することではないのです。

ヒント3：ユーザー定義関数でテーブルを返す

　要件：ヒント1で、単一のアカウントに対応する複数の結果セットを返すストアドプロシージャを実装しました。今度は、この要件を拡張する必要が生じたとしましょう。つまり、ユーザーが選択するアカウントが1個でも、10個でも、あるいは50個でも、それらのアカウントすべてに対応する注文情報を表示できるようにしたいとします。

　開発者によっては、INステートメントと、動的SQLや一時テーブルなどの手法を組み合わせて、この状況に対処する人もいます。しかし、ユーザー定義関数は、スカラー値のみならずテーブルも返すことができます。その機能を使えば、可変個のアカウントに対してストアドプロシージャを実行するというこの要件に、もっとすっきりと対応できます。具体的には、ヒント1のストアドプロシージャに手を加え、次のようにします。

• 選択されたアカウントのリストを、たとえば「1,2,17,912,1091」のようなコンマ区切りのパラメータとしてストアドプロシージャに渡すようにします。
• ユーザー定義関数を作成します。その中では、コンマ区切りのキーのリストを受け取り、リストに含まれている各エントリをそれぞれ1つの行の整数キーとして格納したテーブル変数を作成して、そのテーブル変数を返します（リスト4を参照）。「Orders」テーブルに対するJOINの中でユーザー定義関数を呼び出し、アカウントのリストに対応する注文情報を取得します。

リスト4：キーセットをテーブル変数に変換するユーザー定義関数

CREATE FUNCTION dbo.CsvToTable ( @cList varchar(8000)) 
   -- Converts a comma-delimited list of integer keys to a TABLE
   -- Used to take a variable list of selections               
   -- (accounts, products, etc.) and put them into a TABLE that can
   -- be used in a subsequent JOIN
   RETURNS @IntKeyTable TABLE
      (IntKey INT)
   AS
   BEGIN
      DECLARE @nPosition INT
      DECLARE @cTempValue VARCHAR(8000) 
      DECLARE @nIntKey int
      SET @cList = RTRIM(@cList) + ','
      -- So right now we might have  '1111,2222,'
      -- (Careful if the CSV already ended with a comma,
      -- you'll wind up with an extra 0 in the key table)
      -- see if comma exists in list
      -- (use PATINDEX to return pattern position within a string
      WHILE PATINDEX('%,%' , @cList) <> 0     
      BEGIN
         -- get the position of the comma
         SELECT @nPosition =  PATINDEX('%,%' , @cList)    
         -- get the key, from beginning of string to the comma
         SELECT @cTempValue = LEFT(@cList, @nPosition - 1)   
         SET @nIntKey = CAST(@cTempValue AS INT)
         -- Write out to the Keys table (convert to integer)
         INSERT INTO @IntKeyTable 
            VALUES (@nIntKey)      
         -- wipe out the value we just inserted  
         SELECT @cList = STUFF(@cList, 1, @nPosition, '')     
      END
      RETURN
   END

　開発者は、可変個のアカウントや製品、あるいはその他の可変個のキーに対してクエリを実行するときに、このユーザー定義関数をいつでも利用できます。リスト5は、ヒント1のストアドプロシージャを修正し、ユーザー定義関数を使用するようにしたバージョンです。

リスト5：テーブル値を返すユーザー定義関数を使用して複数のアカウントをクエリする

CREATE PROCEDURE GetOrdersByMultipleAccts 
         @cAcctList varchar(8000) AS
   -- Retrieve Orders for a single customer
      DECLARE @tOrderList TABLE (OrderKey integer)
      INSERT INTO @tOrderList 
         SELECT OrderKey FROM OrderHdr OH
            JOIN dbo.CsvToTable(@cAcctList) IK
               ON IK.IntKey = OH.AcctKey
      SELECT OH.*, AM.AcctName 
         FROM OrderHdr OH
         JOIN AcctMast    AM ON AM.AcctKey = OH.AcctKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OH.OrderKey
      SELECT OD.*, PM.ProdName
         FROM OrderDtl OD
         JOIN ProdMast PM ON PM.ProdKey = OD.ProdKey
         JOIN @tOrderList TMP ON TMP.OrderKey = OD.OrderKey
      SELECT * FROM OrderNotes ON
         JOIN @tOrderList TMP ON TMP.OrderKey = ON.OrderKey
   GO

　なお、リスト4のユーザー定義関数が持つvarcharパラメータは、最大8,000バイトです。コンマ区切りリストで渡すのが整数のキー値だとすると、数百個のキーであれば、varcharの8,000バイトの制限に達しないで渡せるはずです。もっと大きなデータ（数千個のキーや、文字サイズの大きなデータ型など）が必要な場合には、XMLなど他の手法を検討する必要があります。

ヒント4：LIKEを使用した検索の実行

　要件：求人情報アプリケーションのユーザーから、キーワード検索に合致した情報すべてを一覧表示する機能を追加してほしいという要望がありました。

　T-SQLのLIKEコマンドを使用すると、文字列内のパターンに合致するものを検索できます。たとえば、ユーザーが、「Windows XP、FrontPage等のスキル」という表現が含まれているメモ列から、「XP」という単語を検索したいとします。開発者は、LIKEコマンドと、ワイルドカードのパーセント文字（%）を使用して、パターンマッチングを実行できます。

　LIKEには、実行する検索の種類に応じて、複数の使用方法があります。たいていは、列内のいずれかの場所に含まれているパターンを調べるという検索です。しかし、列の先頭が検索パターンに合致する行だけを検索することが必要な場合もあります。あるいは、列の末尾が検索パターンに合致する行だけを検索したい場合もあります。

-- Search anywhere in the column
SELECT * FROM Applicants WHERE
   Skills LIKE '%XP%' 
-- Search where skills begins with XP
SELECT * FROM Applicants WHERE
   Skills LIKE 'XP%'
-- Search where skills ends with XP
SELECT * FROM Applicants WHERE
   Skills LIKE '%XP'

　また、単一のワイルドカード文字を表すアンダーバー（_）も使用できます（たとえばName LIKE '_EVIN'）。

ヒント5：CASEの利用

　要件：顧客の口座収支を、標準の日付区分（1～30日、31～60日等）に基づいて取りまとめた口座年齢表を生成する必要があります。また、口座ごとや収支の種類ごとの集計も必要です。

　ヒント2で、CASEの基本的な機能を使用して顧客のステータスコードを評価し、対応する日付値を返すという例が出てきました。今度は、CASEを使用して要件を満たす別の例を見てみましょう。

　開発ソリューションによっては、詳細な結果セットや一部のみが集計された結果セットを取得し、それ以上の集計はビジネスコンポーネントで行うという方法をとる場合があります。その方法でも間違いではないですが、実はSQL Server 2000だけでも最終的に必要な結果セットを構築できます。たとえば、ソリューションによっては、SQLから口座データを取得し、その結果を処理して、適切な日付区分に取りまとめるという方法をとっている場合があります。リスト6は、口座の各収支を、対応する日付区分に分類する例です。CASEステートメントを使用して、日付範囲（たとえば「基準日－90」日と「基準日－61」日の間かどうか）に照らして日付列を評価することにより、対応する区分に分類できます。

　これにより、顧客の口座収支を日付区分で分類するという最初の要件は達成されます。また、CASEステートメントをGROUP BY句で使用すれば、口座や種類ごとに結果の和を求めるという処理も可能です。動的SQLや、わずかに異なるだけの複数のクエリを作成することを完全に避けるのは困難ですが、CASEを効果的に使用すれば、そうした必要性を確実に減らせます。

リスト6：CASEを何回も使用して結果セットを作成する

CREATE PROCEDURE GetAgingResults (@cAcctList varchar(8000), 
            @dAgeDate DateTime, @nGroupOption int)
   AS
   SELECT CASE WHEN @nGroupOption = 1 THEN CT.Description
            WHEN @nGroupOption = 2 THEN AM.Description
    END AS GroupDesc,
   -- Set up CASE statements for the aging brackets
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-30 AND @dAgeDate       
      THEN CBalance ELSE 0 END) AS Age30 ,
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-60 AND @dAgeDate-31  
      THEN CBalance ELSE 0 END) AS Age60 ,
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-90 AND @dAgeDate-61  
         THEN CBalance ELSE 0 END) AS  Age90 ,
       SUM(CASE WHEN CDate BETWEEN @dAgeDate-120 AND @dAgeDate-91  
         THEN CBalance ELSE 0 END) AS  Age120 ,
       SUM(CASE WHEN CDate < @dAgeDate - 120 
         THEN CBalance ELSE 0 END) AS  AgeGT120, 
       SUM(CASE WHEN CDate > @dAgeDate
               THEN CBalance ELSE 0 END) AS NotAged,
       SUM(CBalance) AS TotBalance
   FROM OpenBalances OB
      JOIN dbo.CsvToTable(@cAcctList) IK   
        ON IK.IntKey = OB.AcctKey   
      JOIN Category CT 
         ON CT.CatCode = OB.CatCode   
   JOIN AcctMast AM
         ON AM.AcctKey = OB.AcctKey
      GROUP BY 
      CASE WHEN @nGroupOption = 1 THEN CT.Description
              WHEN @nGroupOption = 2 THEN AM.Description 
   END
      ORDER BY TotBalance DESC