Compartir a través de

Emulación de registros y colecciones mediante CLR UDT

En este artículo se explica cómo SQL Server Migration Assistant (SSMA) para Oracle usa SQL Server Common Language Runtime (CLR) User-Defined tipos de datos (UDT) para emular registros y colecciones de Oracle.

Declarar tipos de registro o colección y variables

SSMA crea tres UDT basados en CLR:

  • CollectionIndexInt
  • CollectionIndexString
  • Record

El CollectionIndexInt tipo está diseñado para simular colecciones indizadas por entero, como VARRAY, tablas anidadas y matrices asociativas basadas en claves enteros. El CollectionIndexString tipo se usa para matriz asociativa indexada por claves de caracteres. La funcionalidad de registro de Oracle es emulada por el tipo Record.

Todas las declaraciones de los tipos de registro o colección se convierten en esta declaración Transact-SQL:

declare @Collection$TYPE varchar(max) = '<type definition>'

Este <type definition> es un texto descriptivo que identifica de forma única el tipo PL/SQL de origen.

Considere el ejemplo siguiente:

DECLARE
    TYPE Manager IS RECORD
    (
        mgrid integer,
        mgrname varchar2(40),
        hiredate date
    );

    TYPE Manager_table is TABLE OF Manager INDEX BY PLS_INTEGER;

    Mgr_rec Manager;
    Mgr_table_rec Manager_table;
BEGIN
    mgr_rec.mgrid := 1;
    mgr_rec.mgrname := 'Mike';
    mgr_rec.hiredate := sysdate;

    select
        empno,
        ename,
        hiredate
    BULK COLLECT INTO
        mgr_table_rec
    FROM
        emp;
END;

Cuando se convierte mediante SSMA, se convertirá en el siguiente código de Transact-SQL:

BEGIN
    DECLARE
        @CollectionIndexInt$TYPE varchar(max) =
            ' TABLE INDEX BY INT OF ( RECORD ( MGRID INT , MGRNAME STRING , HIREDATE DATETIME ) )'

    DECLARE
        @Mgr_rec$mgrid int,
        @Mgr_rec$mgrname varchar(40),
        @Mgr_rec$hiredate datetime2(0),
        @Mgr_table_rec dbo.CollectionIndexInt =
            dbo.CollectionIndexInt::[Null].SetType(@CollectionIndexInt$TYPE)

    SET @mgr_rec$mgrid = 1
    SET @mgr_rec$mgrname = 'Mike'
    SET @mgr_rec$hiredate = sysdatetime()

    SET @mgr_table_rec = @mgr_table_rec.RemoveAll()
    SET @mgr_table_rec =
        @mgr_table_rec.AssignData(
            ssma_oracle.fn_bulk_collect2CollectionComplex((
                SELECT
                    CAST(EMP.EMPNO AS int) AS mgrid,
                    EMP.ENAME AS mgrname,
                    EMP.HIREDATE AS hiredate
                FROM dbo.EMP
                FOR XML PATH
            ))
        )
END

Aquí, dado que la Manager tabla está asociada a un índice numérico (INDEX BY PLS_INTEGER), la declaración T-SQL correspondiente usada es de tipo @CollectionIndexInt$TYPE. Si la tabla estaba asociada a un índice de juego de caracteres, como VARCHAR2, la declaración de T-SQL correspondiente sería de tipo @CollectionIndexString$TYPE:

-- Oracle
TYPE Manager_table is TABLE OF Manager INDEX BY VARCHAR2(40);

-- SQL Server
@CollectionIndexString$TYPE varchar(max) =
    ' TABLE INDEX BY STRING OF ( RECORD ( MGRID INT , MGRNAME STRING , HIREDATE DATETIME ) )'

La funcionalidad del Registro de Oracle se emula solo por el tipo Record.

Cada uno de los tipos, CollectionIndexInt, CollectionIndexStringy Record, tiene una propiedad [Null] estática que devuelve una instancia vacía. El método SetType se llama para recibir un objeto vacío de un tipo específico (como se muestra en el ejemplo anterior).

Conversiones de llamadas de constructor

La notación del constructor solo se puede usar para tablas anidadas y VARRAY, por lo que todas las llamadas explícitas del constructor se convierten mediante el CollectionIndexInt tipo . Las llamadas de constructor vacías se convierten mediante una llamada invocada en una instancia null de SetType. La [Null] propiedad devuelve la instancia nula. Si el constructor contiene una lista de elementos, las llamadas a métodos especiales se aplican secuencialmente para agregar el valor a la colección.

