Sélection À Partir D'une Procédure Stockée Sélective

COMMENTSInstructions de procédure SQL (PSQL)

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.