Como trabalhar com RESULTSETs¶

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.

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.

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

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

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

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

Copy

Chame o procedimento armazenado:

CALL test_sp();
+---+
| A |
|---|
| 1 |
| 2 |
+---+

Copy

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

Copy

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;

Copy

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

Copy

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

call test_sp_dynamic('t001');

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

Copy

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;

Copy

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

Copy

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

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

Copy

Chame o procedimento armazenado:

CALL test_sp_03();
+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+

Copy