FirebirdSQL logo
 COMMENTSInstructions de procédure SQL (PSQL) 

RETURNING

L’opérateur DELETE peut contenir une construction RETURNING pour retourner les valeurs des enregistrements en cours de suppression.Toutes les colonnes et expressions peuvent être spécifiées dans RETURNING.Un astérisque (*) peut être spécifié à la place d’une liste de colonnes, auquel cas toutes les colonnes de l’enregistrement supprimé seront retournées.

Note

  • En DML, l’instruction DELETE avec la clause RETURNING retourne un curseur (avant Firebird 5.0, elle ne pouvait retourner qu’un seul enregistrement).Actuellement, les instructions avec la clause RETURNING ne peuvent pas être appliqués avec la clause FOR pour boucler sur le curseur dans PSQL.Ce comportement peut être modifié dans les futures versions de Firebird.Si les enregistrements n’ont pas été supprimés, les colonnes retournées contiennent NULL ;

  • En PSQL, si la ligne n’a pas été supprimée, rien n’est renvoyé et les variables cibles conservent leurs valeurs ;

  • La clause INTO n’est disponible que dans PSQL.
Example 1. Utilisation de la clause RETOURNER dans l’instruction DELETE.
DELETE FROM Scholars
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING last_name, fullname, id

DELETE FROM Dumbbells
ORDER BY iq DESC
ROWS 1
RETURNING last_name, iq
INTO :lname, :iq;

DELETE FRMO TempSales ts
WHERE ts.id = tempid
RETURNING ts.qty
INTO new.qty;

MERGE

affectation

Fusionner les enregistrements source dans la table cible (ou la vue actualisable).

Disponible en

DSQL, PSQL

Syntaxe
MERGE
  INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join condition>
  <merge when> [<merge when> ...]
  [<plan clause>]
  [<order by clause>]
  [<returning clause>]

<source> ::= tablename | (<select_stmt>)

<merge when> ::=
    <merge when matched>
  | <merge when not matched by target>
  | <merge when not matched by source>

<merge when matched> ::=
  WHEN MATCHED [ AND <condition> ]
    THEN { UPDATE SET <assignment_list> | DELETE }

<merge when not matched by target> ::=
  WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ]
    THEN INSERT [ <left paren> <column_list> <right paren> ]
    VALUES <left paren> <value_list> <right paren>

<merge when not matched by source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
    { UPDATE SET <assignment list> | DELETE }

<assignment_list> ::=
  col_name = <m_value> [, colname = <m_value> ...]

<column_list> ::= col_name [, col_name ...]

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

<m_value> ::= <value_expression> | DEFAULT

<returning clause> ::= RETURNING <returning_list> [INTO <variable_list>]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::=
  [:]varname [, [:]varname ...]
Table 1. Paramètres de l’opérateur MERGE
Paramètre Description

target

La table ou la vue actualisable cible.

source

Source de données : peut être une table, une vue, une procédure stockée ou une table dérivée.

target_alias

L’alias de la table ou de la vue cible.

source_alias

Alias de la source.

join condition

Une condition pour connecter la table cible et la source.

condition

Conditions de contrôle supplémentaires dans les clauses WHEN MATCHED ou WHEN NOT MATCHED.

col_name

La colonne de la table ou de la vue cible.

m_value

Une valeur assignée à une colonne dans la table cible. Une expression qui peut contenir des littéraux, des variables PSQL, des colonnes d’une source.

return_expression

Expression renvoyée dans une clause RETURNING.

ret_alias

Alias pour l’expression retournée dans la clause RETURNING.

varname

Le nom de la variable PSQL.

L’instruction MERGE fusionne les enregistrements de la table source et de la table cible (ou une vue actualisable).Lors de l’exécution de l’instruction MERGE, les enregistrements sources sont lus et INSERT, UPDATE ou DELETE sont exécutés pour la cible.en fonction des conditions.

La source peut être une table, une vue, une procédure stockée ou une table dérivée. Lorsque l’instruction MERGE est exécuté, une connexion est établie entre la table source (USING) et la table cible. Le type de connexion dépend de la présence de clauses WHEN NOT MATCHED :

  • <merge when not matched by target> and <merge when not matched by source> — FULL JOIN

  • <fusionner quand la source ne correspond pas> — JOINT DROIT

  • <merge quand il n’y a pas de correspondance avec la cible> — LEFT JOIN

  • seulement <fusionner en cas de correspondance> — INNER JOIN

