FirebirdSQL logo
 COMMENTSInstructions de procédure SQL (PSQL) 

WITH LOCK

affectation

Blocage pessimiste.

disponible

DSQL, PSQL

Syntaxe
SELECT ...
FROM single_table
[WHERE ...]
[FOR UPDATE [OF <column-names>]]
WITH LOCK [SKIP LOCKED]

L’option `WITH LOCK', fournit une option de verrouillage pessimiste explicite limitée pour une utilisation prudente dans les jeux de chaînes affectés :

  1. un échantillon extrêmement réduit (idéalement une ligne) et

  2. lors du contrôle à partir de l’application.

Caution
Pour les experts uniquement

Le verrouillage pessimiste est rarement nécessaire lorsqu’on travaille avec Firebird. Cette fonctionnalité ne peut être utilisée qu’avec une bonne compréhension de celle-ci.

Une bonne connaissance des différents niveaux d’isolation et des autres paramètres de transaction est nécessaire avant d’utiliser le verrouillage explicite dans votre application.

Si la clause WITH LOCK réussit, elle verrouillera les lignes de données sélectionnées et les empêchera ainsi d’être modifiées dans d’autres transactions jusqu’à ce que votre transaction soit terminée.

La clause WITH LOCK n’est disponible que pour sélectionner des données (SELECT) dans une seule table.

La clause WITH LOCK ne peut pas être utilisée :

  • dans les sous-requêtes ;

  • dans les requêtes avec jointure de plusieurs tables (JOIN) ;

  • avec l’instruction DISTINCT, la clause GROUP BY et lors de l’utilisation de toute fonction d’agrégation ;

  • lorsque vous travaillez avec des vues ;

  • lors de la sélection de données à partir de procédures stockées sélectives ;

  • lorsque vous travaillez avec des tables externes.

Le serveur, à son tour, pour chaque enregistrement soumis à un verrou explicite, renvoie la version de l’enregistrement qui est actuellement validée (à jour), quel que soit l’état de la base de données au moment de l’exécution de l’instruction d’extraction de données, ou une exception lors de la tentative de mettre à jour un enregistrement verrouillé.

Le comportement attendu et les messages de conflit dépendent des paramètres de transaction définis dans le TPB (Transaction Parameters Block) :

Effet des paramètres TPB sur le verrouillage explicite

Mode TPB Comportement

isc_tpb_consistency

Les verrous explicites sont remplacés par des verrous implicites ou explicites au niveau de la table et sont ignorés.

isc_tpb_concurrency + isc_tpb_nowait

Lorsqu’une modification d’enregistrement est confirmée dans une transaction qui a commencé après la transaction qui a déclenché le verrouillage explicite, une exception de conflit de mise à jour se produit immédiatement.

isc_tpb_concurrency + isc_tpb_wait

Lors de la confirmation d’un changement d’enregistrement dans une transaction qui a commencé après la transaction qui a lancé le lockout explicite, une exception de conflit de mise à jour se produit immédiatement. Si un changement de version d’enregistrement est en cours dans la transaction active (en utilisant le lockout explicite ou le lockout d’enregistrement optimiste normal), la transaction qui tente le lockout explicite attend jusqu’à ce que la transaction de lockout se termine et, lorsqu’elle se termine, essaie de faire en sorte que l’enregistrement soit de nouveau verrouillé.

isc_tpb_read_committed + isc_tpb_nowait

Si une transaction active modifie un enregistrement (en utilisant le verrouillage explicite ou le verrouillage optimiste normal des enregistrements), il y a une exception de conflit de mise à jour immédiate.

isc_tpb_read_committed + isc_tpb_wait

Si un enregistrement est en cours de modification dans une transaction active (à l’aide d’un verrouillage explicite ou d’un verrouillage optimiste normal des enregistrements), la transaction qui tente de procéder à un verrouillage explicite attend la fin de la transaction de verrouillage et, à ce moment-là, tente à nouveau de verrouiller l’enregistrement.

Il n’y a jamais de conflit de mise à jour pour ce mode TPB.

SKIP LOCKED

Affectation

Ne pas tenir compte de la partie bloquée.

La proposition SKIP LOCKED force le moteur à sauter les enregistrements verrouillés par d’autres transactions,au lieu d’attendre ou de provoquer des erreurs de conflit.

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.Un ou plusieurs processus envoient des données à la 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: UPDATE …​ SKIP LOCKED,DELETE FROM …​ SKIP LOCKED.

docnext count = 76

Utilisation de la clause FOR UPDATE

Si la clause FOR UPDATE précède la clause WITH LOCK, la mise en mémoire tampon de l’échantillon n’est pas utilisée. Ainsi, le verrou est appliqué à chaque ligne, une par une, au fur et à mesure que les enregistrements sont récupérés. Cela permet à un verrou de données réussi de cesser de fonctionner lorsqu’une ligne de l’échantillon a été verrouillée par une autre transaction.

Tip

En outre, certains composants d’accès vous permettent de définir la taille du tampon d’échantillonnage et de le réduire à 1 enregistrement, ce qui vous permet de verrouiller et de modifier une ligne avant d’échantillonner et de verrouiller la suivante ou de traiter les erreurs sans annuler les actions de votre transaction.
Note

La clause optionnelle "`OF <column-names>'" ne fait rien du tout.
Voir aussi

FOR UPDATE [OF]

Comment le serveur fonctionne avec WITH LOCK

Tenter de modifier un enregistrement avec une instruction UPDATE verrouillée par une autre transaction déclenche une exception de conflit de mise à jour ou l’attente de la fin de la transaction de verrouillage - selon le mode TPB. Le comportement du serveur est ici le même que si l’enregistrement avait déjà été modifié par la transaction de verrouillage.

Il n’y a pas de gdscode spécial renvoyé pour les conflits de mise à jour liés à une transaction de verrouillage pessimiste.

Le serveur garantit que tous les enregistrements renvoyés par l’instruction de verrouillage explicite sont effectivement verrouillés et correspondent aux conditions de recherche spécifiées dans l’instruction WHERE si ces conditions ne dépendent d’aucune autre table, s’il n’y a pas d’instructions de jointure, de sous-requêtes, etc.

Note

Cette situation peut se produire si une autre transaction concurrente confirme un changement dans l’exécution de la déclaration de verrouillage actuelle.

Le serveur verrouille les lignes au fur et à mesure qu’elles sont échantillonnées, ce qui a des conséquences importantes si vous verrouillez plusieurs lignes à la fois. De nombreuses méthodes d’accès aux bases de données Firebird utilisent des paquets de plusieurs centaines de lignes (appelés "tampon d’échantillonnage") pour échantillonner les données par défaut. La plupart des composants d’accès aux données ne sélectionnent pas les lignes contenues dans le dernier paquet reçu, et pour lesquelles un conflit de mise à jour s’est produit.

Précautions à prendre lors de l’utilisation WITH LOCK

  • Le retour en arrière d’un point de sauvegarde implicite ou explicite annule le verrouillage des enregistrements qui ont été modifiés dans le cadre de son action, mais les transactions en attente ne sont pas notifiées de la fin du verrouillage. Les applications ne doivent pas dépendre de ce comportement car il peut être modifié à l’avenir ;

  • Bien que les verrous explicites puissent être utilisés pour prévenir et/ou traiter les erreurs inhabituelles de conflit de mise à jour, le nombre d’erreurs de mise à jour (deadlocks) augmentera si vous ne concevez pas soigneusement votre stratégie de verrouillage et ne la gérez pas étroitement ;

  • La plupart des applications ne nécessitent pas de verrouillage explicite des enregistrements. Les principaux objectifs du verrouillage explicite sont les suivants :

    • pour éviter le traitement coûteux des erreurs de conflit de mise à jour dans les applications fortement chargées

    • pour maintenir l’intégrité des objets mappés à partir d’une base de données relationnelle dans un environnement en grappe. Si votre utilisation du verrouillage explicite n’entre pas dans l’une de ces deux catégories, alors c’est la mauvaise façon de résoudre les problèmes dans Firebird ;

  • Le verrouillage explicite est une fonction avancée ; n’en abusez pas ! Bien que le verrouillage explicite puisse être très important pour les sites web traitant des milliers de transactions d’écriture simultanées, ou pour des systèmes tels que ERP/CRM fonctionnant dans de grandes entreprises, la plupart des applications ne nécessitent pas son utilisation.

Exemples d’utilisation du blocage explicite

Example 1. Blocage d’une seule entrée
SELECT *
FROM DOCUMENT
WHERE DOCUMENT_ID=? WITH LOCK
Example 2. Verrouillage de plusieurs enregistrements avec leur curseur DSQL séquentiel :
SELECT *
FROM DOCUMENT
WHERE PARENT_ID=?
FOR UPDATE WITH LOCK

OPTIMIZE FOR

Affectation

Changer la stratégie de l’optimiseur.

Синтаксис
SELECT ...
FROM [...]
[WHERE ...]
[...]
[OPTIMIZE FOR {FIRST | ALL} ROWS]

La clause OPTIMIZE FOR vous permet de changer la stratégie de l’optimiseur au niveau de la requête SQL courante.Elle ne peut apparaître que dans l’instruction SELECT de la requête SQL de niveau supérieur.

Il existe deux stratégies d’optimisation des requêtes :

  • FIRST ROWS - l’optimiseur construit le plan de requête pour ne récupérer que les premières lignes de la requête de la manière la plus rapide ;

  • ALL ROWS - l’optimiseur construit le plan de requête pour récupérer toutes les lignes de la requête le plus rapidement possible.

Dans la plupart des cas, une stratégie d’optimisation ALL ROWS' est nécessaire. Cependant, si vous avez des applications avec des grilles de données,dans lesquelles seules les premières lignes du résultat sont affichées et les autres sont récupérées en fonction des besoins, la stratégie `FIRST ROWS peut être préférable car elle réduit le temps de déconnexion.

Par défaut, la stratégie d’optimisation spécifiée dans le paramètre OptimizeForFirstRows du fichier de configuration est utiliséefirebird.conf ou database.conf. OptimiseForFirstRows = false correspond à la stratégie ALL ROWS,OptimiseForFirstRows = true correspond à la stratégie First ROWS.

La stratégie d’optimisation peut également être modifiée au niveau de la session à l’aide de l’opérateur SET OPTIMIZE.La clause OPTIMIZE FOR spécifiée dans l’instruction SQL vous permet de remplacer la stratégie spécifiée au niveau de la session.

La clause OPTIMIZE FOR spécifie toujours la clause OPTIMIZE FOR la plus récente dans une requête SELECT, mais avant la clause INTO.

Note

Si la requête SELECT contient FIRST …​ SKIP, ROWS, OFFSET …​ FETCH, l’optimiseur passe implicitement au mode FIRST ROWS.

INTO

affectation

Transférer les résultats de SELECT dans des variables.

Disponible en:

PSQL

Syntaxe:
SELECT [...] <column-list>
FROM ...
[...]
[INTO <variable-list>]

<variable-list> ::= [:]psqlvar [, [:]psqlvar ...]

En PSQL (procédures stockées, triggers, etc.) les résultats d’une commande SELECT peuvent être chargés ligne par ligne dans des variables locales (le nombre, l’ordre et les types de variables locales doivent correspondre aux champs SELECT). Souvent, un tel chargement est le seul moyen de faire quelque chose avec les valeurs de retour.

L’instruction SELECT simple ne peut être utilisé dans PSQL que s’il ne retourne pas plus d’une chaîne de caractères, c’est-à-dire s’il s’agit d’une requête unique. Pour les requêtes qui retournent plusieurs chaînes de caractères, PSQL suggère d’utiliser l’instruction FOR SELECT.

Important

Lorsque la requête ne renvoie aucune donnée (zéro ligne), les valeurs des variables de la liste INTO ne sont pas modifiées.

De plus, PSQL prend en charge l’instruction DECLARE CURSOR, qui associe un curseur nommé à une commande SELECT particulière — et ce curseur peut alors être utilisé pour naviguer dans l’ensemble de données retourné.

En PSQL, la clause INTO doit apparaître à la toute fin de la commande SELECT.

Important
Veuillez noter.

En PSQL, les deux points avant les noms de variables sont facultatifs.
Exemples

Dans PSQL, vous pouvez attribuer les valeurs de min_amt, avg_amt et max_amt à des variables prédéfinies ou à des paramètres de sortie :

SELECT
  MIN(amount),
  AVG(CAST(amount AS float)),
  MAX(amount)
FROM orders
WHERE artno = 372218
INTO min_amt,
     avg_amt,
     max_amt;

Dans cette requête, CAST est utilisé pour calculer correctement la valeur moyenne. Si la valeur n’est pas convertie en float, la valeur moyenne sera tronquée à la valeur entière la plus proche.

Dans le déclencheur :

SELECT LIST(name, ', ')
FROM persons p
WHERE p.id IN (new.father, new.mother)
INTO new.parentnames;

expressions de table courantes CTE ("WITH …​ AS …​ SELECT")

expressions de table courantes (Common Table Expressions), en abrégé CTE, sont décrites comme des tables ou des vues virtuelles définies dans le préambule de la requête principale, qui participent à la requête principale. La requête principale peut faire référence à n’importe quelle CTE définie dans le préambule, comme lors de l’extraction de données de tables ou de vues ordinaires. Les CTE peuvent être récursives, c’est-à-dire se référer à elles-mêmes, mais ne peuvent pas être imbriquées.

