FirebirdSQL logo

Fonctions externes

Syntaxe
{CREATE [OR ALTER] | RECREATE} FUNCTION funcname [(<inparam> [, <inparam> ...])]
RETURNS <type> [COLLATE collation] [DETERMINISTIC]
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]

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

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

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

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

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

<datatype> ::=
    {SMALLINT | INT[EGER] | BIGINT}
  | BOOLEAN
  | {FLOAT | DOUBLE PRECISION}
  | {DATE | TIME | TIMESTAMP}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [CHARACTER SET charset]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(size)]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset]
  | BLOB [(seglen [, subtype_num])]

Tous les paramètres d’une fonction externe peuvent être modifiés à l’aide de la fonction ALTER statement FUNCTION.

Syntaxe
ALTER FUNCTION funcname [(<inparam> [, <inparam> ...])]
RETURNS <type> [COLLATE collation] [DETERMINISTIC]
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]

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

Vous pouvez supprimer une fonction externe en utilisant l’instruction DROP FUNCTION.

Syntaxe
DROP FUNCTION funcname
Table 1. Quelques paramètres de la fonction externe
Paramètres Description

funcname

Nom de la fonction stockée. Peut contenir jusqu’à 31 octets.

inparam

Description du paramètre d’entrée.

module name

Nom du module externe où réside la fonction.

routine name

Le nom interne de la fonction dans le module externe.

misc info

Informations définies par l’utilisateur à transmettre au module externe de la fonction.

engine

Nom du moteur pour l’utilisation des fonctions externes. Spécifie généralement le nom de l’UDR.

extbody

Corps de la fonction externe. Une chaîne de caractères littérale qui peut être utilisée par l’UDR à diverses fins.

Nous ne décrirons pas ici la syntaxe des paramètres d’entrée et du résultat de sortie. Elle correspond entièrement à la syntaxe des fonctions PSQL ordinaires, qui est décrite en détail dans le manuel du langage SQL. En revanche, nous donnons des exemples de déclaration de fonctions externes avec des explications.

CREATE FUNCTION sum_args (
    n1 INTEGER,
    n2 INTEGER,
    n3 INTEGER
)
RETURNS INTEGER
EXTERNAL NAME 'udrcpp_example!sum_args'
ENGINE UDR;

L’implémentation de la fonction se trouve dans le module udrcpp_example. Dans ce module, la fonction est enregistrée sous le nom sum_args. Le moteur UDR est utilisé pour faire fonctionner la fonction externe.

CREATE OR ALTER FUNCTION regex_replace (
  regex VARCHAR(60),
  str VARCHAR(60),
  replacement VARCHAR(60)
)
RETURNS VARCHAR(60)
EXTERNAL NAME 'org.firebirdsql.fbjava.examples.fbjava_example.FbRegex.replace(
      String, String, String)'
ENGINE java;

L’implémentation de la fonction est située dans la fonction statique replace de la classe org.firebirdsql.fbjava.examples.fbjava_example.FbRegex. Le moteur Java est utilisé pour exécuter la fonction externe.

Procédures externes

Syntaxe
{CREATE [OR ALTER] | RECREATE} PROCEDURE procname [(<inparam> [, <inparam> ...])]
RETURNS (<outparam> [, <outparam> ...])
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]

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

<outparam>  ::=  <param_decl>

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

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

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

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

<datatype> ::=
    {SMALLINT | INT[EGER] | BIGINT}
  | BOOLEAN
  | {FLOAT | DOUBLE PRECISION}
  | {DATE | TIME | TIMESTAMP}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [CHARACTER SET charset]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(size)]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset]
  | BLOB [(seglen [, subtype_num])]

Tous les paramètres d’une procédure externe peuvent être modifiés à l’aide de l’instruction ALTER PROCEDURE.

Syntax
ALTER PROCEDURE procname [(<inparam> [, <inparam> ...])]
RETURNS (<outparam> [, <outparam> ...])
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]

Vous pouvez supprimer une procédure externe en utilisant l’instruction DROP PROCEDURE.

Syntaxe
DROP PROCEDURE procname
Table 1. Quelques paramètres de la procédure externe
Paramètres Description

procname

Nom de la procédure stockée. Peut contenir jusqu’à 31 octets.

inparam

Description du paramètre d’entrée.

outparam

Description of the output parameter.

module name

Le nom du module externe dans lequel se trouve la procédure.

routine name

