Guide Du Langage SQL De Firebird 5.0 | Écrire Le Code Du Corps Du Module | EXECUTE STATEMENT | ON EXTERNAL [DATA SOURCE]

Responsables du traitement des données(DML)fonctions scalaires

Autres commentaires

Le jeu de caractères utilisé pour la connexion externe est le même que celui utilisé pour la connexion actuelle.
Les transactions biphasiques ne sont pas prises en charge.

AS USER, PASSWORD et ROLE

Les clauses facultatives AS USER, PASSWORD et ROLE vous permettent de spécifier au nom de quel utilisateur, et avec quel rôle, l’instruction SQL sera exécutée.La façon dont l’utilisateur est autorisé et si une connexion séparée est ouverte dépend de la présence et des valeurs des paramètres ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD et ROLE.

Lorsque la suggestion ON EXTERNAL est utilisée, une nouvelle connexion est ouverte et :
- Si au moins un des paramètres AS USER, PASSWORD et ROLE est présent, une authentification native sera tentée avec les valeurs des paramètres spécifiés (selon la chaîne de connexion — locale ou distante). Aucune valeur par défaut n’est utilisée pour les paramètres manquants ;
- Si les trois paramètres sont manquants et que la chaîne de connexion ne contient pas de nom de serveur (ou d’adresse IP), une nouvelle connexion est établie avec un serveur local avec l’utilisateur et le rôle de la connexion actuelle. Le terme "local" signifie "l’ordinateur sur lequel le serveur Firebird est installé". Il ne s’agit pas nécessairement de l’ordinateur client ;
- Si ces trois éléments sont absents mais que la chaîne de connexion contient un nom de serveur (ou une adresse IP), une authentification fiable sera tentée sur le serveur distant. Si l’autorisation est réussie, le système d’exploitation distant attribue un nom à l’utilisateur — généralement le compte sous lequel le serveur Firebird est exécuté.
Si l’invite ON EXTERNAL n’est pas présente :
- Si au moins un des paramètres AS USER, PASSWORD et ROLE est présent, une connexion à la base de données courante sera ouverte avec les valeurs des paramètres spécifiés. Aucune valeur par défaut n’est utilisée pour les paramètres manquants ;
- Si les trois paramètres sont manquants, l’Instruction est exécuté dans la connexion en cours.

Important

Si la valeur du paramètre est NULL ou '', alors le paramètre entier est considéré comme manquant. De même, si le paramètre est considéré comme manquant, alors AS USER prend la valeur CURRENT_USER et ROLE — CURRENT_ROLE. La comparaison des autorisations est sensible à la casse : dans la plupart des cas, cela signifie que les noms d’utilisateurs et les rôles doivent être écrits en majuscules.

Précautions

Il n’y a aucun moyen de vérifier la syntaxe de l’instruction SQL en cours d’exécution ;
Il n’y a pas de contrôle de dépendance pour détecter les colonnes supprimées dans le tableau ou le tableau lui-même ;
L’exécution de l’instruction à l’aide de l’instruction EXECUTE STATEMENT est nettement plus lente que son exécution directe ;
Le type de données des valeurs renvoyées est strictement vérifié afin d’éviter les exceptions de conversion de type imprévisibles. Par exemple, la chaîne "1234" sera convertie en un nombre entier 1234, tandis que la chaîne "abc" entraînera une erreur de conversion.

En général, cette fonction doit être utilisée avec beaucoup de précaution, et les facteurs mentionnés ci-dessus doivent toujours être pris en compte.Si le même résultat peut être obtenu en utilisant PSQL et/ou DSQL, c’est toujours préférable.

Voir aussi :

FOR EXECUTE STATEMENT.

`FOR SELECT`

Destination

Parcourir les lignes du résultat de l’Instruction SELECT.

Disponible en

PSQL

Syntaxe

[label:]
FOR
  <select_stmt>
  [INTO <variables>]
  [AS CURSOR cursorname]
DO <compound_statement>

