Создание функции для получения измененных данных

Системная хранимая процедура 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, хранимая процедура также создаст функцию-оболочку для возврата суммарных изменений из каждого применимого экземпляра системы отслеживания.

Хранимая процедура возвращает результирующий набор с двумя столбцами.

• Имя функции-оболочки, созданной хранимой процедурой. Эта хранимая процедура присваивает функции имя на основе имени экземпляра системы отслеживания. Имя функции состоит из префикса «fn_all_changes_», за которым следует имя экземпляра системы отслеживания. Для функции суммарных изменений используется префикс «fn_net_changes_».

• Инструкция CREATE для функции-оболочки.

Обычно для вызова хранимой процедуры sys.sp_cdc_generate_wrapper_function используется инструкция INSERT...EXEC, а созданные процедурой сценарии сохраняются во временной таблице. Затем каждый скрипт можно выбрать отдельно и создать с его помощью соответствующую функцию-оболочку. Однако разработчик также может воспользоваться набором команд 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_<экземпляр_отслеживания> (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_<отслеживаемый_экземпляр> возвращает четыре столбца с метаданными для каждой строки изменений. Если нужно использовать эти значения в потоке данных, можете возвратить их как дополнительные столбцы с помощью возвращающей табличное значение функции оболочки.

Дополнительные сведения см. в разделе cdc.fn_cdc_get_net_changes_<экземпляр_отслеживания> (Transact-SQL).