Como trabalhar com variáveis

No Script Snowflake, você pode usar variáveis em expressões, instruções do Script Snowflake e instruções SQL.

Neste tópico:

Como declarar uma variável

Antes de poder usar uma variável, você deve declarar a variável. Quando você declara uma variável, você deve especificar o tipo da variável:

Você pode declarar uma variável:

  • Dentro da seção DECLARE do bloco, usando qualquer uma das opções a seguir:

    <variable_name> <type>;
    
    <variable_name> DEFAULT <expression> ;
    
    <variable_name> <type> DEFAULT <expression> ;
    
    Copy
  • Dentro da seção BEGIN … END do bloco (antes de usar a variável), usando o comando LET de qualquer uma das maneiras a seguir:

    LET <variable_name> <type> { DEFAULT | := } <expression> ;
    
    LET <variable_name> { DEFAULT | := } <expression> ;
    
    Copy

Onde:

variable_name

O nome da variável. O nome deve seguir as regras de nomenclatura para Identificadores de objetos.

type

O tipo de dados da variável. Ele pode ser:

DEFAULT expression ou . := expression

Atribui o valor de expression à variável.

Se tanto type quanto expression forem especificados, a expressão deve ser avaliada como um tipo de dados correspondente. Se os tipos não corresponderem, você pode converter o valor para o type especificado.

O exemplo a seguir declara variáveis na seção DECLARE e na seção BEGIN ... END do bloco:

DECLARE
    profit number(38, 2) DEFAULT 0.0;
BEGIN
    LET cost number(38, 2) := 100.0;
    LET revenue number(38, 2) DEFAULT 110.0;

    profit := revenue - cost;
    RETURN profit;
END;
Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

EXECUTE IMMEDIATE $$
    DECLARE
        profit number(38, 2) DEFAULT 0.0;
    BEGIN
        LET cost number(38, 2) := 100.0;
        LET revenue number(38, 2) DEFAULT 110.0;

        profit := revenue - cost;
        RETURN profit;
    END;
$$
;
Copy

As próximas seções explicam como são determinados o tipo e o escopo dos dados de uma variável:

Para obter mais informações sobre a atribuição de um valor a uma variável, consulte Como atribuir um valor a uma variável declarada.

Como o Script Snowflake infere o tipo de dados de uma variável

Como mencionado anteriormente, quando você declara uma variável sem especificar explicitamente o tipo de dados, o Script Snowflake infere o tipo de dado a partir da expressão que você atribui à variável.

Se você optar por omitir o tipo de dados da declaração, observe o seguinte:

  • Se a expressão pode ser resolvida como diferentes tipos de dados de diferentes tamanhos, o Snowflake normalmente escolhe o tipo que é flexível (por exemplo, FLOAT em vez de NUMBER(3, 1)) e tem uma alta capacidade de armazenamento (por exemplo, VARCHAR em vez de VARCHAR(4)).

    Por exemplo, se você definir uma variável com o valor 12.3, o Snowflake pode escolher dentre vários tipos de dados para a variável, inclusive:

    • NUMBER(3, 1)

    • NUMBER(38, 1)

    • FLOAT

    Neste exemplo, o Snowflake escolhe FLOAT.

    Se você precisar de um tipo de dados específico para uma variável (especialmente um tipo numérico ou de carimbo de data/hora), o Snowflake recomenda que você especifique o tipo de dado explicitamente, mesmo que você forneça um valor inicial.

  • Se o Snowflake não for capaz de inferir o tipo de dados pretendido, o Snowflake gera um erro de compilação de SQL.

    Por exemplo, o código a seguir declara uma variável sem especificar explicitamente o tipo de dados. O código define a variável como o valor em um cursor.

    ...
    for current_row in cursor_1 do:
        let price := current_row.price_column;
        ...
    
    Copy

    Quando o bloco do Script Snowflake é compilado (por exemplo, quando o comando CREATE PROCEDURE é executado), o cursor não foi aberto, e o tipo de dados da coluna no cursor é desconhecido. Como resultado, o Snowflake relata um erro de compilação de SQL:

    092228 (P0000): SQL compilation error:
        error line 7 at position 4 variable 'PRICE' cannot have its type inferred from initializer
    
    Copy

Explicação do escopo das declarações

O Script Snowflake usa um escopo lexical. Quando uma variável de um valor, conjunto de resultados, cursor ou exceção é declarada na seção DECLARE de um bloco, o escopo (ou visibilidade) do objeto declarado é esse bloco e quaisquer blocos aninhados dentro dele.

Se um bloco declara um objeto com o mesmo nome de um objeto declarado em um bloco externo, dentro do bloco interno (e quaisquer blocos dentro desse bloco), somente o objeto do bloco interno fica no escopo. Quando um nome de objeto é referenciado, o Snowflake procura o objeto com esse nome começando primeiro no bloco atual e depois percorrendo para fora um bloco de cada vez até que um objeto com um nome correspondente seja encontrado.

