Como trabalhar com RESULTSETs¶

Este tópico explica como usar um RESULTSET no Script Snowflake.

Introdução¶

No Script Snowflake, um RESULTSET é um tipo de dados SQL que aponta para o conjunto de resultados de uma consulta.

Como um RESULTSET é apenas um ponteiro para os resultados, você deve fazer uma das ações a seguir para acessar os resultados através do RESULTSET:

Use a sintaxe TABLE(...) para recuperar os resultados como uma tabela.
Itere sobre o RESULTSET com um cursor.

Exemplos de ambos estão incluídos abaixo.

Explicação das diferenças entre um cursor e um RESULTSET¶

Tanto o RESULTSET quanto o cursor permitem acesso ao conjunto de resultados de uma consulta. No entanto, esses objetos diferem das seguintes maneiras:

O momento em que a consulta é executada.
- Para um cursor, a consulta é executada quando você executa o comando OPEN no cursor.
- Para um RESULTSET, a consulta é executada quando você atribui a consulta ao RESULTSET (ou na seção DECLARE ou no bloco BEGIN … END).
Suporte para vinculação no comando OPEN.
- Quando você declara um cursor, você pode especificar os parâmetros de vinculação (caracteres ?). Mais tarde, quando você executar o comando OPEN, você poderá vincular variáveis a esses parâmetros na cláusula USING.
- O RESULTSET não oferece suporte para o comando OPEN. No entanto, você pode vincular variáveis em comandos SQL antes de retornar o conjunto de resultados.

Em geral, é mais simples usar um RESULTSET quando você deseja retornar uma tabela que contém o conjunto de resultados de uma consulta. No entanto, você também pode retornar uma tabela de um bloco de script do Snowflake com um cursor. Para fazer isso, você pode passar o cursor para RESULTSET_FROM_CURSOR(cursor) para retornar um RESULTSET e passar esse RESULTSET para TABLE(...). Consulte Como retornar uma tabela para um cursor.

Como declarar um RESULTSET¶

Você pode declarar um RESULTSET na seção DECLARE de um bloco ou na seção BEGIN … END do bloco.

Dentro da seção DECLARE, use a sintaxe descrita em Sintaxe da instrução RESULTSET. Por exemplo:
```
DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
```
Copy
Dentro do bloco BEGIN … END, use a sintaxe descrita em Sintaxe de atribuição do RESULTSET. Por exemplo:
```
BEGIN
  ...
  LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);
```
Copy

Atribuição de uma consulta a um RESULTSET declarado¶

Para atribuir o resultado de uma consulta a um RESULTSET que já tenha sido declarado, use a seguinte sintaxe:

<resultset_name> := [ ASYNC ] ( <query> ) ;

Copy

Onde:

resultset_name
O nome do RESULTSET.

O nome deve ser exclusivo no escopo atual.

O nome deve seguir as regras de nomenclatura para Identificadores de objetos.

ASYNC

Recurso em versão preliminar — Aberto

Disponível para todas as contas.

Executa a consulta como um trabalho filho assíncrono.

A consulta pode ser qualquer instrução SQL válida, incluindo instruções SELECT e instruções DML, como INSERT ou UPDATE.

Quando essa palavra-chave é omitida, o procedimento armazenado executa os trabalhos secundários sequencialmente, e cada trabalho secundário aguarda a conclusão do trabalho secundário em execução antes de iniciar.

É possível usar essa palavra-chave para executar vários trabalhos secundários simultaneamente, o que pode aumentar a eficiência e reduzir o tempo total de execução.

É possível usar as instruções AWAIT e CANCEL para gerenciar trabalhos filho assíncronos para um RESULTSET.

query
A consulta a ser atribuída ao RESULTSET.

Para atribuir uma consulta a um RESULTSET:

DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT col1 FROM mytable ORDER BY col1);
  ...

Copy

Para atribuir uma consulta a um RESULTSET e executar a consulta como um trabalho filho assíncrono:

DECLARE
  res RESULTSET;
BEGIN
  res := ASYNC (SELECT col1 FROM mytable ORDER BY col1);
  ...

Copy

Para criar uma cadeia de caracteres SQL dinamicamente para a consulta, defina query como (EXECUTE IMMEDIATE string_of_sql). Por exemplo:

DECLARE
  res RESULTSET;
  col_name VARCHAR;
  select_statement VARCHAR;