<variables> ::= [:{endsb}varname [, [:{endsb}varname ...]

Table 1. Paramètres de l’instruction `FOR SELECT`
Paramètre	Description
label	Balise facultative pour `LEAVE` et/ou `CONTINUE`. Doit suivre les règles pour les identifiants.
select_stmt	Instruction `SELECT`.
cursorname	Le nom du curseur doit être unique parmi les noms de variables et de curseurs du module PSQL.
varname	Nom de la variable locale ou du paramètre d’entrée/sortie.
compound_statement	Instruction composé (Instruction ou bloc d’Instructions).

L’instruction FOR SELECT sélectionne la ligne suivante dans une table (vue, procédure stockée sélective), suivie d’une instruction composée. À chaque itération de la boucle, les valeurs des champs de la ligne courante sont copiées dans des variables locales. L’ajout de la clause AS CURSOR rend possible la suppression et la mise à jour des données positionnelles. Les instructions FOR SELECT peuvent être imbriquées.

L’instruction FOR SELECT peut contenir des paramètres nommés qui doivent être préalablement déclarés dans l’instruction DECLARE VARIABLE, ou dans les paramètres d’entrée (de sortie) d’une procédure (bloc PSQL).

L’instruction FOR SELECT doit contenir une clause INTO à la fin de cette instruction, ou une instruction AS CURSOR. A chaque itération de la boucle, les valeurs des champs de la chaîne de requête courante sont copiées dans la liste de variables spécifiée dans l’instruction INTO. La boucle est répétée jusqu’à ce que toutes les lignes aient été lues. Ensuite, la boucle est quittée. La boucle peut également être terminée avant que toutes les lignes soient lues en utilisant l’instruction LEAVE.

Curseur inopiné

La clause facultative AS CURSOR crée un curseur nommé qui peut être référencé (en utilisant la clause WHERE CURRENT OF) dans l’instruction composée qui suit la clause DO afin de supprimer ou de modifier la ligne courante.

Il est permis d’utiliser le nom du curseur comme une variable de type enregistrement (similaire à OLD et NEW dans les triggers) qui permet d’accéder aux colonnes du jeu résultant (i.e. cursor_name . columnname). L’utilisation de la clause AS CURSOR rend la clause INTO facultative.

Règles pour les variables de curseur :

Pour résoudre l’ambiguïté lors de l’accès à une variable curseur, un préfixe deux-points est requis avant le nom du curseur ;
La variable curseur peut être accédée sans le préfixe deux-points, mais dans ce cas, selon la portée des contextes existants dans la requête, le nom peut être résolu comme un contexte de requête au lieu du curseur ;
Les variables du curseur sont en lecture seule ;
Dans une instruction FOR SELECT sans la clause AS CURSOR, la clause INTO doit être utilisée. Si la clause AS CURSOR est spécifiée, la clause INTO n’est pas requise mais autorisée ;
La lecture d’une variable curseur renvoie les valeurs actuelles des champs. Cela signifie que l’Instruction UPDATE (avec la clause WHERE CURRENT OF) mettra également à jour les valeurs des champs dans la variable curseur pour les lectures suivantes. L’exécution de l’instruction DELETE (avec la suggestion WHERE CURRENT OF) mettra NULL pour les valeurs des champs de la variable curseur pour les lectures suivantes.

Note

Les Instructions OPEN, FETCH et CLOSE ne peuvent pas être exécutés sur un curseur déclaré avec une clause AS CURSOR ;
Assurez-vous que le nom du curseur défini ici n’est pas le même qu’un nom précédemment créé par l’instruction DECLARE VARIABLE ;
La clause FOR UPDATE autorisée à être utilisée dans l’instruction SELECT n’est pas nécessaire pour une mise à jour ou une suppression positionnelle réussie.

Exemples en utilisant `FOR SELECT`

Example 1. Utilisation de l’Instruction FOR SELECT

CREATE PROCEDURE SHOWNUMS
RETURNS (
  AA INTEGER,
  BB INTEGER,
  SM INTEGER,
  DF INTEGER)
AS
BEGIN
  FOR SELECT DISTINCT A, B
      FROM NUMBERS
    ORDER BY A, B
    INTO AA, BB
  DO
  BEGIN
    SM = AA + BB;
    DF = AA - BB;
    SUSPEND;
  END
END

Example 2. Ci-joint FOR SELECT

CREATE PROCEDURE RELFIELDS
RETURNS (
  RELATION CHAR(32),
  POS INTEGER,
  FIELD CHAR(32))
AS
BEGIN
  FOR SELECT RDB$RELATION_NAME
      FROM RDB$RELATIONS
      ORDER BY 1
      INTO :RELATION
  DO
  BEGIN
    FOR SELECT
          RDB$FIELD_POSITION + 1,
          RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS
        WHERE
          RDB$RELATION_NAME = :RELATION
        ORDER BY RDB$FIELD_POSITION
        INTO :POS, :FIELD
    DO
    BEGIN
      IF (POS = 2) THEN
        RELATION = ' "';
      -- Pour éviter la répétition des noms de tables et de vues
      SUSPEND;
    END
  END
END

Example 3. Utilisation de la clause `AS CURSOR' pour la suppression d’un enregistrement positionnel

CREATE PROCEDURE DELTOWN (
  TOWNTODELETE VARCHAR(24))
RETURNS (
  TOWN VARCHAR(24),
  POP INTEGER)
AS
BEGIN
  FOR SELECT TOWN, POP
      FROM TOWNS
      INTO :TOWN, :POP
      AS CURSOR TCUR
  DO
  BEGIN
    IF (:TOWN = :TOWNTODELETE) THEN
      -- Suppression de position d'un enregistrement
      DELETE FROM TOWNS
      WHERE CURRENT OF TCUR;
    ELSE
      SUSPEND;
  END
END

Example 4. Utilisation d’un curseur déclaré implicitement comme variable de curseur

EXECUTE BLOCK
RETURNS (
    o CHAR(63))
AS
BEGIN
  FOR
      SELECT
          rdb$relation_name AS name
      FROM
          rdb$relations AS CURSOR c
  DO
  BEGIN
    o = c.name;
    SUSPEND;
  END
END

Example 5. Résolution des ambiguïtés des variables de curseur dans les requêtes

EXECUTE BLOCK
RETURNS (
    o1 CHAR(63),
    o2 CHAR(63))
AS
BEGIN
  FOR
      SELECT
          rdb$relation_name
      FROM
          rdb$relations
      WHERE
          rdb$relation_name = 'RDB$RELATIONS' AS CURSOR c
  DO
  BEGIN
    FOR
        SELECT
            -- avec un préfixe est autorisé comme curseur
            :c.rdb$relation_name x1,
            -- sans préfixe comme alias de la table rdb$relations
            c.rdb$relation_name x2
        FROM
            rdb$relations c
        WHERE
            rdb$relation_name = 'RDB$DATABASE' AS CURSOR d
    DO
    BEGIN
      o1 = d.x1;
      o2 = d.x2;
      SUSPEND;
    END
  END
END

Voir aussi :

SELECT,DECLARE …CURSOR,OPEN,CLOSE,FETCH.

`FOR EXECUTE STATEMENT`

Destination

Exécution d’instructions SQL créées dynamiquement et renvoyant plusieurs lignes de données.

Disponible en

PSQL

Syntaxe

[label:]
FOR <execute_statement> DO <compound_statement>

Table 1. Paramètres de l’instruction `FOR EXECUTE STATEMENT`
Paramètre	Description
label	Libellé facultatif pour `LEAVE` et/ou `CONTINUE`. Doit être conforme aux règles relatives aux identificateurs.
execute_statement	Instruction `EXECUTE STATEMENT`.
compound_statement	Instruction composé (Instruction ou bloc d’Instructions).

L’Instruction FOR EXECUTE STATEMENT est utilisé (de manière similaire à la construction FOR SELECT) pour les Instructions SELECT ou EXECUTE BLOCK qui renvoient plus d’une chaîne.

Exemples `FOR EXECUTE STATEMENT

Example 1. Utilisation de l’Instruction FOR EXECUTE STATEMENT

CREATE PROCEDURE DynamicSampleThree (
   Q_FIELD_NAME VARCHAR(100),
   Q_TABLE_NAME VARCHAR(100)
) RETURNS(
  LINE VARCHAR(32000)
)
AS
  DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
BEGIN
  LINE = '';
  FOR
    EXECUTE STATEMENT
      'SELECT T1.' || :Q_FIELD_NAME || ' FROM ' || :Q_TABLE_NAME || ' T1 '
    INTO :P_ONE_LINE
  DO
    IF (:P_ONE_LINE IS NOT NULL) THEN
      LINE = :LINE || :P_ONE_LINE || ' ';
  SUSPEND;
END

Voir aussi :

EXECUTE STATEMENT.

`OPEN`

Destination

Ouverture du curseur.

Disponible en

PSQL

Syntaxe

OPEN cursor_name;

Table 1. Paramètres de l’instruction OPEN
Paramètre	Description
cursor_name	Nom du curseur : un curseur portant ce nom doit être prédéclaré à l’aide de la commande `DECLARE … CURSOR`.

L’Instruction OPEN ouvre un curseur précédemment déclaré, exécute l’Instruction SELECT qui y est déclaré et récupère les enregistrements de l’ensemble de données résultant. L’Instruction OPEN ne s’applique qu’aux curseurs déclarés dans la section DECLARE … CURSOR.

Note	Si une instruction de curseur `SELECT` contient des paramètres, ils doivent être déclarés comme variables locales ou paramètres d’entrée (sortie) avant que le curseur ne soit déclaré. Lorsque le curseur est ouvert, le paramètre se voit attribuer la valeur actuelle de la variable.

Exemples `OPEN`

Example 1. Utilisation de l’Instruction OPEN

SET TERM ^;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(31)
)
AS
  DECLARE C CURSOR FOR (
    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^

SET TERM ;^

Example 2. Utilisation de l’Instruction OPEN avec des paramètres

Cet exemple renvoie un ensemble de scripts pour créer des vues en utilisant un bloc PSQL avec des curseurs nommés.

EXECUTE BLOCK
RETURNS (
  SCRIPT BLOB SUB_TYPE TEXT)
AS
  DECLARE VARIABLE FIELDS VARCHAR(8191);
  DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
  DECLARE VARIABLE RELATION RDB$RELATION_NAME;
  DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
  -- curseur nommé
  DECLARE VARIABLE CUR_R CURSOR FOR (
    SELECT
      RDB$RELATION_NAME,
      RDB$VIEW_SOURCE
    FROM
      RDB$RELATIONS
    WHERE
      RDB$VIEW_SOURCE IS NOT NULL);
  -- curseur nommé
  DECLARE CUR_F CURSOR FOR (
    SELECT
      RDB$FIELD_NAME
    FROM
      RDB$RELATION_FIELDS
    WHERE
      -- Important : La variable doit avoir été déclarée avant
      RDB$RELATION_NAME = :RELATION);
BEGIN
  OPEN CUR_R;
  WHILE (1 = 1) DO
  BEGIN
    FETCH CUR_R
      INTO :RELATION, :SOURCE;
    IF (ROW_COUNT = 0) THEN
      LEAVE;

    FIELDS = NULL;
    -- Le curseur CUR_F utilise
    -- la valeur de la variable RELATION précédemment initialisée
    OPEN CUR_F;
    WHILE (1 = 1) DO
    BEGIN
      FETCH CUR_F
        INTO :FIELD_NAME;
      IF (ROW_COUNT = 0) THEN
        LEAVE;
      IF (FIELDS IS NULL) THEN
        FIELDS = TRIM(FIELD_NAME);
      ELSE
        FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
    END
    CLOSE CUR_F;

    SCRIPT = 'CREATE VIEW ' || RELATION;

    IF (FIELDS IS NOT NULL) THEN
      SCRIPT = SCRIPT || ' (' || FIELDS || ')';

    SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
    SCRIPT = SCRIPT || SOURCE;

    SUSPEND;
  END
  CLOSE CUR_R;
END

Voir aussi :

FETCH,CLOSE,DECLARE … CURSOR.

`FETCH`

Destination

Lire un enregistrement de l’ensemble de données associé au curseur.

Disponible en

PSQL

Syntaxe

FETCH [<fetch_scroll> FROM] cursor_name
  [INTO [:]varname [, [:]varname ...]];

<fetch_scroll> ::=
    NEXT | PRIOR | FIRST | LAST
  | RELATIVE n
  | ABSOLUTE n

Table 1. Paramètres de l’instruction FETCH
Paramètre	Description
cursor_name	Nom du curseur : un curseur portant ce nom doit être prédéclaré à l’aide de la commande `DECLARE … CURSOR`.
var_name	Variable PSQL.
n	Entier.

L’instruction FETCH sélectionne la ligne de données suivante dans le jeu de données du curseur résultant et affecte les valeurs des colonnes aux variables PSQL. L’instruction FETCH s’applique uniquement aux curseurs déclarés dans l’instruction DECLARE …'. CURSOR.

L’Instruction FETCH peut spécifier dans quelle direction et de combien d’enregistrements la position du curseur avance. La clause NEXT peut être utilisée avec des curseurs défilants ou non.

Les autres clauses ne peuvent être utilisées qu’avec des curseurs déroulants.

Options du curseur de défilement

NEXT: fait avancer le curseur d’une entrée. Il s’agit de l’action par défaut.
PRIOR: déplace le curseur en arrière d’une entrée.
FIRST: déplace le curseur sur la première entrée.
LAST: déplace le curseur sur la dernière entrée.
ABSOLTE n: déplace le curseur à l’entrée spécifiée ; n est une expression entière, où 1 indique la première ligne. Pour les valeurs négatives, la position absolue est prise à partir de la fin du jeu de résultats, donc -1 indique la dernière ligne, -2 l’avant-dernière ligne, etc. Une valeur nulle (0) viendra avant la première ligne.
RELATIVE n: déplace le curseur à n lignes de la position actuelle ; les nombres positifs déplacent le pointeur vers l’avant et les nombres négatifs vers l’arrière ; l’utilisation de zéro (0) ne déplacera pas le curseur et ROW_COUNT sera mis à zéro car aucune nouvelle ligne n’a été sélectionnée.

La clause optionnelle INTO place les données de la ligne courante du curseur dans des variables PSQL.

Il est permis d’utiliser le nom du curseur comme une variable de type d’enregistrement (similaire à OLD et NEW dans les triggers) qui permet d’accéder aux colonnes du jeu résultant (c’est-à-dire cursor_name . columnname).

Règles d’utilisation des variables de curseur

Pour résoudre l’ambiguïté lors de l’accès à une variable curseur, un préfixe deux-points est requis avant le nom du curseur ;
La variable curseur peut être accédée sans le préfixe deux-points, mais dans ce cas, selon la portée des contextes existants dans la requête, le nom peut être résolu comme un contexte de requête au lieu du curseur ;
Les variables du curseur sont en lecture seule ;
La lecture d’une variable curseur renvoie les valeurs actuelles des champs. Cela signifie que l’Instruction UPDATE (avec la clause WHERE CURRENT OF) mettra également à jour les valeurs des champs de la variable curseur pour les lectures suivantes. L’exécution de l’instruction DELETE (avec la suggestion WHERE CURRENT OF) mettra NULL pour les valeurs des champs de la variable curseur pour les lectures suivantes.

Pour vérifier que les entrées du jeu de données sont épuisées, la variable contextuelle ROW_COUNT est utilisée pour retourner le nombre de lignes sélectionnées par l’instruction.Si l’enregistrement suivant dans l’ensemble de données est lu, ROW_COUNT est égal à un, sinon zéro.

Exemples `FETCH`

Example 1. Utilisation de l’Instruction FETCH

SET TERM ^;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(63)
)
AS
  DECLARE C CURSOR FOR (SELECT RDB$RELATION_NAME FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^

SET TERM ;^

Example 2. Utilisation de l’Instruction FETCH avec des curseurs imbriqués

EXECUTE BLOCK
RETURNS (
    SCRIPT BLOB SUB_TYPE TEXT)
AS
DECLARE VARIABLE FIELDS VARCHAR(8191);
DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
DECLARE VARIABLE RELATION RDB$RELATION_NAME;
DECLARE VARIABLE SRC   TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
-- Déclaration d'un curseur nommé
DECLARE VARIABLE CUR_R      CURSOR FOR (
    SELECT
        RDB$RELATION_NAME,
        RDB$VIEW_SOURCE
    FROM
        RDB$RELATIONS
    WHERE
        RDB$VIEW_SOURCE IS NOT NULL);
-- Annonce d'un curseur nommé dans lequel
-- une variable locale est utilisée
DECLARE CUR_F      CURSOR FOR (
    SELECT
        RDB$FIELD_NAME
    FROM
        RDB$RELATION_FIELDS
    WHERE
        -- La chose importante est que la variable doit avoir été déclarée avant
        RDB$RELATION_NAME = :RELATION);
BEGIN
  OPEN CUR_R;
  WHILE (1 = 1) DO
  BEGIN
    FETCH CUR_R
    INTO :RELATION, :SRC;
    IF (ROW_COUNT = 0) THEN
      LEAVE;

    FIELDS = NULL;
    -- Le curseur CUR_F utilisera la valeur
    -- de la variable RELATION initialisée ci-dessus
    OPEN CUR_F;
    WHILE (1 = 1) DO
    BEGIN
      FETCH CUR_F
      INTO :FIELD_NAME;
      IF (ROW_COUNT = 0) THEN
        LEAVE;
      IF (FIELDS IS NULL) THEN
        FIELDS = TRIM(FIELD_NAME);
      ELSE
        FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
    END
    CLOSE CUR_F;

    SCRIPT = 'CREATE VIEW ' || RELATION;

    IF (FIELDS IS NOT NULL) THEN
      SCRIPT = SCRIPT || ' (' || FIELDS || ')';

    SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
    SCRIPT = SCRIPT || SRC;

    SUSPEND;
  END
  CLOSE CUR_R;
END

Example 3. Exemple d’utilisation de l’Instruction FETCH avec un curseur de défilement

EXECUTE BLOCK
RETURNS (
  N INT,
  RNAME CHAR(63))
AS
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER(ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  OPEN C;
  -- passer à la première entrée (N=1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- avancer d'une entrée (N=2)
  FETCH NEXT FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- passer à la cinquième entrée (N=5)
  FETCH ABSOLUTE 5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- reculer d'une entrée (N=4)
  FETCH PRIOR FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- avancer de 3 entrées (N=7)
  FETCH RELATIVE 3 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- reculer de 5 entrées (N=2)
  FETCH RELATIVE -5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- passer à la première entrée (N=1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- passer à la dernière entrée
  FETCH LAST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  CLOSE C;
END

Voir aussi :

OPEN, CLOSE, DECLARE … CURSOR.

`CLOSE`

Destination

Fermeture du curseur.

Disponible en

PSQL

Syntaxe

CLOSE cursor_name;

Table 1. Paramètres de l’instruction `CLOSE`
Paramètre	Description
cursor_name	Le nom du curseur ouvert. Un curseur avec ce nom doit être prédéclaré en utilisant la commande `DECLARE … CURSOR`.

L’instruction CLOSE ferme un curseur ouvert. Tout curseur encore ouvert sera automatiquement fermé après l’exécution du code déclencheur, de la procédure stockée, de la fonction ou du bloc PSQL anonyme dans lequel il a été ouvert. L’instruction CLOSE s’applique uniquement aux curseurs déclarés dans l’instruction DECLARE … CURSOR.

Exemples `CLOSE`

Voir les exemples dans l’Instruction FETCH.

Voir aussi :

FETCH, OPEN, DECLARE … CURSOR.

`DECLARE VARIABLE`

Destination

Déclaration d’une variable locale.

Disponible en

PSQL

Syntaxe

DECLARE [VARIABLE] varname
  <type> [NOT NULL] [COLLATE collation]
  [{= | DEFAULT} <initvalue>] }

<type> ::=
    <non_array_datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<non_array_datatype> ::=
    <scalar_datatype> | <blob_datatype>

<scalar_datatype> ::= Voir Syntaxe des types de données scalaires.

<blob_datatype> ::= Voir Syntaxe du type de données BLOB.

<initvalue> ::= {<literal> | <context_var>}

Table 1. Paramètres de l’instruction `DECLARE VARIABLE`
Paramètre	Description
varname	Le nom de la variable locale.
literal	Litéral.
context_var	Toute variable contextuelle dont le type est compatible avec celui de la variable locale.
non_array_datatype	Type de données SQL autre que les tableaux.
collation	Ordre de tri.
domain	Domaine.
rel	Le nom d’une table ou d’une vue.
col	Le nom de la colonne de la table ou de la vue.

L’Instruction DECLARE [VARIABLE] déclare une variable locale. Le mot clé VARIABLE peut être omis. Une seule variable peut être déclarée dans un Instruction. Un nombre arbitraire de variables locales peut être déclaré dans les procédures et les triggers, en utilisant à chaque fois, un nouvel Instruction DECLARE VARIABLE.

Le nom d’une variable locale doit être unique parmi les noms des variables locales, des paramètres d’entrée et de sortie d’une procédure dans un objet de programme.

Types de données pour les variables

Le type de données de la variable locale peut être n’importe quel type SQL, à l’exception des tableaux.

Vous pouvez spécifier un nom de domaine comme type de variable.Dans ce cas, la variable hérite de toutes les caractéristiques du domaine.Si la clause TYPE OF est utilisée en plus avant le nom de domaine, seul le type de données du domaine est utilisé — ses restrictions NOT NULL, CHECK et/ou ses valeurs par défaut ne sont pas vérifiées (non utilisées).Si le domaine est un type de texte, son jeu de caractères et son ordre de tri sont toujours utilisés.

Les variables locales peuvent être déclarées en utilisant le type de données colonne des tables et vues existantes.Pour ce faire, on utilise la clause TYPE OF COLUMN, suivie du nom des tables ou de la vue et d’un nom de colonne séparé par un point.Lorsque vous utilisez TYPE OF COLUMN, seul le type de données est hérité, et dans le cas des types de chaînes de caractères, également le jeu de caractères et l’ordre de tri.Les contraintes et les valeurs par défaut de la colonne ne sont jamais utilisées.

Restriction `NOT NULL`

La restriction NOT NULL peut être spécifiée pour les variables locales, empêchant ainsi la valeur NULL de lui être passée.

Clauses `CHARACTER SET` et `COLLATE`

Sauf indication contraire, le jeu de caractères et la séquence de mappage (tri) de la variable chaîne seront les valeurs par défaut de la base de données.

Si nécessaire, la clause CHARACTER SET peut être incluse pour gérer les données de chaînes de caractères qui seront dans un jeu de caractères différent.

Une séquence de mappage valide (clause COLLATE) peut également être incluse avec ou sans CHARACTER SET.

Initialisation d’une variable

Une variable locale peut être définie par une valeur d’initialisation, qui est définie avec la clause DEFAULT ou l’Instruction =. La valeur par défaut peut être NULL, un littéral ou toute variable contextuelle compatible avec le type de données.

Important

Assurez-vous d’utiliser l’initialisation avec une valeur initiale pour toutes les variables déclarées avec la contrainte NOT NULL, à moins qu’elles ne reçoivent une valeur par défaut.

Exemples de déclarations de variables locales

Example 1. Différentes façons de déclarer les variables locales

CREATE OR ALTER PROCEDURE SOME_PROC
AS
  -- Déclaration d'une variable de type INT
  DECLARE I INT;
  -- Déclaration d'une variable de type INT qui n'accepte pas NULL
  DECLARE VARIABLE J INT NOT NULL;
  -- Déclaration d'une variable de type INT avec une valeur par défaut de 0
  DECLARE VARIABLE K INT DEFAULT 0;
  -- Déclaration d'une variable de type INT avec une valeur par défaut de 1
  DECLARE VARIABLE L INT = 1;
  -- Déclarer une variable basée sur le domaine COUNTRYNAME
  DECLARE FARM_COUNTRY COUNTRYNAME;
  -- Déclaration d'une variable de type égal au type de domaine COUNTRYNAME
  DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
  -- Déclaration d'une variable avec le type de colonne CAPITAL de la table COUNTRY
  DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
BEGIN
  /* Instructions PSQL */
END

Voir aussi :

Types et sous-types de données,Types de données utilisateur — domaines,CREATE DOMAIN

`IN AUTONOMOUS TRANSACTION`

Destination

Exécution d’un Instruction composé dans une transaction autonome.

Disponible en

PSQL.

Syntaxe

IN AUTONOMOUS TRANSACTION DO <compound_statement>

Table 1. Paramètres de l’instruction IN AUTONOMOUS TRANSACTION
Paramètre	Description
compound_statement	Instruction composé (Instruction ou bloc d’Instructions).

L’instruction IN AUTONOMOUS TRANSACTION permet d’exécuter une instruction composée dans une transaction hors ligne. Le code s’exécutant dans une transaction hors ligne sera acquitté immédiatement après son achèvement, quel que soit le statut de la transaction parente. Ceci est nécessaire lorsque certaines actions ne doivent pas être annulées, même si une erreur se produit dans la transaction parente.

Une transaction hors ligne a le même niveau d’isolation que la transaction parente. Toute exception levée ou soulevée dans le bloc de code d’une transaction hors ligne entraînera le retour en arrière de la transaction hors ligne et l’annulation de toutes les modifications apportées. Si le code est exécuté avec succès, la transaction hors ligne sera acquittée.

Exemples `IN AUTONOMOUS TRANSACTION`

Example 1. Utilisation des transactions hors ligne

Cet exemple montre l’utilisation d’une transaction hors ligne dans un déclencheur d’événements de connexion à une base de données pour enregistrer toutes les tentatives de connexion, y compris celles qui échouent.

CREATE TRIGGER TR_CONNECT ON CONNECT
AS
BEGIN
  -- Toutes les tentatives de connexion à la base de données sont enregistrées
  IN AUTONOMOUS TRANSACTION DO
    INSERT INTO LOG(MSG)
    VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
  IF (CURRENT_USER IN (SELECT
                           USERNAME
                       FROM
                           BLOCKED_USERS)) THEN
  BEGIN
    -- L'enregistrement de cette tentative de connexion
    -- avec la base de données n'a pas abouti
    -- et a envoyer un message d'événement
    IN AUTONOMOUS TRANSACTION DO
    BEGIN
      INSERT INTO LOG(MSG)
      VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
      POST_EVENT 'CONNECTION ATTEMPT' || ' BY BLOCKED USER!';
    END
    -- lève maintenant une exception
    EXCEPTION EX_BADUSER;
  END
END

Voir aussi :

Gestion des transactions.

`POST_EVENT`

Destination

Envoi d’un événement (message) aux applications clientes.

Disponible en

PSQL

Syntaxe

POST_EVENT event_name;

Table 1. Paramètres de l’instruction `POST_EVENT`
Paramètre	Description
event_name	Nom de l’événement, limité à 127 octets.

L’instruction POST_EVENT rapporte l’événement au gestionnaire d’événements, qui le stocke dans la table des événements. Lorsque la transaction est acquittée, le gestionnaire d’événements informe les applications qui attendent l’événement.

Le nom de l’événement est une sorte de code ou de message court, à vous de choisir, car il s’agit simplement d’une chaîne de caractères d’une longueur maximale de 127 octets.

Le nom de l’événement peut être une chaîne littérale, une variable ou toute expression SQL valide.

Exemples `POST_EVENT`

Example 1. Notifier l’application lorsqu’un enregistrement est inséré dans la table SALES.

SET TERM ^;
CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
  POST_EVENT 'new_order';
END^
SET TERM ;^

`RETURN`

Destination

Retourner une valeur à partir d’une fonction stockée

Disponible en

PSQL

Syntaxe

RETURN value;

Table 1. Paramètres de l’instruction `RETURN`
Paramètre	Description
value	Expression permettant de retourner une valeur à partir d’une fonction ; peut être toute expression compatible avec le type de valeur de retour de la fonction.

L’instruction RETURN termine la fonction et renvoie la valeur de l’expression value.

RETURN ne peut être utilisé que dans les fonctions PSQL (fonctions stockées et locales).

Exemples `RETURN`

Voir. Exemples CREATE FUNCTION

Traitement des erreurs

Dans Firebird, il y a des Instructions PSQL pour gérer les erreurs et les exceptions dans les modules. Il y a de nombreuses exceptions intégrées qui se produisent lorsque des erreurs standard se produisent dans les instructions DML et DDL.

Exceptions du système

Une exception est un message qui est généré lorsqu’une erreur se produit.

Toutes les exceptions traitées par Firebird ont des valeurs numériques (caractères) prédéfinies pour les variables de contexte et les textes de message associés. Les messages d’erreur sont écrits en anglais par défaut. Il existe également des versions localisées de la base de données dans lesquelles les messages d’erreur sont traduits dans d’autres langues.

Vous trouverez une liste complète des exceptions du système dans l’annexe. "Traitement des erreurs, codes et messages":

Codes d’erreur SQLSTATE et leurs descriptions
"Codes d’erreur GDSCODE et leur description, et SQLCODE"

Exceptions personnalisées

Les exceptions personnalisées peuvent être déclarées comme des objets persistants dans la base de données et peuvent être appelées à partir du code PSQL pour signaler une erreur lorsque certaines règles de gestion sont violées.Le texte d’une exception personnalisée est limité à 1021 octets.Voir CREATE EXCEPTION pour plus de détails.

Dans le code PSQL, les exceptions sont traitées par l’Instruction WHEN.Si l’exception est gérée dans votre code, vous fournissez un correctif ou une solution de contournement et permettez à l’exécution de se poursuivre, aucun message d’exception n’est renvoyé au client.

L’exception entraîne l’arrêt de l’exécution dans le bloc.Au lieu de passer l’exécution à l’instruction finale END, la procédure remonte maintenant les niveaux dans les blocs imbriqués, en commençant par le bloc où l’erreur a été levée, et va dans les blocs externes pour trouver le code du gestionnaire qui connait l’exception.Il cherche la première instruction WHEN qui peut gérer cette erreur.

`EXCEPTION`

Destination

Déclencher une exception personnalisée ou rappeler une exception.

Disponible en

PSQL

Syntaxe

EXCEPTION [
    exception_name
    [ custom_message | USING (<value_list>)]
  ]

<value_list> ::= <val> [, <val> ...]

Table 1. Paramètres de l’instruction EXCEPTION
Paramètre	Description
exception_name	Le nom de l’exception.
custom_message	Texte du message alternatif donné lorsqu’une exception se produit. La longueur maximale d’un message texte est de 1021 octets.
val	Les valeurs par lesquelles les créneaux sont remplacés dans le texte du message d’exception.

L’Instruction EXCEPTION lance une exception personnalisée avec un nom spécifié. Lorsque vous lancez une exception, vous pouvez également spécifier un texte de message alternatif pour remplacer le texte de message spécifié lors de la création de l’exception.

Le texte du message d’exception peut contenir des emplacements de paramètres qui sont remplis lorsque l’exception est levée.La clause USING est utilisée pour passer des valeurs de paramètres dans l’exception.Les paramètres sont évalués de gauche à droite.Chaque paramètre est transmis à l’instruction qui lève l’exception sous la forme `N-y', N commençant par 1 :

Si le paramètre N n’est pas passé, son emplacement n’est pas remplacé ;
Si NULL est passé, le slot sera remplacé par la chaîne “*** null ***” ;
Si le nombre de paramètres passés est supérieur au nombre de paramètres contenus dans le message d’exception, les paramètres supplémentaires seront ignorés ;
Le nombre maximum de paramètres est de 9 ;
La longueur totale du message, y compris les valeurs des paramètres, est limitée à 1053 octets.

Note

Le vecteur d’état est généré en utilisant une combinaison de codes isc_except, <numéro d’exception>, isc_formatted_exception, <message d’exception formaté>, <paramètres d’exception>.

Comme un nouveau code d’erreur (isc_formatted_exception) est utilisé, le client doit être en version 3.0 ou au moins utiliser firebird.msg à partir de la version 3.0 afin de convertir correctement le vecteur d’état en chaîne de caractères.

Warning

Si le texte du message contient un numéro d’emplacement de paramètre supérieur à 9, le deuxième caractère et les suivants seront considérés comme des littéraux. Par exemple, @10 sera considéré comme @1 suivi du littéral ‘`0`’.

CREATE EXCEPTION ex1
'something wrong in @1 @2 @3 @4 @5 @6 @7 @8 @9 @10 @11';

EXECUTE BLOCK AS
BEGIN
  EXCEPTION ex1 USING ('a','b','c','d','e','f','g','h','i');
END^

Statement failed, SQLSTATE = HY000
exception 1
-EX1
-something wrong in a b c d e f g h i a0 a1

L’exception peut être traitée par l’Instruction WHEN … DO.Si une exception utilisateur n’a pas été traitée dans un déclencheur ou une procédure stockée, les actions effectuées dans cette procédure stockée (déclencheur) sont annulées et le programme appelant reçoit le texte spécifié lors de la création de l’exception ou un autre texte de message.

Dans le bloc de traitement des exceptions (et uniquement dans celui-ci), vous pouvez rappeler une exception ou une erreur capturée en appelant l’instruction EXCEPTION sans paramètres. En dehors du bloc d’exception, un tel appel n’a aucun effet.

Note	Les exceptions des utilisateurs sont stockées dans la table du système `RDB$EXCEPTIONS`.

Exemples `EXCEPTION`

Example 1. Appeler une exception

CREATE OR ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8))
AS
DECLARE VARIABLE ord_stat  CHAR(7);
DECLARE VARIABLE hold_stat CHAR(1);
DECLARE VARIABLE cust_no   INTEGER;
DECLARE VARIABLE any_po    CHAR(8);
BEGIN
  SELECT
      s.order_status,
      c.on_hold,
      c.cust_no
  FROM
      sales s, customer c
  WHERE
      po_number = :po_num AND
      s.cust_no = c.cust_no
  INTO :ord_stat,
       :hold_stat,
       :cust_no;

  /* Cette commande a déjà été envoyée pour être livrée. */
  IF (ord_stat = 'shipped') THEN
    EXCEPTION order_already_shipped;
  /* Autres Instructions */
END

Example 2. Appeler une exception et remplacer le message original par un message alternatif

CREATE OR ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8))
AS
DECLARE VARIABLE ord_stat  CHAR(7);
DECLARE VARIABLE hold_stat CHAR(1);
DECLARE VARIABLE cust_no   INTEGER;
DECLARE VARIABLE any_po    CHAR(8);
BEGIN
  SELECT
      s.order_status,
      c.on_hold,
      c.cust_no
  FROM
      sales s, customer c
  WHERE
      po_number = :po_num AND
      s.cust_no = c.cust_no
  INTO :ord_stat,
       :hold_stat,
       :cust_no;

  /* Cette commande a déjà été envoyée pour être livrée. */
  IF (ord_stat = 'shipped') THEN
    EXCEPTION order_already_shipped 'Order status is "' || ord_stat || '"';
  /* Autres Instructions */
END

Example 3. Utilisation d’une exception paramétrée

CREATE EXCEPTION EX_BAD_SP_NAME
  'Name of procedures must start with ''@1'' : ''@2''';
...
CREATE TRIGGER TRG_SP_CREATE BEFORE CREATE PROCEDURE
AS
  DECLARE SP_NAME VARCHAR(255);
BEGIN
  SP_NAME = RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_NAME');
  IF (SP_NAME NOT STARTING 'SP_') THEN
    EXCEPTION EX_BAD_SP_NAME USING ('SP_', SP_NAME);
END^

Voir aussi :

CREATE EXCEPTION, WHEN … DO.

`WHEN … DO`

Destination

Traitement des erreurs.

Disponible en

PSQL

Syntaxe

WHEN {<error> [, <error> ...] | ANY}
DO <compound_statement>

<error> ::= {
    EXCEPTION exception_name
  | SQLCODE number
  | GDSCODE errcode
  | SQLSTATE 'sqlstate_code'
}

Table 1. Paramètres de l’instruction `WHEN … DO`
Paramètre	Description
exception_name	Le nom de l’exception.
number	Code d’erreur SQLCODE.
errcode	Le nom symbolique de l’erreur est GDSCODE.
sqlstate_code	Code d’erreur SQLSTATE.
compound_statement	Instruction ou bloc d’Instructions.

Le WHEN … DO est utilisé pour gérer les situations d’erreur et les exceptions de l’utilisateur.L’Instruction intercepte toutes les erreurs et les exceptions utilisateur énumérées après le mot-clé WHEN.Si le mot clé ANY est spécifié après le mot clé WHEN, l’Instruction intercepte toutes les erreurs et les exceptions utilisateur, même si elles ont déjà été traitées dans le bloc WHEN ci-dessus.

Le WHEN … DO doit se trouver à la toute fin du bloc d’instructions, avant l’instruction END.

Le mot-clé DO est suivi d’une instruction composée où une erreur ou une exception peut être traitée.Une instruction composée est une instruction ou un bloc d’instructions entouré de crochets Instructions BEGIN et END.Les variables contextuelles sont disponibles dans cette déclaration GDSCODE, SQLCODE, SQLSTATE.Pour récupérer le nom d’une exception utilisateur active ou le texte d’un message d’erreur interprété, vous pouvez utiliser la fonction systèmeRDB$ERROR.Un Instruction de rappel en cas d’erreur ou de situation exceptionnelle est également disponible dans ce bloc.EXCEPTION (aucun paramètre).

Important

Après la clause WHEN GDSCODE vous devez utiliser des noms symboliques — tels que grant_obj_notfound etc. Mais dans une instruction composée, après le mot clé DO une variable contextuelle GDSCODE est disponible qui contient un entier. Pour la comparer à une erreur particulière vous devez utiliser une valeur numérique, telle que 335544551 pour grant_obj_notfound.

Le WHEN … IF l’instruction est exécutée (même si aucune action n’y a été effectuée), une erreur ou une exception utilisateur n’interrompt pas ou n’annule pas l’action du déclencheur ou de la procédure stockée où cette instruction a été émise, l’opération se poursuit comme si aucune situation exceptionnelle ne s’était produite.Toutefois, dans ce cas, l’instruction DML (SELECT, INSERT, UPDATE, DELETE, MERGE) qui a provoqué l’erreur sera annulée et toutes les instructions suivantes du même bloc d’instructions ne seront pas exécutées.

Important

Si l’erreur n’est pas causée par l’une des instructions DML (SELECT, INSERT, UPDATE, DELETE, MERGE), non seulement l’instruction qui a causé l’erreur sera annulée, mais tout le bloc d’instructions. De plus, les actions de l’instruction WHEN … Ceci s’applique également à l’instruction de procédure stockée `EXECUTE PROCEDURE. Pour plus d’informations, voir CORE-4483.

Champ d’action de l’Instruction `WHEN … DO`

L’Instruction intercepte les erreurs et les exceptions dans le bloc d’instructions en cours. Il intercepte également les situations similaires dans les blocs imbriqués si ces situations n’ont pas été traitées dans ceux-ci.

Le WHEN … DO voit toutes les modifications apportées avant l’énoncé causant l’erreur. Cependant, si vous essayez de les enregistrer dans une transaction hors ligne, ces modifications ne seront pas disponibles parce qu’au moment où la transaction hors ligne commence, la transaction dans laquelle ces modifications ont eu lieu n’est pas confirmée.

Exemples d’utilisation `WHEN…DO`

Example 1. Remplacer une erreur standard par la vôtre.

CREATE EXCEPTION COUNTRY_EXIST '';
SET TERM ^;
CREATE PROCEDURE ADD_COUNTRY (
    ACountryName COUNTRYNAME,
    ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country, currency)
  VALUES (:ACountryName, :ACurrency);

  WHEN SQLCODE -803 DO
    EXCEPTION COUNTRY_EXIST 'Un tel pays a déjà été ajouté !';
END^
SET TERM ^;

Example 2. Enregistrez l’erreur dans le journal et ré-exécutez-la dans le bloc WHEN.

CREATE PROCEDURE ADD_COUNTRY (
    ACountryName COUNTRYNAME,
    ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country,
                       currency)
  VALUES (:ACountryName,
          :ACurrency);
  WHEN ANY DO
  BEGIN
    -- Enregistrement de l'erreur
    IN AUTONOMOUS TRANSACTION DO
      INSERT INTO ERROR_LOG (PSQL_MODULE,
                             ERROR_TEXT,
                             EXCEPTION_NAME,
                             GDS_CODE,
                             SQL_CODE,
                             SQL_STATE)
      VALUES ('ADD_COUNTRY',
              RDB$ERROR(MESSAGE), -- texte du message d'erreur
              RDB$ERROR(EXCEPTION), -- le nom de l'exception de l'utilisateur
              GDSCODE,
              SQLCODE,
              SQLSTATE
      );
    -- Relancer l'erreur
    EXCEPTION;
  END
END

Example 3. Traitement en une seule fois WHEN … DO d’un bloc de plusieurs erreurs

...
WHEN GDSCODE GRANT_OBJ_NOTFOUND,
	   GDSCODE GRANT_FLD_NOTFOUND,
	   GDSCODE GRANT_NOPRIV,
	   GDSCODE GRANT_NOPRIV_ON_BASE
DO
BEGIN
	EXECUTE PROCEDURE LOG_GRANT_ERROR(GDSCODE);
	EXIT;
END
...

Example 4. Interception des erreurs par le code SQLSTATE.

EXECUTE BLOCK
AS
    DECLARE VARIABLE I INT;
BEGIN
  BEGIN
    I = 1 / 0;
    WHEN SQLSTATE '22003' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Numeric value out of range.';
    WHEN SQLSTATE '22012' DO
      EXCEPTION E_CUSTOM_EXCEPTION 'Division by zero.';
    WHEN SQLSTATE '23000' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Integrity constraint violation.';
  END
END

Voir aussi :

Instruction EXCEPTION,Codes d’erreur SQLSTATE et leurs descriptions,Codes d’erreur GDSCODE et SQLCODE et leurs descriptions.

`DECLARE … CURSOR`

Destination:

Annonce du curseur.

Disponible en:

PSQL

Syntaxe

DECLARE [VARIABLE] cursor_name
  [SCROLL | NO SCROLL]
  CURSOR FOR (<select_statement>);

Table 1. Paramètres de l’instruction `DECLARE … CURSOR`
Paramètre	Description
cursor_name	Nom du curseur.
select_statement	Instruction SELECT.

La commande DECLARE … CURSOR FOR déclare un curseur nommé, le liant au jeu de données obtenu dans l’instruction SELECT spécifiée dans la clause CURSOR FOR. Le curseur peut alors être ouvert, utilisé pour contourner le jeu de données résultant, et être refermé. Les mises à jour et suppressions positionnées sont également supportées en utilisant WHERE CURRENT OF dans les instructions UPDATE et DELETE.

Le nom du curseur peut être utilisé comme référence au curseur en tant que variable de type d’enregistrement. L’enregistrement courant est accessible via le nom du curseur, ce qui rend inutile la clause INTO dans l’instruction FETCH.

Curseurs unidirectionnels et de défilement

Les curseurs peuvent être défilables unidirectionnellement. La clause optionnelle SCROLL rend le curseur bidirectionnel (défilable), la clause NO SCROLL le rend unidirectionnel. Par défaut, les curseurs sont unidirectionnels.

Les curseurs unidirectionnels permettent uniquement un déplacement vers l’avant dans un ensemble de données, tandis que les curseurs bidirectionnels permettent non seulement un déplacement vers l’avant mais aussi vers l’arrière dans un ensemble de données et N positions par rapport à la position actuelle.

Warning

Les curseurs défilants se matérialisent en interne comme un jeu de données temporaire, ils consomment donc des ressources mémoire/disque supplémentaires, aussi ne les utilisez que lorsque cela est vraiment nécessaire.

Caractéristiques de l’utilisation du curseur

La clause FOR UPDATE est autorisée dans l’instruction SELECT, mais elle n’est pas nécessaire pour une mise à jour ou une suppression positionnelle réussie ;
Assurez-vous que les noms de curseurs déclarés ne correspondent pas à des noms définis plus tard dans les clauses AS CURSOR ;
Si un curseur n’est nécessaire que pour parcourir le jeu de données résultant, il est presque toujours plus facile (et moins sujet aux erreurs) d’utiliser l’instruction FOR SELECT avec la clause AS CURSOR. Les curseurs déclarés doivent être explicitement ouverts, utilisés pour sélectionner des données et fermés. Vous devez également vérifier la variable contextuelle ROW_COUNT après chaque sélection et quitter la boucle si sa valeur est nulle. La clause FOR SELECT effectue cette vérification automatiquement. Cependant, les curseurs déclarés donnent un meilleur contrôle sur les événements séquentiels et permettent de gérer plusieurs curseurs en parallèle ;
L’instruction SELECT peut contenir des paramètres tels que : "SÉLECTIONNER NOM || :SFX À PARTIR DE NOMS OÙ NUMÉRO = :NUM". Chaque paramètre doit être déclaré au préalable comme une variable PSQL (ceci s’applique également aux paramètres d’entrée et de sortie). Lorsque le curseur est ouvert, le paramètre se voit attribuer la valeur variable actuelle ;
Si l’option de défilement est omise, NO SCROLL est supposé par défaut (c’est-à-dire que le curseur n’est ouvert que pour un déplacement vers l’avant). Cela signifie que seules les commandes FETCH [NEXT FROM] peuvent être utilisées. Les autres commandes renverront des erreurs.

Warning

Si la valeur d’une variable PSQL utilisée dans une instruction SELECT change pendant l’exécution d’une boucle, sa nouvelle valeur peut (mais pas toujours) être utilisée lors de la sélection des lignes suivantes. Il est préférable d’éviter de telles situations. Si vous avez besoin de ce comportement, vous devez tester le code avec soin et vous assurer que vous savez exactement comment les changements dans la variable affectent les résultats de la sélection. Je voudrais particulièrement noter que le comportement peut dépendre du plan de requête, en particulier des index utilisés. Il n’y a actuellement aucune règle stricte pour de telles situations, mais en

Exemples d’utilisation d’un curseur nommé

Example 1. Annonce d’un curseur nommé

CREATE OR ALTER TRIGGER TBU_STOCK
BEFORE UPDATE ON STOCK
AS
  -- Annonce d'un curseur nommé
  DECLARE C_COUNTRY CURSOR FOR (
    SELECT
      COUNTRY,
      CAPITAL
    FROM COUNTRY
  );
BEGIN
  /* Instructions PSQL */
END

Example 2. Annonce d’un curseur défilant

EXECUTE BLOCK
RETURNS (
  N INT,
  RNAME CHAR(63))
AS
  -- Annonce d'un curseur défilant
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER(ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  /* Instructions PSQL */
END

Voir aussi :

OPEN,FETCH,CLOSE,FOR SELECT.

`DECLARE PROCEDURE`

Destination

Déclaration et mise en œuvre d’une sous-procédure.

Disponible en

PSQL

Syntaxe

<subproc-declaration> ::=
  DECLARE PROCEDURE subprocname [(<input-parameters>)]
  [RETURNS (<output-parameters>)];

<subproc-implimentation> ::=
  DECLARE PROCEDURE subprocname [(<input-parameters>)]
  [RETURNS (<output-parameters>)]
  <psql-routine-body>

<input-parameters> ::= <inparam> [, <inparam> ...]

<output-parameters> ::= <outparam> [, <outparam> ...]

<psql-routine-body> ::=
  Voir. Syntaxe du corps du module

Table 1. Paramètres de l’instruction `DECLARE PROCEDURE`
Paramètre	Description
subprocname	Nom de la sous-procédure.
inparam	Description du paramètre d’entrée.
outparam	Description du paramètre de sortie.

L’Instruction DECLARE PROCEDURE déclare ou implémente une sous-procédure.

Les restrictions suivantes sont imposées à une sous-procédure :

Un sous-programme ne peut pas être imbriqué dans un autre sous-programme. Ils ne sont pris en charge que dans le module principal (procédure stockée, fonction stockée, déclencheur et bloc PSQL anonyme) ;
Les variables du module principal sont disponibles dans un sous-programme ;
Les variables et paramètres accédés par les sous-programmes peuvent avoir une légère dégradation des performances lors de leur lecture (même dans le programme principal).
Actuellement, les sous-programmes n’ont pas d’accès direct aux curseurs du module principal. Cela pourrait être autorisé à l’avenir.

Un sous-programme peut appeler un autre sous-programme, y compris de manière récursive.Dans certains cas, il peut être nécessaire de déclarer au préalable un sous-programme.Règle générale : un sous-programme peut appeler un autre sous-programme si ce dernier est déclaré au-dessus du point d’appel.Tous les sous-programmes déclarés doivent être mis en œuvre avec la même signature.Les valeurs par défaut des paramètres des sous-programmes ne peuvent pas être remplacées.Cela signifie qu’ils ne peuvent être définis que dans l’implémentation de sous-programmes qui n’ont pas été déclarés auparavant.

Example 1. Utilisation d’une sous-procédure

SET TERM ^;
--
-- Sous-procédures dans EXECUTE BLOCK
--
EXECUTE BLOCK
RETURNS (
    name VARCHAR(63))
AS
  -- Sous-procédure retournant une liste de tableaux
  DECLARE PROCEDURE get_tables
  RETURNS(table_name VARCHAR(63))
  AS
  BEGIN
    FOR
      SELECT
        rdb$relation_name
      FROM
        rdb$relations
      WHERE
        rdb$view_blr IS NULL
      INTO table_name
    DO SUSPEND;
  END

  -- Sous-procédure retournant une liste de vues
  DECLARE PROCEDURE get_views
  RETURNS(view_name  VARCHAR(63))
  AS
  BEGIN
    FOR
      SELECT
        rdb$relation_name
      FROM
        rdb$relations
      WHERE
        rdb$view_blr IS NOT NULL
      INTO view_name
    DO SUSPEND;
  END

BEGIN
  FOR
    SELECT
        table_name
    FROM
        get_tables
    UNION ALL
    SELECT
        view_name
    FROM
        get_views
    INTO name
  DO SUSPEND;
END^

Example 2. Utilisation de sous-procédures avec pré-déclaration

EXECUTE BLOCK RETURNS (o INTEGER)
AS
  -- Pré-annonce P1.
  DECLARE PROCEDURE p1(i INTEGER = 1) RETURNS (o INTEGER);

  -- Pré-annonce P2.
  DECLARE PROCEDURE p2(i INTEGER) RETURNS (o INTEGER);

  -- Mise en œuvre de P1. Vous ne devez pas remplacer la valeur par défaut du paramètre
  DECLARE PROCEDURE p1(i INTEGER) RETURNS (o INTEGER)
  AS
  BEGIN
    EXECUTE PROCEDURE p2(i) RETURNING_VALUES o;
  END

  DECLARE PROCEDURE p2(i INTEGER) RETURNS (o INTEGER)
  AS
  BEGIN
    o = i;
  END
BEGIN
  EXECUTE PROCEDURE p1 RETURNING_VALUES o;
  SUSPEND;
END!

Voir aussi :

DECLARE FUNCTION,CREATE PROCEDURE.

DECLARE FUNCTION

Destination

Déclaration et mise en œuvre d’une sous-fonction.

Disponible en

PSQL

Syntaxe

<subfunc-declaration> ::=
  DECLARE FUNCTION subfuncname [(<input-parameters>)]
  RETURNS <type> [COLLATE collation] [DETERMINISTIC];

<subfunc-implimentation> ::=
  DECLARE FUNCTION subfuncname [(<input-parameters>)]
  RETURNS <type> [COLLATE collation] [DETERMINISTIC]
  <psql-routine-body>

<input-parameters> ::= <inparam> [, <inparam> ...]

<output-parameters> ::= <outparam> [, <outparam> ...]

<psql-routine-body> ::=
  Voir. Syntaxe du corps du module

Table 1. Paramètres de l’instruction DECLARE FUNCTION
Paramètre	Description
subfuncname	Le nom de la sous-fonction.
inparam	Description du paramètre d’entrée.
type	Type de résultat de sortie.
collation	Ordre de tri.

L’Instruction DECLARE FUNCTION déclare une sous-fonction.

Les restrictions suivantes sont imposées au sous-programme :

Un sous-programme ne peut pas être imbriqué dans un autre sous-programme. Ils ne sont pris en charge que dans le module principal (procédure stockée, fonction stockée, déclencheur et bloc PSQL anonyme) ;
Les variables du module principal sont disponibles dans un sous-programme ;
Les variables et paramètres accédés par les sous-programmes peuvent avoir une légère dégradation des performances lors de leur lecture (même dans le programme principal).
Actuellement, les sous-programmes n’ont pas d’accès direct aux curseurs du module principal. Cela pourrait être autorisé à l’avenir.

Example 1. Utilisation d’une sous-fonction

--
-- Une sous-fonction dans une fonction stockée
--
CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
  RETURNS INTEGER
AS
  -- Sous-fonction
  DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
    RETURNS INTEGER
  AS
  BEGIN
    RETURN n1 + n2;
  END

BEGIN
  RETURN SUBFUNC(n1, n2);
END ^

Example 2. Utilisation d’une sous-fonction récursive

EXECUTE BLOCK RETURNS (i INTEGER, o INTEGER)
AS
  -- Sous-programme-fonction récursif sans déclaration préalable.
  DECLARE FUNCTION fibonacci(n INTEGER) RETURNS INTEGER
  AS
  BEGIN
    IF (n = 0 OR n = 1) THEN
      RETURN n;
    ELSE
      RETURN fibonacci(n - 1) + fibonacci(n - 2);
  END
BEGIN
  i = 0;

  WHILE (i < 10)
  DO
  BEGIN
    o = fibonacci(i);
    SUSPEND;
    i = i + 1;
  END
END!

Voir aussi :

DECLARE PROCEDURE,CREATE FUNCTION.

`BEGIN … END`

Destination

La désignation d’un Instruction composé.

Disponible en

PSQL.

Syntaxe

<block> ::=
  BEGIN
    [<compound_statement> ...]
  END

<compound_statement> ::= {<block> | <statement>}

Les parenthèses de l’Instruction BEGIN … END définit une instruction composée ou un bloc d’instructions qui est exécuté comme une seule unité de code.Chaque bloc commence par l’Instruction BEGIN et se termine par l’Instruction END.Les blocs peuvent être imbriqués.La profondeur maximale est limitée à 512 niveaux de blocs imbriqués.Une instruction composée peut être vide, ce qui permet de l’utiliser comme un stub pour éviter d’écrire des instructions factices.

Il n’y a pas de point-virgule après les Instructions BEGIN et END.Toutefois, l’utilitaire de ligne de commande isql exige que la dernière instruction END de la définition du module PSQL soit suivie du caractère de terminaison défini par la commande SET TERM.Le terminateur ne fait pas partie de la syntaxe PSQL.

La dernière instruction END du déclencheur termine le déclencheur.La dernière instruction END d’une procédure stockée fonctionne en fonction du type de procédure :

Dans une procédure sélective, la dernière instruction END rend le contrôle à l’application et définit la valeur SQLCODE à 100, ce qui signifie qu’il n’y a plus de lignes à récupérer ;
Dans une procédure exécutable, la dernière instruction END rend le contrôle et les valeurs actuelles des paramètres de sortie, le cas échéant, à l’application appelante.

Exemples `BEGIN … END`

Exemple de procédure à partir de la base de données employee.fdb, démontrant une utilisation simple des blocs BEGIN … END:

Example 1. Utilisation de BEGIN … END

SET TERM ^;
CREATE OR ALTER PROCEDURE DEPT_BUDGET (
    DNO CHAR(3))
RETURNS (
    TOT DECIMAL(12,2))
AS
    DECLARE VARIABLE SUMB DECIMAL(12,2);
    DECLARE VARIABLE RDNO CHAR(3);
    DECLARE VARIABLE CNT  INTEGER;
BEGIN
  TOT = 0;

  SELECT
      BUDGET
  FROM
      DEPARTMENT
  WHERE DEPT_NO = :DNO
  INTO :TOT;

  SELECT
      COUNT(BUDGET)
  FROM
      DEPARTMENT
  WHERE HEAD_DEPT = :DNO
  INTO :CNT;

  IF (CNT = 0) THEN
    SUSPEND;

  FOR
      SELECT
          DEPT_NO
      FROM
          DEPARTMENT
      WHERE HEAD_DEPT = :DNO
      INTO :RDNO
  DO
  BEGIN
    EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
    RETURNING_VALUES :SUMB;
    TOT = TOT + SUMB;
  END

  SUSPEND;
END^
SET TERM ;^

Voir aussi :

EXIT, LEAVE, SET TERM.

`IF … THEN … ELSE`

Destination

Une transition conditionnelle.

Disponible en

PSQL

Syntaxe

IF (<condition>)
  THEN <compound_statement>
  [ELSE <compound_statement>]

Table 1. Paramètres de l’instruction `IF … THEN … ELSE`
Paramètre	Description
condition	Une condition logique renvoyant VRAI, FAUX ou INCONNU.
compound_statement	Instruction composé (Instruction ou bloc d’Instructions).

L’Instruction conditionnel IF est utilisé pour exécuter un processus de branchement de données dans PSQL. Si la condition retourne TRUE, un Instruction composé est exécuté ou après le mot clé THEN. Sinon (si la condition retourne FALSE ou UNKNOWN), un Instruction composé est exécuté après le mot clé ELSE, s’il est présent. La condition est toujours entre parenthèses.

Opérateur de branchement

PSQL ne fournit pas de transitions plus complexes avec plusieurs branches, comme CASE ou SWITCH. Cependant, il est possible de combiner les instructions IF … else … Alternativement, l’instruction CASE de DSQL est disponible en PSQL et peut satisfaire au moins certains cas d’utilisation comme un switch :

CASE <test_expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

Example 1. L’utilisation de CASE dans PSQL.

...
C = CASE
      WHEN A=2 THEN 1
      WHEN A=1 THEN 3
      ELSE 0
    END;
...

Exemples `IF`

Example 1. Utilisation de l’Instruction "IF".

Supposons que les variables FIRST, LINE2 et LAST aient été déclarées auparavant.

...
IF (FIRST IS NOT NULL) THEN
  LINE2 = FIRST || ' ' || LAST;
ELSE
  LINE2 = LAST;
...

Example 2. Combinez `IF … THEN … ELSE " en une chaîne

Supposons que les variables INT_VALUE et STRING_VALUE aient été déclarées auparavant.

...
IF (INT_VALUE = 1) THEN
  STRING_VALUE = 'one';
ELSE IF (INT_VALUE = 2) THEN
  STRING_VALUE = 'two';
ELSE IF (INT_VALUE = 3) THEN
  STRING_VALUE = 'three';
ELSE
  STRING_VALUE = 'too much';
...

Cet exemple peut être remplacé par la fonctionSimple CASE ou DECODE.

Voir aussi :

WHILE … DO, CASE.

`WHILE … DO`

Destination

Exécution cyclique des Instructions.

Disponible en

PSQL

Syntaxe

[label:]
WHILE (<condition>) DO
  <compound_statement>

Table 1. Paramètres de l’instruction WHILE … DO
Paramètre	Description
condition	Une condition logique renvoyant VRAI, FAUX ou INCONNU.
compound_statement	Instruction composé (Instruction ou bloc d’Instructions).

L’instruction WHILE est utilisée pour organiser des boucles dans PSQL. L’instruction composée sera exécutée tant que la condition est vraie (retourne VRAI). Les boucles peuvent être imbriquées, la profondeur de l’imbrication n’est pas limitée.

Exemples `WHILE … DO`

Example 1. Utilisation de l’Instruction WHILE … DO

Procédure de calcul d’une somme de 1 à I pour démontrer l’utilisation d’un cycle :

CREATE PROCEDURE SUM_INT (I INTEGER)
RETURNS (S INTEGER)
AS
BEGIN
  s = 0;
  WHILE (i > 0) DO
  BEGIN
    s = s + i;
    i = i - 1;
  END
END

Lorsqu’il est exécuté dans isql :

EXECUTE PROCEDURE SUM_INT(4);

le résultat sera le suivant

S
==========
10

Voir aussi :

FOR SELECT, FOR EXECUTE STATEMENT, LEAVE, CONTINUE.

`BREAK`

Destination

Sortir du cycle.

Syntaxe

<loop_stmt>
BEGIN
  ...
  BREAK;
  ...
END

<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list>  DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>) DO

Table 1. Paramètres de l’instruction `BREAK`
Paramètre	Description
select_stmt	Instruction `SELECT`
condition	Une condition logique renvoyant VRAI, FAUX ou INCONNU.

L’instruction BREAK termine momentanément la boucle interne des instructions WHILE ou FOR. Le code continue à s’exécuter à partir de la première instruction après le bloc de boucle terminé.

L’instruction BREAK est similaire à LEAVE sauf qu’elle ne supporte pas d’étiquette de transition.

Note	Cet Instruction est considéré comme obsolète. A partir de Firebird 1.5, il est recommandé d’utiliser l’Instruction compatible SQL-99LEAVE.

Voir aussi :

LEAVE, EXIT, CONTINUE.

Autres Commentaires

Autres commentaires

AS USER, PASSWORD et ROLE

Précautions

FOR SELECT

Curseur inopiné

Exemples en utilisant FOR SELECT

FOR EXECUTE STATEMENT

Exemples `FOR EXECUTE STATEMENT

OPEN

Exemples OPEN

FETCH

Exemples FETCH

CLOSE

Exemples CLOSE

DECLARE VARIABLE

Types de données pour les variables

Restriction NOT NULL

Clauses CHARACTER SET et COLLATE

Initialisation d’une variable

Exemples de déclarations de variables locales

IN AUTONOMOUS TRANSACTION

Exemples IN AUTONOMOUS TRANSACTION

POST_EVENT

Exemples POST_EVENT

RETURN

Exemples RETURN

Traitement des erreurs

Exceptions du système

Exceptions personnalisées

EXCEPTION

Exemples EXCEPTION

WHEN …​ DO

Champ d’action de l’Instruction WHEN …​ DO

Exemples d’utilisation WHEN…​DO

DECLARE …​ CURSOR

Curseurs unidirectionnels et de défilement

Caractéristiques de l’utilisation du curseur

Exemples d’utilisation d’un curseur nommé

DECLARE PROCEDURE

DECLARE FUNCTION

BEGIN …​ END

Exemples BEGIN …​ END

IF …​ THEN …​ ELSE

Exemples IF

WHILE …​ DO

Exemples WHILE …​ DO

BREAK

`FOR SELECT`

Exemples en utilisant `FOR SELECT`

`FOR EXECUTE STATEMENT`

`OPEN`

Exemples `OPEN`

`FETCH`

Exemples `FETCH`

`CLOSE`

Exemples `CLOSE`

`DECLARE VARIABLE`

Restriction `NOT NULL`

Clauses `CHARACTER SET` et `COLLATE`

`IN AUTONOMOUS TRANSACTION`

Exemples `IN AUTONOMOUS TRANSACTION`

`POST_EVENT`

Exemples `POST_EVENT`

`RETURN`

Exemples `RETURN`

`EXCEPTION`

Exemples `EXCEPTION`

`WHEN … DO`

Champ d’action de l’Instruction `WHEN … DO`

Exemples d’utilisation `WHEN…DO`

`DECLARE … CURSOR`

`DECLARE PROCEDURE`

`BEGIN … END`

Exemples `BEGIN … END`

`IF … THEN … ELSE`

Exemples `IF`

`WHILE … DO`

Exemples `WHILE … DO`

`BREAK`