Travailler avec des curseurs

Vous pouvez utiliser un curseur pour itérer dans les résultats d’une requête, une ligne à la fois.

Dans ce chapitre :

Introduction

Pour récupérer des données à partir des résultats d’une requête, utilisez un curseur. Vous pouvez utiliser un curseur dans des boucles pour itérer sur les lignes des résultats.

Pour utiliser un curseur, procédez comme suit :

  1. Dans la section DECLARE, déclarez le curseur. La déclaration comprend la requête pour le curseur.

  2. Exécutez la commande OPEN pour ouvrir le curseur. Cela exécute la requête et charge les résultats dans le curseur.

  3. Exécutez la commande FETCH pour extraire une ou plusieurs lignes et traiter ces lignes.

  4. Lorsque vous avez terminé les résultats, exécutez la commande CLOSE pour fermer le curseur.

Configuration des données pour les exemples

Les exemples de cette section utilisent les données suivantes :

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

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

Déclarer un curseur

Vous pouvez déclarer un curseur pour une instruction SELECT ou un RESULTSET.

Vous déclarez un curseur dans la section DECLARE d’un bloc ou dans la section BEGIN … END du bloc :

  • Dans la section DECLARE, utilisez la syntaxe décrite dans Syntaxe de déclaration du curseur.

    Par exemple, pour déclarer un curseur pour une requête :

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

    Pour déclarer un curseur pour un RESULTSET :

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

  • Dans le bloc BEGIN … END, utilisez la syntaxe décrite dans Syntaxe d’affectation du curseur. Par exemple :

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

Dans l’instruction SELECT, vous pouvez spécifier des paramètres de liaison (caractères ?) que vous pouvez lier à des variables lors de l’ouverture du curseur. Pour lier des variables aux paramètres, spécifiez les variables dans la clause USING de la commande OPEN. Par exemple :

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

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”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

Ouverture d’un curseur

Bien que l’instruction qui déclare un curseur définisse la requête associée à ce curseur, la requête n’est pas exécutée tant que vous n’avez pas ouvert le curseur en exécutant la commande OPEN. Par exemple :

OPEN c1;
Copy

Note

  • Lorsque vous utilisez un curseur dans une boucle FOR, vous n’avez pas besoin d’ouvrir le curseur explicitement.

  • Si vous déclarez un curseur pour un objet RESULTSET, la requête est exécutée lorsque vous associez l’objet à la requête. Dans ce cas, l’ouverture du curseur ne provoque pas une nouvelle exécution de la requête.

Si votre requête contient des paramètres de liaison (caractères ?), ajoutez une clause USING pour spécifier la liste des variables à lier à ces paramètres. Par exemple :

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

L’ouverture du curseur exécute la requête, récupère les lignes spécifiées dans le curseur, et met en place un pointeur interne qui pointe sur la première ligne. Vous pouvez utiliser la commande FETCH pour extraire (lire) des lignes individuelles en utilisant le curseur.

Comme pour toute requête SQL, si la définition de la requête ne contient pas de ORDER BY au niveau le plus externe, le jeu de résultats n’a pas d’ordre défini. Lorsque le jeu de résultats pour le curseur est créé, l’ordre des lignes est persistant jusqu’à ce que le curseur soit fermé. Notez que si vous déclarez ou ouvrez à nouveau le curseur, les lignes peuvent être dans un ordre différent. De même, si vous fermez le curseur et que la table sous-jacente est mise à jour avant que vous n’ouvriez à nouveau le curseur, le jeu de résultats peut également changer.

Utilisation d’un curseur pour récupérer des données

Utilisez la commande FETCH pour récupérer la ligne actuelle dans le jeu de résultats et faire avancer le pointeur de ligne actuelle interne pour qu’il pointe sur la ligne suivante dans le jeu de résultats.

Dans la clause INTO, spécifiez les variables qui doivent être utilisées pour contenir les valeurs de la ligne.

Par exemple :

FETCH c1 INTO var_for_column_value;
Copy

Si le nombre de variables ne correspond pas au nombre d’expressions dans la clause SELECT de la déclaration du curseur, Snowflake tente de faire correspondre les variables avec les colonnes par position :

  • S’il y a plus de variables que de colonnes, Snowflake laisse les variables restantes non définies.

  • S’il y a plus de colonnes que de variables, Snowflake ignore les colonnes restantes.