Syntaxe
<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [   <rows-clause>
    | { [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <cte> [, <cte> ...]

<cte> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]
Table 1. Paramètres CTE
Paramètre Description

query-name

Le nom de l’expression du tableau.

column-name

Un alias (alias) pour une colonne d’une expression de tableau.
Notes

  • Une définition CTE peut contenir n’importe quel instruction SELECT valide tant qu’elle ne contient pas le préambule “`WITH…​`” (les instructions WITH ne peuvent pas être imbriqués) ;

  • Les CTE peuvent s’utiliser mutuellement, mais les références ne doivent pas comporter de boucles ;

  • CTE peut être utilisé dans n’importe quelle partie de la requête principale ou autre expression tabulaire et autant de fois que souhaité ;

  • La requête principale peut faire référence à un CTE plusieurs fois, mais avec des alias différents ;

  • Les CTE peuvent être utilisés dans les instructions INSERT, UPDATE et DELETE comme sous-requêtes ;

  • Si le CTE déclaré n’est pas utilisé, un message d’avertissement “CTE cte is not used in query” sera émis. Dans les versions antérieures, une erreur était émise au lieu d’un avertissement ;

  • Les CTE peuvent également être utilisés dans PSQL dans les boucles FOR :

    FOR
  WITH
    MY_RIVERS AS (
      SELECT *
      FROM RIVERS
      WHERE OWNER = 'me'
    )
  SELECT
    NAME,
    LENGTH
  FROM MY_RIVERS
  INTO :RNAME,
       :RLEN
DO
BEGIN
  ...
END
Exemples
Example 1. Requête utilisant CTE
WITH
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.DEPARTMENT,
  DYB_2008.BUDGET BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPARTMENT D
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2008
    ON D.DEPT_NO = DYB_2008.DEPT_NO AND
       DYB_2008.FISCAL_YEAR = 2008
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2009
    ON D.DEPT_NO = DYB_2009.DEPT_NO AND
       DYB_2009.FISCAL_YEAR = 2009
WHERE EXISTS (SELECT *
              FROM PROJ_DEPT_BUDGET B
              WHERE D.DEPT_NO = B.DEPT_NO)

CTE récursive

Un ETC récursif (autoréférencé) est une UNION qui doit comporter au moins un élément non récursif auquel les autres éléments de l’union sont liés. L’élément non récursif est placé en premier dans la CTE. Les membres récursifs sont séparés des membres non récursifs et les uns des autres par `UNION ALL'. L’association des membres non récursifs peut être de n’importe quel type.

Le CTE récursif nécessite le mot clé RECURSIVE à droite de WITH. Chaque membre récursif ne peut se référer qu’une seule fois à lui-même et cela doit être fait dans une clause FROM.

Le principal avantage des CTE récursifs est qu’ils utilisent beaucoup moins de mémoire et de temps CPU que les procédures stockées récursives équivalentes.

Exécution d’une opération récursive CTE

L’exécution d’un CTE récursif du point de vue du serveur Firebird peut être décrite comme suit :

  • Le serveur commence l’exécution avec le membre non-récursif ;

  • Pour chaque ligne sélectionnée dans la partie non récursive, chaque membre récursif est exécuté un par un, en utilisant les valeurs actuelles de l’itération précédente comme paramètres ;

  • Si l’instance membre récursive ne produit aucune ligne pendant l’exécution, la boucle d’exécution passe au niveau précédent et récupère la ligne suivante dans le jeu de données externe.

Exemples
Example 1. Récursive CTE
WITH RECURSIVE
  DEPT_YEAR_BUDGET AS (
    SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
    FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  ),
  DEPT_TREE AS (
    SELECT
      DEPT_NO,
      HEAD_DEPT,
      DEPARTMENT,
      CAST('' AS VARCHAR(255)) AS INDENT
    FROM DEPARTMENT
    WHERE HEAD_DEPT IS NULL
    UNION ALL
    SELECT
      D.DEPT_NO,
      D.HEAD_DEPT,
      D.DEPARTMENT,
      H.INDENT || ' '
    FROM
      DEPARTMENT D
      JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
  )
SELECT
  D.DEPT_NO,
  D.INDENT || D.DEPARTMENT DEPARTMENT,
  DYB_2008.BUDGET AS BUDGET_08,
  DYB_2009.BUDGET AS BUDGET_09
FROM
  DEPT_TREE D
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
    (D.DEPT_NO = DYB_2008.DEPT_NO) AND
    (DYB_2008.FISCAL_YEAR = 2008)
  LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
    (D.DEPT_NO = DYB_2009.DEPT_NO) AND
    (DYB_2009.FISCAL_YEAR = 2009)

L’exemple suivant permet de dériver le pedigree d’un cheval, la principale différence étant que la récursion passe par deux branches de l’arbre généalogique à la fois.

WITH RECURSIVE
  PEDIGREE (
    CODE_HORSE,
    CODE_FATHER,
    CODE_MOTHER,
    NAME,
    MARK,
    DEPTH
  ) AS (
    SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        CAST('' AS VARCHAR(80)),
        0
    FROM HORSE
    WHERE
      HORSE.CODE_HORSE = :CODE_HORSE
    UNION ALL
    SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        'F' || PEDIGREE.MARK,
        PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
    WHERE
      –- limite de profondeur de récursion
      PEDIGREE.DEPTH < :MAX_DEPTH
    UNION ALL
    SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        'M' || PEDIGREE.MARK,
        PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
    WHERE
      –- limite de profondeur de récursion
      PEDIGREE.DEPTH < :MAX_DEPTH
  )
SELECT
  CODE_HORSE,
  NAME,
  MARK,
  DEPTH
FROM
  PEDIGREE
Notes sur la récurrence CTE:

  • Les agrégats (DISTINCT, GROUP BY, HAVING) et les fonctions d’agrégation (SUM, COUNT, MAX, etc.) ne sont pas autorisés dans les membres d’unions récursives ;

  • Le lien récursif ne peut pas être membre de l’association externe OUTER JOIN ;

  • La profondeur maximale de la récursion est de 1024 ;

  • Un membre récursif ne peut pas être représenté comme une table dérivée.

Liste des champs`SELECT`

La liste des champs contient une ou plusieurs expressions séparées par des virgules. Le résultat de chaque expression est la valeur du champ correspondant dans l’ensemble de données SELECT. L’exception est l’expression * (‘asterisk’), qui renvoie tous les champs de la relation.

Syntaxe
SELECT
  [...]
  [DISTINCT | ALL] <select-list>
  [...]
  FROM ...

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    <qualifier>.*
  | <value-expression> [COLLATE collation] [[AS] alias]



<value-expression> ::=
    [<qualifier>.]col_name
  | [<qualifier>.]selectable_SP_outparm
  | <literal>
  | <context-variable>
  | <function-call>
  | <single-value-subselect>
  | <CASE-construct>
  | <other-single-value-expr>
Table 1. Paramètres de la liste des champs de l’instruction SELECT
Paramètre Description

qualifier

Nom de la table (vue) ou alias d’une table (vue, procédure stockée, table dérivée).

collation

Nom de tri existant (uniquement pour les colonnes de type caractère).

alias

Alias du champ.

col_name

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

selectable-SP-outparm

Le paramètre de sortie d’une procédure stockée sélective.

literal

Litéral

context-variable

La variable de contexte.

function-call

Appeler une fonction scalaire, agrégat ou window.

single-value-subselect

Une sous-requête qui renvoie une seule valeur scalaire.

CASE-construct

Design CASE.

other-single-value-expr

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

Une bonne pratique consiste à spécifier le nom du champ (ou *) avec le nom de la table/vue/procédure de stockage (ou son alias) à laquelle le champ appartient. Par exemple, relationname.columnname, relationname.*, alias.columnname, alias.*. La spécification du nom devient obligatoire si un champ portant le même nom se trouve dans plus d’une relation participant à l’union. La spécification de * est toujours obligatoire si ce n’est pas le seul élément de la liste de colonnes.

Important
Note

Les alias (alias) remplacent le nom original d’une table, d’une vue ou d’une procédure stockée : une fois qu’un alias est défini pour la relation correspondante, le nom original ne peut plus être utilisé.

Les mots-clés DISTINCT ou ALL peuvent être ajoutés au début de la liste des champs :

  • DISTINCT supprime les lignes en double : c’est-à-dire que si deux entrées ou plus contiennent les mêmes valeurs dans tous les champs pertinents, une seule de ces lignes sera incluse dans le jeu de données résultant.

  • ALL inclut toutes les lignes dans le jeu de données résultant. ALL est activé par défaut et donc rarement utilisé : une référence explicite est maintenue pour la compatibilité avec la norme SQL.

La clause COLLATE ne modifie pas le contenu d’un champ ; cependant, le fait de spécifier COLLATE pour un champ particulier peut modifier la sensibilité à la casse ou aux accents des caractères, ce qui peut à son tour affecter :

  • Ordre de tri, au cas où ce champ est spécifié dans une expression ORDER BY ;

  • Le regroupement, au cas où ce champ est spécifié dans la clause GROUP BY ;

  • Le nombre de lignes retournées si DISTINCT est utilisé.

Exemples d’instructions SELECT avec différents types de champs

Un simple SELECT utilisant uniquement les noms des champs :

SELECT cust_id, cust_name, phone
FROM customers
WHERE city = 'London'

Requête avec concaténation et appel de fonction dans la liste des champs :

SELECT
  'Mr./Mrs. ' || lastname,
  street,
  zip,
  upper(city)
FROM contacts
WHERE date_last_purchase(id) = current_date

Une requête avec deux sous-requêtes :

SELECT
  p.fullname,
  (SELECT name FROM classes c
   WHERE c.id = p.class) AS class,
  (SELECT name FROM mentors m
   WHERE m.id = p.mentor) AS mentor
FROM pupils p

La requête suivante fait la même chose que la précédente, mais en utilisant des jointures (JOIN) au lieu de sous-requêtes :

SELECT
  p.fullname,
  c.name AS class,
  m.name AS mentor
FROM pupils p
  JOIN classes c ON c.id = p.class
  JOIN mentors m ON m.id = p.mentor

Cette requête utilise une construction CASE pour déterminer le traitement correct, par exemple, lors de l’envoi de messages à une personne spécifique :

SELECT
  CASE upper(sex)
    WHEN 'F' THEN 'Mrs.'
    WHEN 'M' THEN 'Mr.'
    ELSE ''
  END AS title,
  lastname,
  address
FROM employees

Interrogation à l’aide d’une fonction de fenêtre, qui permet d’obtenir les employés classés par ordre de salaire.

SELECT
    id,
    salary,
    name,
    DENSE_RANK() OVER(ORDER BY salary) AS EMP_RANK
FROM employees
ORDER BY salary;

Interroger une procédure stockée :

SELECT *
FROM interesting_transactions(2010, 3, 'S')
ORDER BY amount

Sélection de champs dans une table dérivée : une table dérivée est une instruction SELECT entre crochets dont le résultat est utilisé dans la requête ci-dessus comme s’il s’agissait d’une table ou d’une vue normale.

SELECT
  fieldcount,
  COUNT(relation) AS num_tables
FROM
  (SELECT
     r.rdb$relation_name AS relation,
     COUNT(*) AS fieldcount
   FROM rdb$relations r
     JOIN rdb$relation_fields rf
       ON rf.rdb$relation_name = r.rdb$relation_name
   GROUP BY relation)
GROUP BY fieldcount

Interroger la variable contextuelle CURRENT_TIME:

SELECT current_time FROM rdb$database

Pour ceux qui ne sont pas familiers avec RDB$DATABASE : c’est une table système qui existe toujours dans toutes les bases de données Firebird, et qui contient toujours une seule ligne.Et bien que cette table n’ait pas été créée spécifiquement dans ce but, il est devenu courant pour les développeurs de Firebird d’interroger cette table si vous voulez exécuter une requête qui n’est liée à aucune table, dans laquelle le résultat est dérivé d’expressions spécifiées dans la liste de champs de l’instruction SELECT.

Par exemple :

SELECT
  power(12, 2) AS twelve_squared,
  power(12, 3) AS twelve_cubed
FROM rdb$database

Enfin, un exemple de requête à la table RDB$DATABASE elle-même, qui peut être utilisée pour obtenir l’encodage par défaut de cette base de données :

SELECT rdb$character_set_name FROM rdb$database
Voir aussi :

Fonctions agrégées,Fonctions window (analytique),Variables de contexte,CASE,Sous-requêtes.

FROM

L’expression FROM spécifie les sources à partir desquelles les données seront échantillonnées. Dans sa forme la plus simple, il peut s’agir d’une table unique ou d’une vue. Cependant, les sources peuvent également être une procédure stockée, une table dérivée ou une expression de table commune (CTE). Différents types de sources peuvent être combinés en utilisant une variété de jointures (JOINs).

Cette section traite de l’interrogation d’une source unique. Les jointures sont abordées dans la section suivante.

Syntaxe:
SELECT
...
FROM <table-reference> [, <table-reference> ...]
[...]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

<derived-table> ::= (<query-expression>)

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]
Table 1. Paramètres d’une expression FROM
Paramètre Description

table-name

Le nom de la table ou de la vue.

query-name

Nom du CTE.

package-name

Nom du paquet.

procedure-name

Le nom de la procédure stockée sélective.

procedure-args

Arguments d’une procédure stockée sélective.

derived-table

Table dérivé.

correlation-name

Un alias d’une source de données (table, vue, procédure stockée, CTE ou table dérivée).

column-name

L’alias de la colonne de la table dérivée.

select-statement

Requête SELECT aléatoire.

Sélection dans un tableau ou une vue

Lors de l’extraction d’une table ou d’une vue, la clause FROM ne requiert rien de plus que son nom. Un alias peut être utile ou même nécessaire lors de l’utilisation de sous-requêtes qui sont corrélées avec la requête principale (généralement les sous-requêtes sont corrélées).

Exemples
SELECT id, name, sex, age
FROM actors
WHERE state = 'Ohio'

SELECT *
FROM birds
WHERE type = 'flightless'
ORDER BY family, genus, species

SELECT
  firstname,
  middlename,
  lastname,
  date_of_birth,
  (SELECT name FROM schools s WHERE p.school = s.id) schoolname
FROM pupils p
WHERE year_started = 2012
ORDER BY schoolname, date_of_birth
Important

Si vous donnez un alias à une table ou à une vue, vous devez utiliser cet alias partout, et non le nom de la table, lorsque vous faites référence aux noms des colonnes.

Utilisation correcte :

SELECT PEARS
FROM FRUIT

SELECT FRUIT.PEARS
FROM FRUIT

SELECT PEARS
FROM FRUIT F

SELECT F.PEARS
FROM FRUIT F

Utilisation incorrecte :

SELECT FRUIT.PEARS
FROM FRUIT F

Sélection à partir d’une procédure stockée sélective

Une procédure stockée sélective (c’est-à-dire sélectionnable) doit satisfaire aux conditions suivantes :

  • Il doit contenir au moins un paramètre de sortie ; * Il doit utiliser le mot-clé SUSPEND afin que l’appelant puisse sélectionner les lignes de sortie une par une, tout comme les lignes d’une table ou d’une vue sont sélectionnées.

Les paramètres de sortie d’une procédure stockée sélective en termes de commande SELECT correspondent aux champs d’une table normale.

La sélection dans une procédure stockée sans paramètres d’entrée se fait de la même manière que la sélection dans une table :

SELECT *
FROM suspicious_transactions
WHERE assignee = 'Dmitrii'

Si la procédure stockée nécessite des paramètres d’entrée, ceux-ci doivent être spécifiés entre parenthèses après le nom de la procédure :

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, '22:30')
WHERE alt >= 20
ORDER BY az, alt

Les valeurs des paramètres facultatifs (c’est-à-dire les paramètres pour lesquels des valeurs par défaut sont définies) peuvent être spécifiées ou omises.

Toutefois, si les paramètres sont partiellement spécifiés, les paramètres omis doivent se trouver à la fin de l’énumération, à l’intérieur des parenthèses.

En supposant que la procédure visible_stars de l’exemple précédent possède deux paramètres optionnels spectral_class (varchar(12)) et min_magn (numeric(3,1)), les commandes suivantes seront correctes :

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, '22:30')

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, '22:30', 4.0)

Cette demande, en revanche, ne serait pas correcte :

SELECT name, az, alt
FROM visible_stars('Brugge', current_date, 4.0)

L’alias de la procédure stockée sélective est spécifié après la liste des paramètres :

SELECT
  number,
  (SELECT name FROM contestants c
   WHERE c.number = gw.number)
FROM get_winners('#34517', 'AMS') gw

Si vous spécifiez un champ (paramètre de sortie) avec le nom complet de la procédure, n’incluez pas la liste des paramètres de la procédure dans ce nom :

SELECT number,
  (SELECT name FROM contestants c
   WHERE c.number = get_winners.number)
FROM get_winners('#34517', 'AMS')
Voir aussi :

Procédures stockées,CREATE PROCEDURE.

Échantillonnage à partir d’une table dérivée (derived table)

Table dérivé — est la commande SELECT correcte, entre parenthèses, éventuellement marquée d’un alias de table et d’alias de champ.

Syntaxe
<derived table> ::=
  (<select-query>)
  [[AS] derived-table-alias]
  [(<derived-column-aliases>)]

<derived-column-aliases> := column-alias [, column-alias ...]

<lateral-derived-table> ::= LATERAL <derived-table>

L’ensemble de données retourné par une telle déclaration est une table virtuelle dans laquelle on peut effectuer des requêtes comme s’il s’agissait d’une table ordinaire.

La table dérivée dans la requête ci-dessous fournit une liste de noms de tables dans la base de données et le nombre de colonnes qu’elles contiennent. La requête de table dérivée fournit le nombre de champs et le nombre de tables avec ce nombre de champs.

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
          ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
      GROUP BY RELATION)
GROUP BY FIELDCOUNT

Un exemple trivial démontrant l’utilisation d’un alias de table dérivé et d’une liste d’alias de colonne (tous deux facultatifs) :

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)
Note
Notes:

  • Les tables dérivées peuvent être imbriquées ;

  • Les tables dérivées peuvent être jointes et utilisées dans les jointures. Ils peuvent contenir des fonctions d’agrégation, des sous-requêtes et des jointures, et peuvent eux-mêmes être utilisés dans des fonctions d’agrégation, des sous-requêtes et des jointures. Il peut également s’agir de procédures stockées ou de requêtes à partir de celles-ci. Ils peuvent comporter des clauses du type "OR", "ORDRE BY" et "GROUPE BY", des instructions du type "FIRST", "SKIPE" , etc ;

  • Chaque colonne d’une table dérivée doit avoir un nom. Si ce n’est pas le cas par nature (par exemple parce qu’il s’agit d’une constante), vous devez alors aliaser ou ajouter une liste d’alias de colonnes à la spécification de la table dérivée de la manière habituelle ;

  • La liste des alias de colonnes est facultative, mais si elle est présente, elle doit être complète (c’est-à-dire qu’elle doit contenir un alias pour chaque colonne de la table dérivée) ;

  • L’optimiseur peut gérer les tables dérivées très efficacement. Toutefois, si la table dérivée est incluse dans une jointure interne et contient une sous-requête, aucun ordre de jointure ne peut être utilisé par l’optimiseur ;

  • Le mot-clé LATERAL permet à une table dérivée de faire référence à des champs de tables précédemment listées dans la liste de référence courante <table.Voir Linking with LATERAL derived tables pour plus de détails.

Voici un exemple de la manière dont l’utilisation de tableaux dérivés peut simplifier la solution d’un problème.

Supposons que nous ayons un tableau COEFFS contenant les coefficients d’une série d’équations quadratiques que nous allons résoudre. Il peut être défini comme suit :

CREATE TABLE coeffs (
  a DOUBLE PRECISION NOT NULL,
  b DOUBLE PRECISION NOT NULL,
  c DOUBLE PRECISION NOT NULL,
  CONSTRAINT chk_a_not_zero CHECK (a <> 0)
)

En fonction des valeurs des coefficients a, b et c, chaque équation peut avoir zéro, une ou deux solutions. Nous pouvons trouver ces solutions en utilisant une requête à un niveau dans la table COEFFS, mais le code d’une telle requête serait lourd et certaines valeurs (comme les discriminants) seraient calculées plusieurs fois dans chaque ligne.

Si nous utilisons un tableau dérivé, la requête peut être rendue beaucoup plus élégante :

SELECT
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM
  (SELECT b, b*b - 4*a*c, 2*a FROM coeffs) (b, D, denom)

Si nous voulons montrer les coefficients à côté des solutions des équations, nous pouvons modifier la requête comme suit :

SELECT
  a, b, c,
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) sol_2
FROM
  (SELECT a, b, c, b*b - 4*a*c AS D, 2*a AS denom
   FROM coeffs)

Notez que dans la première requête, nous avons attribué des alias à tous les champs de la table dérivée sous forme de liste après la table, et que dans la seconde, nous ajoutons des alias dans la requête de la table dérivée selon les besoins. Ces deux méthodes sont correctes, car elles garantissent que chaque champ de la table dérivée a un nom unique lorsqu’elles sont appliquées correctement.

Note

En fait, toutes les colonnes calculées dans la table dérivée seront recalculées autant de fois qu’elles sont spécifiées dans la requête principale. Cela peut conduire à des résultats inattendus lors de l’utilisation de fonctions non déterministes. L’exemple suivant montre ce qui a été dit :

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) T;

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3                              EB176C10-F754-4689-8B84-64B666381154

Vous pouvez utiliser la méthode suivante pour matérialiser le résultat de la fonction GEN_UUID :

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1=0) T;

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3                              80AAECED-65CD-4C2F-90AB-5D548C3C7279

ou envelopper la fonction GEN_UUID dans une sous-requête

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT
          (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

Il s’agit d’une caractéristique de l’implémentation actuelle qui peut être modifiée dans les futures versions du serveur.

Tableaux de dérivés latéraux

Une table dérivée définie avec le mot-clé LATERAL est appelée table dérivée latérale. Si une table dérivée est définie comme latérale, elle est autorisée à faire référence à d’autres tables dans la même clause FROM, mais seulement à celles qui ont été déclarées avant dans la clause FROM.

Example 1. Requêtes avec des tables dérivées latéralement
select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c,
LATERAL (select first 1 city_name, population
         from cities
         where cities.country_name = c.country_name
         order by population desc) AS dt;
select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
LATERAL ( select max(amount) as amount from all_sales
          where all_sales.salesperson_id = salespeople.id
         ) as max_sale,
LATERAL ( select customer_name from all_sales
          where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
        ) as customer_of_max_sale;

Échantillonnage à partir d’expressions de tables communes (CTE)

Les expressions de tables communes sont une variante plus complexe et plus puissante des tables dérivées. Les CTE sont constituées d’un préambule commençant par le mot clé WITH qui définit une ou plusieurs expressions de tables communes (chacune d’entre elles peut avoir une liste d’alias de champs). La requête principale, qui suit le préambule, peut faire référence aux CTE comme s’il s’agissait de tableaux réguliers. Les CTE sont disponibles pour toute partie de la requête située en dessous de leur point de déclaration.

Les CTE sont décrits en détail dans CTE Common table expressions (WITH …​ AS …​ SELECT), et voici juste quelques utilisations en exemples.

La requête suivante présente notre exemple avec une variante de table dérivée pour les expressions de table génériques :

WITH vars (b, D, denom) AS (
  SELECT b, b*b - 4*a*c, 2*a
  FROM coeffs
)
SELECT
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM vars

Ce n’est pas une grande amélioration par rapport à l’option des tableaux dérivés (sauf que les calculs sont effectués avant la requête principale). Nous pouvons encore améliorer la requête en éliminant le double calcul sqrt(D) pour chaque ligne :

WITH vars (b, D, denom) AS (
  SELECT b, b*b - 4*a*c, 2*a
  FROM coeffs
),
vars2 (b, D, denom, sqrtD) AS (
  SELECT
    b, D, denom,
    IIF (D >= 0, sqrt(D), NULL)
  FROM vars
)
SELECT
  IIF (D >= 0, (-b - sqrtD) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrtD) / denom, NULL) AS sol_2
FROM vars2

Le texte de la requête semble plus complexe, mais il est devenu plus efficace (en supposant que l’exécution de la fonction SQRT prend plus de temps que le passage des valeurs des variables b, d et denom par un CTE supplémentaire).

Note

En fait, toutes les colonnes calculées dans le CTE seront recalculées autant de fois qu’elles sont spécifiées dans la requête principale. Cela peut conduire à des résultats inattendus lors de l’utilisation de fonctions non déterministes. L’exemple suivant montre ce qu’il en est :

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3                              EB176C10-F754-4689-8B84-64B666381154

Vous pouvez utiliser la méthode suivante pour matérialiser le résultat de la fonction GEN_UUID :

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE
    UNION ALL
    SELECT NULL FROM RDB$DATABASE WHERE 1=0)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T;

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3                              80AAECED-65CD-4C2F-90AB-5D548C3C7279

ou envelopper la fonction GEN_UUID dans une sous-requête

WITH T(X)
AS (SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T;

Il s’agit d’une caractéristique de l’implémentation actuelle qui peut être modifiée dans les futures versions du serveur.

Bien entendu, nous pourrions également obtenir ce résultat en utilisant des tableaux dérivés, mais cela nécessiterait d’imbriquer les requêtes les unes dans les autres.

Voir aussi :

Expressions de table communes CTE (WITH …​ AS …​ SELECT).

Connexions JOIN

Une jointure est effectuée pour chaque ligne et implique généralement la vérification de la condition de jointure pour déterminer quelles lignes doivent être jointes et se retrouver dans le jeu de données résultant.

Le résultat d’une jointure peut également être joint à un autre ensemble de données en utilisant la jointure suivante.

Il existe plusieurs types (INNER, OUTER) et classes (qualifiées, naturelles, etc.) de jointures, chacune ayant sa propre syntaxe et ses propres règles.

Syntaxe
SELECT
...
FROM <table-reference> [, <table-reference> ...]
[...]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

<derived-table> ::= (<query-expression>)

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table>)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join> :: =
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  {   ON <search-condition>
    | USING (<column-name-list>) }
Table 1. Paramètres de JOIN
Paramètre Description

table-name

Le nom de la table ou de la vue.

query-name

Nom du CTE.

package-name

Nom du paquet.

procedure-name

Le nom de la procédure stockée sélective.

procedure-args

Arguments d’une procédure stockée sélective.

derived-table

Table dérivée

correlation-name

Un alias d’une source de données (table, vue, procédure stockée, CTE ou table dérivée).

column-name

Nom ou alias d’une colonne de source de données (table, vue, procédure stockée, CTE ou table dérivée).

select-statement

Requête de la demande.

search-condition

Condition de connexion.

column-name-list

Une liste d’alias de colonnes de la table dérivée ou une liste de colonnes équivalentes.

Connexions internes (`INNER') et externes (`OUTER')

Une jointure réunit toujours les lignes de deux ensembles de données (généralement appelés "gauche" et "droite"). Par défaut, seules les lignes qui satisfont la condition de jointure (c’est-à-dire qui correspondent à au moins une ligne de l’autre ensemble de lignes selon la condition appliquée) sont incluses dans l’ensemble de données résultant.Ce type de jointure est appelé jointure interne (INNER JOIN). Comme une jointure interne est un type de jointure silencieuse, le mot-clé INNER peut être omis.

Traduit avec www.DeepL.com/Translator (version gratuite)

Supposons que nous ayons deux tables :

Table A
ID S

87

Just some text

35

Silence
Table B
CODE X

-23

56.7735

87

416.0

Si nous joignons ces tables en utilisant cette requête

SELECT *
FROM A
JOIN B ON A.id = B.code

le résultat sera :

ID S CODE X

87

Just some text

87

416.0

Autrement dit, la première ligne de la table A a été jointe à la deuxième ligne de la table B parce qu’elles satisfont ensemble à la condition de jointure “`A.id = B.code`”. Les autres lignes ne correspondent pas et ne sont donc pas incluses dans la connexion. N’oubliez pas que la connexion par défaut est toujours interne (INNER).

Nous pouvons rendre cela explicite en spécifiant le type de connexion :

SELECT *
FROM A
INNER JOIN B ON A.id = B.code

mais généralement le mot "INNER" est omis.

Bien entendu, il peut y avoir des cas où une ligne de l’ensemble de données de gauche correspond à plusieurs lignes de l’ensemble de données de droite (ou vice versa).

Dans ce cas, toutes les combinaisons sont incluses dans l’ensemble de données résultant, et nous pouvons obtenir un résultat comme celui-ci :

ID S CODE X

87

Just some text

87

416.0

87

Just some text

87

-1.0

-23

Don’t know

-23

56.7735

-23

Still don’t know

-23

56.7735

-23

I give up

-23

56.7735

Il est parfois nécessaire d’inclure tous les enregistrements de l’ensemble de données de gauche ou de droite dans le résultat, qu’il existe ou non un enregistrement correspondant dans l’ensemble de données apparié. Dans ce cas, il est nécessaire d’utiliser des connexions externes.

La connexion externe de gauche (LEFT OUTER) comprend tous les enregistrements de l’ensemble de données de gauche et les enregistrements de l’ensemble de données de droite qui satisfont à la condition de connexion.

La connexion externe de droite (RIGHT OUTER) comprend tous les enregistrements de l’ensemble de données de droite et les enregistrements de l’ensemble de données de gauche qui satisfont à la condition de connexion.

FULL OUTER inclut toutes les entrées des deux ensembles de données.

Dans toutes les connexions externes, les champs vides (c’est-à-dire les champs de l’ensemble de données qui n’ont pas d’enregistrement correspondant) sont remplis avec NULL.

Les mots clés LEFT, RIGHT ou FULL avec le mot clé optionnel OUTER sont utilisés pour désigner la connexion externe.

Considérez diverses connexions externes en utilisant les exemples de requêtes avec les tables A et B ci-dessus :

SELECT *
FROM A
LEFT OUTER JOIN B ON A.id = B.code

identique

SELECT *
FROM A
LEFT JOIN B ON A.id = B.code
ID S CODE X

87

Just some text

87

416.0

235

Silence

<null>

<null>

 
SELECT *
FROM A
RIGHT OUTER JOIN B ON A.id = B.code

identique

SELECT *
FROM A
RIGHT JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

 
SELECT *
FROM A
FULL OUTER JOIN B ON A.id = B.code

identique

SELECT *
FROM A
FULL JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

235

Silence

<null>

<null>

Des connexions qualifiées

La syntaxe de jointure qualifiée requiert la spécification d’une condition pour la jointure des enregistrements. Cette condition est spécifiée explicitement dans la clause ON ou implicitement avec la clause USING.

Syntaxe
<qualified-join> ::= [<join-type>] JOIN <source> <join-condition>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

<join-condition> ::= ON <condition> | USING (<column-list>)
Connexions avec des conditions explicites

Dans la syntaxe de jointure explicite, il y a une clause ON, avec une condition de jointure, qui peut contenir n’importe quelle expression logique, mais qui contient généralement une condition de comparaison entre les deux sources impliquées.

Très souvent, cette condition est un contrôle d’égalité (ou un ensemble de contrôles d’égalité réunis par l’instruction AND) utilisant l’instruction "=". De telles connexions sont appelées équi-connexions (les exemples du chapitre sur les connexions INNER et OUTER étaient des équi-connexions).

Exemples de connexions avec des conditions explicites :

/*
 * Un échantillon de tous les clients de la ville de Détroit qui ont
 * effectué un achat.
 */
SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Idem, mais inclut dans l'échantillon les clients qui
 * n'ont pas effectué d'achat.
 */
SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Pour que chaque femme choisisse un homme qui est plus petit qu'elle.
 * Les hommes qui ne correspondent pas,
 * ne seront pas inclus dans l'échantillon.
 */
SELECT
    m.fullname AS man,
    f.fullname AS woman
FROM males m
JOIN females f ON f.height > m.height

/*
 * Un échantillon de tous les élèves, de leur classe et de leur tuteur.
 * Les élèves sans mentor seront inclus dans l'échantillon.
 * Les élèves sans classe ne seront pas inclus dans l'échantillon.
 */
SELECT
    p.firstname,
    p.middlename,
    p.lastname,
    c.name,
    m.name
FROM pupils p
JOIN classes c ON c.id = p.class
LEFT JOIN mentors m ON m.id = p.mentor
Connexions par colonnes nommées

Les connexions équivalentes comparent souvent des colonnes qui ont le même nom dans les deux tables. Pour ces connexions, nous pouvons utiliser un deuxième type de connexion explicite, appelé Named Columns Joins. Les jointures de colonnes nommées sont réalisées à l’aide de la fonction USING, qui énumère uniquement les noms des colonnes.

Note

Les connexions avec des colonnes nommées ne sont disponibles que dans le dialecte 3.

Ainsi, l’exemple suivant :

SELECT *
FROM flotsam f
  JOIN jetsam j
    ON f.sea = j.sea AND f.ship = j.ship

peut être réécrit comme suit :

SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship)

