建立函數以擷取變更資料

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

)

預存程序的所有參數都是選擇性的。如果您在不提供任何參數值的情況下呼叫預存程序，預存程序就會為您可存取的所有擷取執行個體建立包裝函式。

預存程序永遠會產生一個包裝函式來傳回每個擷取執行個體的所有變更。如果 @supports_net_changes 參數在建立擷取執行個體時設定，預存程序也會產生一個包裝函式來傳回每個適用之擷取執行個體的淨變更。

預存程序會傳回包含兩個資料行的結果集：

• 預存程序產生之包裝函式的名稱。此預存程序會從擷取執行個體的名稱衍生函數名稱 (函數名稱為 '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 指令碼，然後再執行這些指令碼。

當您的封裝呼叫包裝函式時，該封裝必須提供三個參數的值。這三個參數就像異動資料擷取函數所使用的三個參數。這三個參數如下所示：

• 間隔的開始日期/時間值和結束日期/時間值。當包裝函式使用日期/時間值做為查詢間隔的端點時，異動資料擷取函數會使用兩個 LSN 值做為端點。

• 資料列篩選器。對於包裝函式與異動資料擷取函數而言，@row_filter_option 參數相同。如需詳細資訊，請參閱＜cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)＞和＜cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)＞。

包裝函式所傳回的結果集包含下列資料：

• 異動資料的所有要求資料行。

• 名稱為 __CDC_OPERATION 的資料行使用一或兩個字元欄位來識別與資料列關聯的作業。此欄位的有效值如下：‘I’ 用於插入、‘D’ 用於刪除、‘UO’ 用於更新舊值，而 ‘UN’ 用於更新新值。

• 當您要求時，更新顯示為作業碼後之位元資料行的旗標，並以 @update_flag_list 參數中指定的順序顯示。這些資料行的命名方式是將 ‘_uflag’ 附加到關聯的資料行名稱。

如果您的封裝呼叫查詢所有變更的包裝函式，該包裝函式也會傳回 __CDC_STARTLSN 和 __CDC_SEQVAL 資料行。這兩個資料行會分別成為結果集的第一和第二個資料行。此包裝函式也會根據這兩個資料行，排序結果集。

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> 函數會針對每個變更資料列傳回四個中繼資料的資料行。如果您要在資料流程中使用這些值，您可以傳回它們，當做資料表值包裝函式的其他資料行。

如需詳細資訊，請參閱＜cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)＞。