変更データを取得する関数の作成

sys.sp_cdc_generate_wrapper_function システム ストアド プロシージャでは、Integration Services パッケージで使用するためのラッパー関数を作成するスクリプトが生成されます。

次に、ストアド プロシージャの定義に含まれる最初の数行を示します。

CREATE PROCEDURE sys.sp_cdc_generate_wrapper_function

(

@capture_instance sysname = null

@closed_high_end_point bit = 1,

@column_list = null,

@update_flag_list = null

)

ストアド プロシージャのパラメーターはすべて省略可能です。どのパラメーターにも値を指定せずにストアド プロシージャを呼び出すと、ストアド プロシージャでは、アクセス権が与えられているすべてのキャプチャ インスタンスに対してラッパー関数が作成されます。

注
このストアド プロシージャの構文とそのパラメーターの詳細については、「sys.sp_cdc_generate_wrapper_function (Transact-SQL)」を参照してください。

ストアド プロシージャでは、常に、各キャプチャ インスタンスからすべての変更を返すラッパー関数が生成されます。キャプチャ インスタンスの作成時に @supports_net_changes パラメーターを設定した場合は、該当する各キャプチャ インスタンスから差分変更を返すラッパー関数も生成されます。

このストアド プロシージャによって返される結果セットには、次の 2 つの列が含まれています。

• ストアド プロシージャによって生成されたラッパー関数の名前。このストアド プロシージャでは、キャプチャ インスタンス名から関数名を取得します (関数名は、'fn_all_changes_' の後にキャプチャ インスタンス名が続きます。差分変更追跡の関数が生成された場合、その関数に使用されるプレフィックスは 'fn_net_changes_' です)。

• ラッパー関数の CREATE ステートメント。

通常、開発者は、INSERT...EXEC ステートメントを使用して sys.sp_cdc_generate_wrapper_function ストアド プロシージャを呼び出し、ストアド プロシージャによって作成されるスクリプトを一時テーブルに保存します。その後、各スクリプトを個別に選択して実行し、対応するラッパー関数を作成できます。ただし、次のサンプル コードに示すように、開発者は、一連の SQL コマンドを使用して、すべての CREATE スクリプトを実行することもできます。

create table #wrapper_functions
      (function_name sysname, create_stmt nvarchar(max))
insert into #wrapper_functions
exec sys.sp_cdc_generate_wrapper_function

declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for 
      select create_stmt from #wrapper_functions
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
      exec sp_executesql @stmt
      fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions

キャプチャした変更データのタイムラインを体系的に進めるために、生成されたラッパー関数では、ある期間の @end_time パラメーターがその次の期間の @start_time パラメーターになることを想定しています。この規則に従うと、生成されたラッパー関数では次のタスクを実行できます。

• 内部で使用される LSN 値に日付/時刻値をマップします。

• データの欠落や重複を防ぎます。

変更テーブルのすべての行に対するクエリの実行を簡略化するために、生成されたラッパー関数では、次の規則もサポートされています。

• @start_time パラメーターが NULL の場合、ラッパー関数では、キャプチャ インスタンスの最小 LSN 値がクエリの下限として使用されます。

• @end_time パラメーターが NULL の場合、ラッパー関数では、キャプチャ インスタンスの最大 LSN 値がクエリの上限として使用されます。

ほとんどのユーザーは、sys.sp_cdc_generate_wrapper_function システム ストアド プロシージャによって作成されたラッパー関数を、変更することなく使用できます。ただし、ラッパー関数をカスタマイズするには、CREATE スクリプトを実行する前にカスタマイズする必要があります。

パッケージでは、ラッパー関数を呼び出すときに、3 つのパラメーターの値を指定する必要があります。この 3 つのパラメーターは、変更データ キャプチャ関数で使用される 3 つのパラメーターに似ています。この 3 つのパラメーターは次のとおりです。

• 期間の開始日時の値と終了日時の値。ラッパー関数では、クエリ範囲のエンドポイントとして日付/時刻値が使用されますが、変更データ キャプチャ関数では、エンドポイントとして 2 つの LSN 値が使用されます。

• 行フィルター。ラッパー関数でも変更データ キャプチャ関数でも、@row_filter_option パラメーターは同じものが使用されます。詳細については、「cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)」および「cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)」を参照してください。

ラッパー関数から返される結果セットには、次のデータが含まれます。

• 変更データの要求されたすべての列。

• 行に関連付けられている操作を示すために 1 文字または 2 文字のフィールドを使用する、__CDC_OPERATION という名前の列。このフィールドに有効な値は、'I' (挿入)、'D' (削除)、'UO' (古い値の更新)、および 'UN' (新しい値の更新) です。

• 操作コードの後に、@update_flag_list パラメーターで指定された順にビット列として表示される更新フラグ (要求時)。これらの列には、関連する列名に '_uflag' が追加された名前が付けられています。

パッケージで、すべての変更に対してクエリを実行するラッパー関数を呼び出すと、そのラッパー関数は __CDC_STARTLSN 列と __CDC_SEQVAL 列も返します。この 2 つの列はそれぞれ、結果セットの 1 番目の列と 2 番目の列になります。また、ラッパー関数は、この 2 つの列に基づいて結果セットを並べ替えます。

CREATE function CDCSample.uf_Customer (
     @start_time datetime
    ,@end_time datetime
)
returns @Customer table (
     CustomerID int
    ,TerritoryID int
    ,CustomerType nchar(1)
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
) as
begin
    declare @from_lsn binary(10), @to_lsn binary(10)

    if (@start_time is null)
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')
    else
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

    if (@end_time is null)
        select @to_lsn = sys.fn_cdc_get_max_lsn()
    else
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)

    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
        return

    -- Query for change data
    insert into @Customer
    select 
        CustomerID,    
        TerritoryID, 
        CustomerType, 
        rowguid, 
        ModifiedDate, 
        case __$operation
                when 1 then 'D'
                when 2 then 'I'
                when 4 then 'U'
                else null
         end as CDC_OPERATION
    from 
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')

    return
end 
go

前述のユーザーが作成したテーブル値関数では __$operation 列のみが使用されていますが、cdc.fn_cdc_get_net_changes_<capture_instance> 関数では、各変更行の 4 つのメタデータ列を返します。これらの値をデータ フローで使用する場合は、テーブル値ラッパー関数から追加の列として値を返すことができます。

詳細については、「cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)」を参照してください。