BEGIN
  col_name := 'col1';
  select_statement := 'SELECT ' || col_name || ' FROM mytable';
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Copy

Embora você possa definir query como uma instrução EXECUTE IMMEDIATE para um RESULTSET, não é possível fazer isso para um cursor.

Como usar um RESULTSET¶

A consulta para um RESULTSET é executada quando o objeto é associado a essa consulta. Por exemplo:

Quando você declara um RESULTSET e define a cláusula DEFAULT para uma consulta, a consulta é executada nesse momento.
Quando você usa o operador := para atribuir uma consulta a um RESULTSET, a consulta é executada nesse momento.

Nota

Como um RESULTSET aponta para o conjunto de resultados de uma consulta (e não contém o conjunto de resultados de uma consulta), um RESULTSET é válido somente enquanto os resultados da consulta estiverem em cache (normalmente 24 horas). Para obter mais detalhes sobre o cache de resultados de consultas, consulte Uso de resultados de consultas persistentes.

Após a execução da consulta, você pode acessar os resultados usando um cursor. Você também pode retornar os resultados como uma tabela de um procedimento armazenado.

Como usar um cursor para acessar dados de um RESULTSET
Como retornar um RESULTSET como uma tabela

Como usar um cursor para acessar dados de um RESULTSET¶

Para usar um cursor para acessar os dados de um RESULTSET, declare o cursor no objeto. Por exemplo:

DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
  c1 CURSOR FOR res;

Copy

Quando você declara um cursor em um RESULTSET, o cursor tem acesso aos dados que já estão no RESULTSET. A execução do comando OPEN no cursor não executa novamente a consulta para o RESULTSET.

Você pode então abrir o cursor e usar o cursor para obter os dados.

Nota

Se os resultados incluírem valores GEOGRAPHY, você deve converter os valores para o tipo GEOGRAPHY antes de passar os valores para qualquer função que espere valores de entrada GEOGRAPHY. Consulte Como usar um cursor para obter um valor GEOGRAPHY.

Como retornar um RESULTSET como uma tabela¶

Se você quiser retornar os resultados para os quais o RESULTSET aponta, passe o RESULTSET para TABLE(...). Por exemplo:

CREATE PROCEDURE f()
  RETURNS TABLE(column_1 INTEGER, column_2 VARCHAR)
  ...
    RETURN TABLE(my_resultset_1);
  ...

Copy

Isso é semelhante à forma como TABLE(...) é usado com funções de tabela (tais como RESULT_SCAN).

Como mostrado no exemplo, se você escreve um procedimento armazenado que retorna uma tabela, você deve declarar que o procedimento armazenado retorna uma tabela.

Nota

Atualmente, a sintaxe TABLE(resultset_name) é suportada apenas na instrução RETURN.

Mesmo que você tenha usado um cursor para obter linhas do RESULTSET, a tabela retornada por TABLE(resultset_name) ainda conterá todas as linhas (não apenas as linhas que começam no ponteiro de linha interno do cursor).

Limitações do tipo de dados RESULTSET¶

Embora RESULTSET seja um tipo de dados, o Snowflake ainda não oferece suporte para:

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

O Snowflake somente oferece suporte para o RESULTSET dentro do Script Snowflake.

Além disso, você não pode usar o RESULTSET diretamente como uma tabela. Por exemplo, o seguinte é inválido:

SELECT * FROM my_result_set;

Copy

Exemplos de uso de um RESULTSET¶

As seções a seguir fornecem exemplos de uso de um RESULTSET:

Configuração dos dados para os exemplos
Exemplo: retorno de uma tabela de um procedimento armazenado
Exemplo: construção da instrução SQL
Exemplo: declaração de uma variável RESULTSET sem cláusula DEFAULT
Exemplo: como usar um CURSOR com um RESULTSET
Exemplo: como executar trabalhos filhos que consultam tabelas ao mesmo tempo
Exemplo: como executar trabalhos filhos que inserem linhas nas tabelas ao mesmo tempo
Outros exemplos que usam um RESULTSET

Configuração dos dados para os exemplos¶

Muitos dos exemplos abaixo usam a tabela e os dados mostrados abaixo:

CREATE OR REPLACE TABLE t001 (a INTEGER, b VARCHAR);
INSERT INTO t001 (a, b) VALUES
  (1, 'row1'),
  (2, 'row2');