L’action sur la table cible et la condition dans laquelle elle se produit sont décrites dans la clause WHEN. Les clauses multiples WHEN MATCHED, WHEN NOT MATCHED [BY TARGET] et WHEN NOT MATCHED BY SOURCE sont autorisées.

Si la condition de la clause WHEN n’est pas remplie, Firebird la saute et passe à la clause suivante. Cela se produira jusqu’à ce que la condition de l’une des clauses WHEN ne soit pas satisfaite. Dans ce cas, l’action associée à la clause WHEN est exécutée et l’enregistrement suivant du résultat de la connexion entre la table source (USING) et la table cible est ignoré. Une seule action est effectuée pour chaque enregistrement de résultat de connexion.

Note

La clause WHEN NOT MATCHED [BY TARGET] est évaluée en fonction de la source, c’est-à-dire la table ou le jeu de données spécifié dans la clause USING. En effet, si l’enregistrement source ne correspond pas à l’enregistrement cible, la clause INSERT est exécutée. Bien entendu, si l’enregistrement cible ne correspond pas à l’enregistrement source, aucune action n’est effectuée.

Actuellement, la variable ROW_COUNT renvoie 1, même si plus d’un enregistrement a été modifié ou inséré. CORE-4400.

docnext count = 10

WHEN MATCHED

Spécifie que toutes les lignes target qui correspondent aux lignes retournées par <source> ON <condition de jonction> et qui satisfont à des conditions de recherche supplémentaires sont mises à jour (clause UPDATE) ou supprimées (claus DELETE) selon la clause <merge when matched>.

Plusieurs clause WHEN MATCHED peuvent être spécifiées. Si plus d’une clause WHEN MATCHED est spécifiée, elles doivent toutes être complétées par des conditions de recherche supplémentaires, sauf la dernière.

Une instruction MERGE ne peut pas mettre à jour une ligne plus d’une fois ou mettre à jour et supprimer la même ligne en même temps.

Note

Si la condition WHEN MATCHED est présente et que plusieurs enregistrements correspondent à des enregistrements de la table cible, une erreur sera émise.

Avant Firebird 4.0, UPDATE sera exécuté pour tous les enregistrements sources correspondants, et chaque mise à jour ultérieure écrasera la précédente. Ce comportement n’est pas conforme au standard SQL.

Dans la liste SET de la clause UPDATE, vous pouvez utiliser le mot clé DEFAULT à la place de la valeur de la colonne. Dans ce cas, la colonne obtiendra la valeur par défaut spécifiée lors de la définition de la table cible. S’il n’y a pas de valeur par défaut pour la colonne, celle-ci prendra la valeur NULL.

WHEN NOT MATCHED [BY TARGET]

Spécifie que toutes les lignes target qui ne correspondent pas aux lignes retournées par l’expression <source> ON <join condition> et qui satisfont à des conditions de recherche supplémentaires sont insérées dans la table cible (clause INSERT) selon la clause <merge when not matched by target>.

Plusieurs clauses "SANS CORRESPONDANCE [PAR LA CIBLE]" sont autorisées. Si plus d’une clause est spécifiéeLorsqu’il n’y a pas de correspondance [avec la cible], ils doivent tous être complétés par des termes de recherche supplémentaires, sauf le dernier.

Dans la liste VALUES d’une clause INSERT, le mot clé DEFAULT peut être utilisé à la place de la valeur de la colonne. Dans ce cas, la colonne obtiendra la valeur par défaut spécifiée lors de la définition de la table cible.S’il n’y a pas de valeur par défaut pour la colonne, celle-ci prendra la valeur NULL.

WHEN NOT MATCHED BY SOURCE

Indique que toutes les lignes target qui ne correspondent pas aux lignes renvoyées par <source> ON <join condition> et qui satisfont à des conditions de recherche supplémentaires, (clause`UPDATE`) ou sont supprimées (clause`DELETE`) selon la clause <merge when not matched by source>.

La clause WHEN NOT MATCHED BY SOURCE est disponible à partir de Firebird 5.0.

