Como trabalhar com RESULTSETs¶

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> := ( <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.

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;

Embora você possa definir query como uma instrução EXECUTEIMMEDIATE 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.

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

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

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

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:

• 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

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

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;

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

CALL test_sp();

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

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

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

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;

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

CALL test_sp_dynamic('t001');

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

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;

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

CALL test_sp_02();

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

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;

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

CALL test_sp_03();

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