qui est considérablement plus courte.Le jeu résultant est légèrement différent, du moins lorsqu’on utilise "SELECT *" :

  • Le jeu de résultats avec la condition de jointure explicite dans la clause ON contiendra chacune des colonnes SEA et `SHIP deux fois : une fois pour la table FLOTSAM et une fois pour la table JETSAM. Il est évident qu’ils les auront et qu’ils auront les mêmes valeurs ;

  • Le résultat d’une jointure de colonnes nommées, utilisant la clause USING, contiendra ces colonnes une fois.

Si vous voulez obtenir toutes les colonnes dans le résultat de la jointure de colonnes nommée, réécrivez la requête comme suit :

SELECT f.*, j.*
FROM flotsam f
JOIN jetsam j USING (sea, ship)

Pour les connexions externes (OUTER) avec des colonnes nommées, il existe des nuances supplémentaires lors de l’utilisation de “SELECT *” ou d’un nom de colonne incomplet.Si une colonne d’une ligne d’une source ne correspond pas à une colonne d’une ligne d’une autre source, mais que le résultat doit quand même être inclus à cause des instructions LEFT, RIGHT ou FULL, alors la colonne fusionnée aura une valeur non NULL.C’est assez juste, mais maintenant vous ne pouvez pas dire de quel ensemble gauche, droite ou les deux proviennent cette valeur.Ceci est particulièrement trompeur lorsque les valeurs proviennent du côté droit de l’ensemble de données, car “*” affiche toujours pour les colonnes combinées les valeurs du côté gauche de l’ensemble de données, même si la connexion RIGHT est utilisée.

Le fait que ce soit un problème dépend de la situation.Si c’est le cas, utilisez l’approche “f.*, j.*” démontrée ci-dessus, où f et j sont les noms ou alias des deux sources.Ou bien il est préférable d’éviter complètement les * dans les requêtes sérieuses et de lister tous les noms de colonnes pour les ensembles à joindre.Cette approche présente l’avantage supplémentaire de vous obliger à réfléchir aux données que vous souhaitez récupérer et à leur origine.

Il vous incombe de vous assurer que les types de colonnes sont compatibles entre les sources liées, dont les noms sont listés dans l’instruction USING.Si les types sont compatibles mais pas égaux, Firebird les convertira en un type avec une gamme de valeurs plus large avant de les comparer.Ce sera également le type de données de la colonne fusionnée qui apparaîtra dans le jeu résultant si “SELECT *” ou un nom de colonne incomplet est utilisé.Les noms de colonnes complets conserveront toujours leur type de données d’origine.

Tip

Si vous utilisez les colonnes de jonction dans la condition de sélection WHERE lorsque vous liez des colonnes nommées, utilisez toujours les noms de colonnes spécifiés, sinon l’index de cette colonne ne sera pas utilisé.

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE x = 0;
PLAN JOIN (A NATURAL, B INDEX (RDB$2))

mais

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE a.x = 0; -- ou 'b.x'
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE b.x = 0;
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

Le fait est que la colonne non spécifiée dans ce cas est implicitement remplacée par COALESCE(a.x, b.x). Cette astuce est utilisée pour éliminer l’ambiguïté dans les noms de colonnes, mais elle empêche également l’application de l’index.

Connexions naturels (NATURAL JOIN)

En prenant comme base les connexions de colonnes nommées, l’étape suivante est une connexion naturelle qui effectue une équipartition sur toutes les colonnes de table de droite et de gauche du même nom. Les types de données de ces colonnes doivent être compatibles.

Note

Les connexions naturels ne sont disponibles qu’en dialecte 3.
Syntaxe
<natural-join> ::= NATURAL [<join-type>] JOIN <source>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Deux tables sont donnés :

CREATE TABLE TA (
    a BIGINT,
    s VARCHAR(12),
    ins_date DATE
);

CREATE TABLE TB (
    a BIGINT,
    descr VARCHAR(12),
    x FLOAT,
    ins_date DATE
);

La connexion naturelle des tables TA et TB se fera par la colonne a et ins_date et les deux instructions suivants donneront le même résultat :

SELECT *
FROM TA
NATURAL JOIN TB;

SELECT *
FROM TA
JOIN TB USING (a, ins_date);

Comme toutes les connexions, les connexions naturelles sont des connexions internes par défaut, mais vous pouvez les transformer en connexions externes en spécifiant LEFT, RIGHT ou FULL avant le mot-clé JOIN.

Important
Attention

Si aucune colonne du même nom n’est trouvée dans les deux tables sources, `CROSS JOIN' sera exécuté.