Plusieurs clause WHEN NOT MATCHED BY SOURCE peuvent être spécifiées. Si plus d’une clause est spécifiéeLes clauses WHEN NOT MATCHED BY SOURCE doivent toutes être complétées par des conditions de recherche supplémentaires, sauf la dernière.

Dans la liste SET de la clause`UPDATE`, le mot clé DEFAULT peut être utilisé à la place de la valeur de la colonne. Dans ce cas, la colonne obtiendra la valeur par défaut spécifiée lors de la définition de la table cible. S’il n’y a pas de valeur par défaut pour la colonne, celle-ci prendra la valeur NULL.

Note

Veuillez noter ! Dans la liste SET d’une clause UPDATE, il n’est pas judicieux d’utiliser des expressions faisant référence à <source>,car aucune des entrées de <source> ne correspond aux entrées de cible.

Exemples

Example 1. Les instructions simples `MERGE'.
MERGE INTO books b
USING purchases p
ON p.title = b.title AND p.booktype = 'bk'
WHEN MATCHED THEN
  UPDATE SET b.descr = b.descr || '; ' || p.descr
WHEN NOT MATCHED THEN
  INSERT (title, descr, bought)
  VALUES (p.title, p.descr, p.bought);

-- utiliser une table de dérivation
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
  UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (cd.id, cd.name);

-- ainsi que la récurrence CTE
MERGE INTO numbers
USING (
  WITH RECURSIVE r(n) AS (
    SELECT 1 FROM rdb$database
    UNION ALL
    SELECT n+1 FROM r WHERE n < 200
  )
  SELECT n FROM r
) t
ON numbers.num = t.n
WHEN NOT MATCHED THEN
  INSERT(num) VALUES(t.n);

-- en utilisant la clause DELETE
MERGE INTO SALARY_HISTORY
USING (
  SELECT EMP_NO
  FROM EMPLOYEE
  WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE
Example 2. Utilisation de l’instruction MERGE avec des conditions supplémentaires dans les clauses WHEN [NOT] MATCHED.

Dans l’exemple suivant, la table PRODUCT_INVENTORY est mise à jour quotidiennement en fonction des commandes traitées dans la table SALES_ORDER_LINE. Si le nombre de commandes pour un produit est tel que le niveau de stock du produit tombe à zéro ou devient encore plus bas, la ligne pour ce produit est supprimée de la table PRODUCT_INVENTORY.

MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
Example 3. Utilisation de l’instruction "MERGE" avec "WHEN NOT MATCHED BY SOURCE".

L’exemple suivant met à jour les enregistrements de la table cible s’ils sont trouvés dans la table source et les supprime s’ils ne sont pas trouvés.

MERGE
  INTO customers c
  USING new_customers nc
  ON (c.id = nc.id)
  WHEN MATCHED THEN
    UPDATE SET
	  name = cd.name
  WHEN NOT MATCHED BY SOURCE THEN
	DELETE
Voir aussi :

SELECT, INSERT, UPDATE, DELETE.

RETURNING

L’instruction MERGE peut contenir une construction RETURNING pour retourner les valeurs des lignes ajoutées, modifiées ou supprimées.Toutes les colonnes de la table cible (la vue mise à jour) et les expressions peuvent être spécifiées dans RETURNING.

Les valeurs de retour contiennent les changements effectués dans les déclencheurs BEFORE.

Les noms de colonnes peuvent être préfixés avec NEW et OLD pour spécifier quelle valeur de colonne vous voulez avant ou après la modification.

Un astérisque (*) peut être spécifié à la place d’une liste de colonnes, auquel cas toutes les colonnes de la table cible seront retournées.Les préfixes NEW et OLD peuvent être utilisés en conjonction avec un astérisque.

Pour les clauses WHEN MATCHED UPDATE et MERGE WHEN NOT MATCHED, les noms de colonne non spécifiés ou spécifiés parou leurs alias sont compris comme des colonnes préfixées avec NEW, pour les clauses MERGE WHEN MATCHED DELETE - avec le préfixe OLD.

Note

  • En DML, l’instruction MERGE avec la clause RETURNING retourne un curseur (avant Firebird 5.0, il ne pouvait retourner qu’un seul enregistrement).Actuellement, les instructions avec la clause RETURNING ne peuvent pas être appliqués avec la clause FOR pour boucler sur le curseur dans PSQL.Ce comportement peut être modifié dans les futures versions de Firebird.

  • La clause INTO n’est disponible que dans PSQL.
Example 1. Utilisation de l’instruction MERGE avec la clause RETOURNEMENT.

Modifions un peu notre exemple précédent pour qu’il ne concerne qu’une seule ligne, et ajoutons une instruction RETURNING qui renvoie l’ancienne et la nouvelle quantité de l’article et la différence entre ces valeurs.

MERGE INTO PRODUCT_IVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
    AND SL.ID_PRODUCT = :ID_PRODUCT
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO :OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

EXECUTE PROCEDURE

affectation

Exécution d’une procédure stockée.

Disponible en

DSQL, ESQL, PSQL

Syntaxe
EXECUTE PROCEDURE procname
   [{ <inparam-list> | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list> ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname
Table 1. Paramètres pour l’instruction `EXECUTE PROCEDURE'.
Paramètre Description

