Como trabalhar com cursores¶

Introdução¶

Para obter dados a partir dos resultados de uma consulta, use um cursor. Você pode usar um cursor em loops para iterar nas linhas dos resultados.

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. 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 terminar com os resultados, execute o comando CLOSE para fechar o cursor.

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

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

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

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. Observe que 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 também 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 devem ser usadas para manter 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;

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)

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

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

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

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;
$$
;

Copy

Esse exemplo produz a seguinte saída:

+----+-------+
| ID | PRICE |
|----+-------|
|  1 | 11.11 |
|  2 | 22.22 |
+----+-------+

Copy

Observe que 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

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 procedimento armazenado 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

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Esse exemplo produz a seguinte saída:

+-----------------+
| anonymous block |
|-----------------|
|           33.33 |
+-----------------+

Copy

Um exemplo usando um loop está incluído na documentação para loops FOR.

Solução de problemas com cursores¶

Sintoma: o cursor parece obter cada segunda fila 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.

Possível solução:

Remova qualquer FETCH desnecessário dentro de um loop FOR.

Sintoma: seu comando FETCH obtém 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, o último FETCH tentará obter uma linha após a última, e os valores serão NULL.

Possível solução:

Remova qualquer FETCH desnecessário dentro de um loop FOR.