FirebirdSQL logo
 Instructions de procédure SQL (PSQL)Fonctions agrégées 

HEX_ENCODE()

Disponible en

DSQL, PSQL

Syntaxe
HEX_ENCODE (binary_data)
Table 1. paramètres de fonction HEX_ENCODE
Paramètre Description

binary_data

Données binaires pour le codage
Le type de résultat renvoyé :

VARCHAR CHARACTER SET ASCII ou BLOB SUB_TYPE TEXT CHARACTER SET ASCII

La fonction HEX_ENCODE encode les données binaires avec un nombre hexadécimal et retourne la valeur encodée sous forme de VARCHAR CHARACTER SET ASCII ou de BLOB SUB_TYPE TEXT CHARACTER SET ASCII selon l’argument d’entrée.

Lorsque l’argument d’entrée n’est pas BLOB, la longueur du type résultant est calculée comme type_length * 2, où type_length est la longueur maximale en octets du type de l’argument d’entrée.

Exemple HEX_ENCODE

Example 1. Utilisation de la fonction HEX_ENCODE
select hex_encode('Hexadecimal')
from rdb$database;
HEX_ENCODE
======================
48657861646563696D616C
Voir aussi :

[fblangref-scalarfuncs-hexdecode], [fblangref-scalarfuncs-base64encode]

docnext count = 38

Fonctions pour travailler avec la date et l’heure

DATEADD()

Disponible en

DSQL, PSQL

Syntaxe
DATEADD (<args>)

<args> ::= <amount> <unit> TO <datetime>
         | <unit>, <amount>, <datetime>

<unit> ::=
    YEAR | MONTH | WEEK | DAY | WEEKDAY | YEARDAY
  | HOUR | MINUTE | SECOND | MILLISECOND
Table 1. paramètres de fonction DATEADD
Paramètre Description

amount

Une expression de type SMALLINT, INTEGER, BIGINT ou NUMERIC (le négatif est soustrait).

unit

Composante date/heure.

datetime

Une expression de type DATE, TIME ou TIMESTAMP.
type de résultat de retour

DATE, TIME ou TIMESTAMP.

La fonction DATEADD vous permet d’ajouter un nombre donné d’années, de mois, de semaines, d’heures, de minutes, de secondes, de millisecondes à une valeur de date/heure donnée.

Note

  • Avec un argument de type TIMESTAMP et DATE, n’importe quel composant date/heure <unit> peut être utilisé ;

  • Seuls HOUR, MINUTE, SECOND et MILLISECOND peuvent être utilisés pour le type de données TIME.

Exemple DATEADD

Example 1. Utilisation de la fonction DATEADD
DATEADD (28 DAY TO CURRENT_DATE)
DATEADD (-6 HOUR TO CURRENT_TIME)
DATEADD (MONTH, 9, DATEOFCONCEPTION)
DATEADD (-38 WEEK TO DATEOFBIRTH)
DATEADD (MINUTE, 90, CAST('NOW' AS TIME))
DATEADD (? YEAR TO DATE '11-SEP-1973')
SELECT
  CAST(DATEADD(-1 * EXTRACT(MILLISECOND FROM ts) MILLISECOND TO ts) AS VARCHAR(30)) AS t,
  EXTRACT(MILLISECOND FROM ts) AS ms
FROM (
    SELECT TIMESTAMP'2014-06-09 13:50:17.4971' as ts
    FROM RDB$DATABASE
) a
T                             MS
------------------------------------
2014-06-09 13:50:17.0000	497.1
Voir aussi :

[fblangref-scalarfuncs-datediff],[fblangref-datatypes-datetimeops].

DATEDIFF()

Disponible en

DSQL, PSQL

Syntaxe
DATEDIFF (<args>)

<args> ::= <unit> FROM <moment_1> TO <moment_2>
         | <unit>, <moment_1>, <moment_2>

<unit> ::=
    YEAR | MONTH | WEEK | DAY | WEEKDAY | YEARDAY
  | HOUR | MINUTE | SECOND | MILLISECOND
Table 1. paramètres de fonction DATEDIFF
Paramètre Description

unit

Composante date/heure.

monent_1

Une expression de type DATE, TIME ou TIMESTAMP.

monent_2

Une expression de type DATE, TIME ou TIMESTAMP.
Le type de résultat renvoyé :

BIGINT

La fonction DATEDIFF renvoie le nombre d’années, de mois, de semaines, de jours, d’heures, de minutes, de secondes ou de millisecondes entre deux valeurs de date/heure.

Caractéristiques d’utilisation :

  • Les paramètres DATE et TIMESTAMP peuvent être utilisés ensemble. La co-utilisation du type TIME avec le type DATE et TIMESTAMP n’est pas autorisée ;

  • Avec un argument de type TIMESTAMP et DATE, n’importe quel composant date/heure <unit> peut être utilisé ;

  • Seuls HOUR, MINUTE, SECOND et MILLISECOND peuvent être utilisés pour le type de données TIME.

Note

  • La fonction DATEDIFF ne vérifie pas les différences dans les composants de date/heure plus petits que ceux spécifiés dans le premier argument <unit>. Le résultat est :

    • DATEDIFF (YEAR, DATE '1-JAN-2009', DATE '31-DEC-2009') retournera 0, mais

    • DATEDIFF (YEAR, DATE '31-DEC-2009', DATE '1-JAN-2010') retournera 1

  • Cependant, pour les plus petits composants de date/heure, nous avons :

    • DATEDIFF (DAY, DATE '26-JUN-1908', DATE '11-SEP-1973') retournera 23818

    • DATEDIFF (DAY, DATE '30-NOV-1971', DATE '8-JAN-1972') retourne 39

  • Une valeur négative de la fonction indique que la date/heure du moment_2 est inférieure à celle du moment_1.

Exemple DATEDIFF