Connexion transversale (CROSS JOIN)

Connexion croisée ou produit cartésien.Chaque ligne du tableau de gauche est reliée à chaque ligne du tableau de droite.

Syntaxe
<cross-join> ::=
    <table-reference> [, <table-reference> ...]
  | <table-reference> CROSS JOIN <table-primary>

Notez que la syntaxe utilisant une virgule est obsolète.

La jonction croisée de deux ensembles est équivalente à leur jonction par une condition de tautologie (une condition qui est toujours vraie).

Les deux requêtes suivantes donneront le même résultat :

SELECT *
FROM TA
CROSS JOIN TB;

SELECT *
FROM TA
JOIN TB ON 1 = 1;

Les jointures croisées sont des jointures internes car elles sélectionnent les lignes pour lesquelles il existe une correspondance - il se trouve que toutes les lignes correspondent ! Une jointure croisée externe, si elle existait, n’ajouterait rien au résultat, car les jointures externes ajoutent des enregistrements pour lesquels il n’y a pas de correspondance, et ils n’existent pas dans la jointure croisée.

Les liaisons transversales sont rarement utiles, sauf si vous souhaitez obtenir une liste de toutes les combinaisons possibles de deux variables ou plus.Supposons que vous vendiez un produit disponible en différentes tailles, différentes couleurs et différents matériaux.Si les valeurs de chaque variable sont répertoriées dans leur propre tableau, cette requête renverra toutes les combinaisons :

SELECT
    m.name,
    s.size,
    c.name
FROM materials m
CROSS JOIN sizes s
CROSS JOIN colors c
Connexions implicites

En SQL-89, les tables impliquées dans la jointure sont spécifiées par une liste séparée par des virgules dans la clause FROM. Les conditions de jointure sont spécifiées dans la clause WHERE parmi d’autres conditions de recherche. De telles jointures sont dites implicites.

Seules les connexions internes peuvent être réalisées avec la syntaxe de jointure implicite.

Un exemple de jointure implicite :

/*
 * Un échantillon de tous les clients de la ville de Détroit qui ont
 * ont effectué un achat.
 */
SELECT *
FROM customers c, sales s
WHERE s.cust_id = c.id AND c.city = 'Detroit'
Important

L’utilisation de la syntaxe de connexion implicite n’est actuellement pas recommandée.
Mélange de connexion explicite et implicite

Le mélange de connexions explicites et implicites n’est pas recommandé, mais est autorisé. Certains types de mélange ne sont pas autorisés dans Firebird.

Par exemple, une requête comme celle-ci provoquera une erreur "Column does not belong to referenced table"

SELECT *
FROM
TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Cela est dû au fait que le JOIN explicite ne peut pas voir la table TA. Cependant, la requête suivante s’exécutera sans erreur car l’isolation n’est pas rompue.

SELECT *
FROM
TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Noms de champs ambigus dans les sélections

Firebird rejette les noms de champs incomplets dans une requête si ces noms de champs existent dans plus d’un jeu de données impliqué dans l’union. Ceci est également vrai pour les équipoins internes où les noms de champs apparaissent dans la clause ON :

SELECT a, b, c
FROM TA
JOIN TB ON TA.a = TB.a

Il existe une exception à cette règle : les jointures de colonnes nommées et les jointures naturelles, qui utilisent un nom de champ incomplet dans le processus de correspondance, peuvent être utilisées légalement. Il en va de même pour les colonnes joignables homonymes. Pour les jointures de colonnes nommées, ces colonnes doivent être listées dans la clause USING. Pour les jointures naturelles, il s’agit de colonnes dont les noms sont présents dans les deux tables.Mais notez encore une fois que, surtout dans les jointures externes, le nom plat colname n’est pas toujours le même que left.colname ou right.colname. Les types de données peuvent être différents, et l’une des colonnes complètes peut avoir la valeur NULL, alors que l’autre ne l’a pas. Dans ce cas, la valeur de la colonne fusionnée et incomplète peut masquer le fait qu’une des valeurs originales est manquante.

Connexions aux procédures stockées

Si la connexion se fait à une procédure stockée qui n’est pas corrélée à d’autres flux de données via des paramètres d’entrée, il n’y a pas de particularités.

Sinon, il y a une particularité : les threads utilisés dans les paramètres d’entrée doivent être décrits avant la connexion à la procédure stockée :

SELECT *
FROM MY_TAB
JOIN MY_PROC(MY_TAB.F) ON 1 = 1

Une requête écrite comme suit provoquera une erreur

SELECT *
FROM MY_PROC(MY_TAB.F)
JOIN MY_TAB ON 1 = 1

