FirebirdSQL logo
 COMMENTSInstructions de procédure SQL (PSQL) 

UPDATE OR INSERT

affectation

Ajout d’une nouvelle entrée ou mise à jour d’une entrée existante dans une table.

Disponible en

DSQL, PSQL

Syntaxe
UPDATE OR INSERT INTO target [(<column_list>)]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [RETURNING <returning_list> [INTO <variables>]]

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

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

<ins_value> ::= <value_expression> | DEFAULT

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

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] 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’instruction UPDATE OR INSERT.
Paramètre Description

target

Nom de la table ou de la vue dont l’entrée sera mise à jour ou une nouvelle entrée sera insérée.

col_name

La colonne d’une table ou d’une vue.

ins_value

Une expression dont la valeur est utilisée pour insérer ou mettre à jour une table.

literal

Litéral.

context-variable

La variable de contexte.

other-single-value-expr

Toute autre expression qui renvoie une seule valeur du type de données Firebird ou NULL.

return_expression

Expression renvoyée dans une clause RETURNING.

alias

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

varname

Le nom de la variable PSQL.

L’instruction UPDATE OR INSERT insère ou met à jour un ou plusieurs enregistrements existants. L’action effectuée dépend des valeurs des colonnes dans l’instruction MATCHING (ou, si elle n’est pas spécifiée, des valeurs des colonnes de la clé primaire — PK). Si des enregistrements correspondant aux valeurs spécifiées sont trouvés, ils sont mis à jour. Sinon, un nouvel enregistrement est inséré.

Une correspondance est définie comme une correspondance complète des valeurs des colonnes MATCHING ou PK. La correspondance est vérifiée en utilisant IS NOT DISTINCT, ainsi NULL est mis en correspondance avec NULL.

Note
Limites

  • Si la table n’a pas de clé primaire, spécifier MATCHING est considéré comme obligatoire ;

  • Dans la liste MATCHING, tout comme dans la liste des colonnes de mise à jour/insertion, chaque colonne ne peut être mentionnée qu’une seule fois ;

  • La clause INTO n’est disponible que dans PSQL.

Mot clé DEFAULT

Dans la liste VALUES, 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 obtiendra la valeur NULL.

Note
Restriction