Example 1. Utilisation de la fonction DATEDIFF
DATEDIFF (HOUR FROM CURRENT_TIMESTAMP TO TIMESTAMP '12-JUN-2059 06:00')
DATEDIFF (MINUTE FROM TIME '0:00' TO CURRENT_TIME)
DATEDIFF (MONTH, CURRENT_DATE, DATE '1-1-1900')
DATEDIFF (DAY FROM CURRENT_DATE TO CAST (? AS DATE))
Voir aussi :

[fblangref-scalarfuncs-dateadd],[fblangref-datatypes-datetimeops].

EXTRACT()

Disponible en

DSQL, PSQL

Syntaxe
EXTRACT (<part> FROM <datetime>)

<part> ::=
    YEAR | MONTH | WEEK | DAY | WEEKDAY | YEARDAY
  | HOUR | MINUTE | SECOND | MILLISECOND
  | TIMEZONE_HOUR | TIMEZONE_MINUTE
Table 1. paramètres de fonction EXTRACT
Paramètre Description

part

Composante date/heure.

datetime

Une expression de type DATE, TIME ou TIMESTAMP.
Le type de résultat renvoyé :

SMALLINT ou NUNERIC.

La fonction EXTRACT extrait les composants de date et d’heure des types de données DATE, TIME et TIMESTAMP.

Table 2. Types et plages de résultats de fonctions EXTRACT
Composant date/heure Type Plage Commentaire

YEAR

SMALLINT

1–9999

L’année

QUARTER

SMALLINT

1-4

Trimestre

MONTH

SMALLINT

1–12

Mois

WEEK

SMALLINT

1–53

Numéro de la semaine de l’année

DAY

SMALLINT

1–31

Jour

WEEKDAY

SMALLINT

0–6

Jour de la semaine. 0 — Dimanche

YEARDAY

SMALLINT

0–365

Le numéro du jour de l’année. 0 = 1 janvier

HOUR

SMALLINT

0–23

Heure

MINUTE

SMALLINT

0–59

Minute

SECOND

NUMERIC(9,4)

0.0000–59.9999

Secondes. Inclut les millisecondes

MILLISECOND

NUMERIC(9,1)

0.0–999.9

Millisecondes

TIMEZONE_HOUR

SMALLINT

de -14 à +14

Décalage des heures du fuseau horaire

TIMEZONE_MINUTE

SMALLINT

de -59 à +59

Décalage des minutes du fuseau horaire
Note

Si le composant date/heure n’est pas présent dans l’argument date/heure, par exemple SECOND dans un argument de type DATE ou YEAR dans TIME, la fonction provoquera une erreur.

À partir d’un argument de type DATE ou TIMESTAMP, vous pouvez extraire le numéro de la semaine. Selon la norme ISO-8601, la semaine commence le lundi et comprend toujours 7 jours. La première semaine de l’année est celle qui compte le plus de jours dans la nouvelle année (au moins 4) : les jours 1-3 peuvent appartenir à la semaine précédente (52 ou 53) de l’année précédente. Par analogie, les jours 1-3 de l’année en cours peuvent appartenir à la semaine 1 de l’année suivante.

Example 1. Utilisation de la fonction EXTRACT
/* récupérer le numéro du trimestre par la date */
SELECT (EXTRACT(MONTH FROM CURRENT_TIMESTAMP)-1)/3+1
FROM RDB$DATABASE
Voir aussi :

Types de données pour travailler avec la date et l’heure.

FIRST_DAY()

Disponible en

DSQL, PSQL

Syntaxe
FIRST_DAY(OF <period> FROM date_or_timestamp)

<period> ::= YEAR | QUARTER | MONTH | WEEK
Table 1. paramètres de fonction FIRST_DAY
Paramètre Description

date_or_timestamp