Connexions aux tables dérivées `LATERAL'.

Une table dérivée définie avec le mot-clé LATERAL est appelée table dérivée latérale.Si une table dérivée est définie comme une table latérale, elle est autorisée à faire référence à d’autres tables dans la même clause FROM, mais seulement à celles déclarées plus tôt dans la clause FROM.Sans LATERAL, chaque sous-requête est exécutée indépendamment et ne peut donc pas faire référence à d’autres éléments FROM.

L’élément LATERAL peut se trouver au niveau supérieur de la liste FROM ou dans l’arbre JOIN.Dans ce dernier cas, il peut également être lié à tout élément situé à gauche du JOIN à droite duquel il se trouve.

Lorsqu’un élément FROM contient des références LATERAL, alors la requête est exécutée de la manière suivante : d’abord les valeurs de toutes les colonnes dont dépend la table dérivée avec le mot-clé LATERAL, puis la table dérivée elle-même avec LATERAL pour chaque enregistrement résultant est calculée.Les lignes obtenues à partir de la table dérivée avec LATERAL sont jointes avec les lignes dont elles sont dérivées.

Les jointures suivantes sont autorisées : CROSS JOIN et LEFT OUTER JOIN.Une jonction interne est également autorisée, mais n’est pas recommandée car des problèmes peuvent survenir lors du calcul de la condition de jonction des fils.

A titre d’exemple, imprimez les résultats des chevaux et leurs dernières mesures.Si un cheval n’a pas de mensurations, il ne sera pas sorti :

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
CROSS JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M

une autre façon d’écrire cette demande

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE,
     LATERAL(SELECT
               *
             FROM MEASURE
             WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
             ORDER BY MEASURE.BYDATE DESC
             FETCH FIRST ROW ONLY) M

Si vous voulez sortir les chevaux sans tenir compte du fait qu’ils ont au moins une mesure, vous devez remplacer CROSS JOIN par LEFT JOIN :

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
LEFT JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M ON TRUE

WHERE

La clause WHERE'' a pour but de limiter le nombre de chaînes retournées à celles qui nous intéressent.La condition qui suit le mot-clé WHERE peut être aussi simple que la vérification de “AMOUNT = 3``”, ou peut être une expression complexe et alambiquée contenant des sous-requêtes, des prédicats, des appels de fonction, des instructions mathématiques et logiques, des variables de contexte, etc.

La condition dans une clause WHERE est souvent appelée une condition de recherche, une expression de recherche, ou simplement une recherche.

Dans DSQL et ESQL, une expression de recherche peut contenir des paramètres.Ceci est utile si la requête doit être répétée plusieurs fois avec des valeurs différentes des paramètres d’entrée.Dans la chaîne de requête SQL envoyée au serveur, les points d’interrogation sont utilisés comme caractères de remplacement pour les paramètres.Ils sont appelés paramètres positionnels car ils ne peuvent rien dire d’autre que la position dans la chaîne.Les bibliothèques d’accès prennent souvent en charge des paramètres nommés tels que :id, :amount, :a etc.C’est plus convivial, la bibliothèque se charge de traduire les paramètres nommés en paramètres de position avant de transmettre la requête au serveur.

Une condition de recherche peut également contenir des noms de variables locales (PSQL) ou hôtes (ESQL), précédés de deux points.

Syntaxe
SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]
Table 1. Paramètres de la clause WHERE
Paramètre Description

search-condition

Une expression logique retournant TRUE, FALSE et éventuellement UNKNOWN (NULL).

Seules les chaînes pour lesquelles la condition de recherche est vraie seront incluses dans le jeu de résultats. Faites attention aux valeurs NULL qui peuvent en résulter : si vous niez une expression donnant NULL avec NOT, le résultat d’une telle expression est toujours NULL et la chaîne ne passera pas. Ceci est démontré dans un des exemples ci-dessous.

Exemples
SELECT genus, species
FROM mammals
WHERE family = 'Felidae'
ORDER BY genus;

SELECT *
FROM persons
WHERE birthyear IN (1880, 1881)
   OR birthyear BETWEEN 1891 AND 1898;

SELECT name, street, borough, phone
FROM schools s
WHERE EXISTS (SELECT * FROM pupils p WHERE p.school = s.id)
ORDER BY borough, street;

SELECT *
FROM employees
WHERE salary >= 10000 AND position <> 'Manager';

SELECT name
FROM wrestlers
WHERE region = 'Europe'
  AND weight > ALL (SELECT weight FROM shot_putters
                    WHERE region = 'Africa');

SELECT id, name
FROM players
WHERE team_id = (SELECT id FROM teams
                 WHERE name = 'Buffaloes');

SELECT SUM (population)
FROM towns
WHERE name LIKE '%dam'
  AND province CONTAINING 'land';

SELECT pass
FROM usertable
WHERE username = current_user;

L’exemple suivant montre ce qui peut se passer si la condition de recherche est calculée comme NULL.

Supposons que vous ayez un tableau avec plusieurs noms d’enfants et le nombre de billes qu’ils possèdent.

CHILD MARBLES

Anita

23

Bob E.

12

Chris

<null>

Deirdre

1

Eve

17

Fritz

0

Gerry

21

Hadassah

<null>

Isaac

6

Tout d’abord, notez la différence entre NULL et 0. On sait que Fritz n’a aucune bille, mais Chris et Hadassah ont un nombre inconnu de billes.

Maintenant, si vous entrez cette instruction SQL :

SELECT LIST(child) FROM marbletable WHERE marbles > 10

vous obtiendrez les noms Anita, Bob E., Eve et Gerry. Tous ces enfants ont plus de 10 billes.

Si vous niez l’expression :

SELECT LIST(child) FROM marbletable WHERE NOT marbles > 10

retournera Deirdre, Fritz et Isaac. Chris et Hadassah ne seront pas inclus dans l’échantillon, car on ne sait pas s’ils ont 10 billes ou moins. Si vous modifiez la dernière requête comme suit

SELECT LIST(child) FROM  marbletable WHERE marbles <= 100

le résultat est le même, car l’expression NULL <= 10 donne UNKNOWN. Ce n’est pas la même chose que TRUE, donc Chris et Hadassah ne sont pas affichés. Si vous voulez que tous les enfants "pauvres" soient listés, modifiez la requête comme suit

SELECT LIST(child)
FROM marbletable
WHERE marbles <= 10 OR marbles IS NULL

Maintenant, la condition de recherche devient vraie pour Chris et Hadassah, parce que la condition "marbles is null retourne TRUE` dans ce cas.En fait, la condition de recherche ne peut être NULL pour aucun d’entre eux.

Enfin, les deux exemples suivants de requêtes SELECT avec des paramètres dans la condition de recherche.La manière dont les paramètres de la requête sont définis et la possibilité de le faire dépendent de l’application.Notez que les requêtes de ce type ne peuvent pas être exécutées immédiatement, elles doivent être préparées à l’avance.Une fois qu’une requête paramétrée a été préparée, l’utilisateur (ou le code appelant) peut substituer des valeurs de paramètres et l’exécuter à plusieurs reprises, en substituant de nouvelles valeurs de paramètres avant chaque appel.La manière dont les valeurs des paramètres sont saisies et le fait qu’elles soient ou non prétraitées dépendent de l’application.Dans les environnements GUI, l’utilisateur saisit généralement les valeurs des paramètres dans une ou plusieurs zones de texte et clique sur le bouton "Exécuter", "Run" ou "Refresh".

SELECT name, address, phone
FROM stores
WHERE city = ? AND class = ?

SELECT *
FROM pants
WHERE model = :model AND size = :size AND color = :col

La dernière requête ne peut pas être envoyée directement au moteur du serveur, l’application doit la convertir dans un autre format, en faisant correspondre les paramètres nommés aux paramètres de position.

GROUP BY

Une clause GROUP BY joint les enregistrements qui ont la même combinaison de valeurs de champs spécifiés dans sa liste en un seul enregistrement.Les fonctions d’agrégation dans la liste de sélection s’appliquent à chaque groupe individuellement, et non à l’ensemble.

Si la liste de sélection ne contient que des colonnes agrégées ou des colonnes dont les valeurs ne dépendent pas des lignes individuelles du jeu principal, la clause GROUP BY est facultative.Lorsque la clause GROUP BY est omise, le jeu de résultats se compose d’une seule ligne (à condition qu’au moins une colonne d’agrégation soit présente).

Si la liste de sélection contient à la fois des colonnes agrégées et des colonnes dont les valeurs dépendent des lignes sélectionnées, la clause GROUP BY devient obligatoire.

Syntaxe
SELECT ...
FROM ...
GROUP BY <grouping-item> [, <grouping-item> ...]
[HAVING <grouped-row-condition>] ...

<grouping-item> ::= <non-aggr-select-item> | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position
Table 1. Paramètres de la clause GROUP BY
Paramètre Description

non-aggr-expression

Toute expression non agrégée qui n’est pas incluse dans la liste d’échantillons, c’est-à-dire les colonnes non sélectionnées de l’ensemble source ou les expressions qui ne dépendent pas du tout de l’ensemble de données.

column-copy

Copie mot à mot d’une expression d’une liste de sélection qui ne contient pas de fonction d’agrégation.

column-alias

Alias d’une expression (colonne) d’une liste de sélection qui ne contient pas de fonction d’agrégation.

column-position

Le numéro de position d’une expression (colonne) d’une liste de sélection qui ne contient pas de fonction d’agrégation.

La règle générale est que chaque colonne non agrégée dans une liste SELECT doit également être incluse dans une liste GROUP BY.Vous pouvez le faire de trois façons :

  1. Copiez l’expression textuelle de la liste de sélection, par exemple “class” ou “'D:' || upper(doccode)” ;

  2. Spécifiez un alias s’il en existe un ;

  3. Spécifiez la position de la colonne sous forme d’un nombre entier compris entre 1 et le nombre de colonnes de la liste SELECT. Les valeurs entières dérivées d’expressions, de paramètres ou simplement d’invariants seront utilisées comme telles dans le regroupement. Ils n’auront aucun effet car leur valeur est la même pour chaque ligne.

Important

Si vous regroupez par position de colonne ou par alias, l’expression correspondant à cette position (alias) sera copiée de la liste de sélection SELECT. Ceci s’applique aussi aux sous-requêtes, donc la sous-requête sera exécutée au moins deux fois.

En plus des éléments requis, la liste de regroupement peut également contenir :

  • Les colonnes de la table source qui ne sont pas incluses dans la liste de sélection SELECT, ou les expressions non agrégées basées sur de telles colonnes. L’ajout de telles colonnes peut encore fragmenter les groupes. Mais comme ces colonnes ne figurent pas dans la liste de sélection SELECT, vous ne pouvez pas savoir quelle valeur de colonne correspond à quelle valeur de ligne agrégée. Donc, si vous êtes intéressé par cette information, vous devez également inclure cette colonne ou expression dans la liste de sélection SELECT, ce qui vous ramène à la règle "chaque colonne non agrégée dans la liste de sélection SELECT doit être incluse dans la liste de regroupement GROUP BY ;

  • Expressions qui ne dépendent pas des données de l’ensemble principal, c’est-à-dire les constantes, les variables contextuelles, les sous-requêtes non corrélées qui renvoient une seule valeur, etc. Ceci n’est mentionné que pour compléter l’image, car l’ajout de ces éléments est complètement inutile, puisqu’ils n’affecteront pas du tout le regroupement. Les éléments "anodins mais inutiles" peuvent également apparaître dans la liste de sélection SELECT sans être copiés dans la liste de regroupement GROUP BY.

Exemples

Lorsque la liste de sélection SELECT ne contient que des colonnes agrégées, la clause GROUP BY est facultative :

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'

Cette requête renvoie une seule ligne avec le nombre d’étudiants masculins et leur âge moyen. L’ajout d’une expression indépendante des lignes de la table STUDENTS ne change rien :

SELECT COUNT(*), AVG(age), current_date
FROM students
WHERE sex = 'M'

La ligne de résultat comportera désormais une colonne supplémentaire indiquant la date du jour, mais à part cela, rien de fondamental n’a changé. Le regroupement n’est toujours pas nécessaire.

Cependant, dans les deux exemples ci-dessus, cela est autorisé, ce qui est parfaitement valable pour la requête également :

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

et retournera le résultat pour chaque classe qui a des garçons, en listant le nombre de garçons et leur âge moyen dans cette classe particulière. Si vous laissez également le champ CURRENT_DATE, cette valeur sera répétée sur chaque ligne, ce qui n’est pas intéressant.

Cette requête a un inconvénient important, bien qu’elle vous donne des informations sur les différentes classes, elle ne vous dit pas quelle ligne appartient à quelle classe. Pour obtenir cette information supplémentaire, la colonne non agrégée CLASS doit être ajoutée à la liste de sélection SELECT :

SELECT class, COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

Nous avons maintenant une requête utile. Notez que l’ajout de la colonne CLASS rend la clause GROUP BY obligatoire. Nous ne pouvons pas enlever cette clause, ni enlever la colonne CLASS de la liste des colonnes.

Le résultat de la dernière requête ressemblera à ceci :

CLASS COUNT AVG

2A

12

13.5

2B

9

13.9

3A

11

14.6

3B

12

14.4

…​

…​

…​

Les rubriques “COUNT” et “AVG” ne sont pas très informatives. Dans le cas le plus simple, vous pouvez contourner ce problème, mais il est préférable de leur donner des noms significatifs en utilisant des alias :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class

Comme vous vous souvenez de la syntaxe formelle de la liste des colonnes, le mot clé AS est facultatif.

L’ajout d’autres colonnes non agrégées (ou plus précisément dépendantes des chaînes de caractères) nécessite de les ajouter également aux clauses GROUP BY. Par exemple, vous voulez que les informations ci-dessus concernant les filles soient les mêmes, et vous voulez voir la différence entre les étudiants internes et les étudiants à plein temps :

SELECT
    class,
    sex,
    boarding_type,
    COUNT(*) AS anumber,
    AVG(age) AS avg_age
FROM students
GROUP BY class, sex, boarding_type
CLASS SEX BOARDING_TYPE ANUMBER AVG_AGE

2A

F

BOARDING

9

13.3

2A

F

DAY

6

