SnowConvert AI - Oracle - COLLECTIONS AND RECORDS¶

Referência de tradução para converter Oracle COLLECTIONS e RECORDS para Script Snowflake

Aviso

Esta seção é um trabalho em andamento, as informações podem mudar no futuro.

Descrição geral¶

PL/SQL permite que você defina dois tipos de dados compostos: coleção e registro, em que composto é um tipo de dados que armazena valores que têm componentes internos.

Em uma coleção, os componentes internos sempre têm o mesmo tipo de dados e são chamados de elementos.

Em um registro, os componentes internos podem ter diferentes tipos de dados e são chamados de campos. (COLLECTIONS AND RECORDS da referência de linguagem Oracle PL/SQL)

Nota

Leve em conta a referência de tradução de instrução CREATE TYPE, pois algumas soluções alternativas podem se sobrepor e podem ser funcionais em ambos os cenários.

Limitações¶

O Snowflake não oferece suporte a tipos de dados definidos pelo usuário, o que inclui Coleções e registros PL, de acordo com a documentação on-line Tipos de dados sem suporte, mas oferece suporte a Tipos de dados semiestruturados, que podem ser usados para imitar a estrutura hierárquica do registro e a estrutura de elementos dos tipos definidos pelo usuário da coleção. Por esse motivo, há vários tipos de recursos que não têm solução alternativa.

A seguir, os recursos para os quais NO propõe uma solução alternativa:

O tamanho da variável não pode exceder 16MB¶

O Snowflake define o tamanho máximo de VARIANT, OBJECT e ARRAY em 16MBs. Isso significa que, se um registro, uma coleção ou qualquer elemento de qualquer um deles exceder esse tamanho, isso causará um erro de tempo de execução.

A capacidade do varray não pode ser limitada¶

Os varrays da Oracle oferecem a capacidade de limitar o número de elementos dentro deles. Isso não é suportado pelo Snowflake.

Proposta de solução alternativa¶

Sobre a definição dos tipos de registro¶

A solução proposta é usar um tipo de dados semiestruturado «OBJECT» para imitar o tipo de dados da Oracle.

Sobre a definição dos tipos de coleção¶

Há duas soluções alternativas diferentes que dependem do tipo de coleção a ser migrada:

Matrizes associativas têm proposta para alteração para um tipo de dados semiestruturados «OBJECT».
Propõe-se que as matrizes e as matrizes de tabelas aninhadas sejam transformadas em um tipo de dados semiestruturado «ARRAY».

Suporte atual do SnowConvert AI¶

The next table shows a summary of the current support provided by the SnowConvert AI tool. Please keep in mind that translations may still not be final, and more work may be needed.


Sub-recurso	Status atual de reconhecimento	Status atual da tradução	Tem soluções alternativas conhecidas
Definições do tipo de registro	Reconhecido.	Não traduzido.	Sim.
Definições de tipo de matriz associativa	Não reconhecido.	Não traduzido.	Sim.
Definições de tipo de varray	Reconhecido.	Não traduzido.	Sim.
Definições de tipo de matriz de tabela aninhada	Reconhecido.	Não traduzido.	Sim.

Problemas conhecidos¶

1. Associate Arrays are considered a Nested Table¶

Atualmente, o SnowConvert AI não diferencia entre uma matriz associativa e uma tabela aninhada, o que significa que elas são misturadas nas mesmas contagens de avaliação.

Definição do tipo de matriz associativa¶

Esta é uma referência de tradução para converter a Declaração de Matriz Associativa Oracle em Snowflake

Aviso

Esta seção é um trabalho em andamento, as informações podem mudar no futuro.

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição¶

Uma matriz associativa (anteriormente chamada de tabela ou índice-por-tabela PL/SQL) é um conjunto de pares chave-valor. Cada chave é um índice exclusivo, usado para localizar o valor associado com a sintaxe variable_name(index).

O tipo de dados de index pode ser do tipo cadeia de caracteres (VARCHAR2, VARCHAR, STRING ou LONG) ou PLS_INTEGER. Os índices são armazenados em ordem de classificação, não em ordem de criação. Para os tipos de cadeia de caracteres, a ordem de classificação é determinada pelos parâmetros de inicialização NLS_SORT e NLS_COMP.

(ASSOCIATIVE ARRAYS da referência de linguagem Oracle PL/SQL)

Aviso

Não confundir com a definição do tipo PL/SQL NESTED TABLE .

Para a conversão, a definição do tipo é substituída por um OBJECT Tipo de dados semiestruturados e, em seguida, seus usos são alterados de acordo com todas as operações.

To define an Associative Array type, the syntax is as follows:

type_definition := TYPE IS TABLE OF datatype INDEX BY indexing_datatype;

indexing_datatype := { PLS_INTEGER
                     | BINARY_INTEGER
                     | string_datatype
                     }

Para declarar uma variável desse tipo:

variable_name collection_type;

Amostra de padrões da origem¶

Matriz associativa indexada a Varchar¶

Oracle¶

CREATE OR REPLACE PROCEDURE associative_array
IS
    TYPE associate_array_typ IS TABLE OF INTEGER
        INDEX BY VARCHAR2(50);
        
    associate_array associate_array_typ := associate_array_typ();
    associate_index VARCHAR2(50);
BEGIN
    associate_array('abc') := 1;
    associate_array('bca') := 2;
    associate_array('def') := 3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array('abc'));
    associate_array('abc') := 4;
    --THROWS 'NO DATA FOUND'
    --DBMS_OUTPUT.PUT_LINE(associate_array('no exists'));
    
    DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
    
    associate_index := associate_array.FIRST;
    WHILE associate_index IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(associate_array(associate_index));
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;

CALL associative_array();

Resultado¶


DBMS OUTPUT
1
3
4
2
3

Snowflake¶

Observe o parâmetro “true” no OBJECT_INSERT. Isso serve para que o elemento seja atualizado se já estiver presente na matriz.

CREATE OR REPLACE PROCEDURE PUBLIC.associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index VARCHAR(50);
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, 'abc', 1, true);
      associate_array := OBJECT_INSERT(associate_array, 'bca', 2, true);
      associate_array := OBJECT_INSERT(associate_array, 'def', 3, true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['abc']);
      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['not found']);
      
      associate_array := OBJECT_INSERT(:associate_array, 'abc', 4, true);
    
      CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
      
      FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
      LOOP
         associate_index := OBJECT_KEYS(:associate_array)[:i-1];
         CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index]);
      END LOOP;
   END;
$$;

CALL PUBLIC.associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Resultado¶


DBMS OUTPUT
1
3
4
2
3

Matriz associativa com índice numérico¶

Oracle¶

CREATE OR REPLACE PROCEDURE numeric_associative_array
IS
    TYPE numeric_associative_array_typ IS TABLE OF INTEGER
        INDEX BY PLS_INTEGER;
        
    associate_array numeric_associativ
    e_array_typ := numeric_associative_array_typ();
    associate_index PLS_INTEGER;
BEGIN
    associate_array(1) := -1;
    associate_array(2) := -2;
    associate_array(3) := -3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array(1));
    associate_array(1) := -4;
    
    DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
    
    associate_index := associate_array.FIRST;
    WHILE associate_index IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(associate_array(associate_index));
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;

CALL numeric_associative_array();

Resultado¶


DBMS OUTPUT
-1
3
-4
-2
-3

Snowflake¶

Observe que o valor numérico é convertido em varchar de acordo com a necessidade da operação. Além disso, observe o parâmetro “true” em OBJECT_INSERT. Isso serve para que o elemento seja atualizado se já estiver presente na matriz.

CREATE OR REPLACE PROCEDURE PUBLIC.numeric_associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index NUMBER;
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, '1', -1, true);
      associate_array := OBJECT_INSERT(associate_array, '2', -2, true);
      associate_array := OBJECT_INSERT(associate_array, '3', -3, true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']);
      
      associate_array := OBJECT_INSERT(:associate_array, '1', -4, true);
    
      CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
      
      FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
      LOOP
         associate_index := OBJECT_KEYS(:associate_array)[:i-1];
         CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index::VARCHAR]);
      END LOOP;
   END;