Nom interne de la procédure dans le module externe.

misc info

Informations définies par l’utilisateur à transmettre à la procédure du module externe.

engine

Nom du moteur pour l’utilisation des procédures externes. Spécifie généralement le nom de l’UDR.

extbody

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

Nous ne décrirons pas ici la syntaxe des paramètres d’entrée et de sortie. Elle est parfaitement cohérente avec la syntaxe des procédures PSQL ordinaires, qui est décrite en détail dans le manuel du langage SQL. Nous allons plutôt prendre des exemples de déclaration de procédures externes avec des explications.

CREATE PROCEDURE gen_rows_pascal (
    start_n INTEGER not null,
    end_n INTEGER not null
)
RETURNS (
    RESULT INTEGER not null
)
EXTERNAL NAME 'pascaludr!gen_rows'
ENGINE UDR;

L’implémentation de la fonction se trouve dans le module pascaludr. Dans ce module, la procédure est enregistrée sous le nom gen_rows. Le moteur UDR y est utilisé pour exécuter la procédure externe.

CREATE OR ALTER PROCEDURE write_log (
  message VARCHAR(100)
)
EXTERNAL NAME 'pascaludr!write_log'
ENGINE UDR;

L’implémentation de la fonction se trouve dans le module pascaludr. Dans ce module, la procédure est enregistrée sous le nom write_log. Le moteur UDR est utilisé pour exécuter la procédure externe.

CREATE OR ALTER PROCEDURE employee_pgsql (
  -- Firebird 3.0.0 présente un bogue avec les procédures externes sans paramètres
  dummy INTEGER = 1
)
RETURNS (
  id TYPE OF COLUMN employee.id,
  name TYPE OF COLUMN employee.name
)
EXTERNAL NAME 'org.firebirdsql.fbjava.examples.fbjava_example.FbJdbc
    .executeQuery()!jdbc:postgresql:employee|postgres|postgres'
ENGINE java
AS 'select * from employee';

L’implémentation de la fonction se trouve dans la fonction statique executeQuery de la classe org.firebirdsql.fbjava.examples.fbjava_example.FbJdbc. Le point d’exclamation " !" contient des informations sur la connexion à une base de données externe via JDBC. Le moteur Java est utilisé pour exécuter la fonction externe. Ici, dans le "corps" de la procédure externe, une requête SQL est transmise pour récupérer les données.

Note

Cette procédure utilise un stub qui passe un paramètre inutilisé. Ceci est dû au fait que dans Firebird 3.0 il y a un bogue avec le traitement des procédures externes sans paramètres.

Placement de procédures et de fonctions externes à l’intérieur de paquets

Un groupe de procédures et de fonctions apparentées est commodément placé dans des paquets PSQL. Les paquets peuvent contenir des procédures et des fonctions psql externes et conventionnelles.

Syntaxe
{CREATE [OR ALTER] | RECREATE} PACKAGE package_name
AS
BEGIN
  [<package_item> ...]
END

{CREATE | RECREATE} PACKAGE BODY package_name
AS
BEGIN
  [<package_item> ...]
  [<package_body_item> ...]
END

<package_item> ::=
    <function_decl>;
  | <procedure_decl>;

<function_decl> ::=
  FUNCTION func_name [(<in_params>)]
  RETURNS <type> [COLLATE collation]
  [DETERMINISTIC]

<procedure_decl> ::=
  PROCEDURE proc_name [(<in_params>)]
  [RETURNS (<out_params>)]

<package_body_item> ::=
    <function_impl>
  | <procedure_impl>

<function_impl> ::=
  FUNCTION func_name [(<in_impl_params>)]
  RETURNS <type> [COLLATE collation]
  [DETERMINISTIC]
  <routine body>

<procedure_impl> ::=
  PROCEDURE proc_name [(<in_impl_params>)]
  [RETURNS (<out_params>)]
  <routine body>

<routine body> ::= <sql routine body> | <external body reference>

<sql routine body> ::=
  AS
    [<declarations>]
  BEGIN
    [<PSQL_statements>]
  END

<declarations> ::= <declare_item> [<declare_item> ...]

<declare_item> ::=
    <declare_var>;
  | <declare_cursor>;
  | <subroutine declaration>;
  | <subroutine implimentation>

<subroutine declaration> ::= <subfunc_decl> | <subproc_decl>

<subroutine implimentation> ::= <subfunc_impl> | <subproc_impl>

