Como trabalhar com RESULTSETs¶
Este tópico explica como usar RESULTSETs no Script Snowflake.
Neste tópico:
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
.
Observe que se você tiver um cursor e precisar retornar uma tabela do bloco do Script Snowflake, 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> := ( <query> ) ;
Onde:
resultset_name
O nome a ser dado ao RESULTSET.
O nome deve ser único dentro do escopo atual.
O nome deve seguir as regras de nomenclatura para Identificadores de objetos.
query
A consulta a ser atribuída ao RESULTSET.
Por exemplo:
DECLARE res RESULTSET; BEGIN res := (SELECT col1 FROM mytable ORDER BY col1); ...
Se você precisar construir uma cadeia de caracteres de 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;
Nota: Embora você possa definir query
como uma instrução EXECUTE IMMEDIATE
para um RESULTSET, você não pode 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;
Observe que 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.
Observe que 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, não se pode usar um 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: Declarando uma variável RESULTSET sem cláusula DEFAULT
Configuração dos dados para os exemplos¶
Muitos dos exemplos abaixo usam a tabela e os dados mostrados abaixo:
CREATE 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;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 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 | +---+Observe que você 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: Construindo a instrução SQLdinamicamente¶
Você pode construir o SQL dinamicamente. A seguir, veja um exemplo que executa a mesma consulta que o procedimento armazenado acima, 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 ' || :table_name || ' ORDER BY a'; BEGIN res := (EXECUTE IMMEDIATE :query); RETURN TABLE (res); END;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 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 ' || :table_name || ' ORDER BY a'; BEGIN res := (EXECUTE IMMEDIATE :query); 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: Declarando 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;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 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; $$;
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;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 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:
CALL test_sp_03(); +------------+ | TEST_SP_03 | |------------| | 3 | +------------+