Une expression de type DATE ou `TIMESTAMP [WITH
type de résultat de retour

DATE ou TIMESTAMP [WITH | WITHOUT] TIME ZONE.

Renvoie le premier jour de l’année, du mois ou de la semaine pour une date donnée.

Note

  • Le premier jour de la semaine est le dimanche, tel que retourné par la fonction EXTRACT avec la partie WEEKDAY.

  • Lorsqu’une expression de type TIMESTAMP est passée comme argument à la fonction, la valeur de retour stocke la partie temps.

Exemple FIRST_DAY

Example 1. Utilisation de la fonction FIRST_DAY
SELECT FIRST_DAY(OF MONTH FROM current_date) FROM rdb$database;
SELECT FIRST_DAY(OF YEAR FROM current_timestamp) FROM rdb$database;
SELECT FIRST_DAY(OF WEEK FROM date '2022-11-01') FROM rdb$database;
Voir aussi :

[fblangref-scalarfuncs-lastday].

LAST_DAY()

Disponible en

DSQL, PSQL

Syntaxe
LAST_DAY(OF <period> FROM date_or_timestamp)

<period> ::=  YEAR | QUARTER | MONTH | WEEK
Table 1. paramètres de fonction LAST_DAY
Paramètre Description

date_or_timestamp

Une expression de type DATE ou `TIMESTAMP [WITH
type de résultat de retour

DATE ou TIMESTAMP [WITH | WITHOUT] TIME ZONE.

Renvoie le dernier jour de l’année, du mois ou de la semaine pour une date donnée.

Note

  • Le dernier jour de la semaine est le samedi, tel que retourné par la fonction EXTRACT avec la partie WEEKDAY.

  • Lorsqu’une expression de type TIMESTAMP est passée comme argument à la fonction, la valeur de retour stocke la partie temps.

Exemple LAST_DAY

Example 1. Utilisation de la fonction LAST_DAY
SELECT LAST_DAY(OF MONTH FROM current_date) FROM rdb$database;
SELECT LAST_DAY(OF YEAR FROM current_timestamp) FROM rdb$database;
SELECT LAST_DAY(OF WEEK FROM date '2017-11-01') FROM rdb$database;
Voir aussi :

[fblangref-scalarfuncs-firstday].

Fonctions permettant de travailler avec le type BLOB

BLOB_APPEND()

Disponible en

DSQL, PSQL

Syntaxe
BLOB_APPEND(<blob> [, <value1>, ... <valueN>]
Table 1. paramètres de fonction BLOB_APPEND
Paramètre Description

blob

BLOB ou NULL.

value

La valeur de n’importe quel type.
type de résultat de retour

BLOB temporaire non fermée avec l’indicateur BLB_close_on_read.

La fonction BLOB_APPEND est conçue pour concaténer des BLOBs sans créer deBLOBs intermédiaires. Une opération de concaténation normale avec des arguments de type BLOB créera toujours autant de BLOB temporaires,autant de fois qu’il est utilisé.

Arguments d’entrée :

  • Le comportement suivant de la fonction est défini pour le premier argument en fonction de sa valeur :

    • NULL : un nouveau BLOB vide et non fermé sera créé

    • BLOB permanent (de la table) ou BLOB temporaire déjà fermé :un nouveau BLOB vide et non fermé sera créé et le contenu de l’espace de stockage du premier BLOB y sera ajouté.

    • BLOB temporaire non fermé : il sera utilisé ultérieurement

    • les autres types de données sont convertis en chaîne, un BLOB temporaire non fermé sera créé avec le contenu de cette chaîne.

  • Le reste des arguments peut être de n’importe quel type. Le comportement suivant est défini pour eux :

    • NULL est ignoré

    • les non-BLOB sont convertis en chaînes de caractères (selon les règles normales) et ajoutés au contenu du résultat.résultats

    • Les BLOB, si nécessaire, sont translittérés dans le jeu de caractères du premier argument et leur contenuleur contenu est ajouté au résultat

Comme valeur de sortie, la fonction BLOB_APPEND renvoie un BLOB temporaire non fermé avec l’indicateur BLOB_close_on_read.Il s’agit soit d’un nouveau BLOB, soit du même que dans le premier argument. Ainsi, une série d’opérations commeblob = BLOB_APPEND(blob, …​) créera au maximum un BLOB (sauf si vous essayez d’ajouter un BLOB à lui-même).Cette BLOB sera automatiquement fermée par le moteur lorsqu’une tentative est faite pour la lire par le client, l’écrire dans une table ou l’utiliser dans d’autres expressions qui nécessitent la lecture de son contenu.

Note

La vérification d’un BLOB pour NULL en utilisant l’opérateur IS [NOT] NULL ne le lit pas, et donc un BLOB temporaire ne sera pas fermé par de telles vérifications.

 
execute block
returns (b blob sub_type text)
as
begin
  -- créera un nouveau BLOB temporaire non fermé.
  -- et y écrira la chaîne de caractères du 2ème argument
  b = blob_append(null, 'Hello ');
  -- ajoute deux lignes au BLOB temporaire sans la fermer
  b = blob_append(b, 'World', '!');
  -- la comparaison d'une BLOB à une chaîne de caractères la fermera, car elle doit lire le contenu du BLOB
  if (b = 'Hello World!') then
  begin
  -- ...
  end
  -- va créer un BLOB fermé temporaire en y ajoutant une ligne
  b = b || 'Close';
  suspend;
end
Tip

Utilisez les fonctions LIST et BLOB_APPEND pour la concaténation des BLOB. Cela permet de réduire la consommation de mémoire, les entrées/sorties sur le disque et d’éviter la prolifération des bases de données due à la création de nombreux BLOB temporaires lors de l’utilisation d’instructions de concaténation.
Example 1. Utilisation de la fonction BLOB_APPEND

Supposons que vous ayez besoin de construire du JSON du côté du serveur. Nous avons un paquet PSQL JSON_UTILS avec un ensemble de fonctions pour convertir les types de données élémentaires en notation JSON. Ensuite, la construction du JSON en utilisant la fonction BLOB_APPEND ressemblera à ceci

EXECUTE BLOCK
RETURNS (
    JSON_STR BLOB SUB_TYPE TEXT CHARACTER SET UTF8)
AS
  DECLARE JSON_M BLOB SUB_TYPE TEXT CHARACTER SET UTF8;
BEGIN
  FOR
      SELECT
          HORSE.CODE_HORSE,
          HORSE.NAME,
          HORSE.BIRTHDAY
      FROM HORSE
      WHERE HORSE.CODE_DEPARTURE = 15
      FETCH FIRST 1000 ROW ONLY
      AS CURSOR C
  DO
  BEGIN
    SELECT
      LIST(
          '{' ||
          JSON_UTILS.NUMERIC_PAIR('age', MEASURE.AGE) ||
          ',' ||
          JSON_UTILS.NUMERIC_PAIR('height', MEASURE.HEIGHT_HORSE) ||
          ',' ||
          JSON_UTILS.NUMERIC_PAIR('length', MEASURE.LENGTH_HORSE) ||
          ',' ||
          JSON_UTILS.NUMERIC_PAIR('chestaround', MEASURE.CHESTAROUND) ||
          ',' ||
          JSON_UTILS.NUMERIC_PAIR('wristaround', MEASURE.WRISTAROUND) ||
          ',' ||
          JSON_UTILS.NUMERIC_PAIR('weight', MEASURE.WEIGHT_HORSE) ||
          '}'
      ) AS JSON_M
    FROM MEASURE
    WHERE MEASURE.CODE_HORSE = :C.CODE_HORSE
    INTO JSON_M;

    JSON_STR = BLOB_APPEND(
      JSON_STR,
      IIF(JSON_STR IS NULL, '[', ',' || ascii_char(13)),
      '{',
      JSON_UTILS.INTEGER_PAIR('code_horse', C.CODE_HORSE),
      ',',
      JSON_UTILS.STRING_PAIR('name', C.NAME),
      ',',
      JSON_UTILS.TIMESTAMP_PAIR('birthday', C.BIRTHDAY),
      ',',
      JSON_UTILS.STRING_VALUE('measures') || ':[', JSON_M, ']',
      '}'
    );
  END
  JSON_STR = BLOB_APPEND(JSON_STR, ']');
  SUSPEND;
END

Un exemple similaire utilisant l’opérateur de concaténation conventionnel || est 18 fois plus lent, et effectue 1000 fois plus d’opérations d’écriture sur le disque.

Fonctions permettant de travailler avec le type DECFLOAT

COMPARE_DECFLOAT()

Disponible en

DSQL, PSQL

Syntaxe
COMPARE_DECFLOAT (decfloat1, decfloat2)
Table 1. paramètres de fonction COMPARE_DECFLOAT
Paramètre Description

decfloat1, decfloat2

Les valeurs ou expressions de type DECFLOAT ou sont compatibles avec le type DECFLOAT.
type de résultat de retour

SMALLINT

La fonction COMPARE_DECFLOAT compare deux valeurs de type DECFLOAT, qui peuvent être identiques, différentes ou non ordonnées. Les zéros de fermeture sont pris en compte dans la comparaison.

La fonction renvoie :

0

Les valeurs sont égales ;
1

La première valeur est inférieure à la seconde ;
2

La première valeur est supérieure à la seconde ;
3

Les valeurs ne sont pas ordonnées (une ou les deux NaN/sNaN).

Contrairement aux opérateurs de comparaison (‘<’, ‘=’, ‘>’ etc.) la comparaison est exacte,i.e., COMPARE_DECFLOAT(2.17, 2.170) retournera 2, et non 0.

Voir aussi :[fblangref-scalarfuncs-totalorder]

NORMALIZE_DECFLOAT()

Disponible en

DSQL, PSQL

Syntaxe
NORMALIZE_DECFLOAT (decfloat_value)
Table 1. paramètres de fonction NORMALIZE_DECFLOAT
Paramètre Description

decfloat_value

Une valeur ou une expression de type DECFLOAT ou être compatible avec le type DECFLOAT.
type de résultat de retour

DECFLOAT

La fonction NORMALIZE_DECFLOAT renvoie le nombre sous forme normalisée, ce qui signifie que pour toute valeur non nulle, les zéros de fin sont supprimés avec la correction appropriée de l’exposant.

Exemple NORMALIZE_DECFLOAT

Example 1. Normalisation de différentes valeurs de type DECFLOAT.
NORMALIZE_DECFLOAT(12.00) -- renvoi 12
NORMALIZE_DECFLOAT(120) -- renvoi 1.2E+2

QUANTIZE()

Disponible en

DSQL, PSQL

Syntaxe
QUANTIZE (decfloat_value, exp_value)
Table 1. paramètres de fonction QUANTIZE
Paramètre Description

decfloat_value

Une valeur ou une expression de type DECFLOAT ou être compatible avec le type DECFLOAT.

exp_value

Une valeur ou une expression à utiliser comme exposant ; doit être de type DECFLOAT ou être compatible avec le type DECFLOAT.
type de résultat de retour

DECFLOAT

La fonction QUANTIZE renvoie la valeur du premier argument mise à l’échelle en utilisant la deuxième valeur comme modèle. En d’autres termes, la fonction QUANTIZE renvoie la valeur de DECFLOAT égale en valeur (à l’exception de tout arrondi) et en signe à decfloat_value, et l’exposant égal en valeur à l’exposant exp_value.La fonction QUANTIZE peut être utilisée pour implémenter l’arrondi au chiffre le plus proche, comme l’arrondi au cent le plus proche en utilisant le mode d’arrondi défini DECFLOAT.

Le type de la valeur de retour est DECFLOAT(16) si les deux arguments sont DECFLOAT(16), sinon le type du résultat est DECFLOAT(34).

Note

La cible est l’exposant utilisé dans le format de stockage Decimal64 ou Decimal128 pour DECFLOAT de exp_value. Ce n’est pas nécessairement le même que l’exposant affiché dans des outils tels que isql. Par exemple, la valeur 1.23E+2 est un multiplicateur de 123 et une puissance de 0, alors que 1.2 est un multiplicateur de 12 et une puissance de -1.

Si la valeur decfloat est supérieure à la valeur exp, la valeur decfloat est multipliée par une puissance de dix et sa valeur est diminuée, si la valeur est inférieure, sa valeur est arrondie en utilisant le mode d’arrondi decfloat actuel et sa valeur est augmentée.

Lorsque l’exposant cible ne peut pas être atteint parce que le quotient dépasse la précision cible (16 ou 34 décimales), alors soit l’erreur “Decfloat float invalid operation”, soit NaN est retournée (selon la configuration actuelle des pièges decfloat).

Il n’y a pratiquement aucune restriction sur les exp_value.Cependant, dans presque tous les cas où NaN/sNaN/Infinity est utilisé, une exception sera levée(sauf si cela est autorisé par la configuration actuelle des pièges decfloat)

Si l’une des valeurs est NULL, le résultat de la fonction sera NULL, etc.

Example 1. Utilisation de la fonction QUANTIZE
select v, pic, quantize(v, pic) from examples;
V                       PIC                   QUANTIZE
======================= ===================== ==================
                   3.16                 0.001              3.160
                   3.16                 0.01               3.16
                   3.16                 0.1                3.2
                   3.16                 1                  3
                   3.16                 1E+1               0E+1
                   -0.1                 1                 -0
                      0                 1E+5               0E+5
                    316                 0.1              316.0
                    316                 1                316
                    316                 1E+1             3.2E+2
                    316                 1E+2               3E+2

TOTALORDER()

Disponible en

DSQL, PSQL

Syntaxe
TOTALORDER (decfloat1, decfloat2)
Table 1. paramètres de fonction TOTALORDER
Paramètre Description

decfloat1, decfloat2

Une valeur ou une expression de type DECFLOAT ou être compatible avec le type DECFLOAT.
type de résultat de retour

SMALLINT

La fonction TOTALORDER compare deux valeurs de type DECFLOAT, y compris les valeurs spéciales.La comparaison est exacte. Les zéros de queue sont pris en compte dans la comparaison.

La fonction renvoie :

  • -1 — si la première valeur est inférieure à la seconde ;

  • 0 — si les valeurs sont égales ;

  • 1 — si la première valeur est supérieure à la seconde.

Les comparaisons des valeurs DEFLOAT se font dans l’ordre suivant :

-nan < -snan < -inf < -0.1 < -0.10 < -0 < 0 < 0.10 < 0.1 < inf < snan < nan

Voir aussi :[fblangref-scalarfuncs-comparedecfloat]

Fonctions cryptographiques

Firebird 5.0 ne prend en charge qu’un sous-ensemble d’algorithmes de chiffrement symétrique (à la fois par blocs et par flux), et RSA.

CRYPT_HASH()

Disponible en

DSQL, PSQL

Syntaxe
CRYPT_HASH (value USING <algorithm>)

<algorithm> ::= { MD5 | SHA1 | SHA256 | SHA512 | SHA3_224 | SHA3_256 | SHA3_384 | SHA3_512  }
Table 1. paramètres de fonction CRYPT_HASH
Paramètre Description

value

Une expression de n’importe quel type. Les types non-voyants et non-binaires sont convertis en chaînes de caractères.

algorithm

Algorithme de hachage.
type de résultat de retour

VARBINARY

La fonction CRYPT_HASH renvoie un hachage cryptographique de la chaîne d’entrée en utilisant l’algorithme spécifié. Cette fonction supporte entièrement les BLOB de texte de n’importe quelle longueur et avec n’importe quel jeu de caractères. La clause USING vous permet de spécifier l’algorithme par lequel le hachage est calculé.

Note

L’utilisation des algorithmes MD5 et SHA1 n’est pas recommandée en raison de problèmes graves connus. Ces algorithmes sont fournis uniquement à des fins de rétrocompatibilité.

Exemple CRYPT_HASH

Example 1. Utilisation de la fonction CRYPT_HASH
SELECT CRYPT_HASH(x USING SHA256) FROM MyTable;
-- Résultat de type VARBINARY

DECRYPT()

Disponible en

DSQL, PSQL

Syntaxe
DECRYPT (encrypted_input
  [USING <algorithm>] [MODE <mode>]
  KEY key
  [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
  [COUNTER initial_counter] )

<algorithm> ::= <block_cipher> | <stream_cipher>

<block_cipher> ::=
    AES | ANUBIS | BLOWFISH | KHAZAD | RC5
  | RC6 | SAFER+ | TWOFISH | XTEA

<stream_cipher> ::= CHACHA20 | RC4 | SOBER128

<mode> ::= CBC | CFB | CTR | ECB | OFB

<ctr_type> ::= CTR_BIG_ENDIAN | CTR_LITTLE_ENDIAN
Table 1. paramètres de fonction DECRYPT
Paramètre Description

encrypted_input

BLOB crypté ou chaîne de caractères (binaire)

algorithm

Algorithme de cryptage. Les algorithmes de bloc et de flux sont pris en charge.

mode

Mode de chiffrement. Obligatoire pour les algorithmes de chiffrement par blocs.

key

Clé de cryptage.

iv

Vecteur d’initialisation (IV). Doit être spécifié pour tous les ciphers de bloc sauf ECB et tous les ciphers de flux sauf RC4.

ctr_type

Ordre des octets du compteur. Ne peut être spécifié qu’en mode CTR. La valeur par défaut est CTR_LITTLE_ENDIAN.

ctr_length

Longueur du compteur en octets. Ne peut être spécifié qu’en mode CTR. La valeur par défaut est la longueur du vecteur d’initialisation IV.

initial_counter

Valeur initiale du compteur. Ne peut être spécifiée que pour l’algorithme CHACHA20. La valeur par défaut est 0.
type de résultat de retour

BLOB ou VARBINARY.

La fonction DECRYPT décrypte les données à l’aide d’un chiffrement symétrique. La taille des chaînes transmises à cette fonction doit répondre aux exigences de l’algorithme et du mode sélectionnés.

Example 1. Utilisation de la fonction DECRYPT
select decrypt(x'0154090759DF' using sober128 key 'AbcdAbcdAbcdAbcd'
               iv '01234567')
from rdb$database;

select decrypt(secret_field using aes mode ofb key '0123456701234567'
               iv init_vector)
from secure_table;
Voir aussi :

[fblangref-scalarfuncs-encrypt].

ENCRYPT()

Disponible en

DSQL, PSQL

Syntaxe
ENCRYPT (input
  [USING <algorithm>] [MODE <mode>]
  KEY key
  [IV iv] [<ctr_type>] [CTR_LENGTH ctr_length]
  [COUNTER initial_counter] )

<algorithm> ::= <block_cipher> | <stream_cipher>

<block_cipher> ::=
    AES | ANUBIS | BLOWFISH | KHAZAD | RC5
  | RC6 | SAFER+ | TWOFISH | XTEA

<stream_cipher> ::= CHACHA20 | RC4 | SOBER128

<mode> ::= CBC | CFB | CTR | ECB | OFB

<ctr_type> ::= CTR_BIG_ENDIAN | CTR_LITTLE_ENDIAN
Table 1. paramètres de fonction ENCRYPT
Paramètre Description

input

Une expression de type chaîne ou BLOB à crypter.

algorithm

Algorithme de cryptage. Les algorithmes de bloc et de flux sont pris en charge.

mode

Mode de chiffrement. Obligatoire pour les algorithmes de chiffrement par blocs.

key

Clé de cryptage.

iv

Vecteur d’initialisation (IV). Doit être spécifié pour tous les ciphers de bloc sauf ECB et tous les ciphers de flux sauf RC4.

ctr_type

Ordre des octets du compteur. Ne peut être spécifié qu’en mode CTR. La valeur par défaut est CTR_LITTLE_ENDIAN.

ctr_length

Longueur du compteur en octets. Ne peut être spécifié qu’en mode CTR. La valeur par défaut est la longueur du vecteur d’initialisation IV.

initial_counter

Valeur initiale du compteur. Ne peut être spécifiée que pour l’algorithme CHACHA20. La valeur par défaut est 0.
type de résultat de retour

BLOB ou VARBINARY.

La fonction ENCRYPT permet de crypter des données en utilisant un chiffrement symétrique.

Note

  • Cette fonction renvoie BLOB SUB_TYPE BINARY si le premier argument est BLOB, et VARBINARY pour tous les autres types de texte et binaires.

  • Les tailles des chaînes (par exemple, key et iv) transmises à cette fonction doivent répondre aux exigences de l’algorithme et du mode choisis.Voir le tableau [fblangref-scalarfuncs-tbl-encrypt-req] pour plus de détails.

    • En règle générale, la taille de iv doit correspondre à la taille du bloc de l’algorithme.

    • Pour les modes ECB et CBC, input doit être un multiple de la taille du bloc, vous devrez le remplir manuellement avec des zéros ou des espaces si nécessaire.

Les caractéristiques des différents algorithmes et modes dépassent le cadre de ce guide linguistique.

Table 2. Exigences des algorithmes de cryptage
Algorithme Taille de la clé (octet) Taille du bloc (octet) Note

Cryptage par blocs

AES

16, 24, 32

16

 

ANUBIS

16 - 40, par incréments 4

16

 

BLOWFISH

8 - 56

8

 

KHAZAD

16

8

 

RC5

8 - 128

8

 

RC6

8 - 128

16

 

SAFER+

16, 24, 32

16

 

TWOFISH

16, 24, 32

16

 

XTEA

16

8

 

Cryptage en ligne

CHACHA20

16, 32

1

La taille (IV) est de 8 ou 12 octets. Pour la taille 8, initial_counter est un entier de 64 bits, pour la taille 12, c’est un entier de 32 bits.

RC4

5 - 256

1

 

SOBER128

4x

1

La taille (IV) est de 4y octets, la longueur est indépendante de la taille de la clé.
Example 1. Utilisation de la fonction ENCRYPT
select encrypt('897897' using sober128 key 'AbcdAbcdAbcdAbcd' iv '01234567')
from rdb$database;
Voir aussi :

[fblangref-scalarfuncs-decrypt].

RSA_PRIVATE()

Disponible en

DSQL, PSQL

Syntaxe
RSA_PRIVATE (size)
Table 1. paramètres de fonction RSA_PRIVATE
Paramètre Description

size

Taille de la clé en octets.
Le type de résultat renvoyé :

VARBINARY

La fonction RSA_PRIVATE renvoie la clé privée RSA d’une longueur donnée (en octets) au format PKCS#1 sous forme de chaîne VARBINARY.

Example 1. Utilisation de la fonction RSA_PRIVATE
select rdb$set_context('USER_SESSION', 'private_key', rsa_private(256))
from rdb$database;
Voir aussi :

[fblangref-scalarfuncs-rsa_public].

RSA_PUBLIC()

Disponible en

DSQL, PSQL

Syntaxe
RSA_PUBLIC (private-key)
Table 1. paramètres de fonction RSA_PUBLIC
Paramètre Description

private-key

Clé privée RSA.
Le type de résultat renvoyé :

VARBINARY

La fonction RSA_PUBLIC renvoie la clé publique RSA pour une clé privée RSA donnée. Les deux clés doivent être au format PKCS#1.

Example 1. Utilisation de la fonction RSA_PUBLIC

La clé privée doit être initialisée auparavant, voir l’exemple dans RSA_PRIVATE

select rdb$set_context('USER_SESSION', 'public_key',
    rsa_public(rdb$get_context('USER_SESSION', 'private_key')))
from rdb$database;
Voir aussi :

[fblangref-scalarfuncs-rsa_private].

RSA_ENCRYPT()

Disponible en

DSQL, PSQL

Syntaxe
RSA_ENCRYPT (<data> KEY <public_key> [LPARAM <tag>] [HASH <hash>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }
Table 1. paramètres de fonction RSA_ENCRYPT
Paramètre Description

data

Données (chaîne ou BLOB) à crypter.

public_key

La clé publique RSA retournée par la fonction RSA_PUBLIC.

tag

Une balise système facultative qui peut être utilisée pour déterminer quel système a encodé le message. La valeur par défaut est NULL.

hash

Algorithme de hachage. La valeur par défaut est SHA256.
Le type de résultat renvoyé :

VARBINARY

Remplit les données à l’aide de la complétion OAEP et les crypte à l’aide de la clé publique RSA. Généralement utilisé pour crypter des clés symétriques courtes, qui sont ensuite utilisées dans des ciphers de bloc pour crypter le message.

Example 1. Utilisation de la fonction RSA_ENCRYPT

La clé publique doit être initialisée auparavant, voir l’exemple dans [fblangref-scalarfuncs-rsa_public]

select rdb$set_context('USER_SESSION', 'msg',
    rsa_encrypt('Some message' key rdb$get_context('USER_SESSION', 'public_key')))
from rdb$database;
Voir aussi :

[fblangref-scalarfuncs-rsa_public], [fblangref-scalarfuncs-rsa_decrypt].

RSA_DECRYPT()

Disponible en

DSQL, PSQL

Syntaxe
RSA_DECRYPT (<data> KEY <private_key> [LPARAM <tag>] [HASH <hash>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }
Table 1. paramètres de fonction RSA_DECRYPT
Paramètre Description

data

Données (chaîne ou BLOB) à décrypter.

private_key

La clé privée RSA retournée par la fonction RSA_PRIVATE.

tag

Balise système optionnelle. Doit être la même valeur que celle passée à RSA_ENCRYPT. Si elle ne correspond pas à celle utilisée lors du codage, cette fonction ne décryptera pas le paquet. La valeur par défaut est NULL.

hash

Algorithme de hachage. La valeur par défaut est SHA256.
Le type de résultat renvoyé :

VARCHAR

Déchiffre à l’aide d’une clé privée RSA et supprime les données augmentées par l’OAEP.

Example 1. Utilisation de la fonction RSA_DECRYPT

La clé privée doit être initialisée plus tôt, voir l’exemple dans [fblangref-scalarfuncs-rsa_private]. Les données de déchiffrement sont utilisées à partir de l’exemple dans [fblangref-scalarfuncs-rsa_encrypt].

select RSA_DECRYPT(rdb$get_context('USER_SESSION', 'msg')
    key rdb$get_context('USER_SESSION', 'private_key'))
from RDB$DATABASE;
Voir aussi :

[fblangref-scalarfuncs-rsa_private], [fblangref-scalarfuncs-rsa_encrypt].

RSA_SIGN_HASH()

Disponible en

DSQL, PSQL

Syntaxe
RSA_SIGN_HASH (<data> KEY <private_key> [HASH <hash>] [SALT_LENGTH <length>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }
Table 1. paramètres de fonction RSA_SIGN_HASH
Paramètre Description

data

Données (chaîne ou BLOB) à encoder.

private_key

La clé privée RSA retournée par la fonction RSA_PRIVATE.

hash

Algorithme de hachage. La valeur par défaut est SHA256.

length

Indique la longueur du sel souhaité et doit généralement être petite. Une bonne valeur se situe entre 8 et 16.
Le type de résultat renvoyé :

VARBINARY

Effectue le codage PSS du condensé du message pour la signature et le signe en utilisant la clé privée RSA. Renvoie la signature du message.

Example 1. Utilisation de la fonction RSA_SIGN_HASH

La clé privée doit être initialisée plus tôt voir l’exemple dans [fblangref-scalarfuncs-rsa_private].

select rdb$set_context('USER_SESSION', 'msg',
    rsa_sign_hash(crypt_hash('Test message' using sha256)
                  key rdb$get_context('USER_SESSION', 'private_key')))
from rdb$database;
Voir aussi :

[fblangref-scalarfuncs-rsa_private], [fblangref-scalarfuncs-rsa_verify_hash].

RSA_VERIFY_HASH()

Disponible en

DSQL, PSQL

Syntaxe
RSA_VERIFY_HASH (<data> SIGNATURE <signature> KEY <public_key> [HASH <hash>]
  [SALT_LENGTH <length>])

<hash> ::= { MD5 | SHA1 | SHA256 | SHA512 }
Table 1. paramètres de fonction RSA_VERIFY_HASH
Paramètre Description

data

Données (chaîne ou BLOB) à encoder.

signature

Signature. Doit être la valeur renvoyée par la fonction RSA_SIGN_HASH.

public_key

La clé publique RSA retournée par la fonction RSA_PUBLIC.

hash

Algorithme de hachage. La valeur par défaut est SHA256.

length

Indique la longueur du sceau souhaité et doit généralement être petite.Une bonne valeur se situe entre 8 et 16.
type de résultat de retour

BOOLEAN

Effectue le codage PSS du condensé du message pour la signature et vérifie sa signature numérique en utilisant la clé publique RSA. Renvoie le résultat de la vérification de la signature.

Example 1. Utilisation de la fonction RSA_VERIFY_HASH

La clé publique doit être initialisée plus tôt voir exemple dans [fblangref-scalarfuncs-rsa_public]. La signature numérique est obtenue plus tôt avec [fblangref-scalarfuncs-rsa_sign_hash].

select rsa_verify_hash(crypt_hash('Test message' using sha256)
    signature rdb$get_context('USER_SESSION', 'msg')
    key rdb$get_context('USER_SESSION', 'public_key'))
from rdb$database;
Voir aussi :

[fblangref-scalarfuncs-rsa_sign_hash], [fblangref-scalarfuncs-rsa_public].

Fonctions de conversion de type

CAST()

Disponible en

DSQL, PSQL

Syntaxe
CAST(value | NULL AS <type>)

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN relname.colname

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

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

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

<array_datatype> ::= Voir syntaxe des tableaux.
Table 1. paramètres de fonction CAST
Paramètre Description

value

Expression SQL.

datatype

Type de données SQL.

domain

Domaine.

relname

Le nom d’une table ou d’une vue.

colname

Le nom de la colonne de la table ou de la vue.
type de résultat de retour

<type>.

La fonction CAST est utilisée pour convertir explicitement des données d’un type de données vers un autre type de données ou un autre domaine. Si cela n’est pas possible, une erreur sera émise.

Table 2. Transformations permises avec la fonction CAST
Type d’origine Type en sortie

Type numérique

Types numériques, [VAR]CHAR, BLOB.

[VAR]CHAR, BLOB

[VAR]CHAR, BLOB, BOOLEAN, Types numériques, DATE, TIME, TIMESTAMP

DATE, TIME

[VAR]CHAR, BLOB, TIMESTAMP

TIMESTAMP

[VAR]CHAR, BLOB, TIME, DATE

BOOLEAN

[VAR]CHAR, BLOB

Notez que parfois des informations peuvent être perdues, par exemple lorsque vous convertissez le type TIMESTAMP en DATE.De plus, le fait que les types soient compatibles pour la fonction CAST ne garantit pas que la conversion sera réussie.“CAST (123456789 AS SMALLINT)” provoquera certainement une erreur, tout comme “CAST('Judgement Day' as DATE)”.

Vous pouvez appliquer une conversion de type aux paramètres de l’opérateur :

CAST (? AS INTEGER)

Cela vous permet de contrôler le type de champs de saisie.

Conversion en un domaine ou en son type de base

Lors de la conversion vers un domaine, toute restriction (NOT NULL et/ou CHECK) déclarée pour le domaine doit être satisfaite, sinon la conversion n’aura pas lieu. Rappelez-vous que la vérification CHECK passe si son calcul donne VRAI ou UNKNOWN (NULL). Pour les opérateurs suivants :

CREATE DOMAIN quint AS INT CHECK (VALUE >= 5000)
SELECT CAST (2000 AS quint) FROM rdb$database -- (1)
SELECT CAST (8000 AS quint) FROM rdb$database -- (2)
SELECT CAST (null AS quint) FROM rdb$database -- (3)

seul (1) se terminera par une erreur.

Si le modificateur TYPE OF est utilisé, l’expression sera convertie au type de base du domaine, en ignorant les contraintes. Pour le domaine quint déclaré ci-dessus, les deux conversions seront équivalentes et les deux réussiront :

SELECT CAST (2000 AS TYPE OF quint) FROM rdb$database
SELECT CAST (2000 AS INT) FROM rdb$database

Lorsque TYPE OF est utilisé avec le type [VAR]CHAR, son jeu de caractères et son ordre de tri (collate) sont conservés.

CREATE DOMAIN iso20 VARCHAR(20) CHARACTER SET iso8859_1;
CREATE DOMAIN dunl20 VARCHAR(20) CHARACTER SET iso8859_1 COLLATE du_nl;
CREATE TABLE zinnen (zin VARCHAR(20));
COMMIT;
INSERT INTO zinnen VALUES ('Deze');
INSERT INTO zinnen VALUES ('Die');
INSERT INTO zinnen VALUES ('die');
INSERT INTO zinnen VALUES ('deze');
SELECT CAST(zin AS TYPE OF iso20) FROM zinnen ORDER BY 1;
-- returns Deze -> Die -> deze -> die
SELECT CAST(zin AS TYPE OF dunl20) FROM zinnen ORDER BY 1;
-- returns deze -> Deze -> die -> Die
Warning

Si la définition du domaine est modifiée, les conversions existantes du domaine ou de son type peuvent devenir erronées. Si de telles conversions se produisent dans les modules PSQL, leurs erreurs peuvent être détectées. Champ d’application RDB$VALID_BLR.

Conversion en type de colonne

Permet de convertir une expression en un type de colonne d’une table ou d’une vue existante. Seul le type lui-même sera utilisé, pour les types de chaînes de caractères, le même jeu de caractères sera utilisé, mais pas la séquence de tri. Les restrictions et les valeurs par défaut de la colonne originale ne sont pas appliquées.

CREATE TABLE ttt (
  s VARCHAR(40) CHARACTER SET utf8 COLLATE unicode_ci_ai
);
COMMIT;
SELECT CAST ('Jag har många vänner' AS TYPE OF COLUMN ttt.s)
FROM rdb$database;
Warning

Si la définition d’une colonne est modifiée, les conversions existantes de son type peuvent devenir erronées. Si de telles conversions se produisent dans les modules PSQL, leurs erreurs peuvent être détectées. Champ RDB$VALID_BLR.
Voir aussi :

[fblangref-datatypes-convert-explicit].

Exemple de conversion de type

SELECT CAST ('12' || '-June-' || '1959' AS DATE) FROM rdb$database

Notez que dans certains cas, vous n’aurez pas besoin d’utiliser la syntaxe de conversion comme dans l’exemple ci-dessus, car Firebird comprendra à partir du contexte (comparaison avec un champ DATE) comment interpréter la chaîne :

UPDATE People SET AgeCat = 'Old'
WHERE BirthDate < '1-Jan-1943'

Mais cela n’est pas toujours possible. La conversion dans l’exemple ci-dessous ne peut pas être omise car le système essaiera de convertir la chaîne en un nombre afin de lui soustraire un nombre :

SELECT CAST('TODAY' AS DATE) - 7 FROM rdb$database

Fonctions d’opérations binaires

BIN_AND()

Disponible en

DSQL, PSQL

Syntaxe
BIN_AND (number, number [, number ...])
Table 1. paramètres de fonction BIN_AND
Paramètre Description

number

Entier.
type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_AND renvoie le résultat de l’opération ET binaire des arguments.

Voir aussi :

[fblangref-scalarfuncs-bin-or], [fblangref-scalarfuncs-bin-xor].

BIN_NOT()

Disponible en

DSQL, PSQL

Syntaxe
BIN_NOT (number)
Table 1. paramètres de fonction BIN_NOT
Paramètre Description

number

Entier.
type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_NOT renvoie le résultat d’une opération NOT au sens du bit sur l’argument.

Voir aussi :

[fblangref-scalarfuncs-bin-or], [fblangref-scalarfuncs-bin-and].

BIN_OR()

Disponible en

DSQL, PSQL

Syntaxe
BIN_OR (number, number [, number ...])
Table 1. paramètres de fonction BIN_OR
Paramètre Description

number

Entier.
type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_OR renvoie le résultat de l’opération "bitwise OR" des arguments.

Voir aussi :

[fblangref-scalarfuncs-bin-and], [fblangref-scalarfuncs-bin-xor].

BIN_SHL()

Disponible en

DSQL, PSQL

Syntaxe
BIN_SHL (number, shift)
Table 1. paramètres de fonction BIN_SHL
Paramètre Description

number

Entier.

shift

Le nombre de bits par lequel la valeur number est décalée.
type de résultat de retour

BIGINT ou INT128.

La fonction BIN_SHL renvoie le premier paramètre décalé bit à bit de la valeur du second paramètre, c’est-à-dire a << b ou a-2b.

Voir aussi :

[fblangref-scalarfuncs-bin-shr].

BIN_SHR()

Disponible en

DSQL, PSQL

Syntaxe
BIN_SHR (number, shift)
Table 1. paramètres de fonction BIN_SHR
Paramètre Description

number

Entier.

shift

Le nombre de bits par lequel la valeur numérique est décalée.
Le type de résultat renvoyé :

BIGINT ou INT128.

La fonction BIN_SHR renvoie le premier paramètre décalé bit à bit de la valeur du second paramètre, c’est-à-dire a >> b ou a/2b.

  • L’opération effectuée est un décalage arithmétique vers la droite (SAR), ce qui signifie que le signe du premier opérande est toujours conservé.

Voir aussi :

[fblangref-scalarfuncs-bin-shl].

BIN_XOR()

Disponible en

DSQL, PSQL

Syntaxe
BIN_XOR (number, number [, number ...])
Table 1. paramètres de fonction BIN_XOR
Paramètre Description

number

Entier.
type de résultat de retour

SMALLINT, INTEGER, BIGINT ou INT128

La fonction BIN_XOR renvoie le résultat de l’opération XOR bit à bit des arguments.

Voir aussi :

[fblangref-scalarfuncs-bin-and], [fblangref-scalarfuncs-bin-or].