Previous Up Next
Université Lille 3-Charles de GaulleUFR de Mathématiques Sciences Économiques et Sociales

Chapitre 59  PL/pgSQL : curseurs

Plutôt que d'exécuter la totalité d'une requête à la fois, il est possible de créer un curseur qui encapsule la requête, puis en lit le résultat quelques lignes à la fois. Une des raisons pour faire de la sorte est d'éviter les surcharges de mémoire quand le résultat contient un grand nombre de lignes. (Cependant, les utilisateurs PL/pgSQL n'ont généralement pas besoin de se préoccupper de cela, puisque les boucles FOR utilisent automatiquement un curseur en interne pour éviter les problèmes de mémoire). Un usage plus intéressant est de renvoyer une référence à un curseur qu'elle a créé, permettant à l'appelant de lire les lignes. Ceci fournit un moyen efficace de renvoyer de grands ensembles de lignes à partir des fonctions.

59.1  Déclaration de variables curseur

Tous les accès aux curseurs dans PL/pgSQL se font par les variables curseur, qui sont toujours du type de données spécial refcursor. Un des moyens de créer une variable curseur est de simplement la déclarer comme une variable de type refcursor. Un autre moyen est d'utiliser la syntaxe de déclaration de curseur qui est en général :
nom CURSOR [ ( arguments ) ] FOR requête ;
(FOR peut être remplacé par IS pour la compatibilité avec Oracle.) arguments, si spécifié, est une liste de paires de nom type-de-donnée qui définit les noms devant être remplacés par les valeurs des paramètres dans la requête donnée. La valeur effective à subsituer pour ces noms sera spécifiée plus tard, lors de l'ouverture du curseur.

Quelques exemples :
DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
Ces variables sont toutes trois du type de données refcursor, mais la première peut être utilisées avec n'importe quelle requête, alors que la seconde a une requête completement spécifiée qui lui est déjà liée, et la dernière est liée à une requête paramétrée. (key sera remplacée par un paramètre de valeur entière lors de l'ouverture du curseur.) La variable curs1 est dite non liée puiqu'elle n'est pas liée a une requête particulière.

59.2  Ouverture de curseurs

Avant qu'un curseur puisse être utilsé pour rapatrier des lignes, il doit être ouvert. (C'est l'action equivalente de la commande SQL DECLARE CURSOR.) PL/pgSQL a trois formes pour l'instruction OPEN, dont deux utilisent des variables curseur non liées et les autres utilisent une variable curseur liée.

59.2.1  OPEN FOR SELECT

OPEN curseur-non-lié FOR SELECT ...;
La variable curseur est ouverte et reçoit la requête spécifiée à exécuter. Le curseur ne peut pas être déjà ouvert, et il doit avoir été déclaré comme curseur non lié. (c'est à dire comme une simple variable refcursor). La requête SELECT est traitée de la même facon que les autres instructions SELECT dans PL/pgSQL : les noms de variables PL/pgSQL sont remplacés, et le plan de requête est mis en cache pour une possible réutilisation.

Exemple :
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

59.2.2  OPEN FOR EXECUTE

OPEN curseur-non-lié FOR EXECUTE chaîne-requête;
La variable curseur est ouverte et reçoit la requête spécifiée à exécuter. Le curseur ne peut pas être déjà ouvert, et il doit avoir été déclaré comme curseur non-lié (c'est a dire comme une simple variable refcursor. La requête est spécifiée comme une expression chaîne de la même facon que dans une commande EXECUTE. Comme d'habitude, ceci donne assez de flexibilité pour que la requête puisse changer d'une exécution à l'autre.

Exemple :
OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);

59.2.3  Ouverture d'un curseur lié

OPEN curseur-lié [ ( arguments ) ];
Cette forme d'OPEN est utilisée pour ouvrir une variable curseur à laquelle la requête est liée au moment de la déclaration. Le curseur ne peut pas être déjà ouvert. Une liste des expressions arguments doit apparaître si et seulement si le curseur a été déclaré comme acceptant des arguments. Ces valeurs seront remplacées dans la requête. Le plan de requête pour un curseur lié est toujours considéré comme pouvant être mis en cache ; il n'y a pas d'équivalent de la commande EXECUTE dans ce cas.

Exemples :
OPEN curs2;
OPEN curs3(42);

59.3  Utilisation des curseurs

Une fois qu'un curseur a été ouvert, il peut être manipulé grâce aux instructions décrites ci-dessous.

Ces manipulations n'ont pas besoin de se dérouler dans la même fonction que celle qui a ouvert le curseur. Vous pouvez renvoyer une valeur refcursor à partir d'une fonction et laisser l'appelant opérer sur le curseur. (D'un point de vue interne, une valeur refcursor est simplement la chaîne de caractères du nom d'un portail contenant la requête active pour le curseur. Ce nom peut être passé à d'autres, assigné à d'autres variables refcursor et ainsi de suite, sans déranger le portail.)

Tous les portails sont implicitement fermés à la fin de la transaction. C'est pourquoi une valeur refcursor est utilisable pour référencer un cuseur ouvert seulement jusqu'à la fin de la transaction.

59.3.1  FETCH

FETCH curseur INTO target;


FETCH rapatrie le rang suivant depuis le curseur dans une cible, qui peut être une variable ligne, une variable record, ou une liste de simples variables séparées d'une virgule, exactement comme SELECT INTO. Comme pour SELECT INTO, la variable spéciale FOUND peut être verifiée pour voir si une ligne a été obtenue ou pas.

Exemple :
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;

59.3.2  CLOSE

CLOSE curseur;


CLOSE ferme le portail sous-tendant un curseur ouvert. Ceci peut être utilisé pour libérer des ressources avant la fin de la transaction, ou de libérer la variable cuseur pour pouvoir la réouvrir.

Exemple :
CLOSE curs1;

59.3.3  Le renvoi de curseurs

Des PL/pgSQL peuvent renvoyer un curseur à l'appelant. Ceci est utilisé pour renvoyer plusieurs lignes ou colonnes d'une fonction. Pour ce faire, la fonction ouvre le curseur et renvoie le nom du curseur à l'appelant. L'appelant peut alors rapatrier des lignes du curseur. Le curseur peut être fermé par l'appelant, ou il peut être fermé automatiquement quand la transaction se termine.

Le nom du curseur renvoyé par la fonction peut être spécifié par l'appelant ou automatiquement généré. Les exemples suivants montrent comment un nom de curseur peut être fourni par l'appelant :
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
L'exemple suivant utilise la génération automatique du nom du curseur :
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
D.Gonzalez (gonzalez@univ-lille3.fr) en date du th , 

Previous Up Next