13.5

2A

M

BOARDING

7

13.6

2A

M

DAY

5

13.4

2B

F

BOARDING

11

13.7

2B

F

DAY

5

13.7

2B

M

BOARDING

6

13.8

…​

…​

…​

…​

…​

Chaque ligne du jeu résultant correspond à une combinaison particulière des variables CLASS, SEX et BOARDING_TYPE. Les résultats agrégés — nombre et âge moyen — sont donnés pour chacun des groupes spécifiés séparément. Vous ne pouvez pas voir les résultats agrégés pour les garçons séparément ou pour les étudiants à temps plein séparément comme résultat de la requête.Il faut donc trouver un compromis. Plus vous ajoutez de colonnes non agrégées, plus vous instanciez les groupes, et plus vous perdez de vue la vue d’ensemble. Bien sûr, vous pouvez toujours obtenir des agrégats "plus grands", en utilisant des requêtes séparées.

HAVING

Comme la clause WHERE, la clause HAVING restreint les lignes du jeu de données à celles qui satisfont la condition de recherche, à la différence que la clause HAVING impose des restrictions sur les lignes agrégées du jeu groupé.La clause HAVING est facultative et ne peut être utilisée qu’en conjonction avec la clause GROUP BY.

La (les) condition(s) dans la clause 'HAVING' peuvent se référer à :

  • Toute colonne agrégée dans la liste de sélection SELECT. C’est le cas le plus couramment utilisé ;

  • Toute expression agrégée qui ne figure pas dans la liste de sélection SELECT, mais qui est autorisée dans le contexte de la requête. C’est parfois utile ;

  • Toute colonne de la liste GROUP BY. Cependant, il est plus efficace de filtrer les données non agrégées plus tôt dans la clause WHERE ;

  • Toute expression dont la valeur est indépendante du contenu de l’ensemble de données (telle qu’une constante ou une variable contextuelle). Cela est acceptable, mais n’a aucun sens, car une telle clause, qui n’a rien à voir avec l’ensemble de données lui-même, va soit supprimer l’ensemble entier, soit le laisser intact.

Une clause HAVING ne peut pas contenir :

  • Expressions de colonnes non agrégées qui ne sont pas dans la liste GROUP BY ;

  • Position de la colonne. Un nombre entier dans une clause HAVING est simplement un nombre entier ;

  • Les alias de colonnes — même s’ils apparaissent dans une clause GROUP BY.

Exemples

Reconstruire nos premiers exemples. Nous pouvons utiliser la clause "HAVING" pour exclure de petits groupes d’élèves :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING COUNT(*) >= 5

Ne choisissez que les groupes dont l’écart d’âge minimum est de 1,2 an :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING MAX(age) - MIN(age) > 1.2

Notez que si vous êtes vraiment intéressé par cette information, c’est une bonne idée d’inclure min(age) et max(age) ou l’expression max(age) - min(age) dans la liste de sélection.

La requête suivante ne sélectionne que les élèves de 3ème année :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING class STARTING WITH '3'

Cependant, il est bien mieux de déplacer cette condition dans la clause WHERE:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M' AND class STARTING WITH '3'
GROUP BY class

WINDOW

La clause WINDOW est destinée à spécifier des fenêtres nommées qui sont utilisées par window functions. Comme l’expression window peut être assez complexe, et utilisée de nombreuses fois, cette fonctionnalité peut être utile.

Syntaxe
<query spec> ::=
  SELECT
    [<first clause>] [<skip clause>]
    [<distinct clause>]
    <select list>
    <from clause>
    [<where clause>]
    [<group clause>]
    [<having clause>]
    [<named windows clause>]
    [<order clause>]
    [<rows clause>]
    [<offset clause>] [<limit clause>]
    [<plan clause>]

<named windows clause> ::=
  WINDOW <window definition> [, <window definition>] ...

<window definition> ::=
  window-name AS <window specification>

<window specification> ::=
   ([window-name] [<window partition>] [<window order>] [<window frame>])


<window partition> ::= PARTITION BY <expr> [, <expr> ...]

<window order> ::=
  ORDER BY <expr> [<direction>] [<nulls placement>]
        [, <expr> [<direction>] [<nulls placement>] ...]

<direction> ::= {ASC | DESC}

<nulls placement> ::= NULLS {FIRST | LAST}

<window frame> ::=
  {ROWS | RANGE} <window frame extent>

<window frame extent> ::=
  <window frame preceding> | <window frame between>

<window frame preceding> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

<window frame between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <expr> PRECEDING | <expr> FOLLOWING | CURRENT ROW }
      AND { UNBOUNDED FOLLOWING | <expr> PRECEDING | <expr> FOLLOWING | CURRENT ROW }

Le nom d’une window peut être utilisé dans une clause OVER pour faire référence à une définition de window, et il peut également être utilisé comme window de base pour une autre window nommée ou intégrée (dans une clause OVER). Les window encadrées (avec les clause RANGE et ROWS) ne peuvent pas être utilisées comme window de base (mais peuvent être utilisées dans une clause OVER _nom_de_la_window). Une window qui utilise une référence à une window de base ne peut pas avoir de clause PARTITION BY et ne peut pas remplacer le tri par une clause ORDER BY.

Exemples
Example 1. Utilisation de window nommées
SELECT
    id,
    department,
    salary,
    count(*) OVER w1,
    first_value(salary) OVER w2,
    last_value(salary) OVER w2,
    sum(salary) over (w2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s
FROM employee
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY salary)
ORDER BY department, salary;
Voir aussi :

Fonctions de window (analytique).

PLAN

La proposition PLAN permet à l’utilisateur de spécifier son propre plan d’exécution de la requête, en remplaçant le plan que l’optimiseur a généré automatiquement.

Syntaxe
PLAN <plan-expr>

<plan-expr> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
  | <hash-item>

<sorted-item> ::= SORT (<plan-item>)

<joined-item> ::= JOIN (<plan-item>, <plan-item> [, <plan-item> ...])

<merged-item> ::=
  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])

<hash-item> ::= HASH (<plan-item>, <plan-item> [, <plan-item> ...])

<plan-item> ::= <basic-item> | <plan-expr>

<basic-item> ::= <relation> {
    NATURAL
  | INDEX (<indexlist>)
  | ORDER index [INDEX (<indexlist>)]
}

<relation> ::= table | view [table]

<indexlist> ::= index [, index ...]
Table 1. Paramètres de la clause PLAN
Paramètre Description

table

Le nom de la table ou son alias.

view

Le nom de la vue.

index

Nom de l’index.

Chaque fois qu’un utilisateur envoie une requête au noyau Firebird, l’optimiseur calcule une stratégie d’extraction de données. La plupart des clients Firebird ont la possibilité d’afficher un plan d’extraction de données à l’utilisateur. Dans l’outil natif isql, ceci est fait avec la commande SET PLAN ON.Si vous souhaitez uniquement examiner le plan de requête sans l’exécuter, vous devez entrer la commande SET PLANONLY ON, qui récupérera les plans de requête sans les exécuter. Pour ramener isql en mode d’exécution de requête, entrez la commande SET PLANONLY OFF.

Note

Un plan plus détaillé peut être obtenu en activant le plan avancé. Dans isql, cela se fait avec la commande SET EXPLAIN ON. Ce plan fournit des informations plus détaillées sur les méthodes d’accès utilisées par l’optimiseur, mais il ne peut pas être inclus dans une requête. Une description du plan avancé sort du cadre de ce manuel.

Dans la plupart des cas, vous pouvez faire confiance à Firebird pour choisir le plan de requête le plus optimal. Cependant, si vos requêtes sont très complexes et que vous avez l’impression qu’elles ne s’exécutent pas efficacement, vous devez examiner le plan de requête et voir si vous pouvez l’améliorer.

Des plans simples

Les plans les plus simples se composent uniquement d’un nom de table et de la méthode d’extraction suivante. Par exemple, pour une sélection non triée à partir d’une seule table sans clause WHERE:

SELECT * FROM students
PLAN (students NATURAL)

Plan sous forme EXPLAIN:

Select Expression
  -> Table "STUDENTS" Full Scan

