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);
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);
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> ) ;
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
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);
...
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);
...
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;
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¶
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;
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);
...
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;
Exemplos de uso de um RESULTSET¶
As seções a seguir fornecem exemplos de uso de um RESULTSET:
Exemplo: retorno de uma tabela de um procedimento armazenado
Exemplo: declaração de uma variável RESULTSET sem cláusula DEFAULT
Exemplo: como executar trabalhos filhos que consultam tabelas ao mesmo tempo
Exemplo: como executar trabalhos filhos que inserem linhas nas tabelas ao mesmo tempo
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');
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;
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;
$$;
Chame o procedimento armazenado:
CALL test_sp();
+---+
| 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;
+---+
| 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;
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_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
$$
;
Para executar o exemplo, chame o procedimento armazenado e passe o nome da tabela:
CALL test_sp_dynamic('t001');
+---+
| 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;
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_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET;
BEGIN
res := (SELECT a FROM t001 ORDER BY a);
RETURN TABLE(res);
END;
$$;
Para executar o exemplo, chame o procedimento armazenado:
CALL test_sp_02();
+---+
| 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;
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_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;
$$;
Chame o procedimento armazenado e os resultados somam os valores para a
na tabela (1 + 2):
CALL test_sp_03();
+------------+
| 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);
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;
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_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;
$$;
Chame o procedimento armazenado:
CALL test_sp_async_child_jobs_query();
+--------------------------------+
| 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
einsert_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 tabelaorders_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;
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_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;
$$;
Chame o procedimento armazenado:
CALL test_sp_async_child_jobs_insert(1, 325, 2, 241);
+----------+--------------+
| 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.
-
Este exemplo mostra como usar o RESULTSET para coletar dados e inseri-los em uma tabela para rastrear tendências históricas.