Por ejemplo:

-- Oracle

DECLARE
    TYPE nested_type IS TABLE OF VARCHAR2(20);
    TYPE varray_type IS VARRAY(5) OF INTEGER;

    v1 nested_type;
    v2 varray_type;
BEGIN
   v1 := nested_type('Arbitrary','number','of','strings');
   v2 := varray_type(10, 20, 40, 80, 160);
END;

-- SQL Server

BEGIN
    DECLARE
        @CollectionIndexInt$TYPE varchar(max) = ' VARRAY OF INT',
        @CollectionIndexInt$TYPE$2 varchar(max) = ' TABLE OF STRING',
        @v1 dbo.CollectionIndexInt,
        @v2 dbo.CollectionIndexInt

    SET @v1 =
        dbo.CollectionIndexInt::[Null]
            .SetType(@CollectionIndexInt$TYPE$2)
            .AddString('Arbitrary')
            .AddString('number')
            .AddString('of')
            .AddString('strings')

   SET @v2 =
       dbo.CollectionIndexInt::[Null]
            .SetType(@CollectionIndexInt$TYPE)
            .AddInt(10)
            .AddInt(20)
            .AddInt(40)
            .AddInt(80)
            .AddInt(160)
END

Referenciar y asignar elementos de registro y colección

Cada uno de los UDT tiene un conjunto de métodos que funcionan con elementos de los distintos tipos de datos. Por ejemplo, el SetDouble método asigna un float(53) valor a un registro o colección y GetDouble puede leer este valor. Esta es la lista completa de métodos:

GetCollectionIndexInt(@key <KeyType>) returns CollectionIndexInt;
SetCollectionIndexInt(@key <KeyType>, @value CollectionIndexInt) returns <UDT_type>;
GetCollectionIndexString(@key <KeyType>) returns CollectionIndexString;
SetCollectionIndexString(@key <KeyType>, @value CollectionIndexString) returns <UDT_type>;
Record GetRecord(@key <KeyType>) returns Record;
SetRecord(@key <KeyType>, @value Record) returns <UDT_type>;
GetString(@key <KeyType>) returns nvarchar(max);
SetString(@key <KeyType>, @value nvarchar(max)) returns nvarchar(max);
GetDouble(@key <KeyType>) returns float(53);
SetDouble(@key <KeyType>, @value float(53)) returns <UDT_type>;
GetDatetime(@key <KeyType>) returns datetime;
SetDatetime(@key <KeyType>, @value datetime) returns <UDT_type>;
GetVarbinary(@key <KeyType>) returns varbinary(max);
SetVarbinary(@key <KeyType>, @value varbinary(max)) returns <UDT_type>;
SqlDecimal GetDecimal(@key <KeyType>);
SetDecimal(@key <KeyType>, @value numeric) returns <UDT_type>;
GetXml(@key <KeyType>) returns xml;
SetXml(@key <KeyType>, @value xml) returns <UDT_type>;
GetInt(@key <KeyType>) returns bigint;
SetInt(@key <KeyType>, @value bigint) returns <UDT_type>;

Estos métodos se usan al hacer referencia o asignar un valor a un elemento de una colección o registro.

-- Oracle
a_collection(i) := 'VALUE';

-- SQL Server
SET @a_collection = @a_collection.SetString(@i, 'VALUE');

Al convertir instrucciones de asignación para colecciones multidimensionales o colecciones con elementos de registro, SSMA agrega los métodos siguientes para hacer referencia a un elemento primario dentro del método set:

GetOrCreateCollectionIndexInt(@key <KeyType>) returns CollectionIndexInt;
GetOrCreateCollectionIndexString(@key <KeyType>) returns CollectionIndexString;
GetOrCreateRecord(@key <KeyType>) returns Record;

Por ejemplo, se crea una colección de elementos de registro de esta manera:

-- Oracle
DECLARE
    TYPE rec_details IS RECORD (id int, name varchar2(20));
    TYPE ntb1 IS TABLE of rec_details index BY binary_integer;
    c ntb1;
BEGIN
    c(1).id := 1;
END;

-- SQL Server
DECLARE
   @CollectionIndexInt$TYPE varchar(max) =
       ' TABLE INDEX BY INT OF ( RECORD ( ID INT , NAME STRING ) )',
   @c dbo.CollectionIndexInt =
       dbo.CollectionIndexInt::[Null].SetType(@CollectionIndexInt$TYPE)