procname

Le nom de la procédure stockée.

inparam

Une expression dont le type est compatible avec le paramètre d’entrée de la procédure stockée.

varname

Une variable PSQL qui renvoie la valeur du paramètre de sortie de la procédure.

L’instruction EXECUTE PROCEDURE exécute une procédure stockée, en récupérant une liste d’un ou plusieurs paramètres d’entrée s’ils sont définis, et en renvoyant un ensemble de valeurs d’une seule ligne s’ils sont définis.

"Procédures stockées" exécutables

L’instruction EXECUTE PROCEDURE est le style d’appel de procédure stockée le plus couramment utilisé.pour modifier certaines données. Leur code ne contient pas l’instruction SUSPEND.Ces procédures stockées peuvent renvoyer un ensemble de données ne comprenant pas plus d’une chaîne de caractères.Cet ensemble peut être passé aux variables d’une autre procédure (appelante) en utilisant la clause RETURNING_VALUES.Les interfaces client ont généralement des wrappers API qui peuvent extraire les valeurs de sortie dans un tampon d’une seule ligne lorsque la procédure est appelée via EXECUTE PROCEDURE dans DSQL.

Lorsqu’un autre type de procédure (procédures sélectives) est appelé avec EXECUTE PROCEDURE, seule la première procédure est appelée.du jeu de résultats sera retourné, même si cette procédure est susceptible de retourner un résultat de plusieurs lignes.Les procédures stockées "sélectives" doivent être appelées avec l’instruction SELECT, auquel cas elles se comportent comme des tables virtuelles.

Note

  • Dans PSQL et DSQL, les paramètres d’entrée peuvent être n’importe quelle expression compatible avec le type ;

  • Bien que les parenthèses pour séparer la liste des paramètres à passer soient facultatives après le nom de la procédure stockée, il est conseillé de les utiliser ;

  • La clause RETURNING_VALUES n’est disponible que dans PSQL.