S’il y a une clause `WHERE', vous pouvez spécifier l’index à utiliser pour trouver des correspondances :

SELECT *
FROM students
WHERE class = '3C'
PLAN (students INDEX (ix_stud_class))

Plan sous forme EXPLAIN:

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Bitmap
              -> Index "IX_STUD_CLASS" Range Scan (full match)

La directive INDEX peut également être utilisée pour les conditions de connexion (qui seront discutées plus tard). Il contient une liste d’index séparés par des virgules.

La directive ORDER spécifie l’index qui est utilisé pour trier le jeu de données si les clause ORDER BY ou GROUP BY sont présentes :

SELECT *
FROM students
PLAN (students ORDER pk_students)
ORDER BY id

Plan sous forme EXPLAIN:

Select Expression
  -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan

Les instructions ORDER et INDEX peuvent être combinées :

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER pk_students INDEX (ix_stud_class))
ORDER BY id

Plan sous forme d’EXPLAIN :Plan sous forme d’EXPLAIN :

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "PK_STUDENTS" Full Scan
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Les instructions ORDER et INDEX sont autorisées à spécifier le même index :

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER ix_stud_class INDEX (ix_stud_class))
ORDER BY class

Plan sous forme d’EXPLAIN :

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Pour trier des ensembles de données lorsqu’un index ne peut pas être utilisé (ou que vous souhaitez supprimer son utilisation), supprimez l’instruction ORDER et faites précéder l’expression du plan de l’instruction SORT:

SELECT *
FROM students
PLAN SORT (students NATURAL)
ORDER BY name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 128, key length: 56)
      -> Table "STUDENTS" Full Scan

Ou lorsque l’index est utilisé pour la recherche :

SELECT *
FROM students
WHERE class >= '3'
PLAN SORT (students INDEX (ix_stud_class))
ORDER BY name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 136, key length: 56)
      -> Filter
          -> Table "STUDENTS" Access By ID
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Notez que l’instruction SORT, contrairement à ORDER, est en dehors des parenthèses, ce qui reflète le fait que les lignes de données sont récupérées sans être triées et triées plus tard.

Lors d’une extraction à partir d’une vue, la vue elle-même et la table participante sont spécifiées. Par exemple, si vous avez une vue FRESHMEN qui ne sélectionne que les étudiants de première année :

SELECT *
FROM freshmen
PLAN (freshmen students NATURAL)

Plan sous forme d’EXPLAIN:

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Ou, par exemple.:

SELECT *
FROM freshmen
WHERE id > 10
PLAN SORT (freshmen students INDEX (pk_students))
ORDER BY name DESC

Plan sous forme d’EXPLAIN:

Select Expression
  -> Sort (record length: 144, key length: 24)
      -> Filter
          -> Table "STUDENTS" as "FRESHMEN" Access By ID
              -> Bitmap
                  -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Remarque : si vous avez attribué un alias à une table ou à une vue, vous devez utiliser l’alias, et non le nom original, dans la clause PLAN.

Plans composites

Si vous établissez une connexion, vous pouvez spécifier l’index à utiliser pour le mappage. Vous devez également utiliser la directive JOIN pour les deux threads du plan :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s NATURAL, c INDEX (pk_classes))

Plan sous forme d’EXPLAIN :

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

Même connexion, triée par colonne indexée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s ORDER pk_students, c INDEX (pk_classes))
ORDER BY s.id

Plan sous forme d’EXPLAIN :

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Access By ID
          -> Index "PK_STUDENTS" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

Et une connexion triée par une colonne non indexée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN SORT (JOIN (S NATURAL, c INDEX (pk_classes))))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 152, key length: 12)
      ->  Nested Loop Join (inner)
          -> Table "STUDENTS" as "S" Full Scan
          -> Filter
              -> Table "CLASSES" as "C" Access By ID
                  -> Bitmap
                      -> Index "PK_CLASSES" Unique Scan

Connexion avec une condition de recherche ajoutée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (s INDEX (fk_student_class), c INDEX (pk_classes)))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 152, key length: 12)
     ->  Nested Loop Join (inner)
         -> Filter
            -> Table "STUDENTS" as "S" Access By ID
               -> Bitmap
                  -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
         -> Filter
            -> Table "CLASSES" as "C" Access By ID
               -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

La même chose, mais en utilisant la connexion externe de gauche :

SELECT s.id, s.name, s.class, c.mentor
FROM classes c
LEFT JOIN students s ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (c NATURAL, s INDEX (fk_student_class)))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 192, key length: 56)
      -> Filter
         ->  Nested Loop Join (outer)
             -> Table "CLASSES" as "C" Full Scan
             -> Filter
                -> Table "STUDENTS" as "S" Access By ID
                   -> Bitmap
                      -> Index "FK_STUDENT_CLASS" Range Scan (full match)

Si aucun index n’est disponible pour la condition de jointure (ou si vous ne voulez pas en utiliser un), il est possible de joindre des fils en utilisant la méthode HASH ou MERGE.

Pour une jointure HASH, le plan utilise la directive HASH au lieu de la directive JOIN. Dans ce cas, le plus petit thread (esclave) est lu complètement dans le tampon interne. Pendant le processus de lecture, une fonction de hachage est appliquée à chaque clé de lien et la paire {hash, pointeur dans le tampon} est écrite dans la table de hachage. Le thread maître est ensuite lu et sa clé de lien est validée dans la table de hachage.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN HASH (c NATURAL, s NATURAL)

Plan sous forme d’EXPLAIN :

Select Expression
    -> Filter
        -> Hash Join (inner)
            -> Table "STUDENTS" as "S" Full Scan
            -> Record Buffer (record length: 145)
                -> Table "CLASSES" as "C" Full Scan

Lors d’une jointure MERGE, le plan doit d’abord trier les deux threads par les colonnes à joindre, puis effectuer la fusion. Ceci est réalisé avec la directive SORT (que vous avez déjà vue) et MERGE utilisée à la place de JOIN.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN MERGE (SORT (c NATURAL), SORT (s NATURAL))

L’ajout de la clause "ORDER BY" signifie que le résultat de la fusion doit également être trié :

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN SORT (MERGE (SORT (c NATURAL), SORT (s NATURAL)))
ORDER BY c.name, s.id

Enfin, nous ajoutons une condition de recherche sur les deux colonnes indexées de la table STUDENTS :

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
WHERE s.id < 10 AND s.class <= '2'
PLAN SORT (MERGE (SORT (c NATURAL),
                  SORT (s INDEX (pk_students, fk_student_class))))
ORDER BY c.name, s.id

Comme le suggère la définition formelle de la syntaxe, JOIN et MERGE peuvent combiner plus de deux threads dans un plan. De plus, chaque expression de plan peut être utilisée comme élément d’un plan englobant. Cela signifie que les plans de certaines requêtes complexes peuvent avoir différents niveaux d’imbrication.

Enfin, vous pouvez écrire SORT MERGE au lieu de MERGE. Comme cela n’a absolument aucune signification et peut créer une confusion avec la directive SORT (qui a une signification), il est probablement préférable de s’en tenir à la simple directive MERGE.

En plus du plan pour la requête principale, vous pouvez spécifier un plan pour chaque sous-requête. Par exemple, la requête suivante avec les plans spécifiés serait absolument correcte.

SELECT *
FROM COLOR
WHERE EXISTS(
             SELECT *
             FROM HORSE
             WHERE HORSE.CODE_COLOR = COLOR.CODE_COLOR
             PLAN (HORSE INDEX (FK_HORSE_COLOR)))
PLAN(COLOR NATURAL)

UNION

La clause UNION joint deux ou plusieurs ensembles de données, augmentant ainsi le nombre total de lignes mais pas de colonnes. Les ensembles de données participant à l’union doivent avoir le même nombre de colonnes. Cependant, les colonnes dans les positions respectives ne doivent pas nécessairement être du même type de données, elles peuvent être complètement indépendantes.

Par défaut, la fusion supprime les lignes en double.UNION ALL affiche toutes les chaînes de caractères, y compris les doublons. Le mot-clé optionnel DISTINCT rend le comportement par défaut explicite.

Syntaxe
<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [<rows-clause> | {[<result-offset-clause>] [<fetch-first-clause>]}]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body>
      [<order-by-clause>]
      [<result-offset-clause>] [<fetch-first-clause>]
    )

<query-specification> ::=
  SELECT
    <limit-clause>
    [{ ALL | DISTINCT }] <select-list>
  FROM <table-reference> [, <table-reference> ...]
  [WHERE <search-condition>]
  [GROUP BY <value-expression> [, <value-expression> ...]]
  [HAVING <search-condition>]
  [WINDOW <window-definition> [, <window-definition> ...]]
  [PLAN <plan-expression>]

Les jointures récupèrent les noms de colonnes de la première requête de sélection.Si vous voulez aliaser les colonnes à fusionner, faites-le pour la liste des colonnes dans la requête la plus haute de la sélection.Les alias dans d’autres échantillons participants sont autorisés, et peuvent même être utiles, mais ils ne se propageront pas au niveau de la fusion.

Si l’union a une clause ORDER BY, les seuls éléments de tri possibles sont des littéraux entiers pointant sur les positions des colonnes, pas nécessairement accompagnés des directives ASC | DESC et/ou NULLS {FIRST | LAST}.Cela signifie également que vous ne pouvez pas ordonner l’union par un élément qui n’est pas une colonne d’union.(Vous pouvez toutefois l’envelopper dans une table dérivée, qui vous donnera tous les paramètres de tri habituels).

Les fusions sont autorisées dans les sous-requêtes de tout type, et peuvent contenir des sous-requêtes à part entière.Ils peuvent également contenir des jointures, et peuvent prendre part à des jointures s’ils sont enveloppés dans une table dérivée.

Exemples

Cette requête présente des informations provenant de différentes collections de musique dans un seul ensemble de données en utilisant des associations :

SELECT id, title, artist, len, 'CD' AS medium
FROM cds
UNION
SELECT id, title, artist, len, 'LP'
FROM records
UNION
SELECT id, title, artist, len, 'MC'
FROM cassettes
ORDER BY 3, 2 -- artist, title

Si id, title, artist et length sont les seuls champs de toutes les tables participantes, la requête peut être écrite comme suit

SELECT c.*, 'CD' AS medium
FROM cds c
UNION
SELECT r.*, 'LP'
FROM records r
UNION
SELECT c.*, 'MC'
FROM cassettes c
ORDER BY 3, 2 -- artist, title

La clarification des " * " ( astérisque ) est nécessaire ici car elles ne sont pas le seul élément de la liste des colonnes. Notez que les alias "c" dans le premier et le troisième échantillon ne se mordent pas l’un l’autre. Ils n’ont pas de contexte d’association, mais s’appliquent uniquement aux requêtes individuelles de l’échantillon.

La requête suivante permet d’obtenir les noms et les numéros de téléphone des traducteurs et des correcteurs. Les traducteurs qui travaillent également comme correcteurs ne seront affichés qu’une seule fois dans le jeu résultant si leurs numéros de téléphone sont les mêmes dans les deux tables. Le même résultat peut être obtenu sans le mot-clé DISTINCT. Si le mot-clé ALL est spécifié au lieu de DISTINCT, ces personnes seront affichées deux fois.

SELECT name, phone
FROM translators
UNION DISTINCT
SELECT name, telephone
FROM proofreaders

Un exemple de l’utilisation de UNION dans une sous-requête :

SELECT name, phone, hourly_rate
FROM clowns
WHERE hourly_rate < ALL
  (SELECT hourly_rate FROM jugglers
   UNION
   SELECT hourly_rate FROM acrobats)
ORDER BY hourly_rate

L’utilisation d’expressions de requête entre parenthèses permet d’afficher les salariés les mieux payés et les moins bien payés :

(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

Syntaxe complète SELECT

Les sections précédentes ont utilisé des fragments incomplets ou simplifiés de la syntaxe de l’opérateur SELECT. La syntaxe complète est donnée ci-dessous.

Note

Dans la mesure du possible, la syntaxe ci-dessous utilise les noms de syntaxe de la norme SQL, qui ne sont pas nécessairement les mêmes que les noms de syntaxe dans le code source de Firebird. Dans certains cas, les représentations syntaxiques ont été regroupées parce que les représentations de la norme SQL sont détaillées et parce qu’elles sont également utilisées pour ajouter des règles ou des définitions supplémentaires à un élément syntaxique.

Bien que la syntaxe complète soit décrite ici, certaines représentations ne sont pas affichées (par exemple, <expression de valeur>) et sont supposées être claires pour le lecteur, et dans certains cas, nous utilisons des abréviations, telles que query-name ou column-alias pour les identificateurs dans la représentation syntaxique.

La syntaxe suivante n’inclut pas la syntaxe PSQL SELECT…​. INTO, qui est essentiellement <spécification du curseur> INTO <liste de variables>.
Syntaxe complète de l’opérateur SELECT
<cursor-specification> ::=
  <query-expression> [<updatability-clause>] [<lock-clause>]

<query-expression> ::=
  [<with-clause>] <query-expression-body> [<order-by-clause>]
    [{ <rows-clause>
     | [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <with-list-element> [, <with-list-element> ...]

<with-list-element> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body> [<order-by-clause>]
     [<result-offset-clause>] [<fetch-first-clause>])

<query-specification> ::=
  SELECT <limit-clause> [{ ALL | DISTINCT }] <select-list>
    FROM <table-reference> [, <table-reference> ...]
    [WHERE <search-condition>]
    [GROUP BY <value-expression> [, <value-expression> ...]]
    [HAVING <search-condition>]
    [WINDOW <window-definition> [, <window-definition> ...]]
    [PLAN <plan-expression>]

<limit-clause> ::= [FIRST <limit-expression>] [SKIP <limit-expression>]

<limit-expression> ::=
    <integer-literal>
  | <query-parameter>
  | (<value-expression>)

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    table-alias.*
  | <value-expression> [[AS] column-alias]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression> [, <value-expression> ...]

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<derived-table> ::= (<query-expression>)

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table>)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join>
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }

<window-definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  [existing-window-name]
    [<window-partition-clause>]
    [<order-by-clause>]
    [<window-frame-clause>]

<window-partition-clause> ::=
  PARTITION BY <value-expression> [, <value-expression> ...]

<order-by-clause> ::=
  ORDER BY <sort-specification> [, <sort-specification> ...]

<sort-specification> ::=
  <value-expression> [<ordering-specification>] [<null-ordering>]

<ordering-specification> ::=
    ASC  | ASCENDING
  | DESC | DESCENDING

<null-ordering> ::=
    NULLS FIRST
  | NULLS LAST

<window-frame-clause> ::= { RANGE | ROWS } <window-frame-extent>

<window-frame-extent> ::=
    <window-frame-start>
  | <window-frame-between>

<window-frame-start> ::=
    UNBOUNDED PRECEDING
  | <value-expression> PRECEDING
  | CURRENT ROW

<window-frame-between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <value-expression> PRECEDING
          | CURRENT ROW | <value-expression> FOLLOWING }
  AND { <value-expression> PRECEDING | CURRENT ROW
      | <value-expression> FOLLOWING | UNBOUNDED FOLLOWING }

<rows-clause> ::= ROWS <value-expression> [TO <value-expression>]

<result-offset-clause> :: =
  OFFSET <offset-fetch-expression> { ROW | ROWS }

<offset-fetch-expression> ::=
    <integer-literal>
  | <query-parameter>

<fetch-first-clause> ::=
  [FETCH { FIRST | NEXT }
   [<offset-fetch-expression>] { ROW | ROWS } ONLY]

<updatability-clause> ::= FOR UPDATE [OF <column-name-list>]

<lock-clause> ::= WITH LOCK [SKIP LOCKED]

INSERT

affectation

Insérer des données dans un tableau.

Disponible en

DSQL, ESQL, PSQL

Syntaxe
INSERT INTO target [(<column_list>)]
  [OVERRIDE {SYSTEM | USER} VALUE]
  {DEFAULT VALUES | <value_source>}
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col-name [, col-name ...]

<value_source> ::= VALUES (<value_list>) | <select_stmt>

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

<ins_value> :: = <value_expression> | DEFAULT

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

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] 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 la clause INSERT
Paramètre Description

target

Nom de la table ou de la vue dans laquelle la ou les nouvelles entrées sont insérées.

col-name

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

value_expression

Une expression dont la valeur est utilisée pour insérer dans une table ou retourner à 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.

return_expression

Expression renvoyée dans une clause RETURNING.

collation

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

alias

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

varname

Le nom de la variable PSQL.

L’instruction INSERT ajoute des lignes à la table ou à une ou plusieurs tables de présentation. Si les valeurs des colonnes sont spécifiées sous VALUES, une ligne sera insérée. Les valeurs des colonnes peuvent également être obtenues à partir de l’instruction SELECT, auquel cas zéro ou plusieurs lignes peuvent être insérées. Dans le cas de DEFAULT VALUES, les valeurs peuvent ne pas être spécifiées et une ligne sera insérée.

Note

  • Une colonne ne peut pas être spécifiée plus d’une fois dans la liste des colonnes ;

  • Lorsque l’on retourne les valeurs des colonnes d’un enregistrement inséré en utilisant INTO vers les variables de contexte NEW.columnname dans les triggersle préfixe deux points (“:”) n’a pas besoin d’être utilisé.

INSERT …​ VALUES

La liste VALUES doit spécifier des valeurs pour toutes les colonnes de la liste de colonnes dans le même ordre et compatibles en type. S’il n’y a pas de liste de colonnes, les valeurs doivent être spécifiées pour chaque colonne de la table ou de la vue (à l’exclusion des colonnes calculées).

Note

Introducer syntax permet de définir un jeu de caractères pour les valeurs constantes des chaînes de caractères (littéraux).La syntaxe d’entrée ne fonctionne que sur les littéraux de chaîne : elle ne peut pas être appliquée aux variables de chaîne, aux paramètres, aux références de colonnes ou de valeurs, ou aux expressions.
Example 1. Utiliser INSERT avec une clause VALUES.
INSERT INTO cars (make, model, byyear)
VALUES ('Ford', 'T', 1908);

INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);

-- notez le préfixe '_' (introducer syntax)
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

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.

Si le mot-clé DEFAULT est spécifié pour une colonne définie comme GENERATED BY DEFAULT AS IDENTITY, la colonne obtiendra la valeur d’identification suivante, comme si cette colonne n’était pas du tout spécifiée dans la requête.

Example 1. Utilisation du mot clé DEFAULT dans l’instruction 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
INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus');

-- la colonne id aura la valeur 2, comme si nous n'avions pas spécifié de valeur pour la colonne id.
INSERT INTO cars (id, byyear, name)
VALUES (DEFAULT, 1996, 'Ford Mondeo');

INSERT …​ SELECT

Dans ce cas, les colonnes de sortie de l’instruction SELECT doivent fournir des valeurs pour chaque colonne cible de la liste de colonnes, dans le même ordre et d’un type compatible. S’il n’y a pas de liste de colonnes, des valeurs doivent être fournies pour chaque colonne de la table ou de la vue (à l’exclusion des colonnes calculées).

Example 1. En utilisant l’instruction sous la forme INSERT …​ SELECT
INSERT INTO cars (make, model, byyear)
SELECT make, model, byyear
FROM new_cars;

INSERT INTO cars
SELECT *
FROM new_cars;

INSERT INTO Members (number, name)
SELECT number, name
FROM NewMembers
WHERE Accepted = 1
UNION ALL
SELECT number, name
FROM SuspendedMembers
WHERE Vindicated = 1

INSERT INTO numbers(num)
WITH RECURSIVE r(n) AS (
  SELECT 1 FROM rdb$database
  UNION ALL
  SELECT n+1 FROM r where n < 100
)
SELECT n FROM r

Bien entendu, les noms des colonnes de la table source ne doivent pas nécessairement être les mêmes que ceux de la table de destination.

Tout type d’instruction SELECT est autorisé, à condition que ses colonnes de sortie correspondent exactement aux colonnes d’insertion en nombre et en type. Il n’est pas nécessaire que les types soient exactement les mêmes, mais ils doivent être compatibles avec les affectations.

INSERT …​ DEFAULT VALUES

La clause DEFAULT VALUES vous permet d’insérer des enregistrements sans spécifier de valeurs du tout, soit directement (dans la clause VALUES), soit à partir de l’instruction SELECT. Cela n’est possible que si pour chaque champ NOT NULL et les champs auxquels d’autres restrictions sont appliquées, soit il existe des valeurs par défaut déclarées valides, soit ces valeurs sont définies dans le déclencheur BEFORE INSERT.

Example 1. Utilisation de "valeurs par défaut" dans l’instruction "INSERT".
INSERT INTO journal
DEFAULT VALUES
RETURNING entry_id

Directive OVERRIDING

Les valeurs des colonnes d’identité (GENERATED BY DEFAULT AS IDENTITY) peuvent être remplacées par les instructions INSERT, UPDATE OR INSERT, MERGE. Pour cela, il suffit de spécifier la valeur de la colonne dans la liste des valeurs. Cependant, pour les colonnes définies comme GENERATED ALWAYS, cela n’est pas autorisé.

La directive OVERRIDING SYSTEM VALUE permet de remplacer une valeur générée par le système par une valeur spécifiée par l’utilisateur. La directive OVERRIDING SYSTEM VALUE provoquera une erreur s’il n’y a pas de colonnes d’identité dans la table ou si elles sont définies comme GENERATED BY DEFAULT AS IDENTITY.

Example 1. Utilisation de la directive OVERRIDING SYSTEM VALUE dans l’instruction INSERT.
CREATE TABLE objects (
  id INT GENERATED ALWAYS AS IDENTITY,
  name CHAR(50));

-- la valeur avec le code 11 sera insérée
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (11, 'Laptop');

La directive OVERRIDE USER VALUE effectue la tâche inverse, c’est-à-dire qu’elle remplace la valeur spécifiée par l’utilisateur par la valeur générée par le système si la colonne d’identité est définie comme GENERATED BY DEFAULT AS IDENTITY. La directive OVERRIDING USER VALUE provoquera une erreur si la table ne contient pas de colonne d’identité ou si elle est définie comme GENERATED ALWAYS AS IDENTITY.

Example 2. Utilisation de la directive OVERRIDING USER VALUE dans l’instruction INSERT.
CREATE TABLE objects (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  name CHAR(50));

-- la valeur 12 sera ignorée
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (12, 'Laptop');
Voir aussi :

Colonnes d’identitée (auto-incrémentation).

RETURNING

L’instruction INSERT peut inclure une clause RETURNING facultative pour retourner les valeurs de la ligne insérée. Si la clause est spécifiée, elle peut contenir toutes les colonnes spécifiées dans l’instruction ou d’autres colonnes et expressions. Vous pouvez spécifier un astérisque (*) au lieu d’une liste de colonnes pour retourner toutes les valeurs des colonnes de la table. Les valeurs retournées contiennent toutes les modifications apportées dans les déclencheurs BEFORE.

Important

  • En DML, le INSERT …​ SELECT avec RETURNING retourne un curseur (avant Firebird 5.0 ne pouvait retourner qu’un seul enregistrement).

  • Actuellement, les instructions avec une clause RETURNING ne peuvent pas être utilisées en conjonction avec une clause FOR pour boucler sur un curseur dans PSQL.

Ce comportement peut être modifié dans les versions futures de Firebird.* La fonction INSERT …​ VALUES avec une clause RETURNING retourne toujours un seul enregistrement. Si aucun enregistrement n’a été inséré, tous les champs de la chaîne retournée seront mis à NULL. Ce comportement peut être modifié ultérieurement. En PSQL, si aucun enregistrement n’est inséré, rien n’est retourné et toutes les variables cibles conservent leurs valeurs précédentes.
Example 1. Utilisation de la clause RETURNING dans l’instruction INSERT.
INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (first_name, last_name, iq)
SELECT fname, lname, iq
FROM Friends
ORDER BY iq ROWS 1
RETURNING id, first_name, iq
INTO :id, :fname, :iq;

Insertion de colonnes BLOB

L’insertion dans les colonnes BLOB n’est possible que dans les circonstances suivantes :

  1. L’application cliente insère un BLOB via l’API de Firebird. Dans ce cas, cela dépend de l’application, et n’est pas couvert par ce manuel ; . La longueur des chaînes de caractères ne peut dépasser 65 533 octets (64K - 3).

    Note

    La limite de caractères est calculée au moment de l’exécution. Pour les jeux de caractères à plusieurs octets, cela peut être différent. Par exemple, pour une chaîne UTF8 (4 octets par caractère), la limite du littéral de chaîne serait probablement d’environ (floor (65533/4)) = 16383 caractères.

  2. Si la source de données est une colonne BLOB ou une expression qui renvoie une BLOB. Par exemple, lorsque vous utilisez le formulaire INSERT …​ SELECT ou dans le code PSQL lorsqu’une variable BLOB est utilisée comme paramètre.

UPDATE

affectation

Mettre à jour les données dans une table.

Disponible en

DSQL, ESQL, PSQL

Syntaxe
UPDATE target [[AS] alias]
  SET col_name = <upd_value> [, col_name = <upd_value> ...]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<upd_value> ::= <value_expression> | DEFAULT

<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 UPDATE
Paramètre Description

target

Nom de la table ou de la vue dans laquelle les enregistrements sont mis à jour.

alias

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

col_name

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

upd_value

Expression de la nouvelle valeur de la colonne à mettre à jour dans la table ou la vue par l’instruction.

literal

Litéral

context-variable

La variable de contexte.

other-single-value-expr

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

search-conditions

Une condition de recherche limitant l’ensemble des lignes à mettre à jour.

cursorname

Le nom du curseur sur lequel se trouve l’enregistrement à mettre à jour.

plan_items

Parties du plan de requête.

sort_items

Les colonnes énumérées dans la clause `ORDER BY'.

