Une fonction stockée est un programme stocké dans la zone de métadonnées d’une base de données et exécuté du côté du serveur.Une fonction stockée est accessible aux procédures stockées, aux fonctions stockées (y compris elle-même), aux déclencheurs et aux programmes clients.Lorsqu’une fonction stockée se réfère à elle-même, cette fonction stockée est dite récursive.

Contrairement aux procédures stockées, les fonctions stockées renvoient toujours une seule valeur scalaire.Pour renvoyer une valeur à partir d’une fonction stockée, on utilise l’opérateur RETURN, qui met immédiatement fin à la fonction.

`CREATE FUNCTION`

affectation

Créez une nouvelle fonction stockée.

Disponible en

DSQL

Syntaxe

CREATE FUNCTION funcname [(<inparam> [, <inparam> ...])]
  RETURNS <type> [NOT NULL] [COLLATE collation]
  [DETERMINISTIC]
  <routine-body>

<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<value> ::=  {<literal> | NULL | <context_var>}

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

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<datatype> ::=
    <scalar_datatype> | <blob_datatype>

<scalar_datatype> ::= Voir Syntaxe des types de données scalaires.

<blob_datatype> ::= Voir Syntaxe du type de données BLOB.


<routine-body> ::=
    <psql-routine-spec>
  | <external-routine-reference>

<psql-routine-spec> ::=
  [<rights-clause>] <psql-routine-body>


<rights-clause> ::=
  SQL SECURITY {DEFINER | INVOKER}

<psql-routine-body> ::=
  Voir module-body syntax.

<external-routine-spec> ::=
  <external-routine-reference>
  [AS <extbody>]

<external-routine-reference> ::= EXTERNAL NAME '<extname>' ENGINE <engine>

<extname> ::= '<module-name>!<routine-name>[!<misc-info>]'

Table 1. Paramètres de la fonction `CREATE FUNCTION`
Paramètre	Description
funcname	Le nom de la fonction stockée.Peut contenir jusqu’à 63 caractères.
inparam	Description du paramètre d’entrée.
literal	Un littéral dont le type est compatible avec le paramètre.
context_var	Toute variable contextuelle dont le type est compatible avec celui du paramètre.
paramname	Le nom du paramètre d’entrée d’une fonction. Peut contenir jusqu’à 63 caractères. Le nom du paramètre doit être unique parmi les paramètres d’entrée de la fonction ainsi que ses variables locales.
module-name	Nom du module externe dans lequel se trouve la fonction.
routine-name	Nom de la fonction interne dans un module externe.
misc-info	Informations définies par l’utilisateur à envoyer à une fonction de module externe.
extbody	Le corps de la fonction externe. Une chaîne littérale qui peut être utilisée par l’UDR à diverses fins.
engine	Nom du moteur pour l’utilisation des fonctions externes. Habituellement, le nom de l’UDR est donné.
datatype	Type de données SQL.
collation	Ordre de tri.
domain_name	Nom de domaine.
rel	Le nom d’une table ou d’une vue.
col	Le nom de la colonne de la table ou de la vue.

L’instruction CREATE FUNCTION crée une nouvelle fonction stockée. Le nom de la fonction stockée doit être unique parmi les noms de toutes les fonctions stockées et des fonctions externes (UDF), sauf s’il s’agit d’une fonction interne ("sous-programme"). Pour les fonctions internes, l’unicité est suffisante uniquement dans les modules qui les "couvrent".

Note

Il est également souhaitable que le nom de la fonction stockée soit unique parmi les noms des fonctions situées dans les packages PSQL, bien que cela soit acceptable. Le fait est qu’actuellement vous ne pouvez pas appeler une fonction/procédure à partir de l’espace de noms global dans un package si une fonction/procédure du même nom est déclarée dans le package. Dans ce cas, la procédure/fonction du package sera toujours appelée.

CREATE FUNCTION L’en-tête définit le nom de la fonction stockée, déclare les paramètres d’entrée et le type de la valeur de retour.

Le corps d’une fonction consiste en des déclarations facultatives de variables locales, de sous-routines et de curseurs nommés, et en une ou plusieurs instructions, ou blocs d’instructions, enfermés dans un bloc externe qui commence par le mot-clé BEGIN et se termine par le mot-clé END. Les déclarations de variables locales et de curseurs nommés et les instructions internes doivent se terminer par un point-virgule ( ;).

docnext count = 22

