Como trabalhar com variáveis¶

Como declarar uma variável¶

Antes de poder usar uma variável, você deve declarar a variável. Ao declarar uma variável, você deve especificar o tipo da variável de uma das seguintes maneiras:

• Especificando explicitamente o tipo de dados.

• Especificando uma expressão com o valor inicial da variável. O Script Snowflake usa a expressão para determinar o tipo de dados da variável. Consulte Como o Script Snowflake infere o tipo de dados de uma variável.

Você pode declarar uma variável das seguintes maneiras:

• 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. O tipo de dados pode ser qualquer um dos seguintes:

• Um tipo de dados SQL

• CURSOR

• RESULTSET

• EXCEPTION

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;

Observação: se você estiver usando o método SnowSQL, o Classic Console, execute_stream ou execute_string no código Python Connector, use este exemplo (consulte Uso do Script Snowflake em SnowSQL, Classic Console e conector Python):

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;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|           10.00 |
+-----------------+

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

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

• Explicação do escopo das declarações

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 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> ;

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 SQL e UDFs (funções definidas pelo usuário).

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;

Para usar uma variável em um manipulador de exceção (a seção EXCEPTION de um bloco), a variável deve ser declarada na seção DECLARE ou passada como um argumento para um procedimento armazenado. Não pode ser declarada na seção BEGIN … END. Para obter mais informações, consulte Como passar variáveis para um manipulador de exceção.

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

Você pode usar uma variável em uma instrução SQL, que às vezes é chamada de vínculo de uma variável. Para fazer isso, preceda o nome da variável com dois pontos. Por exemplo:

INSERT INTO my_table (x) VALUES (:my_variable)

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)

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, não é necessário o prefixo de dois pontos nos seguintes casos:

• 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

Além disso, a função TO_QUERY fornece uma sintaxe simples para aceitar uma cadeia de caracteres SQL diretamente na cláusula FROM de uma instrução SELECT. Para uma comparação da função TO_QUERY com SQL dinâmico, consulte Construção de SQL no tempo de execução.

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 ...;

Quando você usa essa sintaxe:

• variable1 é definido como o valor de expression1.

• variable2 é definido como o valor de expression2.

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');

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

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

Observação: se você estiver usando o método SnowSQL, o Classic Console, execute_stream ou execute_string no código Python Connector, use este exemplo (consulte Uso do Script Snowflake em SnowSQL, Classic Console e conector Python):

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

O exemplo imprime a id e o name da linha retornada pela instrução SELECT.

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

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;

Observação: se você estiver usando o método SnowSQL, o Classic Console, execute_stream ou execute_string no código Python Connector, use este exemplo (consulte Uso do Script Snowflake em SnowSQL, Classic Console e conector Python):

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;
$$
;

+-----------------+
| anonymous block |
|-----------------|
| 168, 2020-09-30 |
+-----------------+

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

my_variable := SQRT(variable_x);

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;

Observação: se você estiver usando o método SnowSQL, o Classic Console, execute_stream ou execute_string no código Python Connector, use este exemplo (consulte Uso do Script Snowflake em SnowSQL, Classic Console e conector Python):

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

+-----------------+
| anonymous block |
|-----------------|
|              10 |
+-----------------+

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).

O exemplo se baseia nesta tabela:

CREATE OR REPLACE TABLE names (v VARCHAR);

Neste 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 OR REPLACE 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;

Observação: se você estiver usando o método SnowSQL, o Classic Console, execute_stream ou execute_string no código Python Connector, use este exemplo (consulte Uso do Script Snowflake em SnowSQL, Classic Console e conector Python):

CREATE OR REPLACE 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;
$$
;

Chame o procedimento armazenado:

CALL duplicate_name('parameter');

Verifique os valores na tabela:

SELECT *
    FROM names
    ORDER BY v;

+--------------------------+
| V                        |
|--------------------------|
| innermost block variable |
| middle block variable    |
| parameter                |
+--------------------------+

A saída mostra o seguinte:

• 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.

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