Por exemplo, se uma exceção é declarada dentro de um procedimento armazenado, o escopo da exceção é limitado a esse procedimento armazenado. Procedimentos armazenados chamados por esse procedimento armazenado não podem gerar (ou tratar) essa exceção. Procedimentos armazenados que chamam esse procedimento não podem tratar (ou gerar) essa exceção.

Como atribuir um valor a uma variável declarada

Para atribuir um valor a uma variável que já tenha sido declarada, use o operador :=:

<variable_name> := <expression> ;
Copy

Onde:

variable_name

O nome da variável. O nome deve seguir as regras de nomenclatura para Identificadores de objetos.

expression

A expressão é avaliada e o valor resultante é atribuído à variável.

A expressão deve ser avaliada como um tipo de dados que corresponda ao tipo da variável. Se a expressão não corresponder ao tipo, você pode converter o valor para o tipo da variável.

Na expressão, você pode usar funções, incluindo funções internas e UDFs (funções definidas pelo usuário) de SQL.

Como usar uma variável

Você pode usar variáveis em expressões e com elementos da linguagem de Script Snowflake (como RETURN). Por exemplo, o código abaixo usa as variáveis revenue e cost em uma expressão e a variável profit em uma instrução RETURN:

DECLARE
    profit NUMBER(38, 2);
    revenue NUMBER(38, 2);
    cost NUMBER(38, 2);
BEGIN
    ...
    profit := revenue - cost;
    ...
    RETURN profit;
Copy

Como usar uma variável em uma instrução SQL (vinculação)

Você pode usar uma variável em uma instrução SQL. (Isso às vezes é chamado de variável de vinculação). Preceda o nome da variável com dois pontos. Por exemplo:

INSERT INTO my_table (x) VALUES (:my_variable)
Copy

Se você estiver usando a variável como nome de um objeto (por exemplo, o nome de uma tabela na cláusula FROM de uma instrução SELECT), use a palavra-chave IDENTIFIER para indicar que a variável representa um identificador de objeto. Por exemplo:

SELECT COUNT(*) FROM IDENTIFIER(:table_name)
Copy

Observe que se você estiver usando uma variável em uma expressão ou com um elemento de linguagem de Script Snowflake (por exemplo, RETURN), você não precisa preceder a variável com dois pontos.

Por exemplo, você não precisa do prefixo de dois pontos se:

  • Você estiver usando a variável com RETURN. Neste exemplo, a variável profit é usada com um elemento da linguagem de Script Snowflake e não precisa do prefixo de dois pontos.

    RETURN profit;
    
    Copy
  • Você estiver formando uma cadeia de caracteres contendo uma instrução SQL a executar. Neste exemplo, a variável id_variable é usada em uma expressão e não precisa do prefixo de dois pontos.

    LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id_variable;
    
    Copy

Definição de variáveis como os resultados de uma instrução SELECT

Em um bloco do Script Snowflake, você pode usar a cláusula INTO para definir variáveis para os valores das expressões especificadas em uma cláusula SELECT:

SELECT <expression1>, <expression2>, ... INTO :<variable1>, :<variable2>, ... FROM ... WHERE ...;
Copy

Quando você usa essa sintaxe:

  • variable1 é definido como o valor de expression1.

  • variable2 é definido como o valor de expression2.

Observe que a instrução SELECT deve retornar uma única linha.

O exemplo a seguir contém uma instrução SELECT que retorna uma única linha. O exemplo se baseia nos dados dessa tabela:

CREATE OR REPLACE TABLE some_data (id INTEGER, name VARCHAR);
INSERT INTO some_data (id, name) VALUES
  (1, 'a'),
  (2, 'b');
Copy

O exemplo define as variáveis do Script Snowflake id e name como os valores retornados para as colunas com esses nomes.

DECLARE
  id_variable INTEGER;
  name_variable VARCHAR;
BEGIN
  SELECT id, name INTO :id_variable, :name_variable FROM some_data WHERE id = 1;
  RETURN id_variable || ' ' || name_variable;
END;
Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
  id INTEGER;
  name VARCHAR;
BEGIN
  SELECT id, name INTO :id, :name FROM some_data WHERE id = 1;
  RETURN :id || ' ' || :name;
END;
$$
;
Copy

O exemplo imprime a ID e o nome da linha retornada pela instrução SELECT.

+-----------------+
| anonymous block |
|-----------------|
| 1 a             |
+-----------------+
Copy

Definição de uma variável para o valor de retorno de um procedimento armazenado

Consulte Uso do valor retornado a partir de uma chamada de procedimento armazenado.

Exemplos de uso de variáveis

O exemplo a seguir mostra como declarar uma variável, atribuir um valor ou expressão a uma variável e converter um valor para o tipo de dados de uma variável:

