Criando a função para recuperar os dados de alteração

O procedimento armazenado de sistema sys.sp_cdc_generate_wrapper_function gera scripts para criar funções de invólucro para uso por pacotes Integration Services.

Estas são as primeiras linhas da definição do procedimento armazenado:

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

)

Todos os parâmetros do procedimento armazenado são opcionais. Se você chamar o procedimento armazenado sem fornecer valores para nenhum dos parâmetros, o procedimento armazenado criará funções de invólucro para todas as instâncias de captura às quais você tem acesso.

Observação
Para obter mais informações sobre a sintaxe desse procedimento armazenado e seus parâmetros, consulte sys.sp_cdc_generate_wrapper_function (Transact-SQL).

O procedimento armazenado sempre gera uma função de invólucro para retornar todas as alterações de cada instância de captura. Se o parâmetro @supports_net_changes foi definido quando a instância de captura foi criada, o procedimento armazenado também gerará uma função de invólucro para retornar alterações globais de cada instância de captura de aplicativo.

O procedimento armazenado retorna um conjunto de resultados com duas colunas:

• O nome da função de invólucro que o procedimento armazenado gerou. Esse procedimento armazenado deriva o nome da função do nome do nome da instância de captura. (O nome da função é 'fn_all_changes_' seguido pelo nome da instância de captura. O prefixo usado para a função de alterações globais, se for criado, será 'fn_net_changes_'.)

• A instrução CREATE da função de invólucro.

Em geral, um desenvolvedor usa uma instrução INSERT...EXEC para chamar o procedimento armazenado sys.sp_cdc_generate_wrapper_function e salvar os scripts criados por esse procedimento em uma tabela temporária. Cada script pode ser selecionado executado individualmente para criar a função de invólucro correspondente. No entanto, um desenvolvedor também pode usar um conjunto de comandos SQL para executar todos os scripts CREATE, conforme mostrado no código de exemplo a seguir:

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

Para percorrer sistematicamente a linha de tempo dos dados de alteração capturados, as funções de invólucro geradas esperam que o parâmetro @end_time de um intervalo seja o parâmetro @start_time do intervalo subsequente. Quando essa convenção é seguida, as funções de invólucro geradas podem executar as seguintes tarefas:

• Mapear os valores de data/hora para os valores LSN usados interiormente.

• Assegurar que nenhum dado seja perdido ou repetido.

Para simplificar a consulta a todas as linhas de uma alteração, as funções de invólucro geradas também dão suporte às seguintes convenções:

• Se o parâmetro @start_time for nulo, as funções de invólucro usarão o valor LSN mais baixo na instância de captura do limite inferior da consulta.

• Se o parâmetro @end_time for nulo, as funções de invólucro usarão o valor LSN mais alto na instância de captura do limite superior da consulta.

A maioria dos usuários deverá ser capaz de usar as funções de invólucro que o procedimento armazenado de sistema sys.sp_cdc_generate_wrapper_function cria sem modificação. No entanto, para personalizar as funções de invólucro, você tem que personalizar os scripts CREATE antes de executar os scripts.

Quando seu pacote chama as funções de invólucro, o pacote deve fornecer valores para três parâmetros. Esses três parâmetros são como os três parâmetros que as funções de captura de dados de alteração usam. Esses três parâmetros são os seguintes:

• O valor de data/hora inicial e o valor de data/hora final do intervalo. Enquanto as funções de invólucro usam valores de data/hora como os pontos finais do intervalo de consulta, as funções de captura de dados de alteração usam dois valores LSN como os pontos finais.

• O filtro de linha. Para as funções de invólucro e as funções de captura de dados de alteração, o parâmetro @row_filter_option é o mesmo. Para obter mais informações, consulte cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) e cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

O conjunto de resultados retornado pelas funções de invólucro inclui os seguintes dados:

• Todas as colunas solicitadas de dados de alteração.

• Uma coluna denominada __CDC_OPERATION que usa um campo de um ou dois caracteres para identificar a operação associada à linha. Os valores válidos para esse campo são os seguintes: ‘I’ de inserir, ‘D’ de excluir, ‘UO’ de atualizar valores antigos e ‘UN’ de atualizar valores novos.

• Os sinalizadores de atualização, quando você os solicita, aparecem como colunas de bit após o código da operação e na ordem especificada no parâmetro @update_flag_list. Essas colunas são denominadas com a anexação de '_uflag' ao nome de coluna associado.

Se o pacote chamar uma função de invólucro que consulte todas as alterações, essa função também retornará as colunas __CDC_STARTLSN e __CDC_SEQVAL. Essas duas colunas se tornam a primeira e a segunda colunas, respectivamente, do conjunto de resultados. A função de invólucro também classifica o conjunto de resultados com base nessas duas colunas.

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

Embora a função com valor de tabela criada pelo usuário mostrada anteriormente use apenas a coluna __$operation, a função cdc.fn_cdc_get_net_changes_<capture_instance> retorna quatro colunas de metadados para cada linha de alteração. Se quiser usar esses valores no seu fluxo de dados, poderá retorná-los como colunas adicionais com a função de invólucro com valor de tabela.

Para obter mais informações, consulte cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).