$$;

CALL PUBLIC.numeric_associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Resultado¶


DBMS OUTPUT
-1
3
-4
-2
-3

Matriz associativa indexada numérica de elemento de registro¶

Nesse caso, a matriz associativa é composta por uma estrutura de registro, e essa estrutura precisa ser preservada. Para isso, foram adicionadas outras operações de inserção.

Oracle¶

CREATE OR REPLACE PROCEDURE record_associative_array
IS
    TYPE record_typ IS RECORD(col1 INTEGER);
    TYPE record_associative_array_typ IS TABLE OF record_typ
        INDEX BY PLS_INTEGER;
        
    associate_array record_associati ve_array_typ := record_associative_array_typ();
    associate_index PLS_INTEGER;
BEGIN
    associate_array(1).col1 := -1;
    associate_array(2).col1 := -2;
    associate_array(3).col1 := -3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array(1).col1);
    associate_array(4).col1 := -4;
    
    DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
    
    associate_index := associate_array.FIRST;
    WHILE associate_index IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(associate_array(associate_index).col1);
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;
/

CALL record_associative_array();

Resultado¶


DBMS OUTPUT
-1
3
-4
-2
-3

Snowflake¶

Nesse cenário, a inserção/atualização pressupõe a criação automática do registro dentro da matriz associativa e isso precisa ser levado em conta ao criar novos registros.

CREATE OR REPLACE PROCEDURE PUBLIC.record_associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index NUMBER;
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -1, true), true);
      associate_array := OBJECT_INSERT(associate_array, '2', OBJECT_INSERT(NVL(associate_array['2'], OBJECT_CONSTRUCT()), 'col1', -2, true), true);
      associate_array := OBJECT_INSERT(associate_array, '3', OBJECT_INSERT(NVL(associate_array['3'], OBJECT_CONSTRUCT()), 'col1', -3, true), true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']:col1);
      
      associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -4, true), true);
    
      CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
      
      FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
      LOOP
         associate_index := OBJECT_KEYS(:associate_array)[:i-1];
         CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index::VARCHAR]:col1);
      END LOOP;
   END;
$$;

CALL PUBLIC.record_associative_array();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Resultado¶


DBMS OUTPUT
-1
3
-4
-2
-3

Problemas conhecidos¶

1. They are currently not being recognized¶

O SnowConvert AI trata essas coleções como arrays de tabelas aninhadas. Há um item de trabalho para corrigir isso.

EWIs relacionados¶

Sem EWIs relacionados.

Métodos de coleta¶

Esta é uma referência de tradução para converter os métodos de coleção Oracle em Snowflake

Aviso

Esta seção é um trabalho em andamento, as informações podem mudar no futuro

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição¶

Um método de coleção é um subprograma PL/SQL - uma função que retorna informações sobre uma coleção ou um procedimento que opera em uma coleção. Os métodos de coleção tornam as coleções mais fáceis de usar e os aplicativos mais fáceis de manter.

(COLLECTION METHODS da referência de linguagem Oracle PL/SQL)

Alguns desses métodos podem ser mapeados para operações semiestruturadas nativas do Snowflake. Os que não puderem ou tiverem diferenças serão mapeados para uma implementação de UDF.

Suporte atual do SnowConvert AI¶

The next table shows a summary of the current support provided by the SnowConvert AI tool. Please keep in mind that translations may still not be final, and more work may be needed.


Método	Status atual de reconhecimento	Status atual da tradução	Mapeado para
DELETE	Não reconhecido.	Não traduzido.	UDF
TRIM	Não reconhecido.	Não traduzido.	UDF (A ser definido)
EXTEND	Não reconhecido.	Não traduzido.	UDF
EXISTS	Não reconhecido.	Não traduzido.	ARRAY_CONTAINS
FIRST	Não reconhecido.	Não traduzido.	UDF
LAST	Não reconhecido.	Não traduzido.	UDF
COUNT	Não reconhecido.	Não traduzido.	ARRAY_SIZE
LIMIT	Não reconhecido.	Não traduzido.	Sem suporte.
PRIOR	Não reconhecido.	Não traduzido.	UDF (A ser definido)
NEXT	Não reconhecido.	Não traduzido.	UDF (A ser definido)

Amostra de padrões da origem¶

COUNT¶

Esse método retorna a contagem de elementos «não definidos» (não confundir com nulos) em uma coleção (as tabelas aninhadas podem se tornar esparsas, deixando esses elementos no meio). Em matrizes associativas, ele retorna o número de chaves na matriz.

Oracle¶

CREATE OR REPLACE PROCEDURE collection_count
IS
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1);
    varray_variable varray_typ := varray_typ(1, 2, 3);
    nt_variable nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    DBMS_OUTPUT.PUT_LINE(associative_array.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable.COUNT);
END;

CALL collection_count();

Resultado¶


DBMS OUTPUT
2
3
4

Snowflake¶

O equivalente no Snowflake é o método ARRAY_SIZE.

CREATE OR REPLACE PROCEDURE PUBLIC.collection_count()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array OBJECT := OBJECT_CONSTRUCT('abc', 1, 'bca', 1);
    varray_variable ARRAY := ARRAY_CONSTRUCT(1, 2, 3);
    nt_variable ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
BEGIN
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associative_array)));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(:varray_variable));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(:nt_variable));
END;
$$;

CALL PUBLIC.collection_count();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Resultado¶


DBMS OUTPUT
2
3
4

EXISTS¶

Esse método retorna True se o elemento fornecido estiver contido na coleção. Em arrays associativos, ele testa se a chave está contida.

Oracle¶

CREATE OR REPLACE PROCEDURE collection_exists
IS
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1);
    nt_variable nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    IF associative_array.EXISTS('abc')
    THEN DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF NOT associative_array.EXISTS('not found')
    THEN DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
    
    IF nt_variable.EXISTS(1)
    THEN DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF NOT nt_variable.EXISTS(5)
    THEN DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
END;
/

CALL collection_exists();

Resultado¶


DBMS OUTPUT
2
3
4

Snowflake¶

O equivalente no Snowflake é o método ARRAY_CONTAINS. Observe que, ao usar elementos Varchar, é necessário fazer a conversão para Variant.

CREATE OR REPLACE PROCEDURE PUBLIC.collection_exists()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array OBJECT := OBJECT_CONSTRUCT('abc', 1, 'bca', 1);
    nt_variable ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
BEGIN
    IF (ARRAY_CONTAINS('abc'::VARIANT, OBJECT_KEYS(associative_array)))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF (NOT ARRAY_CONTAINS('not found'::VARIANT, OBJECT_KEYS(associative_array)))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
    
    IF (ARRAY_CONTAINS(1, nt_variable))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Found');
    END IF;
    
    IF (NOT ARRAY_CONTAINS(5, nt_variable))
    THEN CALL DBMS_OUTPUT.PUT_LINE('Not found');
    END IF;
END;
$$;

CALL PUBLIC.collection_exists();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Resultado¶


DBMS OUTPUT
2
3
4

FIRST/LAST¶

Esses dois métodos retornam o primeiro/último elemento da coleção, respectivamente. Se a coleção estiver vazia, ele retornará null. Essa operação é mapeada para um UDF, que será adicionado em revisões futuras.

Oracle¶

CREATE OR REPLACE PROCEDURE collection_first_last
IS
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1);
    nt_variable nt_typ := nt_typ();
BEGIN
    DBMS_OUTPUT.PUT_LINE(associative_array.FIRST);
    DBMS_OUTPUT.PUT_LINE(associative_array.LAST);
    
    DBMS_OUTPUT.PUT_LINE(nt_variable.FIRST);
    DBMS_OUTPUT.PUT_LINE(nt_variable.LAST);
    nt_variable := nt_typ(1, 2, 3, 4);
    DBMS_OUTPUT.PUT_LINE(nt_variable.FIRST);
    DBMS_OUTPUT.PUT_LINE(nt_variable.LAST);
END;
/

CALL collection_first_last();

Resultado¶