Chaque commande FETCH suivante que vous exécutez obtient la ligne suivante jusqu’à ce que la dernière ligne ait été extraite. Si vous essayez de FETCH une ligne après la dernière ligne, vous obtenez des valeurs NULL.

Un RESULTSET ou CURSOR ne met pas nécessairement en cache toutes les lignes du jeu de résultats au moment de l’exécution de la requête. Les opérations FETCH peuvent subir une latence.

Utiliser un curseur pour récupérer une valeur GEOGRAPHY

Si les résultats comprennent une colonne de type GEOGRAPHY, le type de la valeur dans la colonne est OBJECT, et non GEOGRAPHY. Cela signifie que vous ne pouvez pas transmettre directement cette valeur aux fonctions géospatiales qui acceptent un objet GEOGRAPHY en entrée :

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

Pour contourner ce problème, attribuez à la valeur de la colonne le type GEOGRAPHY :

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

Retourner une table pour un curseur

Si vous devez retourner une table de données à partir d’un curseur, vous pouvez passer le curseur à RESULTSET_FROM_CURSOR(cursor), qui à son tour peut être passé à TABLE(...).

Le bloc suivant renvoie une table de données à partir d’un curseur :

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

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”Classic Console) :

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

Cet exemple produit le résultat suivant :

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

Notez que même si vous avez déjà utilisé le curseur pour extraire des lignes, RESULTSET_FROM_CURSOR renvoie toujours un RESULTSET contenant toutes les lignes, et pas seulement celles qui commencent à partir du pointeur de ligne interne.

Comme indiqué ci-dessus, l’exemple récupère la première ligne et définit le pointeur de ligne interne sur la deuxième ligne. RESULTSET_FROM_CURSOR renvoie un RESULTSET contenant les deux lignes (pas seulement la deuxième).

Clôture d’un curseur

Lorsque vous avez terminé avec le jeu de résultats, fermez le curseur en exécutant la commande CLOSE. Par exemple :

CLOSE c1;
Copy

Note

Lorsque vous utilisez un curseur dans une boucle FOR, vous n’avez pas besoin de fermer le curseur explicitement.

Vous ne pouvez pas exécuter la commande FETCH sur un curseur qui a été fermé.

En outre, après avoir fermé un curseur, le pointeur de ligne actuel devient non valide. Si vous ouvrez à nouveau le curseur, celui-ci pointe sur la première ligne du nouveau jeu de résultats.

Exemple d’utilisation d’un curseur

Cet exemple utilise les données que vous avez configurées dans Configuration des données pour les exemples.

Voici une procédure stockée qui utilise un curseur pour lire deux lignes et additionner les prix dans ces lignes :

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

Remarque : si vous utilisez SnowSQL ou l”Classic Console, utilisez cet exemple à la place (voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l”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

Cet exemple produit le résultat suivant :

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

Un exemple utilisant une boucle est inclus dans la documentation des boucles FOR.

Dépannage des problèmes liés aux curseurs

Symptôme : le curseur semble récupérer une ligne sur deux plutôt que chaque ligne.

Cause possible :

Vous avez peut-être exécuté FETCH dans une boucle FOR <record> IN <cursor>. Une boucle FOR sur un curseur récupère automatiquement la ligne suivante. Si vous faites une autre opération fetch à l’intérieur de la boucle, vous obtenez une ligne sur deux.

Solution possible :

Supprimez toute opération FETCH inutile à l’intérieur d’une boucle FOR.

Symptôme : votre commande FETCH récupère des valeurs NULL inattendues.

Cause possible :

Vous avez peut-être exécuté FETCH dans une boucle FOR <record> IN <cursor>. Une boucle FOR sur un curseur récupère automatiquement la ligne suivante. Si vous faites une autre opération fetch à l’intérieur de la boucle, vous obtenez une ligne sur deux. S’il y a un nombre impair de lignes, la dernière opération FETCH essaiera de récupérer une ligne au-delà de la dernière ligne, et les valeurs seront NULL.

Solution possible :

Supprimez toute opération FETCH inutile à l’intérieur d’une boucle FOR.

Langage: Français