SET @c = @c.SetRecord(1, @c.GetOrCreateRecord(1).SetInt(N'ID', 1))

Métodos integrados de colección

SSMA usa los siguientes métodos UDT para emular métodos integrados de colecciones PL/SQL.

Métodos de colección de Oracle CollectionIndexInt y CollectionIndexString equivalentes
CONTAR Count returns int
ELIMINAR RemoveAll() returns <UDT_type>
DELETE(n) Remove(@index int) returns <UDT_type>
DELETE(m,n) RemoveRange(@indexFrom int, @indexTo int) returns <UDT_type>
EXISTE ContainsElement(@index int) returns bit
EXTENDER Extend() returns <UDT_type>
EXTEND(n) Extend() returns <UDT_type>
EXTEND(n,i) ExtendDefault(@count int, @def int) returns <UDT_type>
PRIMERO First() returns int
ÚLTIMO Last() returns int
LÍMITE No disponible
PREVIO Prior(@current int) returns int
SIGUIENTE Next(@current int) returns int
RECORTAR Trim() returns <UDT_type>
TRIM(n) TrimN(@count int) returns <UDT_type>

Operación BULK COLLECT

SSMA convierte las instrucciones BULK COLLECT INTO en una instrucción de SQL Server SELECT ... FOR XML PATH , cuyo resultado se encapsula en una de las funciones siguientes:

  • ssma_oracle.fn_bulk_collect2CollectionSimple
  • ssma_oracle.fn_bulk_collect2CollectionComplex

La elección depende del tipo del objeto de destino. Estas funciones devuelven valores XML que pueden analizarse mediante los tipos CollectionIndexInt, CollectionIndexString y Record. Una función especial AssignData asigna una colección basada en XML al UDT.

SSMA reconoce tres tipos de BULK COLLECT INTO sentencias.

La colección contiene elementos con tipos escalares y la SELECT lista contiene una columna.

-- Oracle
SELECT column_name_1
BULK COLLECT INTO <collection_name_1>
FROM <data_source>

-- SQL Server
SET @<collection_name_1> =
    @<collection_name_1>.AssignData(
        ssma_oracle.fn_bulk_collect2CollectionSimple(
            (SELECT column_name_1 FROM <data_source> FOR XML PATH)))

La colección contiene elementos con tipos de registro y la SELECT lista contiene una columna.

-- Oracle
SELECT
    column_name_1[, column_name_2...]
BULK COLLECT INTO
    <collection_name_1>
FROM
    <data_source>

-- SQL Server
SET @<collection_name_1> =
    @<collection_name_1>.AssignData(
        ssma_oracle.fn_bulk_collect2CollectionComplex(
            (SELECT
                column_name_1 as [collection_name_1_element_field_name_1],
                column_name_2 as [collection_name_1_element_field_name_2]
            FROM <data_source>
            FOR XML PATH)))

La colección contiene elementos con tipo escalar y la SELECT lista contiene varias columnas.

-- Oracle
SELECT
    column_name_1[, column_name_2 ...]
BULK COLLECT INTO
    <collection_name_1>[, <collection_name_2> ...]
FROM
    <data_source>

-- SQL Server
;WITH bulkC AS (
    SELECT
        column_name_1 [collection_name_1_element_field_name_1],
        column_name_2 [collection_name_1_element_field_name_2]
    FROM
        <data_source>
)
SELECT
    @<collection_name_1> =
        @<collection_name_1>.AssignData(
            ssma_oracle.fn_bulk_collect2CollectionSimple(
                (SELECT
                    [collection_name_1_element_field_name_1]
                FROM
                    bulkC
                FOR XML PATH))),
    @<collection_name_2> =
        @<collection_name_2>.AssignData(
            ssma_oracle.fn_bulk_collect2CollectionSimple(
                (SELECT
                    [collection_name_1_element_field_name_2]
                FROM bulkC
                FOR XML PATH)))

SELECT INTO Registro

Cuando el resultado de la consulta de Oracle se guarda en una variable de registro PL/SQL, tiene dos opciones en función de la configuración de SSMA para Convertir registro como una lista de variables separadas (disponibles en el menú Herramientas, Configuración del proyecto y, a continuación, Conversión>). Si el valor de esta configuración es Sí (valor predeterminado), SSMA no crea una instancia del tipo Record. En su lugar, divide el registro en los campos que constituyen creando una variable de Transact-SQL independiente por cada campo de registro. Si la configuración es No, se crea una instancia del registro y a cada campo se le asigna un valor mediante Set métodos.