DBMS OUTPUT
abc
bca
–Estes espaços vazios são devido a sua avaliação como nulo

1
4

Snowflake¶

CREATE OR REPLACE PROCEDURE PUBLIC.collection_first_last()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array OBJECT := OBJECT_CONSTRUCT('abc', 1, 'bca', 1);
    nt_variable ARRAY := ARRAY_CONSTRUCT();
BEGIN
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_FIRST(:associative_array));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_LAST(:associative_array));
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_FIRST(:nt_variable));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_LAST(:nt_variable));
    nt_variable := ARRAY_CONSTRUCT(1, 2, 3, 4);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_FIRST(:nt_variable));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_LAST(:nt_variable));
END;
$$;

CALL PUBLIC.collection_first_last();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

UDFs¶

CREATE OR REPLACE FUNCTION ARRAY_FIRST(array_variable VARIANT)
RETURNS VARIANT
LANGUAGE SQL
AS
$$
    IFF (IS_OBJECT(array_variable),
        ARRAY_FIRST(OBJECT_KEYS(array_variable)),
        IFF (ARRAY_SIZE(array_variable) = 0, null, array_variable[0]))
$$;

CREATE OR REPLACE FUNCTION ARRAY_LAST(array_variable VARIANT)
RETURNS VARIANT
LANGUAGE SQL
AS
$$
    IFF (IS_OBJECT(array_variable),
        ARRAY_LAST(OBJECT_KEYS(array_variable)),
        IFF (ARRAY_SIZE(array_variable) = 0, null, array_variable[ARRAY_SIZE(array_variable)-1]))
$$;

Resultado¶


DBMS OUTPUT
abc
bca
–Estes espaços vazios são devido a sua avaliação como nulo

1
4

DELETE¶

Esse método é usado para remover elementos de uma coleção. Ele tem três variantes possíveis:

.DELETE remove todos os elementos.
.DELETE(n) remove o elemento cujo índice corresponde a “n”.
.DELETE(n, m) remove os índices de “n” a “m”.

Nota

No Oracle, o uso dessa operação em tabelas aninhadas faz com que elas tenham elementos «indefinidos» devido ao fato de serem esparsas.

Aviso

Observe que a segunda e a terceira versões não se aplicam a Varrays.

Oracle¶

Para simplificar, esse exemplo verifica apenas o número de elementos, mas pode ser modificado para exibir o conteúdo de cada coleção.

CREATE OR REPLACE PROCEDURE collection_delete
IS
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    associative_array1 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array2 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array3 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    
    varray_variable1 varray_typ := varray_typ(1, 2, 3, 4);
    
    nt_variable1 nt_typ := nt_typ(1, 2, 3, 4);
    nt_variable2 nt_typ := nt_typ(1, 2, 3, 4);
    nt_variable3 nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    varray_variable1.DELETE;--delete everything
    
    nt_variable1.DELETE;--delete everything
    nt_variable2.DELETE(2);--delete second position
    nt_variable3.DELETE(2, 3);--delete range
    
    associative_array1.DELETE;--delete everything
    associative_array2.DELETE('def');--delete second position
    associative_array3.DELETE('def', 'jkl');--delete range
    
    DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable2.COUNT);
    DBMS_OUTPUT.PUT_LINE(nt_variable3.COUNT);
    
    DBMS_OUTPUT.PUT_LINE(associative_array1.COUNT);
    DBMS_OUTPUT.PUT_LINE(associative_array2.COUNT);
    DBMS_OUTPUT.PUT_LINE(associative_array3.COUNT);
END;
/

CALL collection_delete();

Resultado¶


DBMS OUTPUT
0
0
3
2
0
3
1

Snowflake¶

O Snowflake não oferece suporte a exclusões de um ARRAY existente e, por esse motivo, a única solução alternativa oferecida é reconstruir um novo ARRAY dependendo dos parâmetros originais do DELETE.

Nota

Observe que um UDF foi adicionado para implementar a funcionalidade de atualização do elemento.

Este UDF será adicionado em revisões posteriores.

CREATE OR REPLACE PROCEDURE PUBLIC.collection_delete()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    associative_array1 OBJECT := OBJECT_CONSTRUCT('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array2 OBJECT := OBJECT_CONSTRUCT('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    associative_array3 OBJECT := OBJECT_CONSTRUCT('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
    
    varray_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    
    nt_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    nt_variable2 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    nt_variable3 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
BEGIN
    varray_variable1 := ARRAY_CONSTRUCT();--delete everything
    
    nt_variable1 := ARRAY_CONSTRUCT();--delete everything
    nt_variable2 := ARRAY_DELETE_UDF(nt_variable2, 2);--delete second position
    nt_variable3 := ARRAY_DELETE_UDF(nt_variable3, 2, 3);--delete range
    
    associative_array1 := OBJECT_CONSTRUCT();--delete everything
    associative_array2 := ASSOCIATIVE_ARRAY_DELETE_UDF('def');--delete second position
    associative_array3 := ASSOCIATIVE_ARRAY_DELETE_UDF('def', 'jkl');--delete range
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(varray_variable1));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable1);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable2);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable3);
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(associative_array1));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(associative_array2));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(associative_array3));
END;
$$;

CALL PUBLIC.collection_first_last();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Resultado¶


DBMS OUTPUT
0
0
3
2
0
3
1

EXTEND¶

Esse método é usado para acrescentar novos elementos a uma tabela aninhada ou a Varray. Ele tem três variantes possíveis:

.EXTEND insere um elemento null.
.EXTEND(n) insere “n” elementos null.
.EXTEND(n, i) insere “n” cópias do elemento em “i”.

Oracle¶

CREATE OR REPLACE PROCEDURE collection_extend
IS 
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    
    nt_variable1 nt_typ := nt_typ(1, 2, 3, 4);
    varray_variable1 varray_typ := varray_typ(1, 2, 3);
    varray_variable2 varray_typ := varray_typ(1, 2, 3);
BEGIN
    nt_variable1.EXTEND;
    varray_variable1.EXTEND(2);
    varray_variable2.EXTEND(2, 1);
    
    DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable2.COUNT);
END;
/

CALL collection_extend();

Resultado¶


DBMS OUTPUT
5
5
5

Snowflake¶

Nota

Observe que um UDF foi adicionado para implementar a funcionalidade de atualização do elemento.

Este UDF será adicionado em revisões posteriores.

CREATE OR REPLACE PROCEDURE PUBLIC.collection_first_last()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    nt_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);
    varray_variable1 ARRAY := ARRAY_CONSTRUCT(1, 2, 3);
    varray_variable2 ARRAY := ARRAY_CONSTRUCT(1, 2, 3);
BEGIN
    nt_variable1 := ARRAY_EXTEND_UDF(nt_variable);
    varray_variable1 := ARRAY_EXTEND_UDF(varray_variable1, 2);
    varray_variable2 := ARRAY_EXTEND_UDF(varray_variable2, 2, 1);
    
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(nt_variable1);
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(varray_variable1));
    CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(varray_variable2));
END;
$$;

CALL PUBLIC.collection_first_last();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Resultado¶


DBMS OUTPUT
5
5
5

TRIM¶

Esse método é usado para remover os últimos elementos de uma tabela aninhada ou de uma Varray. Ele tem duas variantes possíveis:

.TRIM remove o último elemento.
.TRIM(n) remove os últimos “n” elementos.

Nota

Essa funcionalidade pode ser implementada usando ARRAY_SLICE

Oracle¶

CREATE OR REPLACE PROCEDURE collection_trim
IS 
    TYPE varray_typ IS VARRAY(5) OF INTEGER;
    TYPE nt_typ IS TABLE OF INTEGER;
    
    varray_variable1 varray_typ := varray_typ(1, 2, 3);
    nt_variable1 nt_typ := nt_typ(1, 2, 3, 4);
BEGIN
    varray_variable1.TRIM;
    nt_variable1.TRIM(2);
    
    DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT);
    DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT);
END;
/

CALL collection_trim();

Resultado¶

DBMS OUTPUT
-----------
2
2

LIMIT¶