Copy

Exemplo: retorno de uma tabela de um procedimento armazenado¶

O código a seguir mostra como declarar um RESULTSET e retornar os resultados para os quais o RESULTSET aponta. A cláusula RETURNS no comando CREATE PROCEDURE declara que o procedimento armazenado retorna uma tabela, que contém uma coluna do tipo INTEGER.

A instrução RETURN dentro do bloco usa a sintaxe TABLE(...) para retornar os resultados como uma tabela.

Crie o procedimento armazenado:

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  BEGIN
    RETURN TABLE(res);
  END;

Copy

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 test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET default (SELECT a FROM t001 ORDER BY a);
  BEGIN
      RETURN TABLE(res);
  END;
$$;

Copy

Chame o procedimento armazenado:

CALL test_sp();

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Você também pode usar a função RESULT_SCAN para processar os resultados da chamada do procedimento armazenado:

SELECT *
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  ORDER BY 1;

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Exemplo: construção da instrução SQL¶

Você pode construir o SQL dinamicamente. A seguir, veja um exemplo que executa a mesma consulta que o procedimento armazenado anterior, mas usando uma instrução SQL que é construída dinamicamente:

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
  RETURNS TABLE(a INTEGER)
  LANGUAGE SQL
AS
DECLARE
  res RESULTSET;
  query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
BEGIN
  res := (EXECUTE IMMEDIATE :query USING(table_name));
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
  BEGIN
    res := (EXECUTE IMMEDIATE :query USING(table_name));
    RETURN TABLE(res);
  END
$$
;

Copy

Para executar o exemplo, chame o procedimento armazenado e passe o nome da tabela:

CALL test_sp_dynamic('t001');

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Exemplo: declaração de uma variável RESULTSET sem cláusula DEFAULT¶

O código a seguir mostra como declarar um RESULTSET sem uma cláusula DEFAULT (isso é, sem associar uma consulta com o RESULTSET) e depois associar o RESULTSET a uma consulta mais tarde.

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    res := (SELECT a FROM t001 ORDER BY a);
    RETURN TABLE(res);
  END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET;
  BEGIN
      res := (SELECT a FROM t001 ORDER BY a);
      RETURN TABLE(res);
  END;
$$;

Copy

Para executar o exemplo, chame o procedimento armazenado:

CALL test_sp_02();

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Exemplo: como usar um CURSOR com um RESULTSET¶

O código a seguir mostra como usar um cursor para iterar sobre as linhas em um RESULTSET:

Crie o procedimento armazenado:

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS VARCHAR
LANGUAGE SQL
AS

DECLARE
  accumulator INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  cur1 CURSOR FOR res1;
BEGIN
  FOR row_variable IN cur1 DO
    accumulator := accumulator + row_variable.a;
  END FOR;
  RETURN accumulator::VARCHAR;
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
    cur1 CURSOR FOR res1;
  BEGIN
    FOR row_variable IN cur1 DO
        accumulator := accumulator + row_variable.a;
    END FOR;
    RETURN accumulator;
  END;
$$;

Copy

Chame o procedimento armazenado e os resultados somam os valores para a na tabela (1 + 2):

CALL test_sp_03();

Copy

+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+

Exemplo: como executar trabalhos filhos que consultam tabelas ao mesmo tempo¶

O código a seguir mostra como usar a palavra-chave ASYNC para executar vários trabalhos filhos que consultam tabelas simultaneamente.

Este exemplo usa os dados das tabelas a seguir:

CREATE OR REPLACE TABLE orders_q1_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q1_2024 VALUES (1, 500.00);
INSERT INTO orders_q1_2024 VALUES (2, 225.00);
INSERT INTO orders_q1_2024 VALUES (3, 725.00);
INSERT INTO orders_q1_2024 VALUES (4, 150.00);
INSERT INTO orders_q1_2024 VALUES (5, 900.00);

CREATE OR REPLACE TABLE orders_q2_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q2_2024 VALUES (1, 100.00);
INSERT INTO orders_q2_2024 VALUES (2, 645.00);
INSERT INTO orders_q2_2024 VALUES (3, 275.00);
INSERT INTO orders_q2_2024 VALUES (4, 800.00);
INSERT INTO orders_q2_2024 VALUES (5, 250.00);

Copy

