Como trabalhar com cursores

Você pode usar um cursor para iterar nos resultados da consulta uma linha de cada vez.

Introdução

Para obter dados a partir dos resultados de uma consulta, você pode usar um cursor. Para iterar sobre as linhas nos resultados, você pode usar um cursor em loops.

Para usar um cursor, faça o seguinte:

  1. Na seção DECLARE, declare o cursor. A declaração inclui a consulta para o cursor.

  2. Antes de usar o cursor pela primeira vez, execute o comando OPEN para abrir o cursor. Isso executa a consulta e carrega os resultados para o cursor.

  3. Execute o comando FETCH para obter uma ou mais linhas e processá-las.

  4. Quando você terminar de usar os resultados ou o cursor não for mais necessário, execute o comando CLOSE para fechar o cursor.

Nota

Você também pode usar um RESULTSET para recuperar os resultados de uma consulta quando você usa o Script Snowflake. Para obter informações sobre as diferenças entre um cursor e um RESULTSET, consulte Explicação das diferenças entre um cursor e um RESULTSET.

Configuração dos dados para os exemplos

Os exemplos nesta seção utilizam os seguintes dados:

CREATE OR REPLACE TABLE invoices (id INTEGER, price NUMBER(12, 2));

INSERT INTO invoices (id, price) VALUES
  (1, 11.11),
  (2, 22.22);
Copy

Como declarar um cursor

Você pode declarar um cursor para uma instrução SELECT ou um RESULTSET.

Você declara um cursor 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 do cursor.

    Por exemplo, para declarar um cursor para uma consulta:

    DECLARE
  ...
  c1 CURSOR FOR SELECT price FROM invoices;
    Copy

    Para declarar um cursor para um RESULTSET:

    DECLARE
  ...
  res RESULTSET DEFAULT (SELECT price FROM invoices);
  c1 CURSOR FOR res;
    Copy

  • Dentro do bloco BEGIN … END, use a sintaxe descrita em Sintaxe da atribuição do cursor. Por exemplo:

    BEGIN
  ...
  LET c1 CURSOR FOR SELECT price FROM invoices;
    Copy

Na instrução SELECT, você pode especificar parâmetros de vinculação (caracteres ?) que você pode vincular às variáveis ao abrir o cursor. Para vincular variáveis aos parâmetros, especifique as variáveis na cláusula USING do comando OPEN. Por exemplo:

DECLARE
  id INTEGER DEFAULT 0;
  minimum_price NUMBER(13,2) DEFAULT 22.00;
  maximum_price NUMBER(13,2) DEFAULT 33.00;
  c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
BEGIN
  OPEN c1 USING (minimum_price, maximum_price);
  FETCH c1 INTO id;
  RETURN id;
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):

EXECUTE IMMEDIATE $$
DECLARE
  id INTEGER DEFAULT 0;
  minimum_price NUMBER(13,2) DEFAULT 22.00;
  maximum_price NUMBER(13,2) DEFAULT 33.00;
  c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
BEGIN
  OPEN c1 USING (minimum_price, maximum_price);
  FETCH c1 INTO id;
  RETURN id;
END;
$$
;
Copy
+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

Como abrir um cursor

Embora a instrução que declara um cursor defina a consulta associada a esse cursor, a consulta não é executada até que você abra o cursor executando o comando OPEN. Por exemplo:

OPEN c1;
Copy

Nota

  • Ao utilizar um cursor em um loop FOR, você não precisa abrir o cursor explicitamente.

  • Se você declarar um cursor para um objeto RESULTSET, a consulta será executada quando você associar o objeto à consulta. Nesse caso, a abertura do cursor não faz com que a consulta seja executada novamente.

Se sua consulta contém quaisquer parâmetros de vinculação (caracteres ?), adicione uma cláusula USING para especificar a lista de variáveis a serem vinculadas a esses parâmetros. Por exemplo:

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);
Copy

A abertura do cursor executa a consulta, obtém as linhas especificadas no cursor e configura um ponteiro interno que aponta para a primeira linha. Você pode usar o comando FETCH para obter (ler) linhas individuais usando o cursor.

Como em qualquer consulta SQL, se a definição da consulta não contiver um ORDER BY no nível mais externo, o conjunto de resultados não terá uma ordem definida. Quando o conjunto de resultados definido para o cursor é criado, a ordem das linhas é persistente até que o cursor seja fechado. Se você declarar ou abrir o cursor novamente, as linhas poderão estar em uma ordem diferente. Da mesma forma, se você fechar o cursor e a tabela subjacente for atualizada antes que você abra o cursor novamente, o conjunto de resultados pode mudar.

Como usar um cursor para obter dados

Use o comando FETCH para obter a linha atual do conjunto de resultados e avançar o ponteiro interno da linha atual para apontar para a próxima linha no conjunto de resultados.

Na cláusula INTO, especifique as variáveis que contêm os valores da linha.

Por exemplo:

FETCH c1 INTO var_for_column_value;
Copy

Se o número de variáveis não corresponder ao número de expressões na cláusula SELECT da declaração do cursor, o Snowflake tentará corresponder as variáveis às colunas por posição:

  • Se houver mais variáveis do que colunas, o Snowflake deixará as variáveis restantes indefinidas.

  • Se houver mais colunas do que variáveis, o Snowflake ignorará as colunas restantes.

Cada comando FETCH subsequente que você executar receberá a próxima fileira até que a última linha tenha sido obtida. Se você tentar executar FETCH em uma linha após a última linha, você obterá valores NULL.