Esse método retorna o limite máximo de um Varray.

Perigo

Esse método não é compatível com o Snowflake.

Oracle¶

CREATE OR REPLACE PROCEDURE collection_limit
IS 
    TYPE varray_typ1 IS VARRAY(5) OF INTEGER;
    TYPE varray_typ2 IS VARRAY(6) OF INTEGER;
    
    varray_variable1 varray_typ1 := varray_typ1(1, 2, 3);
    varray_variable2 varray_typ2 := varray_typ2(1, 2, 3, 4);
BEGIN
    DBMS_OUTPUT.PUT_LINE(varray_variable1.LIMIT);
    DBMS_OUTPUT.PUT_LINE(varray_variable2.LIMIT);
END;
/

CALL collection_limit();

Resultado¶


DBMS OUTPUT
5
6

PRIOR/NEXT¶

Esse método retorna o índice anterior/próximo, dado um índice. Se não houver um anterior/próximo, ele retornará null. Ele é usado com mais frequência para percorrer uma coleção.

Oracle¶

CREATE OR REPLACE PROCEDURE collection_prior_next
IS 
    TYPE varray_typ1 IS VARRAY(5) OF INTEGER;
    TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20);
    
    varray_variable1 varray_typ1 := varray_typ1(-1, -2, -3);
    associative_array1 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);
BEGIN
    DBMS_OUTPUT.PUT_LINE(varray_variable1.PRIOR(1));
    DBMS_OUTPUT.PUT_LINE(varray_variable1.PRIOR(2));
    DBMS_OUTPUT.PUT_LINE(varray_variable1.NEXT(2));
    DBMS_OUTPUT.PUT_LINE(varray_variable1.NEXT(3));
    
    DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('abc'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('def'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.NEXT('ghi'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.NEXT('jkl'));
    DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('not found'));
END;
/

CALL collection_prior_next();

Resultado¶


DBMS OUTPUT
– Os espaços vazios são devido a resultados nulos
1
3


abc
jkl

jkl

Problemas conhecidos¶

1. Limit method is not supported in Snowflake¶

O Snowflake não tem suporte para varrays de espaço limitado. Por esse motivo, esse método não é compatível.

EWIs relacionados¶

Sem EWIs relacionados

Definição do tipo de matriz de tabela aninhada¶

Esta é uma referência de tradução para converter a Declaração de Matriz de Tabela Aninhada Oracle em Snowflake

Aviso

Esta seção é um trabalho em andamento, as informações podem mudar no futuro.

Nota

Esta seção é para a cersão PL/SQL das matrizes de tabelas aninhadas; para a versão autônoma, consulte Definição do tipo de tabela aninhada.

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição¶

No banco de dados, uma tabela aninhada é um tipo de coluna que armazena um número não especificado de linhas em nenhuma ordem específica.

Quando você recupera um valor de tabela aninhada do banco de dados em uma variável de tabela aninhada PL/SQL, PL/SQL fornece às linhas índices consecutivos, começando em 1. Usando esses índices, você pode acessar as linhas individuais da variável da tabela aninhada. A sintaxe é variable_name(index). Os índices e a ordem das linhas de uma tabela aninhada podem não permanecer estáveis à medida que você armazena e recupera a tabela aninhada do banco de dados.

(NESTED TABLES da referência de linguagem Oracle PL/SQL)

Para a conversão, a definição do tipo é substituída por um ARRAY Tipo de dados semiestruturados e, em seguida, seus usos são alterados de acordo com todas as operações. Observe como a conversão para tabelas aninhadas e Varrays é a mesma.

To define a Nested Table Array type, the syntax is as follows:

type_definition := TYPE IS TABLE OF datatype;

Para declarar uma variável desse tipo:

variable_name collection_type;

Amostra de padrões da origem¶

Definições de matriz de tabela aninhada¶

Isso ilustra como criar diferentes matrizes de tabelas aninhadas e como migrar as definições das variáveis.

Oracle¶

CREATE OR REPLACE PROCEDURE nested_table_procedure
IS
    TYPE nested_table_array_typ IS TABLE OF INTEGER;
    TYPE nested_table_array_typ2 IS TABLE OF DATE;
    
    nested_table_array nested_table_array_typ;
    nested_table_array2 nested_table_array_typ2;
BEGIN
    NULL;
END;

Snowflake¶

CREATE OR REPLACE PROCEDURE nested_table_procedure()
RETURNS INTEGER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    -- NO LONGER NEEDED
    /*
    TYPE associative_array_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(30);
    TYPE associative_array_typ2 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
    */
    
    associative_array ARRAY;
    associative_array2 ARRAY;
BEGIN
    NULL;
END;
$$;

Iteração de tabela aninhada¶

Oracle¶

CREATE OR REPLACE PROCEDURE nested_table_iteration
IS
    TYPE nested_table_typ IS TABLE OF INTEGER;
    nested_table_variable nested_table_typ := nested_table_typ (10, 20, 30);
BEGIN
    FOR i IN 1..nested_table_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(nested_table_variable(i));
    END LOOP;
    
    nested_table_variable (1) := 40;
    
    FOR i IN 1..nested_table_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(nested_table_variable(i));
    END LOOP;
END;
/

CALL nested_table_iteration();

Resultado¶


DBMS OUTPUT
10
20
30
40
20
30

Snowflake¶

Nota

Observe que um UDF foi adicionado para implementar a funcionalidade de atualização do elemento.

Este UDF será adicionado em revisões posteriores.

CREATE OR REPLACE PROCEDURE PUBLIC.nested_table_iteration()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    nested_table_variable ARRAY := ARRAY_CONSTRUCT(10, 20, 30);
BEGIN
    FOR i IN 1 TO ARRAY_SIZE(nested_table_variable)
    LOOP
        CALL DBMS_OUTPUT.PUT_LINE(:nested_table_variable[:i-1]);
    END LOOP;

    nested_table_variable:= INSERT_REPLACE_COLLECTION_ELEMENT_UDF(nested_table_variable, 1, 40);
    
    FOR i IN 1 TO ARRAY_SIZE(nested_table_variable)
    LOOP
        CALL DBMS_OUTPUT.PUT_LINE(:nested_table_variable[:i-1]);
    END LOOP;
END;
$$;

CALL PUBLIC.nested_table_iteration();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

UDF¶

CREATE OR REPLACE FUNCTION PUBLIC.INSERT_REPLACE_COLLECTION_ELEMENT_UDF(varray ARRAY, position INTEGER, newValue VARIANT)
RETURNS ARRAY
LANGUAGE SQL
AS
$$
    ARRAY_CAT(
        ARRAY_APPEND(ARRAY_SLICE(varray, 0, (position)-1), newValue),
        ARRAY_SLICE(varray, position, ARRAY_SIZE(varray)))
$$;

Resultado¶


DBMS OUTPUT
10
20
30
40
20
30

Problemas conhecidos¶

1. They are currently not being converted¶

O SnowConvert AI não oferece suporte à tradução desses elementos.

2. Indexing needs to be modified¶

Os índices do Oracle começam em 1; no Snowflake, eles começarão em 0.

EWIs relacionados¶

Sem EWIs relacionados

Definição do tipo de registro¶

Esta é uma referência de tradução para converter a Declaração de Registro Oracle em Snowflake

Aviso

Esta seção é um trabalho em andamento, as informações podem mudar no futuro.

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição¶

Uma variável de registro é uma variável composta cujos componentes internos, chamados de campos, podem ter diferentes tipos de dados. O valor de uma variável de registro e os valores de seus campos podem mudar.

Você faz referência a uma variável de registro inteira pelo seu nome. Você faz referência a um campo de registro com a sintaxe record.field.

Você pode criar uma variável de registro de qualquer uma dessas maneiras:

Defina um tipo de registro e, em seguida, declare uma variável desse tipo.

Use %ROWTYPE para declarar uma variável de registro que representa uma linha completa ou parcial de uma tabela ou visualização de banco de dados.

Use %TYPE para declarar uma variável de registro do mesmo tipo que uma variável de registro declarada anteriormente.

(RECORD VARIABLES da referência de linguagem Oracle PL/SQL)

Para a conversão, a definição do tipo é substituída por um OBJECT Tipo de dados semiestruturados e, em seguida, seus usos são alterados de acordo com todas as operações.

To define a Record type, the syntax is as follows:

type_definition := TYPE IS RECORD ( field_definition [, field_definition...] );

field_definition := field_name datatype [ { [NOT NULL default ] | default } ]

default := [ { := | DEFAULT } expression]

Para declarar uma variável desse tipo:

variable_name { record_type
              | rowtype_attribute
              | record_variable%TYPE
              };

Amostra de padrões da origem¶

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Inicialização e atribuição de registros¶

Esse exemplo tenta inserir duas novas linhas usando uma variável de registro que é reatribuída no meio do procedimento.

Oracle¶

CREATE TABLE record_table(col1 FLOAT, col2 INTEGER);

CREATE OR REPLACE PROCEDURE record_procedure
IS
    TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT);
    record_variable record_typ := record_typ(1, 1.5);--initialization
