FirebirdSQL logo
 TRIGGERFUNCTION 

Une procédure stockée (SP) est un module logiciel qui peut être appelé depuis le client, une autre procédure, une fonction, un bloc exécutable ou un déclencheur. Les procédures stockées, les fonctions stockées, les blocs exécutables et les déclencheurs sont écrits en langage procédural SQL (PSQL). La plupart des instructions SQL sont également disponibles dans PSQL, parfois avec des restrictions ou des extensions. Les exceptions notables sont les déclarations DDL et de gestion des transactions.

Les procédures stockées peuvent accepter et renvoyer de nombreux paramètres.

CREATE PROCEDURE

Objectif

Créez une nouvelle procédure stockée.

Disponible en

DSQL, ESQL

Syntaxe
CREATE PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine body>

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

<outparam>  ::=  <param_decl>

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

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

<type> ::= <datatype> | [TYPE OF] domain_name | 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-spec>

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

procname

Le nom de la procédure stockée. Peut contenir jusqu’à 63 caractères.

inparam

Description du paramètre d’entrée.

outparam

Description du paramètre de sortie.

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

extbody

Le corps de la procédure externe. Une chaîne littérale qui peut être utilisée par l’UDR à diverses fins.

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.

engine

Nom du moteur pour utiliser les fonctions externes. En général, le nom de l’UDR est spécifié.

datatype

Le 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

Nom de la colonne de la table ou de la vue.

L’instruction CREATE PROCEDURE crée une nouvelle procédure stockée. Le nom de la procédure stockée doit être unique parmi les noms de toutes les procédures stockées, tables et vues de la base de données.

Note

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

CREATE PROCEDURE est une déclaration composite composée d’un en-tête et d’un corps.

L’en-tête définit le nom de la procédure stockée et déclare les paramètres d’entrée et de sortie s’ils doivent être renvoyés par la procédure.

Le corps de la procédure est constitué de déclarations facultatives de variables locales, de sous-routines et de curseurs nommés, et d’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 les curseurs nommés ainsi que les instructions internes doivent se terminer par un point-virgule (“;”).

Opérateur de terminaison

Certains éditeurs d’instructions SQL — notamment l’utilitaire isql de la suite Firebird, et peut-être certains éditeurs tiers - utilisent une convention interne qui exige que toutes les déclarations 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 de la brochure titré Changer la terminaison dans `isql.

Paramètres

Chaque paramètre a un type de données. Il est également possible de spécifier une restriction NOT NULL pour un paramètre, interdisant ainsi de lui passer une valeur NULL.

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

Paramètres d’entrée

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

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

Paramètres de sortie

La clause optionnelle RETURNS vous permet de spécifier une liste de paramètres de sortie pour la procédure stockée.

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

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

Si la clause TYPE OF est utilisée en plus devant un nom de domaine, seul le type de données du domaine est utilisé — ses contraintes NOT NULL et CHECK (si elles existent) ne sont pas vérifiées et sa valeur par défaut n’est pas utilisée. Si le domaine est un type de texte, son jeu de caractères et son ordre de tri sont toujours utilisés.

Utilisation d’un 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 du nom de la colonne séparés 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 contraintes et les valeurs par défaut de la colonne ne sont jamais utilisées.

Droits et privilèges d’exécution

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

Après le mot clé As suit le corps de la procédure stockée.

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

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

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

Procédures stockées externes

La procédure stockée peut être située dans un module externe. Dans ce cas, au lieu du corps de la procédure, son emplacement dans le module externe est spécifié avec la phrase EXTERNAL NAME. L’argument de cette phrase est une chaîne de caractères contenant le nom du module externe, le nom de la procédure à l’intérieur du module et les informations définies par l’utilisateur, séparés par un séparateur. La clause ENGINE spécifie le nom du moteur pour la gestion des connexions de modules externes. Dans Firebird, le moteur UDR est utilisé pour gérer les modules externes. Le mot-clé AS peut être suivi d’une chaîne littérale — le "corps" d’une procédure externe, il peut être utilisé par un module externe à des fins diverses.

Qui peut créer une procédure stockée ?

L’instruction `CREATE PROCEDURE' peut être exécutée :

L’utilisateur qui a créé la procédure stockée devient son propriétaire.

Exemples