Opérateur 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 <<fblangref-psql-setterm,Changing the terminator in isql>.

Qui peut créer une fonction ?

L’instruction CREATE FUNCTION peut être exécutée :

Administrateurs
Utilisateurs avec le privilège CREATE FUNCTION.

L’utilisateur qui a créé la fonction stockée devient le propriétaire de la fonction.

Exemples

Example 1. Création d’une fonction stockée

CREATE FUNCTION ADD_INT(A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
  RETURN A+B;
END

Fonction dans une requête :

SELECT ADD_INT(2, 3) AS R FROM RDB$DATABASE

Appel d’une fonction à l’intérieur du code PSQL, le deuxième paramètre optionnel n’est pas spécifié :

MY_VAR = ADD_INT(A);

Example 2. Création d’une fonction stockée déterministe

CREATE FUNCTION FN_E()
RETURNS DOUBLE PRECISION DETERMINISTIC
AS
BEGIN
  RETURN EXP(1);
END

Example 3. Création d’une fonction stockée avec des paramètres de type colonne de table

Fonction qui renvoie le nom mnémonique par le nom de la colonne et la valeur mnémonique.

CREATE FUNCTION GET_MNEMONIC (
    AFIELD_NAME TYPE OF COLUMN RDB$TYPES.RDB$FIELD_NAME,
    ATYPE TYPE OF COLUMN RDB$TYPES.RDB$TYPE)
RETURNS TYPE OF COLUMN RDB$TYPES.RDB$TYPE_NAME
AS
BEGIN
  RETURN (SELECT RDB$TYPE_NAME
          FROM RDB$TYPES
          WHERE RDB$FIELD_NAME = :AFIELD_NAME
            AND RDB$TYPE = :ATYPE);
END

Idem, mais la fonction stockée sera exécutée avec les privilèges de l’utilisateur qui la définit (propriétaire de la fonction).

CREATE FUNCTION GET_MNEMONIC (
    AFIELD_NAME TYPE OF COLUMN RDB$TYPES.RDB$FIELD_NAME,
    ATYPE TYPE OF COLUMN RDB$TYPES.RDB$TYPE)
RETURNS TYPE OF COLUMN RDB$TYPES.RDB$TYPE_NAME
SQL SECURITY DEFINER
AS
BEGIN
  RETURN (SELECT RDB$TYPE_NAME
          FROM RDB$TYPES
          WHERE RDB$FIELD_NAME = :AFIELD_NAME
            AND RDB$TYPE = :ATYPE);
END

Example 4. Création d’une fonction stockée externe

Création d’une fonction située dans un module externe (UDR). L’implémentation de la fonction se trouve dans le module externe udrcpp_example. Nom de la fonction dans le module — wait_event.

CREATE FUNCTION wait_event (
   event_name varchar(63) CHARACTER SET ascii
) RETURNS INTEGER
EXTERNAL NAME 'udrcpp_example!wait_event'
ENGINE udr

Example 5. Création d’une fonction stockée contenant une sous-fonction

Créez une fonction pour convertir un nombre au format hexadécimal.

CREATE FUNCTION INT_TO_HEX (
    ANumber BIGINT,
    AByte_Per_Number SMALLINT = 8)
RETURNS CHAR(66)
AS
DECLARE VARIABLE xMod SMALLINT;
DECLARE VARIABLE xResult VARCHAR(64);
DECLARE FUNCTION TO_HEX(ANum SMALLINT) RETURNS CHAR
AS
BEGIN
  RETURN CASE ANum
           WHEN 0 THEN '0'
           WHEN 1 THEN '1'
           WHEN 2 THEN '2'
           WHEN 3 THEN '3'
           WHEN 4 THEN '4'
           WHEN 5 THEN '5'
           WHEN 6 THEN '6'
           WHEN 7 THEN '7'
           WHEN 8 THEN '8'
           WHEN 9 THEN '9'
           WHEN 10 THEN 'A'
           WHEN 11 THEN 'B'
           WHEN 12 THEN 'C'
           WHEN 13 THEN 'D'
           WHEN 14 THEN 'E'
           WHEN 15 THEN 'F'
           ELSE NULL
         END;
END
BEGIN
  xMod = MOD(ANumber, 16);
  ANumber = ANumber / 16;
  xResult = TO_HEX(xMod);
  WHILE (ANUMBER > 0) DO
  BEGIN
    xMod = MOD(ANumber, 16);
    ANumber = ANumber / 16;
    xResult = TO_HEX(xMod) || xResult;
  END
  RETURN '0x' || LPAD(xResult, AByte_Per_Number * 2, '0');
END

Voir aussi :

CREATE OR ALTER FUNCTION,ALTER FUNCTION,RECREATE FUNCTION,DROP FUNCTION.

Paramètres d’entrée

Les paramètres d’entrée sont placés entre parenthèses après le nom de la fonction enregistrée. Ils sont transmis à la fonction par valeur, c’est-à-dire que toute modification des paramètres d’entrée dans la fonction n’aura aucun effet sur les valeurs de ces paramètres dans le programme appelant.

Chaque paramètre a un type de données et vous pouvez également spécifier une restriction NOT NULL pour le paramètre, l’empêchant ainsi de passer une valeur NULL.

Pour un paramètre de type chaîne, il est possible de spécifier l’ordre de tri en utilisant la phrase COLLATE.

Les paramètres d’entrée peuvent avoir une valeur par défaut. Les paramètres pour lesquels des valeurs sont spécifiées doivent être placés à la fin de la liste des paramètres.

Utilisation de domaines lors de la déclaration de paramètres

Vous pouvez spécifier un nom de domaine comme type de paramètre, auquel cas le paramètre héritera de toutes les caractéristiques du domaine.

Si la clause TYPE OF est utilisée en plus avant le nom du domaine, seul le type de données du domaine est utilisé — sa restriction (si elle existe dans le domaine) n’est pas vérifiée (non utilisée) pour les restrictions NOT NULL, CHECK et/ou les valeurs par défaut. Si le domaine est de type texte, son jeu de caractères et son ordre de tri sont toujours utilisés.

Utilisation du type de colonne lors de la déclaration des paramètres

Les paramètres d’entrée et de sortie peuvent être déclarés en utilisant le type de données colonne des tables et vues existantes. Pour ce faire, on utilise la phrase TYPE OF COLUMN, suivie du nom de la table ou de la vue et d’un nom de colonne séparé par un point.

Lorsque vous utilisez TYPE OF COLUMN, seul le type de données est hérité, et dans le cas des types de chaînes de caractères, également le jeu de caractères et l’ordre de tri. Les limitations et les valeurs par défaut de la colonne ne sont jamais utilisées.

Valeur de retour

La phrase RETURNS spécifie le type de valeur de retour de la fonction stockée. Si la fonction retourne une valeur de type chaîne de caractères, il est possible de spécifier l’ordre de tri en utilisant la phrase COLLATE. Le type de valeur de retour peut être un nom de domaine, une référence à son type (en utilisant la phrase TYPE OF) ou une référence à un type de colonne de table (en utilisant la phrase TYPE OF COLUMN).

Fonctions déterministes

La clause facultative DETERMINISTIC indique que la fonction est déterministe. Les fonctions déterministes renvoient le même résultat à chaque fois si on leur donne le même ensemble de valeurs d’entrée. Les fonctions non déterministes peuvent renvoyer des résultats différents à chaque fois, même si on leur donne le même ensemble de valeurs d’entrée. Si une fonction est dite déterministe, elle n’est pas recalculée si elle a déjà été calculée une fois avec l’ensemble donné d’arguments d’entrée, mais elle prend ses valeurs du cache

Note

En fait, dans la version actuelle de Firebird, il n’y a pas de cache de fonctions stockées avec des arguments d’entrée mis en correspondance avec des valeurs de sortie.

Spécifier une instruction DETERMINISTIC est en fait une sorte de promesse que le code de la fonction retournera la même chose. A ce stade, une fonction déterministe est considérée comme un invariant et fonctionne sur les mêmes principes que les autres invariants. C’est-à-dire qu’elle est calculée et mise en cache au niveau d’exécution actuel de cette requête.

Cet exemple le démontre aisément :

CREATE FUNCTION FN_T
RETURNS DOUBLE PRECISION DETERMINISTIC
AS
BEGIN
  RETURN rand();
END

-- la fonction sera calculée deux fois et retournera 2 valeurs différentes
SELECT fn_t() FROM rdb$database
UNION ALL
SELECT fn_t() FROM rdb$database

-- la fonction sera calculée une fois et retournera 2 valeurs identiques
WITH t(n) AS (
  SELECT 1 FROM rdb$database
  UNION ALL
  SELECT 2 FROM rdb$database
)
SELECT n, fn_t() FROM t

Privilèges d’exécution

La clause facultative SQL SECURITY vous permet de spécifier avec quels privilèges la fonction stockée est exécutée. Si l’option INVOKER est sélectionnée, la fonction stockée est exécutée avec les privilèges de l’utilisateur appelant. Si l’option DEFINER est sélectionnée, la fonction stockée est exécutée avec les privilèges de l’utilisateur définissant (propriétaire de la fonction). Ces privilèges s’ajouteront aux privilèges accordés à la fonction stockée elle-même à l’aide de l’opérateur GRANT. Par défaut, la fonction stockée est exécutée avec les privilèges de l’utilisateur appelant.

Tip	Les privilèges d’exécution par défaut pour les objets de métadonnées nouvellement créés peuvent être modifiés à l’aide de la commande ALTER DATABASE SET DEFAULT SQL SECURITY {DEFINER \| INVOKER}

Le corps de la fonction stockée

Le mot clé "AS" est suivi du corps de la fonction stockée.

Déclaration des variables locales, des curseurs et des sous-routines

La section optionnelle <déclarations> décrit les variables locales des fonctions, les curseurs nommés et les sous-programmes (sous-procédures et sous-fonctions). Les variables locales sont soumises aux mêmes règles que les paramètres des fonctions d’entrée en ce qui concerne la spécification du type de données. Pour plus de détails, voir "Langage procédural PSQL" dans les sections DECLARE VARIABLE etDECLARE CURSOR,DECLARE PROCEDURE,DECLARE FUNCTION.

Une section de déclaration facultative est nécessairement suivie d’une instruction composée. Une instruction composée consiste en une ou plusieurs instructions PSQL comprises entre les mots-clés BEGIN et END. Une instruction composée peut contenir une ou plusieurs autres instructions composées. L’imbrication est limitée à 512 niveaux. Toute instruction BEGIN … END peuvent être vides, y compris le bloc principal.

Fonctions externes

Une fonction stockée peut être située dans un module externe. Dans ce cas, au lieu du corps de la fonction, l’emplacement de la fonction dans le module externe est spécifié en utilisant la phrase EXTERNAL NAME. L’argument de cette phrase est une chaîne de caractères avec le nom du module externe, le nom de la fonction dans le module et des informations définies par l’utilisateur séparées par un séparateur. La phrase ENGINE spécifie le nom du plug-in pour gérer les modules externes. Dans Firebird, le plug-in pour gérer les modules externes est UDR. Après le mot clé AS, une chaîne de caractères peut être spécifiée

Warning

Les fonctions externes déclarées comme DECLARE EXTERNAL FUNCTION, également connues sous le nom d’UDF, ne doivent pas être confondues avec les fonctions situées dans les modules externes déclarés comme CREATE FUNCTION … EXTERNAL NAME, appelé UDR (User Defined Routine). Les premières sont un héritage des versions précédentes de Firebird, avec des capacités nettement inférieures au nouveau type de fonctions externes. Dans Firebird 4.0, les UDF sont déclarées obsolètes.

`ALTER FUNCTION`

affectation

Modifier une fonction stockée existante.

Disponible en

DSQL

Syntaxe

ALTER FUNCTION funcname
[(<inparam> [, <inparam> ...])]
RETURNS <type> [COLLATE collation]
[DETERMINISTIC]
<routine-body>

Pour plus d'informations, voir. CREATE FUNCTION.

L’instruction ALTER FUNCTION permet de modifier la composition et les caractéristiques des paramètres d’entrée, le type de valeur de sortie, les variables locales, les curseurs nommés, les sous-programmes et le corps de la fonction stockée. Pour les fonctions externes (UDR), vous pouvez modifier le point d’entrée et le nom du moteur. Les fonctions externes déclarées comme DECLARE EXTERNAL FUNCTION, également connues sous le nom d’UDF, ne peuvent pas être converties en fonctions PSQL et vice versa. Une fois exécutées, les privilèges et dépendances existants sont conservés.

Note

Soyez prudent lorsque vous modifiez le nombre et les types de paramètres d’entrée des fonctions stockées. Le code d’application existant peut devenir inapplicable car le format d’appel de la fonction est incompatible avec la nouvelle description des paramètres. De plus, les modules PSQL utilisant la fonction stockée modifiée peuvent devenir invalides. Des informations sur la façon de détecter cela peuvent être trouvées dans Field RDB$VALID_BLR.

Warning

Si vous avez déjà une fonction externe de style Legacy (DECLARE EXTERNAL FUNCTION), l’instruction ALTER FUNCTION la transformera en fonction normale sans aucun avertissement. Ceci a été fait intentionnellement pour faciliter la migration vers le nouveau style d’écriture des fonctions externes connu sous le nom d’UDR.

Qui peut modifier une fonction ?

L’instruction ALTER FUNCTION peut être exécutée :

Administrateurs
Le propriétaire de la fonction stockée ;
Les utilisateurs ayant le privilège ALTER ANY FUNCTION.

Exemples

Example 1. Modification d’une fonction stockée

ALTER FUNCTION ADD_INT(A INT, B INT, C INT)
RETURNS INT
AS
BEGIN
  RETURN A+B+C;
END

Voir aussi :

CREATE FUNCTION,CREATE OR ALTER FUNCTION,DROP FUNCTION.

`CREATE OR ALTER FUNCTION`

affectation

Créez une nouvelle fonction stockée ou modifiez une fonction stockée existante.

Disponible en

DSQL

Syntaxe

CREATE OR ALTER FUNCTION funcname
[(<inparam> [, <inparam> ...])]
RETURNS <type> [COLLATE collation]
[DETERMINISTIC]
<routine-body>

Pour plus d'informations, voir. CREATE FUNCTION.

L’instruction CREATE OR ALTER FUNCTION crée une nouvelle fonction stockée ou modifie une fonction stockée existante. Si la fonction stockée n’existe pas, elle sera créée à l’aide de l’instruction CREATE FUNCTION. Si elle existe déjà, elle sera modifiée et recompilée, tout en préservant les privilèges et dépendances existants.

Warning

Si vous avez déjà une fonction externe de style Legacy (DECLARE EXTERNAL FUNCTION), l’instruction CREATE OR ALTER FUNCTION la transformera en fonction normale sans aucun avertissement. Ceci a été fait intentionnellement pour faciliter la migration vers le nouveau style d’écriture des fonctions externes connu sous le nom d’UDR.

Exemples

Example 1. Créer une nouvelle fonction stockée ou modifier une fonction stockée existante

CREATE OR ALTER FUNCTION ADD_INT(A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
  RETURN A+B;
END

Voir aussi :

CREATE FUNCTION, ALTER FUNCTION.

`DROP FUNCTION`

affectation

Suppression d’une fonction stockée.

Disponible en

DSQL

Syntaxe

DROP FUNCTION funcname

Table 1. Paramètres de la fonction`DROP FUNCTION`
Paramètre	Description
funcname	Le nom de la fonction stockée.

L’instruction DROP FUNCTION supprime une fonction stockée existante. S’il y a des dépendances sur une fonction stockée, une erreur sera générée lorsque vous essayez de supprimer une telle fonction.

Qui peut supprimer une fonction ?

L’instruction DROP FUNCTION peut être exécutée :

Administrateurs
Le propriétaire de la fonction stockée ;
Les utilisateurs ayant le privilège DROP ANY FUNCTION.

Exemples

Example 1. Suppression d’une fonction stockée

DROP FUNCTION ADD_INT;

Voir aussi :

CREATE FUNCTION.

`RECREATE FUNCTION`

affectation

Créez une nouvelle fonction stockée ou recréez une fonction stockée existante.

Disponible en

DSQL

Syntaxe

RECREATE FUNCTION funcname
[(<inparam> [, <inparam> ...])]
RETURNS <type> [COLLATE collation]
[DETERMINISTIC]
<routine-body>

Pour plus d'informations, voir. CREATE FUNCTION

L’instruction RECREATE FUNCTION crée une nouvelle fonction stockée ou recrée une fonction existante. Si une fonction portant ce nom existe déjà, l’instruction tentera de la supprimer et de créer une nouvelle fonction. L’opération échouera lors de la confirmation de la transaction si la fonction a des dépendances.

Note	Notez que les erreurs de dépendance ne sont pas détectées avant la phase de confirmation de la transaction.

Après la recréation d’une fonction, les privilèges d’exécution de la fonction stockée et les privilèges de la fonction stockée elle-même ne sont pas conservés.

Exemples

Example 1. Création ou recréation d’une fonction stockée

RECREATE FUNCTION ADD_INT(A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
  RETURN A+B;
END

Voir aussi :

CREATE FUNCTION, DROP FUNCTION.