O procedimento armazenado a seguir executa as seguintes ações:

Consulta ambas as tabelas para os valores order_amount em todas as linhas e retorna os resultados para diferentes RESULTSETs (um para cada tabela).
Especifica que as consultas são executadas como trabalhos filhos simultâneos usando a palavra-chave ASYNC.
Executa a instrução AWAIT para cada RESULTSET para que o procedimento aguarde a conclusão das consultas antes de prosseguir. Os resultados de consulta para um RESULTSET não podem ser acessados até que o AWAIT seja executado para o RESULTSET.
Usa um cursor para calcular a soma das linhas order_amount de cada tabela.
Adiciona os totais das tabelas e retorna o valor.

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  accumulator1 INTEGER DEFAULT 0;
  accumulator2 INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
  res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
BEGIN
  AWAIT res1;
  LET cur1 CURSOR FOR res1;
  OPEN cur1;
  AWAIT res2;
  LET cur2 CURSOR FOR res2;
  OPEN cur2;
  FOR row_variable IN cur1 DO
      accumulator1 := accumulator1 + row_variable.order_amount;
  END FOR;
  FOR row_variable IN cur2 DO
      accumulator2 := accumulator2 + row_variable.order_amount;
  END FOR;
  RETURN accumulator1 + accumulator2;
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator1 INTEGER DEFAULT 0;
    accumulator2 INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
    res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
  BEGIN
    AWAIT res1;
    LET cur1 CURSOR FOR res1;
    OPEN cur1;
    AWAIT res2;
    LET cur2 CURSOR FOR res2;
    OPEN cur2;
    FOR row_variable IN cur1 DO
        accumulator1 := accumulator1 + row_variable.order_amount;
    END FOR;
    FOR row_variable IN cur2 DO
        accumulator2 := accumulator2 + row_variable.order_amount;
    END FOR;
    RETURN accumulator1 + accumulator2;
  END;
$$;

Copy

Chame o procedimento armazenado:

CALL test_sp_async_child_jobs_query();

Copy

+--------------------------------+
| TEST_SP_ASYNC_CHILD_JOBS_QUERY |
|--------------------------------|
|                           4570 |
+--------------------------------+

Exemplo: como executar trabalhos filhos que inserem linhas nas tabelas ao mesmo tempo¶

O código a seguir mostra como usar a palavra-chave ASYNC para executar vários trabalhos filhos que inserem linhas em uma tabela simultaneamente.

O procedimento armazenado a seguir executa as seguintes ações:

Cria a tabela orders_q3_2024 se ela não existir.
Cria dois RESULTSETs, insert_1 e insert_2, que contêm os resultados das inserções na tabela. Os argumentos do procedimento armazenado especificam os valores que são inseridos na tabela.
Especifica que as inserções são executadas como trabalhos filhos simultâneos usando a palavra-chave ASYNC.
Executa a instrução AWAIT para cada RESULTSET para que o procedimento aguarde a conclusão das inserções antes de prosseguir. Os resultados de um RESULTSET não podem ser acessados até que o AWAIT seja executado para o RESULTSET.
Cria um novo RESULTSET res que contém os resultados de uma consulta na tabela orders_q3_2024.
Retorna os resultados da consulta.

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
$$
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;
$$;

Copy

Chame o procedimento armazenado:

CALL test_sp_async_child_jobs_insert(1, 325, 2, 241);

Copy

+----------+--------------+
| ORDER_ID | ORDER_AMOUNT |
|----------+--------------|
|        1 |       325.00 |
|        2 |       241.00 |
+----------+--------------+

Outros exemplos que usam um RESULTSET¶

Veja a seguir outros exemplos que usam o RESULTSET:

Use um loop FOR baseado em RESULTSET

Este exemplo mostra como usar um loop FOR que itera sobre um RESULTSET.
Retorne uma tabela para um cursor

Este exemplo mostra como usar um cursor para retornar uma tabela de dados em um RESULTSET.
Atualização de dados da tabela com entrada do usuário

Este exemplo mostra como usar variáveis de vinculação com base na entrada do usuário para atualizar dados em uma tabela. Ele usa um loop FOR com lógica condicional para iterar sobre as linhas em um RESULTSET.
Filtro e coleta de dados

Este exemplo mostra como usar o RESULTSET para coletar dados e inseri-los em uma tabela para rastrear tendências históricas.