BEGIN
    INSERT INTO record_table(col1, col2)
        VALUES (record_variable.col2, record_variable.col1);--usage
        
    --reassignment of properties
    record_variable.col1 := 2;
    record_variable.col2 := 2.5;
    
    INSERT INTO record_table(col1, col2)
        VALUES (record_variable.col2, record_variable.col1);--usage
END;

CALL record_procedure();
SELECT * FROM record_table;

Resultado¶


COL1	COL2
1,5	1
2.5	2

Snowflake¶

Observe como as reatribuições são substituídas por um OBJECT_INSERT que é atualizado se a coluna já existir, e como a cláusula VALUES é substituída por SELECT.

CREATE OR REPLACE TABLE record_table (col1 FLOAT,
    col2 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE record_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
        TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT);
        record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - record_typ DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT('COL1', 1, 'COL2', 1.5);--initialization

    BEGIN
        INSERT INTO record_table(col1, col2)
        SELECT
            :record_variable:COL2,
            :record_variable:COL1;--usage

        --reassignment of properties
        record_variable := OBJECT_INSERT(record_variable, 'COL1', 2, true);
        record_variable := OBJECT_INSERT(record_variable, 'COL2', 2.5, true);

        INSERT INTO record_table(col1, col2)
        SELECT
            :record_variable:COL2,
            :record_variable:COL1;--usage

    END;
$$;

CALL record_procedure();

SELECT * FROM
    record_table;

Resultado¶


COL1	COL2
1,5	1
2.5	2

Registro de %ROWTYPE e registro de valores¶

Como as operações são as que definem a estrutura, essas definições podem ser substituídas por um tipo de dados OBJECT, mas os valores do registro precisam ser decompostos, pois não há suporte para a inserção do registro «como está».

Oracle¶

CREATE TABLE record_table(col1 INTEGER, col2 VARCHAR2(50), col3 DATE);
CREATE OR REPLACE PROCEDURE insert_record
IS
    record_variable record_table%ROWTYPE;
BEGIN
    record_variable.col1 := 1;
    record_variable.col2 := 'Hello';
    record_variable.col3 := DATE '2020-12-25';
    
    INSERT INTO record_table VALUES record_variable;
END;

CALL insert_record();
SELECT * FROM record_table;

Resultado¶


COL1	COL2	COL3
1	«Hello»	25-DEC-20

Snowflake¶

Please note finally how the OBJECT variable needs to be initialized to add the information to it.