<external body reference> ::=
  EXTERNAL NAME <extname> ENGINE <engine> [AS <extbody>]

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

Pour les procédures et fonctions externes, l’en-tête du paquet spécifie le nom, les paramètres d’entrée, leurs types, les valeurs par défaut et les paramètres de sortie. Dans le corps du paquet, tout est identique, à l’exception des valeurs par défaut, ainsi que de l’emplacement dans le module externe (déclaration EXTERNAL NAME), du nom du moteur et éventuellement du "corps" de la procédure/fonction.

Supposons que vous ayez écrit un UDR pour travailler avec des expressions régulières, qui se trouve dans un module externe (bibliothèque dynamique) PCRE, et que vous ayez plusieurs autres UDR qui effectuent d’autres tâches. Si nous n’utilisions pas les paquets PSQL, toutes nos procédures et fonctions externes seraient entremêlées les unes avec les autres et avec les procédures et fonctions PSQL normales. Il est donc difficile de trouver les dépendances et d’apporter des modifications aux modules externes, ce qui crée une certaine confusion et oblige à utiliser au moins des préfixes pour regrouper les procédures et les fonctions.

SET TERM ^;

CREATE OR ALTER PACKAGE REGEXP
AS
BEGIN
  PROCEDURE preg_match(
      APattern VARCHAR(8192), ASubject VARCHAR(8192))
    RETURNS (Matches VARCHAR(8192));

  FUNCTION preg_is_match(
      APattern VARCHAR(8192), ASubject VARCHAR(8192))
    RETURNS BOOLEAN;

  FUNCTION preg_replace(
      APattern VARCHAR(8192),
      AReplacement VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS VARCHAR(8192);

  PROCEDURE preg_split(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS (Lines VARCHAR(8192));

  FUNCTION preg_quote(
      AStr VARCHAR(8192),
      ADelimiter CHAR(10) DEFAULT NULL)
    RETURNS VARCHAR(8192);
END^

RECREATE PACKAGE BODY REGEXP
AS
BEGIN
  PROCEDURE preg_match(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS (Matches VARCHAR(8192))
    EXTERNAL NAME 'PCRE!preg_match' ENGINE UDR;

  FUNCTION preg_is_match(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS BOOLEAN
  AS
  BEGIN
    RETURN EXISTS(
      SELECT * FROM preg_match(:APattern, :ASubject));
  END

  FUNCTION preg_replace(
      APattern VARCHAR(8192),
      AReplacement VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS VARCHAR(8192)
    EXTERNAL NAME 'PCRE!preg_replace' ENGINE UDR;

  PROCEDURE preg_split(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS (Lines VARCHAR(8192))
    EXTERNAL NAME 'PCRE!preg_split' ENGINE UDR;

  FUNCTION preg_quote(
      AStr VARCHAR(8192),
      ADelimiter CHAR(10))
    RETURNS VARCHAR(8192)
    EXTERNAL NAME 'PCRE!preg_quote' ENGINE UDR;
END^

SET TERM ;^

Déclencheurs externes

Syntaxe
{CREATE [OR ALTER] | RECREATE} TRIGGER trigname
{
    <relation_trigger_legacy>
  | <relation_trigger_sql2003>
  | <database_trigger>
  | <ddl_trigger>
}
<external-body>

<external-body> ::=
  EXTERNAL NAME <extname> ENGINE <engine>
  [AS <extbody>]

<relation_trigger_legacy> ::=
  FOR {tablename | viewname}
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  [POSITION number]

<relation_trigger_sql2003> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  [POSITION number]
  ON {tablename | viewname}

<database_trigger> ::=
  [ACTIVE | INACTIVE]
  ON db_event
  [POSITION number]

<ddl_trigger> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <ddl_events>
  [POSITION number]

<mutation_list> ::= <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= INSERT | UPDATE | DELETE

<db_event> ::=
    CONNECT
  | DISCONNECT
  | TRANSACTION START
  | TRANSACTION COMMIT
  | TRANSACTION ROLLBACK


<ddl_events> ::=
    ANY DDL STATEMENT
  | <ddl_event_item> [{OR <ddl_event_item>} ...]

<ddl_event_item> ::=
    CREATE TABLE | ALTER TABLE | DROP TABLE
  | CREATE PROCEDURE | ALTER PROCEDURE | DROP PROCEDURE
  | CREATE FUNCTION | ALTER FUNCTION | DROP FUNCTION
  | CREATE TRIGGER | ALTER TRIGGER | DROP TRIGGER
  | CREATE EXCEPTION | ALTER EXCEPTION | DROP EXCEPTION
  | CREATE VIEW | ALTER VIEW | DROP VIEW
  | CREATE DOMAIN | ALTER DOMAIN | DROP DOMAIN
  | CREATE ROLE | ALTER ROLE | DROP ROLE
  | CREATE SEQUENCE | ALTER SEQUENCE | DROP SEQUENCE
  | CREATE USER | ALTER USER | DROP USER
  | CREATE INDEX | ALTER INDEX | DROP INDEX
  | CREATE COLLATION | DROP COLLATION
  | ALTER CHARACTER SET
  | CREATE PACKAGE | ALTER PACKAGE | DROP PACKAGE
  | CREATE PACKAGE BODY | DROP PACKAGE BODY
  | CREATE MAPPING | ALTER MAPPING | DROP MAPPING

Un déclencheur externe peut être modifié avec l’instruction ALTER TRIGGER.

Syntaxe
ALTER TRIGGER trigname {
[ACTIVE | INACTIVE]
[
    {BEFORE | AFTER} {<mutation_list> | <ddl_events>}
  | ON db_event
]
[POSITION number]
[<external-body>]

<external-body> ::=
  EXTERNAL NAME <extname> ENGINE <engine>
  [AS <extbody>]

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

<mutation_list> ::= <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= { INSERT | UPDATE | DELETE }

Vous pouvez supprimer un déclencheur externe en utilisant l’instruction DROP TRIGGER.

Syntaxe
DROP TRIGGER trigname
Table 1. Quelques paramètres de déclencheur externe
Paramètres Description

trigname

Nom du déclencheur. Peut contenir jusqu’à 31 octets.

relation_trigger_legacy

Déclaration de déclencheur de table (héritée).

relation_trigger_sql2003

Déclaration de déclencheur de table selon la norme SQL-2003.

database_trigger

Déclaration d’un trigger de base de données.

ddl_trigger

Déclaration de déclencheur DDL.

tablename

Nom de la table.

viewname

Le nom de la vue.

mutation_list

Liste des événements de la table.

mutation

Un des événements de la table.

db_event

Événement de connexion ou de transaction.

ddl_events

Liste des événements de modification des métadonnées.

ddl_event_item

L’un des événements de modification des métadonnées.

number

Ordre dans lequel le déclencheur se déclenche. De 0 à 32767.

extbody

Corps du déclencheur externe. Une chaîne littérale qui peut être utilisée par l’UDR à diverses fins.

module name

Nom du module externe où se trouve le déclencheur.

routine name

Nom interne du déclencheur dans le module externe.

misc info

Informations définies par l’utilisateur à transmettre au module externe de déclenchement.

engine

Nom du moteur pour utiliser les déclencheurs externes. Il s’agit généralement du nom de l’UDR.

Voici des exemples de déclaration de déclencheurs externes accompagnés d’explications.

CREATE DATABASE 'c:\temp\slave.fdb';

CREATE TABLE persons (
    id INTEGER NOT NULL,
    name VARCHAR(60) NOT NULL,
    address VARCHAR(60),
    info BLOB SUB_TYPE TEXT
);

COMMIT;

CREATE DATABASE 'c:\temp\master.fdb';

CREATE TABLE persons (
    id INTEGER NOT NULL,
    name VARCHAR(60) NOT NULL,
    address VARCHAR(60),
    info BLOB SUB_TYPE TEXT
);

CREATE TABLE replicate_config (
    name VARCHAR(31) NOT NULL,
    data_source VARCHAR(255) NOT NULL
);

INSERT INTO replicate_config (name, data_source)
   VALUES ('ds1', 'c:\temp\slave.fdb');

CREATE TRIGGER persons_replicate
AFTER INSERT ON persons
EXTERNAL NAME 'udrcpp_example!replicate!ds1'
ENGINE UDR;

L’implémentation du trigger se trouve dans le module udrcpp_example. Dans ce module, le déclencheur est enregistré sous le nom replicate. Le moteur UDR est utilisé pour faire fonctionner le déclencheur externe.

Le lien vers le module externe utilise un paramètre supplémentaire ds1, selon lequel, dans le déclencheur externe, la configuration pour la connexion à la base de données externe est lue à partir de la table replicate_config.