Example 1. Création d’une procédure stockée
CREATE PROCEDURE ADD_BREED (
  NAME D_BREEDNAME, /* Les caractéristiques du domaine sont héritées */
  NAME_EN TYPE OF D_BREEDNAME, /* Seul le type de domaine est hérité */
  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME, /* Hérite du type de colonne de table */
  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
  CODE_ANIMAL INT NOT NULL DEFAULT 1
)
RETURNS (
  CODE_BREED INT
)
AS
BEGIN
  INSERT INTO BREED (
    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
  VALUES (
    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
  RETURNING CODE_BREED INTO CODE_BREED;
END

Idem, mais la procédure sera exécutée avec les droits de l’utilisateur qui la définit (propriétaire de la procédure).

CREATE PROCEDURE ADD_BREED (
  NAME D_BREEDNAME, /* Les caractéristiques du domaine sont héritées */
  NAME_EN TYPE OF D_BREEDNAME, /* Seul le type de domaine est hérité */
  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME, /* Hérite du type de colonne de table */
  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
  CODE_ANIMAL INT NOT NULL DEFAULT 1
)
RETURNS (
  CODE_BREED INT
)
SQL SECURITY DEFINER
AS
BEGIN
  INSERT INTO BREED (
    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
  VALUES (
    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
  RETURNING CODE_BREED INTO CODE_BREED;
END
Example 2. Création d’une procédure stockée externe
Créer une procédure située dans un module externe (UDR). L’implémentation de la procédure se trouve dans le module externe udrcpp_example. Le nom de la procédure à l’intérieur du module est gen_rows. 
CREATE PROCEDURE gen_rows (
    start_n INTEGER NOT NULL,
    end_n INTEGER NOT NULL
) RETURNS (
    n INTEGER NOT NULL
)
    EXTERNAL NAME 'udrcpp_example!gen_rows'
    ENGINE udr;
Voir aussi :

CREATE OR ALTER PROCEDURE, ALTER PROCEDURE, RECREATE PROCEDURE, DROP PROCEDURE.

ALTER PROCEDURE

Utilisation

Pour modifier une procédure stockée existante.

Disponible en

DSQL, ESQL

Syntaxe
ALTER PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine-body>

Pour plus de détails, voir CREATE PROCEDURE.

L’instruction `ALTER PROCEDURE' vous permet de modifier la composition et les caractéristiques des paramètres d’entrée et de sortie, des variables locales, des curseurs nommés et du corps de la procédure stockée. Pour les procédures externes (UDR), vous pouvez modifier le point d’entrée et le nom du moteur. Les privilèges et dépendances existants sont conservés après l’exécution.

Warning

Soyez prudent lorsque vous modifiez le nombre et les types de paramètres d’entrée et de sortie des procédures stockées. Le code d’application existant peut devenir inapplicable parce que le format d’appel de procédure est incompatible avec la nouvelle description des paramètres. En outre, les modules PSQL qui utilisent la procédure stockée modifiée peuvent devenir invalides. Des informations sur la manière de détecter cela peuvent être trouvées dans l’application Field RDB$VALID_BLR.

Qui peut modifier une procédure stockée ?

L’instruction `ALTER PROCEDURE' peut être exécutée :

  • administrators.

  • Le propriétaire de la procédure stockée ;

  • Les utilisateurs avec le privilège ALTER ANY PROCEDURE.

Exemples

Example 1. Modification d’une procédure stockée
ALTER PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
          PROJ_ID
      FROM
          EMPLOYEE_PROJECT
      WHERE
          EMP_NO = :emp_no
      INTO :proj_id
  DO
    SUSPEND;
END
Voir aussi :

CREATE PROCEDURE, CREATE OR ALTER PROCEDURE, RECREATE PROCEDURE, DROP PROCEDURE.

CREATE OR ALTER PROCEDURE

Objectif

Pour créer une nouvelle procédure stockée ou modifier une procédure existante.

Disponible en

DSQL, ESQL

Syntaxe
CREATE OR ALTER PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine-body>

Pour plus de détails, voir CREATE PROCEDURE.

L’instruction CREATE OR ALTER PROCEDURE crée une nouvelle procédure stockée ou modifie une procédure existante. Si la procédure stockée n’existe pas, elle sera créée en utilisant la proposition CREATE PROCEDURE. S’il existe déjà, il sera modifié et compilé avec les privilèges et dépendances existants préservés.

Exemples

Example 1. Création ou modification d’une procédure stockée
CREATE OR ALTER PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
          PROJ_ID
      FROM
          EMPLOYEE_PROJECT
      WHERE
          EMP_NO = :emp_no
      INTO :proj_id
  DO
    SUSPEND;
END
Voir aussi :

CREATE PROCEDURE, ALTER PROCEDURE, RECREATE PROCEDURE, DROP PROCEDURE.

DROP PROCEDURE

Destination

Suppression d’une procédure stockée existante.

Disponible en

DSQL, ESQL

Syntaxe
DROP PROCEDURE procname
Table 1. Paramètres de la fonction DROP PROCEDURE
Paramètre Description

procname

Le nom de la procédure stockée.

L’instruction DROP PROCEDURE supprime une procédure stockée existante. Si des dépendances existent sur la procédure stockée, une erreur sera générée si une tentative est faite pour supprimer une telle procédure.

Qui peut supprimer une procédure stockée ?

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

  • <<fblangrefre-security-administrators,Administrateurs

  • Le propriétaire de la procédure stockée ;

  • Utilisateurs avec le privilège DROP ANY PROCEDURE.

Exemples

Example 1. Suppression d’une procédure stockée
DROP PROCEDURE GET_EMP_PROJ;
Voir aussi :

CREATE PROCEDURE, RECREATE PROCEDURE.

RECREATE PROCEDURE

Objectif

Pour créer une nouvelle procédure stockée ou recréer une procédure existante.

Disponible en

DSQL, ESQL

Syntaxe
RECREATE PROCEDURE procname [(<inparam> [, <inparam> ...])]
[ RETURNS (<outparam> [, <outparam> ...]) ]
<routine-body>

Pour plus de détails, voir CREATE PROCEDURE.

L’instruction `RECREATE PROCEDURE' crée une nouvelle procédure stockée ou recrée une procédure existante. Si une procédure portant ce nom existe déjà, l’opérateur tentera de la supprimer et de créer une nouvelle procédure. L’opération échouera lors de la confirmation de la transaction si la procédure 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.

Une fois que la procédure a été recréée, les privilèges d’exécution de la procédure stockée et les privilèges de la procédure stockée elle-même ne sont pas conservés.

Exemples

Example 1. Création d’une nouvelle procédure stockée ou recréation d’une procédure existante
RECREATE PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
          PROJ_ID
      FROM
          EMPLOYEE_PROJECT
      WHERE
          EMP_NO = :emp_no
      INTO :proj_id
  DO
    SUSPEND;
END
Voir aussi :

CREATE PROCEDURE, CREATE OR ALTER PROCEDURE, DROP PROCEDURE.