COMMENTSInstructions de procédure SQL (PSQL)

`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

docnext count = 5

`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.