Um RESULTSET ou cursor não necessariamente armazena todas as linhas do conjunto de resultados definido no momento em que a consulta é executada. Operações FETCH podem sofrer latência.

Como usar um cursor para obter um valor GEOGRAPHY

Se os resultados incluírem uma coluna do tipo GEOGRAPHY, o tipo do valor na coluna será OBJECT, e não GEOGRAPHY. Isso significa que você não pode passar esse valor diretamente para funções geoespaciais que aceitem um objeto GEOGRAPHY como entrada:

DECLARE
  geohash_value VARCHAR;
BEGIN
  LET res RESULTSET := (SELECT TO_GEOGRAPHY('POINT(1 1)') AS GEOGRAPHY_VALUE);
  LET cur CURSOR FOR res;
  FOR row_variable IN cur DO
    geohash_value := ST_GEOHASH(row_variable.geography_value);
  END FOR;
  RETURN geohash_value;
END;
Copy
001044 (42P13): Uncaught exception of type 'EXPRESSION_ERROR' on line 7 at position 21 : SQL compilation error: ...
Invalid argument types for function 'ST_GEOHASH': (OBJECT)
Copy

Para contornar isso, converta o valor da coluna para o tipo GEOGRAPHY:

geohash_value := ST_GEOHASH(TO_GEOGRAPHY(row_variable.geography_value));
Copy

Como retornar uma tabela para um cursor

Se você precisar retornar uma tabela de dados de um cursor, você pode passar o cursor para RESULTSET_FROM_CURSOR(cursor), que por sua vez você pode passar para TABLE(...).

O bloco a seguir retorna uma tabela de dados a partir de um cursor:

DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
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):

EXECUTE IMMEDIATE $$
DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;
$$
;
Copy
+----+-------+
| ID | PRICE |
|----+-------|
|  1 | 11.11 |
|  2 | 22.22 |
+----+-------+

Mesmo que você já tenha usado o cursor para obter linhas, RESULTSET_FROM_CURSOR ainda retorna um RESULTSET contendo todas as linhas, e não apenas as linhas que começam a partir do ponteiro interno das linhas.

Como mostrado acima, o exemplo obtém a primeira linha e coloca o ponteiro interno da linha na segunda linha. RESULTSET_FROM_CURSOR retorna um RESULTSET contendo as duas linhas (e não apenas a segunda).

Como fechar um cursor

Quando você terminar com o conjunto de resultados, feche o cursor executando o comando CLOSE. Por exemplo:

CLOSE c1;
Copy

Nota

Ao utilizar um cursor em um loop FOR, você não precisa fechar o cursor explicitamente.

Não é possível executar o comando FETCH em um cursor que tenha sido fechado.

Além disso, depois de fechar um cursor, o ponteiro da linha atual torna-se inválido. Se você abrir o cursor novamente, o ponteiro apontará para a primeira linha do novo conjunto de resultados.

Exemplo de uso de um cursor

Esse exemplo usa dados que você configurou em Configuração dos dados para os exemplos.

Aqui está um bloco anônimo que usa um cursor para ler duas linhas e somar os preços nessas linhas:

DECLARE
  row_price FLOAT;
  total_price FLOAT;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  row_price := 0.0;
  total_price := 0.0;
  OPEN c1;
  FETCH c1 INTO row_price;
  total_price := total_price + row_price;
  FETCH c1 INTO row_price;
  total_price := total_price + row_price;
  CLOSE c1;
  RETURN total_price;
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):

EXECUTE IMMEDIATE $$
DECLARE
    row_price FLOAT;
    total_price FLOAT;
    c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
    row_price := 0.0;
    total_price := 0.0;
    OPEN c1;
    FETCH c1 INTO row_price;
    total_price := total_price + row_price;
    FETCH c1 INTO row_price;
    total_price := total_price + row_price;
    CLOSE c1;
    RETURN total_price;
END;
$$
;
Copy
+-----------------+
| anonymous block |
|-----------------|
|           33.33 |
+-----------------+

É possível obter o mesmo resultado usando um cursor com um loop FOR:

DECLARE
  total_price FLOAT;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  total_price := 0.0;
  FOR record IN c1 DO
    total_price := total_price + record.price;
  END FOR;
  RETURN total_price;
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):

EXECUTE IMMEDIATE $$
DECLARE
  total_price FLOAT;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  total_price := 0.0;
  FOR record IN c1 DO
    total_price := total_price + record.price;
  END FOR;
  RETURN total_price;
END;
$$
;
Copy
+-----------------+
| anonymous block |
|-----------------|
|           33.33 |
+-----------------+

Solução de problemas com cursores

A seção a seguir descreve problemas comuns com cursores e identifica uma possível causa e solução em cada caso.

Sintoma: o cursor recupera cada segunda linha em vez de cada linha

  • Possível causa: você pode ter executado FETCH dentro de um loop FOR <registro> IN <cursor>. Um loop FOR em um cursor obtém automaticamente a próxima linha. Se você fizer outro fetch dentro do loop, você receberá cada segunda linha.

  • Solução possível: remova qualquer comando FETCH desnecessário dentro de um loop FOR.

Sintoma: comando FETCH recupera valores NULL inesperados

  • Possível causa: você pode ter executado FETCH dentro de um loop FOR <registro> IN <cursor>. Um loop FOR em um cursor obtém automaticamente a próxima linha. Se você fizer outro fetch dentro do loop, você receberá cada segunda linha. Se houver um número ímpar de linhas, a última recuperação tentará obter uma linha após a última, e os valores serão NULL.

  • Solução possível: remova qualquer comando FETCH desnecessário dentro de um loop FOR.

Linguagem: Português