CREATE OR REPLACE TABLE record_table (col1 INTEGER,
    col2 VARCHAR(50),
    col3 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE insert_record ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
    BEGIN
        record_variable := OBJECT_INSERT(record_variable, 'COL1', 1, true);
        record_variable := OBJECT_INSERT(record_variable, 'COL2', 'Hello', true);
        record_variable := OBJECT_INSERT(record_variable, 'COL3', DATE '2020-12-25', true);
        INSERT INTO record_table
        SELECT
            :record_variable:COL1,
            :record_variable:COL2,
            :record_variable:COL3;
    END;
$$;

CALL insert_record();

SELECT * FROM
    record_table;

Resultado¶


COL1	COL2	COL3
1	«Hello»	25-DEC-20

Obtenção de dados em um registro¶

Oracle¶

CREATE TABLE record_table(col1 INTEGER, col2 VARCHAR2(50), col3 DATE);
INSERT INTO record_table(col1, col2 , col3)
    VALUES (1, 'Hello', DATE '2020-12-25');

CREATE OR REPLACE PROCEDURE load_cursor_record
IS
    CURSOR record_cursor IS
        SELECT *
        FROM record_table;
        
    record_variable record_cursor%ROWTYPE;
BEGIN
    OPEN record_cursor;
    LOOP
        FETCH record_cursor INTO record_variable;
        EXIT WHEN record_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(record_variable.col1);
        DBMS_OUTPUT.PUT_LINE(record_variable.col2);
        DBMS_OUTPUT.PUT_LINE(record_variable.col3);
    END LOOP;
    CLOSE record_cursor;
END;

CALL load_cursor_record();

Resultado¶


DBMS OUTPUT
1
Hello
25-DEC-20

Snowflake¶

Observe o OBJECT_CONSTRUCT adicional na definição do Cursor, pois é isso que permite extrair um OBJECT, que pode ser usado para migrar sem problemas a instrução FETCH.

CREATE OR REPLACE TABLE record_table (col1 INTEGER,
    col2 VARCHAR(50),
    col3 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO record_table(col1, col2 , col3)
    VALUES (1, 'Hello', DATE '2020-12-25');

CREATE OR REPLACE PROCEDURE load_cursor_record ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        --** SSC-PRF-0009 - PERFORMANCE REVIEW - CURSOR USAGE **
        record_cursor CURSOR
        FOR
            SELECT
                OBJECT_CONSTRUCT( *) sc_cursor_record
            FROM
                record_table;
    record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
    BEGIN
        OPEN record_cursor;
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
            --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        FETCH record_cursor INTO
                :record_variable;
        IF (record_variable IS NULL) THEN
                EXIT;
        END IF;
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF(:record_variable:COL1);
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF(:record_variable:COL2);
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF(:record_variable:COL3::DATE);
    END LOOP;
    CLOSE record_cursor;
    END;
$$;

CALL load_cursor_record();

Resultado¶


DBMS OUTPUT
1
Hello
25-DEC-20

Atribuição de uma variável de registro em uma SELECT INTO¶

Essa transformação consiste em aproveitar a função OBJECT_CONTRUCT para inicializar o registro usando as colunas SELECT como argumentos.

Exemplo de código auxiliar¶

Oracle¶

create table sample_table(ID number, NAME varchar2(23));
CREATE TABLE RESULTS (COL1 VARCHAR(20), COL2 VARCHAR(40));
insert into sample_table values(1, 'NAME 1');
insert into sample_table values(2, 'NAME 2');
insert into sample_table values(3, 'NAME 3');
insert into sample_table values(4, 'NAME 4');

Snowflake¶

CREATE OR REPLACE TABLE sample_table (ID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
NAME VARCHAR(23))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE TABLE RESULTS (COL1 VARCHAR(20),
COL2 VARCHAR(40))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

insert into sample_table
values(1, 'NAME 1');

insert into sample_table
values(2, 'NAME 2');

insert into sample_table
values(3, 'NAME 3');

insert into sample_table
values(4, 'NAME 4');

Oracle¶

CREATE OR REPLACE PROCEDURE sp_sample1 AS
-- Rowtype variable
rowtype_variable sample_table%rowtype;

--Record variable
TYPE record_typ_def IS RECORD(ID number, NAME varchar2(23));
record_variable_def record_typ_def;

-- Auxiliary variable
name_var VARCHAR(20);
BEGIN
   SELECT * INTO rowtype_variable FROM sample_table WHERE ID = 1 FETCH NEXT 1 ROWS ONLY;
   name_var := rowtype_variable.NAME;
   INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 1', name_var);
  
   SELECT ID, NAME INTO rowtype_variable FROM sample_table WHERE ID = 2 FETCH NEXT 1 ROWS ONLY;
   name_var := rowtype_variable.NAME;
   INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 2', name_var);
  
   SELECT * INTO record_variable_def FROM sample_table WHERE ID = 3 FETCH NEXT 1 ROWS ONLY;
   name_var := record_variable_def.NAME;
   INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 3', name_var);
  
   SELECT ID, NAME INTO record_variable_def FROM sample_table WHERE ID = 4 FETCH NEXT 1 ROWS ONLY;
   name_var := record_variable_def.NAME;
   INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 4', name_var);
END;


call sp_sample1();

SELECT * FROM results;

Resultado¶


COL1	COL2
SELECT 1	NAME 1
SELECT 2	NAME 2
SELECT 3	NAME 3
SELECT 4	NAME 4

Snowflake¶

CREATE OR REPLACE PROCEDURE sp_sample1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      -- Rowtype variable
      rowtype_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();

      --Record variable
      !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
      TYPE record_typ_def IS RECORD(ID number, NAME varchar2(23));
      record_variable_def OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - record_typ_def DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();

      -- Auxiliary variable
      name_var VARCHAR(20);
   BEGIN
      SELECT
         OBJECT_CONSTRUCT( *) INTO
         :rowtype_variable
      FROM
         sample_table
      WHERE ID = 1
      FETCH NEXT 1 ROWS ONLY;
      name_var := :rowtype_variable:NAME;
      INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 1', :name_var);

      SELECT
         OBJECT_CONSTRUCT()
      INTO
         :rowtype_variable
      FROM
         sample_table
      WHERE ID = 2
      FETCH NEXT 1 ROWS ONLY;
      name_var := :rowtype_variable:NAME;
      INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 2', :name_var);

      SELECT
         OBJECT_CONSTRUCT( *) INTO
         :record_variable_def
      FROM
         sample_table
      WHERE ID = 3
      FETCH NEXT 1 ROWS ONLY;
      name_var := :record_variable_def:NAME;
      INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 3', :name_var);

      SELECT
         OBJECT_CONSTRUCT('ID', ID, 'NAME', NAME) INTO
         :record_variable_def
      FROM
         sample_table
      WHERE ID = 4
      FETCH NEXT 1 ROWS ONLY;
      name_var := :record_variable_def:NAME;
      INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 4', :name_var);
   END;
$$;

call sp_sample1();

SELECT * FROM
   results;

Resultado¶


COL1	COL2
SELECT 1	NAME 1
SELECT 2	NAME 2
SELECT 3	NAME 3
SELECT 4	NAME 4

Problemas conhecidos¶

1. The following functionalities are currently not being converted:¶

Busca de dados em um registro.
Registros aninhados (registros dentro de registros).
Coleções dentro de registros.

EWIs relacionados¶

SSC-EWI-0036: Tipo de dados convertido em outro tipo de dados.
SSC-EWI-0056: Criação de tipo sem suporte
SSC-FDM-0006: A coluna do tipo número pode não se comportar de forma semelhante no Snowflake.
SSC-FDM-OR0042: O tipo de data transformado em carimbo de data/hora tem um comportamento diferente.
SSC-FDM-OR0035: DBMS_OUTPUT. Implementação UDF de verificação PUTLINE.
SSC-PRF-0003: Fetch dentro de um loop é considerado um padrão complexo, o que pode degradar o desempenho do Snowflake.

Definição do tipo Varray¶

Esta é uma referência de tradução para converter a instrução Oracle Varray para Snowflake

Aviso

Esta seção é um trabalho em andamento, as informações podem mudar no futuro.

Nota

Esta seção é para a versão PL/SQL de Varrays; para a versão autônoma, consulte Definição do tipo de matriz.

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição¶

Um varray (variable-size array) é um array cujo número de elementos pode variar de zero (vazio) até o tamanho máximo declarado.

Para acessar um elemento de uma variável varray, use a sintaxe variable_name(index). O limite inferior de index é 1; o limite superior é o número atual de elementos. O limite superior muda à medida que você adiciona ou exclui elementos, mas não pode exceder o tamanho máximo. Quando você armazena e recupera um varray do banco de dados, seus índices e a ordem dos elementos permanecem estáveis.

(VARRAYS da referência de linguagem Oracle PL/SQL)

To define a varray type, the syntax is as follows:

type_definition := { VARRAY | [VARYING] ARRAY } (size_limit) OF datatype
            [NOT NULL];

Para declarar uma variável desse tipo:

variable_name collection_type;

Amostra de padrões da origem¶

Definições de varray¶

Isso ilustra três maneiras diferentes de criar um varray e como migrar essas definições para as variáveis.

Oracle¶

CREATE OR REPLACE PROCEDURE associative_array_procedure
IS
    TYPE varray_typ IS ARRAY(10) OF INTEGER;
    TYPE varray_typ2 IS VARRAY(10) OF INTEGER;
    TYPE varray_typ3 IS VARYING ARRAY(10) OF INTEGER;

    array_variable varray_typ;
    array_variable2 varray_typ2;
    array_variable3 varray_typ3;
BEGIN
    NULL;
END;

Snowflake¶

CREATE OR REPLACE PROCEDURE associative_array_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ IS ARRAY(10) OF INTEGER;
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ2 IS VARRAY(10) OF INTEGER;
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ3 IS VARYING ARRAY(10) OF INTEGER;

        array_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ' USAGE CHANGED TO VARIANT ***/!!!;
        array_variable2 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ2' USAGE CHANGED TO VARIANT ***/!!!;
        array_variable3 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ3' USAGE CHANGED TO VARIANT ***/!!!;
    BEGIN
        NULL;
    END;
$$;

Iteração de varray¶

Oracle¶

CREATE OR REPLACE PROCEDURE varray_iteration
IS
    TYPE varray_typ IS VARRAY(3) OF INTEGER;
    varray_variable varray_typ := varray_typ(10, 20, 30);
BEGIN
    FOR i IN 1..varray_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(varray_variable(i));
    END LOOP;
    
    varray_variable(1) := 40;
    
    FOR i IN 1..varray_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(varray_variable(i));
    END LOOP;
END;
/

CALL varray_iteration();

Resultado¶


DBMS OUTPUT
10
20
30
40
20
30

Snowflake¶

Nota

Observe que um UDF foi adicionado para implementar a funcionalidade de atualização do elemento.

Este UDF será adicionado em revisões posteriores.

CREATE OR REPLACE PROCEDURE varray_iteration ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ IS VARRAY(3) OF INTEGER;
        varray_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ' USAGE CHANGED TO VARIANT ***/!!! := varray_typ(10, 20, 30);
    BEGIN
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        FOR i IN 1 TO 0 /*varray_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'VARRAY CUSTOM TYPE EXPRESSION' NODE ***/!!!
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(varray_variable(i));
        END LOOP;
            !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
            varray_variable(1) := 40;
            --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
            FOR i IN 1 TO 0 /*varray_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'VARRAY CUSTOM TYPE EXPRESSION' NODE ***/!!!
            --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
               LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(varray_variable(i));
               END LOOP;
    END;
$$;

CALL varray_iteration();

UDF¶

CREATE OR REPLACE FUNCTION PUBLIC.INSERT_REPLACE_COLLECTION_ELEMENT_UDF(varray ARRAY, position INTEGER, newValue VARIANT)
RETURNS ARRAY
LANGUAGE SQL
AS
$$
    ARRAY_CAT(
        ARRAY_APPEND(ARRAY_SLICE(varray, 0, (position)-1), newValue),
        ARRAY_SLICE(varray, position, ARRAY_SIZE(varray)))
$$;

Resultado¶


DBMS OUTPUT
10
20
30
40
20
30

Problemas conhecidos¶

1. They are currently not being converted¶

O SnowConvert AI não oferece suporte à tradução desses elementos.

2. Indexing needs to be modified¶

Os índices do Oracle começam em 1; no Snowflake, eles começarão em 0.

3. Array Density may not match the original¶

Como o tipo de dados ARRAY pode se tornar esparso, é preciso ter cuidado ao fazer adições ou exclusões da matriz. Usar ARRAY_COMPACT() após essas operações pode ser útil se a densidade for uma preocupação.

EWIs relacionados¶

SSC-EWI-0058: A funcionalidade é atualmente incompatível com o Script Snowflake.
SSC-EWI-0062: O uso do tipo personalizado foi alterado para variante.
SSC-EWI-0073: Revisão de equivalência funcional pendente.
SSC-EWI-OR0108: A seguinte instrução de atribuição não é compatível com o Script Snowflake.
SSC-FDM-OR0035: DBMS_OUTPUT. Implementação UDF de verificação PUTLINE.

Operações de coleta em massa¶

Esta é uma referência de tradução para converter as operações em massa da coleção Oracle para Snowflake

Aviso

Esta seção é um trabalho em andamento, as informações podem mudar no futuro

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição¶

A cláusula BULK COLLECT, um recurso de SQL em massa, retorna resultados SQL para PL/SQL em lotes, em vez de um de cada vez.

A cláusula BULK COLLECT pode aparecer em:

Instrução SELECT``INTO

Instrução FETCH

Cláusu RETURNING INTO de:

Instrução DELETE

Instrução INSERT

Instrução UPDATE

Instrução EXECUTE``IMMEDIATE

Com a cláusula BULK COLLECT, cada uma das instruções anteriores recupera um conjunto de resultados inteiro e o armazena em uma ou mais variáveis de coleção em uma única operação (o que é mais eficiente do que usar uma instrução de loop para recuperar uma linha de resultado por vez).

(BULK COLLECT CLAUSE da referência de linguagem Oracle PL/SQL)

Esta seção apresenta algumas soluções alternativas para SELECTs e cursor FETCH com cláusulas em massa.

Amostra de padrões da origem¶

Tabela de origem¶

Oracle¶

CREATE TABLE bulk_collect_table(col1 INTEGER);

INSERT INTO bulk_collect_table VALUES(1);
INSERT INTO bulk_collect_table VALUES(2);
INSERT INTO bulk_collect_table VALUES(3);
INSERT INTO bulk_collect_table VALUES(4);
INSERT INTO bulk_collect_table VALUES(5);
INSERT INTO bulk_collect_table VALUES(6);

Snowflake¶

CREATE OR REPLACE TABLE bulk_collect_table (col1 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO bulk_collect_table
VALUES(1);

INSERT INTO bulk_collect_table
VALUES(2);

INSERT INTO bulk_collect_table
VALUES(3);

INSERT INTO bulk_collect_table
VALUES(4);

INSERT INTO bulk_collect_table
VALUES(5);

INSERT INTO bulk_collect_table
VALUES(6);

Coleta em massa de uma tabela¶

Oracle¶

CREATE OR REPLACE PROCEDURE bulk_collect_procedure
IS
    CURSOR record_cursor IS
        SELECT *
        FROM bulk_collect_table;
        
    TYPE fetch_collection_typ IS TABLE OF record_cursor%ROWTYPE;
    fetch_collection_variable fetch_collection_typ;
    
    TYPE collection_typ IS TABLE OF bulk_collect_table%ROWTYPE;
    collection_variable collection_typ;
BEGIN
    SELECT * BULK COLLECT INTO collection_variable FROM bulk_collect_table;
    
    FOR i IN 1..collection_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(collection_variable(i).col1);
    END LOOP;
    
    collection_variable := null;
    OPEN record_cursor;
    FETCH record_cursor BULK COLLECT INTO collection_variable;
    CLOSE record_cursor;
    
    FOR i IN 1..collection_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(collection_variable(i).col1+6);
    END LOOP;
    
    collection_variable := null;
    EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_table' BULK COLLECT INTO collection_variable;
    
    FOR i IN 1..collection_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(collection_variable(i).col1+12);
    END LOOP;
END;
/

CALL bulk_collect_procedure();

Resultado¶


DBMS OUTPUT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Snowflake¶

Perigo

EXECUTE IMMEDIATE com a cláusula Bulk Collect não tem soluções alternativas oferecidas.

Nota

Observe que, embora o cursor FETCH possa ser preservado em sua maior parte, é recomendável que seja alterado para instruções SELECT sempre que possível por questões de desempenho.

CREATE OR REPLACE PROCEDURE bulk_collect_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        --** SSC-PRF-0009 - PERFORMANCE REVIEW - CURSOR USAGE **
        record_cursor CURSOR
        FOR
            SELECT *
            FROM
                bulk_collect_table;
--                !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--                TYPE fetch_collection_typ IS TABLE OF record_cursor%ROWTYPE;
    fetch_collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'fetch_collection_typ' USAGE CHANGED TO VARIANT ***/!!!;
--                !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE collection_typ IS TABLE OF bulk_collect_table%ROWTYPE;
    collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collection_typ' USAGE CHANGED TO VARIANT ***/!!!;
    BEGIN
                !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'RECORDS AND COLLECTIONS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
                SELECT * BULK COLLECT INTO collection_variable FROM bulk_collect_table;
                --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                FOR i IN 1 TO 0 /*collection_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!!
                --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                   LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(:collection_variable(i).col1);
                   END LOOP;
                !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

                collection_variable := null;
                OPEN record_cursor;
                --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
                record_cursor := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:record_cursor)
                );
                collection_variable := :record_cursor:RESULT;
                CLOSE record_cursor;
                --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                FOR i IN 1 TO 0 /*collection_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!!
                --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                   LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(
            !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
            :collection_variable(i).col1+6);
                   END LOOP;
                !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

                collection_variable := null;
                !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
                EXECUTE IMMEDIATE 'SELECT * FROM
   bulk_collect_table'
                      !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
                      BULK COLLECT INTO collection_variable;
                --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                FOR i IN 1 TO 0 /*collection_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!!
                --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                   LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(
            !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
            :collection_variable(i).col1+12);
                   END LOOP;
    END;
$$;

CALL bulk_collect_procedure();

Resultado¶


DBMS OUTPUT
1
2
3
4
5
6
7
8
9
10
11
– EXECUTE IMMEDIATE NOT EXECUTED, não é compatível

Caso de instrução SELECT INTO¶

Nesse caso, a especificação de conversão usa RESULTSETs. Consulte a documentação das instruções WITH, SELECT e BULK COLLECT INTO aqui:

with-select-and-bulk-collect-into-statements.md

Problemas conhecidos¶

1. Heavy performance issues on FETCH Cursor workaround¶

A solução alternativa para o cursor Fetch tem grandes requisitos de desempenho devido à tabela Temporary. É recomendável que eles sejam migrados manualmente para instruções SELECT

2. Execute immediate statements are not transformed¶

Não são compatíveis com o SnowConvert AI mas pode ser manualmente alterados para instruções SELECT.

EWIs relacionados¶

SSC-EWI-0058: A funcionalidade é atualmente incompatível com o Script Snowflake.
SSC-EWI-0062: O uso do tipo personalizado foi alterado para variante.
SSC-EWI-0073: Revisão de equivalência funcional pendente
SSC-EWI-OR0036: Problemas de resolução de tipos; a operação aritmética pode não se comportar corretamente entre a cadeia de caracteres e a data
SSC-EWI-OR0108: A seguinte instrução de atribuição não é compatível com o Script Snowflake.
SSC-FDM-OR0035: DBMS_OUTPUT. Implementação UDF de verificação PUTLINE.
SSC-PRF-0001: Esta instrução tem usos de operações de busca de cursor em massa.
SSC-EWI-0030: A instrução abaixo tem usos de SQL dinâmico

Instruções WITH, SELECT e BULK COLLECT INTO¶

Perigo

Esta seção é uma especificação de conversão. As informações podem mudar no futuro.

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição¶

Esta seção é uma especificação de conversão para a instrução WITH subsequente a uma instrução SELECT que usa uma instrução BULK COLLECT INTO. Para obter mais informações, consulte a documentação a seguir:

Documentação da instrução SELECT INTO.
[Tradução de coleção em massa do SnowConvert AI](# Contas-collect-from-a-table).

Amostra de padrões da origem¶

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

A consulta a seguir é usada para os exemplos a seguir.

Oracle¶

-- Sample MySampleTable table
CREATE TABLE MySampleTable (
  MySampleID NUMBER PRIMARY KEY,
  FirstName VARCHAR2(50),
  Salary NUMBER,
  Department VARCHAR2(50)
);

-- Insert some sample data
INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (1, 'Bob One', 50000, 'HR');

INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (2, 'Bob Two', 60000, 'HR');

INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (3, 'Bob Three', 75000, 'IT');

INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (4, 'Bob Four', 80000, 'IT');

Snowflake¶

-- Sample MySampleTable table
CREATE OR REPLACE TABLE MySampleTable (
   MySampleID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ PRIMARY KEY,
   FirstName VARCHAR(50),
   Salary NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
   Department VARCHAR(50)
 )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

-- Insert some sample data
INSERT INTO MySampleTable(MySampleID, FirstName, Salary, Department)
VALUES (1, 'Bob One', 50000, 'HR');

INSERT INTO MySampleTable(MySampleID, FirstName, Salary, Department)
VALUES (2, 'Bob Two', 60000, 'HR');

INSERT INTO MySampleTable(MySampleID, FirstName, Salary, Department)
VALUES (3, 'Bob Three', 75000, 'IT');

INSERT INTO MySampleTable(MySampleID, FirstName, Salary, Department)
VALUES (4, 'Bob Four', 80000, 'IT');

1. Inside procedure simple case¶

Perigo

Essa é uma abordagem que usa um tipo de dados de conjunto de resultados. Os tipos definidos pelo usuário devem ser revisados. Consulte a seguinte documentação do Snowflake para obter mais informações sobre RESULTSETs.

O exemplo a seguir usa um tipo definido pelo usuário e é declarado indiretamente como uma tabela. A conversão para esse caso implementa um RESULTSET como um tipo de dados no Snowflake. O conjunto de resultados é armazenado em uma variável que deve ser retornada em uma função TABLE().

Oracle¶

CREATE OR REPLACE PROCEDURE simple_procedure
IS
  TYPE salary_collection IS TABLE OF NUMBER;
  v_salaries salary_collection := salary_collection();

BEGIN
  WITH IT_Employees AS (
    SELECT Salary
    FROM MySampleTable
    WHERE Department = 'IT'
  )
  SELECT Salary BULK COLLECT INTO v_salaries
  FROM IT_Employees;
END;

CALL simple_procedure();

Resultado¶

Nota

A consulta não retorna resultados, mas as informações coletadas esperadas seriam as IT Salary Information usadas no exemplo:


IT_Salary
75000
80000

Perigo

Uma das limitações de RESULTSETs é que ele não pode ser usado como tabela. Por exemplo: select * from my_result_set; (Isso é um erro, consulte a documentação a seguir para obter mais informações).

Snowflake¶

CREATE OR REPLACE PROCEDURE simple_procedure ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert AI Helpers Code section is omitted.

  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ***/!!!
  /*   TYPE salary_collection IS TABLE OF NUMBER */
  ;
  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
  /*   v_salaries salary_collection := salary_collection() */
  ;
  EXEC(`SELECT Salary
    FROM
       MySampleTable
    WHERE Department = 'IT'`);
  [
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlBulkCollectionItem' NODE ***/!!!
    //v_salaries
    null,V_SALARIES] = EXEC(`SELECT
   Salary
 FROM IT_Employees`);
$$;

CALL simple_procedure();

Resultado¶


SALARY
77500
80000

2. Simple case for iterations: FOR LOOP statement¶

O caso a seguir é para definir uma conversão para iteração com FOR...LOOP. Nesse caso, o tipo definido pelo usuário é implicitamente uma tabela, portanto, é possível usar um cursor para iterar. Consulte a documentação a seguir para saber mais:

Documentação do Snowflake sobre o retorno de uma tabela para um cursor
Nesse caso, é necessário criar um cursor para a iteração. Examine a seguinte documentação sobre a sintaxe de atribuição de cursor.

Oracle¶

CREATE OR REPLACE PROCEDURE simple_procedure
IS
  TYPE salary_collection IS TABLE OF NUMBER;
  v_salaries salary_collection := salary_collection();
  v_average_salary NUMBER;
  salaries_count NUMBER;

BEGIN
  salaries_count := 0;
  WITH IT_Employees AS (
    SELECT Salary
    FROM MySampleTable
    WHERE Department = 'IT'
  )
  SELECT Salary BULK COLLECT INTO v_salaries
  FROM IT_Employees;

  -- Calculate the average salary
  IF v_salaries.COUNT > 0 THEN
    v_average_salary := 0;
    FOR i IN 1..v_salaries.COUNT LOOP
		v_average_salary := v_average_salary + v_salaries(i);
		salaries_count := salaries_count + 1;
    END LOOP;
    v_average_salary := v_average_salary / salaries_count;
  END IF;

  -- Display the average salary
  DBMS_OUTPUT.PUT_LINE('Average Salary for IT Department: ' || v_average_salary);
END;
/

CALL simple_procedure();

Resultado¶

Statement processed.
Average Salary for IT Department: 77500

Snowflake¶

CREATE OR REPLACE PROCEDURE simple_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
--		!!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--		TYPE salary_collection IS TABLE OF NUMBER;
		v_salaries VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'salary_collection' USAGE CHANGED TO VARIANT ***/!!! := salary_collection();
		v_average_salary NUMBER(38, 18);
		salaries_count NUMBER(38, 18);
	BEGIN
		salaries_count := 0;
		WITH IT_Employees AS
		(
		  SELECT Salary
		  FROM
		  	MySampleTable
		  WHERE Department = 'IT'
		)
		!!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'RECORDS AND COLLECTIONS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
		SELECT Salary BULK COLLECT INTO v_salaries
		FROM IT_Employees;
		-- Calculate the average salary
		IF (null /*v_salaries.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!! > 0) THEN
		  v_average_salary := 0;
		  --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
		  FOR i IN 1 TO 0 /*v_salaries.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!!
 		                                                                                                                                                                        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
 		                                                                                                                                                                        LOOP
		  	v_average_salary :=
		  	!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN NUMBER AND salary_collection ***/!!!
		  	:v_average_salary + v_salaries(i);
		  	salaries_count := :salaries_count + 1;
 		                                                                                                                                                                           END LOOP;
		  v_average_salary := :v_average_salary / :salaries_count;
		END IF;
		-- Display the average salary
		--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
		CALL DBMS_OUTPUT.PUT_LINE_UDF('Average Salary for IT Department: ' || NVL(:v_average_salary :: STRING, ''));
	END;
$$;

CALL simple_procedure();

Resultado¶


SIMPLE_PROCEDURE
Average Salary for IT Department: 77500

Problemas conhecidos¶

1. Resulset limitations.¶

Há limitações ao usar o tipo de dados RESULTSET. Consulte a seguinte documentação do Snowflake para saber mais. As limitações marcantes são as seguintes:

A declaração de uma coluna do tipo RESULTSET.
A declaração de um parâmetro do tipo RESULTSET.
Declarando o tipo de retorno de um procedimento armazenado como um RESULTSET.

2. Execute statements with Bulk Collect clause are not supported.¶

Consulte a seguinte documentação.

EWIs relacionados¶

SSC-EWI-0058: A funcionalidade é atualmente incompatível com o Script Snowflake.
SSC-EWI-0062: O uso do tipo personalizado foi alterado para variante.
SSC-EWI-0073: Revisão de equivalência funcional pendente
SSC-EWI-OR0036: Problemas de resolução de tipos; a operação aritmética pode não se comportar corretamente entre a cadeia de caracteres e a data
SSC-EWI-OR0072: Membros de procedimento sem suporte
SSC-EWI-OR0104: Variável de coleção não utilizável.
SSC-FDM-0006: A coluna do tipo número pode não se comportar de forma semelhante no Snowflake.
SSC-FDM-OR0035: DBMS_OUTPUT. Implementação UDF de verificação PUTLINE.