m, n

Expressions entières pour limiter le nombre de lignes à mettre à jour.

return_expression

Expression retournée dans la clause RETURNING.

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

Le nom de la variable PSQL.

L’instruction UPDATE modifie les valeurs des colonnes d’une table, ou d’une ou plusieurs tables sous-jacentes. Les nouvelles valeurs des colonnes sont spécifiées dans l’instruction SET. Les lignes affectées peuvent être limitées par les instructions WHERE et ROWS. Si ni WHERE ni ROWS n’existent, toutes les entrées de la table seront mises à jour.

Utilisation d’un alias

Si vous attribuez un alias à une table ou à une vue, vous doit utiliser l’alias pour spécifier les colonnes de la table.

Exemples

Utilisation correcte :

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

Utilisation incorrecte :

update Fruit F set Fruit.soort = 'pisang' where ...

SET

Les colonnes à modifier sont spécifiées dans la clause SET. Les colonnes et leurs valeurs sont listées séparées par des virgules. Le nom de la colonne est à gauche, et la valeur ou l’expression à droite.

Il est autorisé d’utiliser les noms de colonnes dans les expressions de droite. Il utilisera toujours l’ancienne valeur de la colonne, même si l’affectation à cette colonne a déjà eu lieu plus tôt dans l’énumération SET. Une colonne ne peut être utilisée qu’une seule fois dans une construction SET.

Example 1. Utilisation de la clause `UPDATE

Données dans le table TSET :

A B
---
1 0
2 0

Après avoir exécuté l’instruction

update tset set a = 5, b = a
A B
---
5 1
5 2

Notez que les anciennes valeurs (1 et 2) sont utilisées pour mettre à jour la colonne b, même après qu’une nouvelle valeur (5) ait été attribuée à la colonne a.

Mot clé DEFAULT

Le mot-clé DEFAULT peut être utilisé dans la clause SET à la place de la valeur de la colonne. Dans ce cas, la colonne prendra 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.

Example 1. Utilisation du mot clé DEFAULT dans l’instruction UPDATE.
CREATE TABLE cars (
  ID INTEGER NOT NULL,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

INSERT INTO cars (1, byyear, name)
VALUES (1, 1985, 'Ford Focus');

-- la colonne BYYEAR se verra attribuer la valeur 1990
UPDATE cars
SET BYYEAR = DEFAULT
WHERE ID = 1;

WHERE

La clause WHERE limite l’ensemble des enregistrements à mettre à jour à une condition donnée, ou — en PSQL — à la ligne courante du curseur nommé si la clause WHERE CURRENT OF est spécifiée.

Note

La clause `WHERE CURRENT OF' n’est utilisée qu’en PSQL car DSQL n’a pas d’instruction DSQL pour créer un curseur.

Les littéraux de type chaîne peuvent être précédés d’un nom de jeu de caractères, afin que Firebird comprenne comment interpréter les données.

Example 1. Utilisation de la clause "WHERE" dans l’instruction "UPDATE".
UPDATE addresses
SET city = 'Saint Petersburg', citycode = 'PET'
WHERE city = 'Leningrad';

UPDATE employees
SET salary = 2.5 * salary
WHERE title = 'CEO';

-- noter le préfixe '_'
UPDATE People
SET name = _ISO8859_1 'Hans-Jörg Schäfer'
WHERE id = 53662;

UPDATE employee e
SET salary = salary * 1.05
WHERE EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);

PLAN

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

Example 1. Utilisation de la clause PLAN dans une déclaration UPDATE.
UPDATE company c SET c.company_name =
    ( SELECT k.contact_name
      FROM contact k
      WHERE k.id = c.contact_id
      PLAN (K INDEX (CONTACT_ID)))
WHERE c.company_name IS NULL OR c.company_name = ''
PLAN (C NATURAL)

ORDER BY et ROWS

La clause ORDER BY vous permet de définir l’ordre dans lequel les enregistrements sont mis à jour, ce qui peut être utile dans certains cas.

La clause ROWS n’a de sens qu’avec la clause ORDER BY.Toutefois, il peut être utilisé séparément.

Avec un seul argument m, ROWS limite la mise à jour aux m premiers enregistrements.

Caractéristiques :

  • Si m est supérieur au nombre d’enregistrements de la table cible en cours de traitement, l’ensemble des lignes est mis à jour ;

  • Si m = 0, aucun enregistrement n’est mis à jour ;

  • Si m < 0, une erreur est émise.

Avec les deux arguments m et n, ROWS limite la mise à jour des enregistrements de m à n inclus.Les deux arguments sont des entiers et commencent par 1.

Caractéristiques :

  • Si m est supérieur au nombre d’enregistrements dans la table cible, aucun enregistrement n’est mis à jour ;

  • Si n est supérieur au nombre d’enregistrements dans la table cible, les enregistrements de m à la fin du jeu sont mis à jour ;

  • Si m < 1 ou n < 1, une erreur est émise ;

  • Si n = m - 1, aucun enregistrement n’est mis à jour ;

  • Si n < m - 1, une erreur est générée.

Example 1. Utilisation de la clause ROWS dans la déclaration UPDATE.
-- Accorder une augmentation aux 20 employés les moins bien payés
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;

SKIP LOCKED

Affectation

Ne pas tenir compte du blocage.

La proposition SKIP LOCKED force le moteur à sauter les enregistrements verrouillés par d’autres transactions,au lieu d’attendre ou de provoquer des erreurs de conflit.

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,DELETE FROM …​ SKIP LOCKED.

RETURNING

L’instruction UPDATE, peut inclure RETURNING pour retourner les valeurs des enregistrements mis à jour. L’instruction RETURNING peut inclure n’importe quelles colonnes, pas nécessairement seulement celles qui sont mises à jour.

Les valeurs retournées contiennent les changements effectués dans les déclencheurs BEFORE UPDATE mais pas dans les déclencheurs AFTER UPDATE. Les expressions OLD.fieldname et NEW.fieldname peuvent être utilisées comme noms de colonnes. Si OLD. ou NEW. n’est pas spécifié, les nouvelles valeurs de colonnes NEW. 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 DML, l’instruction UPDATE avec la clause RETURNING renvoie un curseur (avant Firebird 5.0, elle ne pouvait renvoyer qu’un seul enregistrement).

  • Actuellement, les instructions avec la clause RETURNING ne peuvent pas être appliquées avec la clause FOR pour boucler sur un curseur en PSQL.

Ce comportement peut être modifié dans les futures versions de Firebird.* Si les enregistrements n’ont pas été mis à jour par l’instruction, les valeurs de retour contiennent NULL.

INTO

La clause INTO permet de passer des valeurs aux variables locales. Elle n’est disponible que dans PSQL. Si les enregistrements n’ont pas été mis à jour, rien n’est retourné et les variables spécifiées dans RETURNING conserveront leurs valeurs précédentes.

Example 1. Utilisation de la clause "RETURNING" dans l’instruction "UPDATE".
UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING id, old.last_name, new.last_name;
Example 2. Utilisation de * dans une clause "RETURNING" dans une déclaration "UPDATE".
UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING old.*;

Mise à jour des colonnes BLOB

La mise à jour des colonnes BLOB modifie toujours complètement leur contenu.Même l’ID BLOB, qui est une référence aux données BLOB et qui est stocké dans la colonne, change.Les colonnes de type BLOB peuvent être modifiées si :

  1. L’application client modifie le BLOB via l’API de Firebird. Dans ce cas, cela dépend de l’application et n’est pas couvert par ce manuel ;

  2. La longueur des chaînes de caractères ne peut dépasser 65 533 octets (64K - 3).

    Note

    La limite de caractères est calculée au moment de l’exécution. Pour les jeux de caractères à plusieurs octets, cela peut être différent. Par exemple, pour une chaîne UTF8 (4 octets par caractère), la limite du littéral de chaîne serait probablement d’environ (floor (65533/4)) = 16383 caractères.

  3. Si la source de données est une colonne de type BLOB ou une expression retournant BLOB.

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);

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.