Example 1. Utilisation de l’instruction `EXECUTE PROCEDURE' dans PSQL
EXECUTE PROCEDURE MakeFullName(:First_Name, :Middle_Name, :Last_Name)
RETURNING_VALUES :FullName;

Dans cet instruction, l’utilisation des deux points (“:”) pour les paramètres d’entrée et de sortie est facultative.

Il est permis d’utiliser des expressions comme paramètres.

EXECUTE PROCEDURE MakeFullName
   ('Mr./Mrs. ' || First_Name, Middle_Name, upper(Last_Name))
RETURNING_VALUES FullName;
Example 2. Appel de l’instruction EXECUTE PROCEDURE dans isql.
EXECUTE PROCEDURE MakeFullName
  'J', 'Edgar', 'Hoover';

EXECUTE BLOCK

affectation

Exécution d’un bloc PSQL anonyme.

Disponible en

DSQL

Syntaxe
EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-module-body>

<inparams> ::= <param_decl> = ? [, <inparams> ]

<outparams> ::= <param_decl> [, <outparams>]

<param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation]

<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 des types de données BLOB.

<psql-routine-body> ::=
  Voir syntaxe du corps du module.
Table 1. Paramètres de l’instruction EXECUTE BLOCK.
Paramètre Description

param_decl

Une description du paramètre d’entrée ou de sortie.

paramname

Nom du paramètre d’entrée ou de sortie de la procédure. Peut contenir jusqu’à 63 caractères. Le nom du paramètre doit être unique parmi les paramètres d’entrée et de sortie de la procédure, ainsi que parmi ses variables locales.

non_array_datatype

Type de données SQL à l’exclusion des 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.

Exécute un bloc de code PSQL comme s’il s’agissait d’une procédure stockée, éventuellement avec des paramètres d’entrée et de sortie et des variables locales. Cela permet à l’utilisateur d’exécuter PSQL "à la volée" dans un contexte DSQL.

Exemples:

Cet exemple saisit les nombres de 0 à 127 et leurs caractères ASCII correspondants dans la table ASCIITABLE :

EXECUTE BLOCK
AS
  DECLARE i INT = 0;
BEGIN
  WHILE (i < 128) DO
  BEGIN
    INSERT INTO AsciiTable VALUES (:i, ascii_char(:i));
    i = i + 1;
  END
END

L’exemple suivant calcule la moyenne géométrique de deux nombres et la renvoie à l’utilisateur :

EXECUTE BLOCK (
  x DOUBLE PRECISION = ?,
  y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
  gmean = sqrt(x*y);
  SUSPEND;
END

Comme ce bloc a des paramètres d’entrée, il doit être préparé à l’avance.Il est alors possible de définir les paramètres et d’exécuter le bloc.La façon dont cela est fait, et si cela peut être fait du tout, dépend du logiciel client.Voir les notes ci-dessous.

Notre dernier exemple prend deux valeurs entières, la plus petite et la plus grande.Pour tous les nombres compris dans l’intervalle le plus petit…​le plus grand, le bloc fournit le nombre lui-même, son carré, le cube et la quatrième puissance.

EXECUTE BLOCK (smallest INT = ?, largest INT = ?)
RETURNS (
  number INT,
  square BIGINT,
  cube BIGINT,
  fourth BIGINT)
AS
BEGIN
  number = smallest;
  WHILE (number <= largest) DO
  BEGIN
    square = number * number;
    cube = number * square;
    fourth = number * cube;
    SUSPEND;
    number = number + 1;
  END
END

Là encore, la manière dont vous pouvez définir les valeurs des paramètres dépend du logiciel client.

Voir aussi :

instructions linguistiques PSQL.

Paramètres d’entrée et de sortie

L’exécution d’un bloc sans paramètres d’entrée devrait être possible avec tout client Firebird qui permet à l’utilisateur de saisir ses propres instructions DSQL.S’il y a des paramètres d’entrée, les choses se compliquent : ces paramètres doivent recevoir leurs valeurs après la préparation de l’instruction, mais avant qu’elle ne puisse être exécutée.Cela nécessite des fonctionnalités spéciales que toutes les applications clientes ne possèdent pas (par exemple, isql ne propose pas une telle fonctionnalité).

Le serveur n’accepte que les points d’interrogation (" ?") comme caractères de remplacement pour les valeurs d’entrée, et non les " :[remplaçable]`a'", " :MyParam'" etc. ou les valeurs littérales.Le logiciel client peut prendre en charge la forme " :[remplaçable]xxx``", auquel cas la demande sera prétraitée avant d’être envoyée au serveur.

Si le bloc a des paramètres de sortie, vous devez utiliser SUSPEND sinon rien ne sera renvoyé.

La sortie est toujours renvoyée sous forme d’ensemble de données, comme avec l’instruction SELECT.Vous ne pouvez pas utiliser RETURNING_VALUES ou exécuter un bloc en retournant des valeurs dans certaines variables à l’aide de INTO, même si une seule chaîne est retournée.

Pour plus d’informations sur les paramètres et les déclarations de variables, [TYPE OF] [remplaçable] "domaine", TYPE OF COLUMN, etc.voir le chapitre DECLARE VARIABLE.

instruction de terminaison

Certains éditeurs d’instructions SQL — notamment l’utilitaire isql fourni avec Firebird, et peut-être certains éditeurs tiers — utilisent une convention interne qui exige que toutes les instructions soient terminées par un point-virgule.

Cela crée un conflit avec la syntaxe PSQL lors du codage dans ces environnements. Si vous n’êtes pas familier avec ce problème et sa solution, veuillez étudier les détails dans le chapitre PSQL dans la section intitulée Changer le terminateur dans isql.