Une colonne pour laquelle le mot-clé DEFAULT est utilisé au lieu d’une valeur ne peut pas être utilisée dans une clause MATCHING.
Example 1. Utilisation du mot-clé DEFAULT dans une instruction UPDATE OR INSERT.
CREATE TABLE cars (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

-- la colonne BYYEAR contiendra la valeur 1990
UPDATE OR INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus')
MATCHING (name);

docnext count = 18

RETURNING

La clause RETURNING peut contenir toutes les colonnes spécifiées dans la déclaration ou d’autres colonnes et expressions. Les valeurs de retour contiennent tous les changements effectués dans les déclencheurs BEFORE mais pas dans les déclencheurs AFTER. Les expressions OLD.fieldname et NEW.fieldname peuvent être utilisées comme valeurs de retour. Pour les noms de colonnes normaux, les nouvelles valeurs sont retournées.

Vous pouvez spécifier un astérisque (*) au lieu d’une liste de colonnes. Dans ce cas, toutes les valeurs des colonnes de la table seront retournées. L’astérisque peut être utilisé avec les spécificateurs NEW ou OLD.

Note

  • En DSQL, une déclaration avec RETURNING ne retourne toujours qu’une seule chaîne de caractères.
Example 1. Utilisation de la clause "RETURNING" dans une instruction "UPDATE OR INSERT".
UPDATE OR INSERT INTO Cows (Name, Number, Location)
VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
MATCHING (Number)
RETURNING rec_id
INTO :id;

DELETE

affectation

Suppression des données d’une table.

Disponible en

DSQL, ESQL, PSQL

Syntaxe:
DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

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

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

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

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

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

target

Nom de la table ou de la vue à partir de laquelle les enregistrements doivent être supprimés.

alias

L’alias d’une table ou d’une vue.

col-name

Le nom de la colonne de la table ou de la vue.

search-conditions

Une condition de recherche limitant le jeu d’enregistrements à supprimer.

cursorname

Le nom du curseur sur lequel est positionné l’enregistrement à supprimer.

plan_items

Proposition de plan.

sort_items

Tri de la proposition

m, n

Expressions entières pour limiter le nombre d’entrées à supprimer.

return_expression

Expression renvoyée dans une clause RETURNING.

literal

Litéral.

context-variable

La variable de contexte.

other-single-value-expr

Toute autre expression qui renvoie une seule valeur du type de données Firebird ou NULL.

collation

Nom de tri existant (pour les types de caractères uniquement).

ret_alias

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

varname

Nom de la variable PSQL.

L’instruction DELETE supprime les lignes d’une table ou d’une ou plusieurs tables de présentation.

Si un alias est spécifié pour une table, il doit être utilisé pour toutes les colonnes de la table.

WHERE

La condition de la clause WHERE limite le jeu d’enregistrements à supprimer. Seuls les enregistrements qui satisfont à la condition de recherche ou seulement l’enregistrement curseur nommé actuel sont supprimés.

La suppression avec WHERE CURRENT OF est appelée positioned delete, car elle supprime l’enregistrement à la position actuelle. La suppression avec `WHERE condition' est appelée searched delete, car Firebird recherche les enregistrements qui satisfont à la condition.

Note

En DSQL pur, l’expression WHERE CURRENT OF n’a aucun sens car DSQL n’a pas d’instruction pour créer un curseur.
Example 1. Utilisation de la clause WHERE dans l’instruction DELETE.
DELETE FROM People
WHERE first_name <> 'Boris' AND last_name <> 'Johnson';
DELETE FROM employee e
WHERE NOT EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);
DELETE FROM Cities
WHERE CURRENT OF Cur_Cities; -- uniquement en PSQL

PLAN

L’instruction `PLAN' vous permet de spécifier manuellement un plan pour l’optimiseur.

Example 1. Utilisation de la clause PLAN dans l’instruction DELETE.
DELETE FROM Submissions
WHERE date_entered < '1-Jan-2002'
PLAN (Submissions INDEX ix_subm_date)

ORDER BY et ROWS

La clause "ORDER BY" organise l’ensemble avant de le supprimer, ce qui peut être important dans certains cas.

La clause ROWS vous permet de limiter le nombre de lignes à supprimer.Elle n’a de sens qu’en combinaison avec la clause ORDER BY, mais est valable sans elle.

Les valeurs m et n peuvent être des expressions entières quelconques.

Avec un seul argument m, les m premiers enregistrements sont supprimés.L’ordre des enregistrements sans ORDER BY est indéfini (aléatoire).

Notes :

  • Si m est supérieur au nombre total d’entrées dans l’ensemble, l’ensemble entier est supprimé ;

  • Si m = 0, aucune suppression n’a lieu ;

  • Si m < 0, un message d’erreur est émis.

Si les arguments m et n sont spécifiés, la suppression est limitée au nombre d’entrées comprises entre m et n, inclusivement.La numérotation des enregistrements commence à partir de 1.

Note sur l’utilisation de deux arguments :

  • Si m est supérieur au nombre total de lignes de l’ensemble, aucune ligne n’est supprimée ;

  • Si m > 0 et ⇐ le nombre de lignes dans l’ensemble, et que n est en dehors de ces valeurs, les lignes de m à la fin de l’ensemble sont supprimées ;

  • Si m < 1 ou n < 1, un message d’erreur est émis ;

  • Si n = m - 1, aucune entrée n’est supprimée ;

  • Si n < m - 1, un message d’erreur est émis.

Example 1. Utilisation de "ORDER BY" et "ROWS" dans l’instruction "DELETE".

Suppression de l’achat le plus ancien

DELETE FROM Purchases ORDER BY ByDate ROWS 1

Suppression des commandes pour les 10 clients ayant les numéros les plus élevés

DELETE FROM Sales ORDER BY custno DESC ROWS 1 TO 10

Supprime tous les enregistrements des ventes, car aucun ROWS n’est spécifié.

DELETE FROM Sales ORDER BY custno DESC

Supprime une entrée "depuis la fin", c’est-à-dire depuis Z…​

DELETE FROM popgroups ORDER BY name DESC ROWS 1

Supprime les cinq groupes les plus anciens

DELETE FROM popgroups ORDER BY formed ROWS 5

Aucun tri (ORDER BY) n’est spécifié, donc 8 enregistrements détectés seront supprimés, en commençant par le cinquième.

DELETE FROM popgroups ROWS 5 TO 12

SKIP LOCKED

Affectation

Ne pas tenir compte du blocage.

La proposition SKIP LOCKED fait en sorte que le moteur saute les enregistrements verrouillés par d’autres transactions, à la place,d’attendre ou de provoquer des erreurs lorsqu’un conflit se produit.

Cette fonctionnalité est utile pour mettre en œuvre des files d’attente de travail, dans lesquelles un ou plusieurs processus envoient des données à une table et génèrent un événement, tandis que les processus de travail écoutent ces événements et lisent/suppriment des éléments de la table.En utilisant SKIP LOCKED, plusieurs threads de travail peuvent obtenir des éléments de travail exclusifs de la table sans conflit.

Note

Si la phrase SKIP LOCKED est utilisée en conjonction avec FIRST/SKIP/ROWS/OFFSET/FETCH, les enregistrements verrouillés sont ignorés en premier,puis les limiteurs FIRST/SKIP/ROWS/OFFSET/FETCH sont appliqués aux enregistrements restants.

Voir aussi: SELECT …​ SKIP LOCKED,UPDATE …​ SKIP LOCKED.

Example 1. Utiliser la phrase SKIP LOCKED pour organiser une file d’attente

Préparation des métadonnées.

create table emails_queue (
    subject varchar(60) not null,
    text blob sub_type text not null
);

set term !;

create trigger emails_queue_ins after insert on emails_queue
as
begin
    post_event('EMAILS_QUEUE');
end!

set term ;!

Envoi de données par une application ou un sous-programme

insert into emails_queue (subject, text)
  values ('E-mail subject', 'E-mail text...');
commit;

L’application cliente peut écouter l’événement EMAILS_QUEUE pour envoyer des courriels à l’aide de cette requête :

delete from emails_queue
  rows 10
  skip locked
  returning subject, text;

Plus d’une instance d’une application peut fonctionner, par exemple pour l’équilibrage de la charge.

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.

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.