DECLARE
  w INTEGER;
  x INTEGER DEFAULT 0;
  dt DATE;
  result_string VARCHAR;
BEGIN
  w := 1;                     -- Assign a value.
  w := 24 * 7;                -- Assign the result of an expression.
  dt := '2020-09-30'::DATE;   -- Explicit cast.
  dt := '2020-09-30';         -- Implicit cast.
  result_string := w::VARCHAR || ', ' || dt::VARCHAR;
  RETURN result_string;
END;
Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
    w INTEGER;
    x INTEGER DEFAULT 0;
    dt DATE;
    result_string VARCHAR;
BEGIN
    w := 1;                     -- Assign a value.
    w := 24 * 7;                -- Assign the result of an expression.
    dt := '2020-09-30'::DATE;   -- Explicit cast.
    dt := '2020-09-30';         -- Implicit cast.
    result_string := w::VARCHAR || ', ' || dt::VARCHAR;
    RETURN result_string;
END;
$$
;
Copy

O exemplo a seguir utiliza uma função interna de SQL na expressão:

my_variable := SQRT(variable_x);
Copy

A declaração a seguir especifica implicitamente os tipos de dados das variáveis profit, cost, e revenue especificando um valor inicial do tipo de dados pretendido para cada variável.

O exemplo também demonstra como usar a instrução LET para declarar as variáveis cost e revenue fora da seção DECLARE do bloco:

DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
END;
Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
    profit DEFAULT 0.0;
BEGIN
    LET cost := 100.0;
    LET revenue DEFAULT 110.0;
    profit := revenue - cost;
    RETURN profit;
END;
$$
;
Copy

O exemplo a seguir demonstra o escopo de uma variável. Este exemplo inclui duas variáveis e um parâmetro que têm todos o mesmo nome, mas um escopo diferente.

O exemplo contém três blocos: o mais externo, o do meio e o mais interno.

  • Dentro do bloco mais interno, PV_NAME é resolvida como a variável declarada e definida no bloco mais interno (que é definido como innermost block variable).

  • Dentro do bloco do meio (e fora do bloco mais interno), PV_NAME é resolvida como a variável declarada e definida no bloco do meio (que é definida como middle block variable).

  • Dentro do bloco mais externo (e fora de qualquer um dos blocos aninhados), PV_NAME é resolvida como o parâmetro passado ao procedimento armazenado (que é definido como parameter pela instrução CALL).

Por exemplo, a atribuição da cadeia de cadeia de caracteres innermost block variable a PV_NAME no bloco mais interno não afeta o valor da variável no bloco do meio. A variável no bloco mais interno é diferente da variável no bloco do meio, mesmo que ambas as variáveis tenham o mesmo nome.

CREATE PROCEDURE duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  DECLARE
    PV_NAME VARCHAR;
  BEGIN
    PV_NAME := 'middle block variable';
    DECLARE
      PV_NAME VARCHAR;
    BEGIN
      PV_NAME := 'innermost block variable';
      INSERT INTO names (v) VALUES (:PV_NAME);
    END;
    -- Because the innermost and middle blocks have separate variables
    -- named "pv_name", the INSERT below inserts the value
    -- 'middle block variable'.
    INSERT INTO names (v) VALUES (:PV_NAME);
  END;
  -- This inserts the value of the input parameter.
  INSERT INTO names (v) VALUES (:PV_NAME);
  RETURN 'Completed.';
END;
Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

CREATE PROCEDURE duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
        DECLARE
            PV_NAME VARCHAR;
        BEGIN
            PV_NAME := 'middle block variable';
            DECLARE
                PV_NAME VARCHAR;
            BEGIN
                PV_NAME := 'innermost block variable';
                INSERT INTO names (v) VALUES (:PV_NAME);
            END;
            -- Because the innermost and middle blocks have separate variables
            -- named "pv_name", the INSERT below inserts the value
            -- 'middle block variable'.
            INSERT INTO names (v) VALUES (:PV_NAME);
        END;
        -- This inserts the value of the input parameter.
        INSERT INTO names (v) VALUES (:PV_NAME);
        RETURN 'Completed.';
    END;
$$
;
Copy

Chame o procedimento armazenado:

CALL duplicate_name('parameter');
Copy

Verifique os valores na tabela. Isso mostra que:

  • No bloco mais interno (que foi aninhado duas camadas), foi utilizada a variável PV_NAME do bloco interno.

  • No bloco do meio (que foi aninhado uma camada), foi utilizada a variável PV_NAME do bloco do meio.

  • No bloco mais externo, o parâmetro foi utilizado.

    SELECT *
        FROM names
        ORDER BY v;
    +--------------------------+
    | V                        |
    |--------------------------|
    | innermost block variable |
    | middle block variable    |
    | parameter                |
    +--------------------------+
    
    Copy

Para um exemplo de vinculação de uma variável ao abrir um cursor, veja os exemplos de abertura de cursores.