CREATE TABLE T_VOL
(VOL_ID INTEGER,
 VOL_REFERENCE CHAR(6),
 VOL_DATE DATE,
 VOL_PLACES_LIBRES INTEGER)

INSERT INTO T_VOL VALUES (1, 'AF 714', 7)
INSERT INTO T_VOL VALUES (2, 'AF 812', 6)
INSERT INTO T_VOL VALUES (4, 'AF 325', 258)

CREATE TABLE T_CLIENT_VOL
(CLI_ID  INTEGER, 
 VOL_ID INTEGER,
 VOL_PLACE_PRISE INTEGER)

INSERT INTO T_CLIENT_VOL VALUES (7, 1, 2)
INSERT INTO T_CLIENT_VOL VALUES (82, 4, 1)

T1 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 5
   then
   begin
 
 
 

T3    UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
      WHERE  VOL_ID = 2
 
 

T5    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
   end

T2 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 3
   then
   begin
 
 

T4    UPDATE T_VOL
      SET   VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
      WHERE  VOL_ID = 2

T6    INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
   end

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         1

2       AF 812         -2

2       AF 812         -2

77      2       5

2       AF 812         -2

77      2       5
88      2       3

T1 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 5
   then
   begin
 
 
 

T3    UPDATE T_VOL
      SET    	VOL_PLACES_LIBRES = 
		VOL_PLACES_LIBRES - 5
      WHERE  VOL_ID = 2
 
 
 

T5 if (SELECT VOL_PLACES_LIBRES
          FROM T_VOL WHERE VOL_ID = 2) < 0
 

T7    then
      begin
         UPDATE T_VOL
         SET    VOL_PLACES_LIBRES = 
         	VOL_PLACES_LIBRES + 5
         WHERE  VOL_ID = 2 
      return
      end 
 
 
 
 
 
 

T9    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
   end

 
 

T2 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 3
   then
   begin
 
 

T4    UPDATE T_VOL
      SET	VOL_PLACES_LIBRES = 
      		VOL_PLACES_LIBRES - 3
      WHERE  VOL_ID = 2
 
 

T6 if (SELECT VOL_PLACES_LIBRES
          FROM T_VOL WHERE VOL_ID = 2) < 0
 
 
 
 
 
 

T8 then
     begin
        UPDATE T_VOL
        SET    	VOL_PLACES_LIBRES = 
        	VOL_PLACES_LIBRES + 3
        WHERE  VOL_ID = 2 
       return
     end 
 

T10    INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
   end

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         1

2       AF 812         -2

2       AF 812         -2

2       AF 812         -2

2       AF 812         3

2       AF 812         6

T1 BEGIN TRANSACTION PLACE_AVION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
   WHERE  VOL_ID = 2
T4 INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
T5 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) < 0
T6 then
       ROLLBACK TRANSACTION PLACE_AVION 1
T7 else
       COMMIT TRANSACTION PLACE_AVION 1

T2 BEGIN TRANSACTION PLACE_AVION 2

-- la transaction attend la libération
-- des ressources de l'autre transaction
 
 
 
 
 
 
 
 

T8 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
   WHERE  VOL_ID = 2
T9 INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
T10 if (SELECT VOL_PLACES_LIBRES
        FROM T_VOL WHERE VOL_ID = 2) < 0
T11 then
      ROLLBACK TRANSACTION PLACE_AVION 2
T12 else
      COMMIT TRANSACTION PLACE_AVION 2

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         1

2       AF 812         1

77      2       5

2       AF 812         1

77      2       5

2       AF 812         1

77      2       5

2       AF 812         -2

77      2       5
88      2       3

2       AF 812         -2

77      2       5
88      2       3

2       AF 812         -2

77      2       5
88      2       3

2       AF 812         1

77      2       5

BEGIN TRANSACTION [nom_transaction]
...
ROLLBACK | COMMIT TRANSACTION [nom_transaction]
...
COMMIT | ROLLBACK TRANSACTION [nom_transaction]

...
COMMIT | ROLLBACK
...
ROLLBACK | COMMIT

T1 BEGIN TRANSACTION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 10
   WHERE  VOL_ID = 2
 
 
 

T5 ROLLBACK TRANSACTION 1

T2 BEGIN TRANSACTION 2
 
 
 

T4 if (SELECT VOL_PLACES_LIBRES 
       FROM T_VOL WHERE VOL_ID = 2) >= 7
   then

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 7
      WHERE  VOL_ID = 2
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         16

2       AF 812         16

2       AF 812         6

2       AF 812         -1

2       AF 812         -1

77      2       5

2       AF 812         -1

77      2       5

 
 

T3 BEGIN TRANSACTION 1
T4 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 4
   WHERE  VOL_ID = 2
T5 COMMIT TRANSACTION 1

T1 BEGIN TRANSACTION 2
T2 if (SELECT VOL_PLACES_LIBRES 
       FROM T_VOL WHERE VOL_ID = 2) >= 4
   then
 
 
 
 
 

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES -
             (SELECT VOL_PLACES_LIBRES
              FROM T_VOL WHERE VOL_ID = 2) 
      WHERE  VOL_ID = 2
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 2,
              (SELECT VOL_PLACES_LIBRES
               FROM T_VOL WHERE VOL_ID = 2))
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         16

2       AF 812         2

2       AF 812         2

2       AF 812         0

2       AF 812         0

77      2       2

2       AF 812         0

77      2       2

T3 BEGIN TRANSACTION 1
T4 INSERT INTO T_VOL
   VALUES (5, 'AF 111', 125)
T5 COMMIT TRANSACTION 1

T1 BEGIN TRANSACTION 2
T2 if EXISTS (SELECT *
              FROM T_VOL
       WHERE VOL_PLACE_LIBRE >= 11)   then
 
 
 

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 11
      WHERE  VOL_PLACES_LIBRES >= 11
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 4, 11)
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

4       AF 325         258

4       AF 325         258

4       AF 325         258

4       AF 325         258
5       AF 111         125

4       AF 325         258
5       AF 111         125

4       AF 325         247
5       AF 111         114

4	AF 325         247
5       AF 111         114

77      4       11

4       AF 325         247
5       AF 111         114

77      2       11

T1 BEGIN TRANSACTION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
   WHERE  VOL_ID = 1
 
 

T5 INSERT INTO T_CLIENT_VOL VALUES (77, 1, 3)
 
 

T7 COMMIT

T2 BEGIN TRANSACTION 2
 
 

T4 DELETE FROM T_CLIENT_VOL
   WHERE CLI_ID = 88
     AND VOL_ID = 1

T6 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 5
   WHERE  VOL_ID = 1

T8 COMMIT

BEGIN TRANSACTION 1

La transaction T1 démarre normalement

BEGIN TRANSACTION 2

La transaction T2 démarre normalement

UPDATE T_VOL
SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
WHERE  VOL_ID = 1

AVANT d'exécuter la requête, le SGBDR pose
un verrou sur la table T_VOL pour l'utilisateur 1

DELETE FROM T_CLIENT_VOL
WHERE CLI_ID = 88
  AND VOL_ID = 1

AVANT d'exécuter la requête, le SGBDR pose
un verrou sur la table T_CLIENT_VOL pour 
l'utilisateur 2

INSERT INTO T_CLIENT_VOL VALUES (77, 1, 3)

Le SGBDR tente d'obtenir un verrou sur la table
T_CLIENT_VOL pour l'utilisateur T1  mais il lui
est momentanément refusé parce que la table est
vérouillée par T2

UPDATE T_VOL
SET VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 5
   WHERE  VOL_ID = 1

Le SGBDR tente d'obtenir un verrou sur la table
T_VOL pour l'utilisateur T2  mais il lui
est momentanément refusé parce que la table est
vérouillée par T1

ON DELETE { NO ACTION | CASCADE }

ON UPDATE { NO ACTION | CASCADE }

ON DELETE NO ACTION

ON DELETE CASCADE

ON UPDATE NO ACTION

ON UPDATE CASCADE

ON DELETE SET DEFAULT

ON DELETE SET NULL

ON UPDATE SET DEFAULT

ON UPDATE SET NULL

CREATE TABLE T_CLIENT
(CLI_ID INTEGER NOT NULL PRIMARY KEY,
 CLI_NOM CHAR(32) NOT NULL,
 CLI_PRENOM VARCHAR(32),
 CLI_STATUT CHAR(1))

INSERT INTO T_CLIENT VALUES (1, 'DUPONT', 'Marcel', 'C')
INSERT INTO T_CLIENT VALUES (4, 'DUFOUR', 'Martin', 'C')
INSERT INTO T_CLIENT VALUES (17, 'DUHAMEL', 'Manuel', 'C')
INSERT INTO T_CLIENT VALUES (161, 'DUBOIS', 'Marie', 'C')

CREATE TABLE T_PROSPECT
(PRP_ID INTEGER NOT NULL PRIMARY KEY,
 PRP_NOM CHAR(32) NOT NULL,
 PRP_PRENOM VARCHAR(32))

INSERT INTO T_PROSPECT VALUES (14, 'Laporte', 'Eric')
INSERT INTO T_PROSPECT VALUES (17, 'Lambert', 'Ernest')
INSERT INTO T_PROSPECT VALUES (161, 'Laurent', 'Emeric')
INSERT INTO T_PROSPECT VALUES (4874, 'Lautier', 'Etienne')

INSERT INTO T_CLIENT
SELECT PRP_ID, PRP_NOM, PRP_PRENOM, 'P'
FROM T_PROSPECT

Violation de la contrainte PRIMARY KEY 'PK__T_CLIENT__251C81ED'.
Impossible d'insérer une clé en double dans l'objet 'T_CLIENT'.
L'instruction a été arrêtée.

INSERT INTO T_CLIENT
SELECT PRP_ID, PRP_NOM, PRP_PRENOM, 'P'
FROM T_PROSPECT

(4) lignes ajoutées

SELECT * 
FROM T_CLIENT

CLI_ID      CLI_NOM                          CLI_PRENOM                       CLI_STATUT 
----------- -------------------------------- -------------------------------- ---------- 
1           DUPONT                           Marcel                           C
4           DUFOUR                           Martin                           C
17          DUHAMEL                          Manuel                           C
161         DUBOIS                           Marie                            C
175         LAPORTE                          Eric                             P
178         LAMBERT                          Ernest                           P
322         LAURENT                          Emeric                           P
5035        LAUTIER                          Etienne                          P

CREATE TABLE TAB1 (COL1 INT) 
CREATE TABLE TAB2 (COL2 INT)

INSERT INTO TAB1 VALUES (1) 
INSERT INTO TAB1 VALUES (1) 
INSERT INTO TAB1 VALUES (2) 
INSERT INTO TAB2 VALUES (1) 
INSERT INTO TAB2 VALUES (2) 
INSERT INTO TAB2 VALUES (2)

SELECT TAB1.COL1 AS COL 
FROM   TAB1 
WHERE  TAB1.COL1 IN (SELECT TAB2.COL2 
                     FROM   TAB2)

COL 
---
1
1 
2

SELECT OBJ_NOM AS NOM, OBJ_PRIX AS PRIX
FROM   T_OBJET
UNION 
SELECT MAC_NOM AS NOM, MAC_PRIX AS PRIX 
FROM   T_MACHINE
ORDER  BY NOM, PRIX

NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0

-- union à base de jointure avec utilisation de COALESCE
SELECT COALESCE(OBJ_NOM, MAC_NOM) AS NOM,
       COALESCE(OBJ_PRIX, MAC_PRIX) AS PRIX
FROM   T_OBJET O
       FULL OUTER JOIN T_MACHINE M
            ON O.OBJ_NOM = M.MAC_NOM
               AND O.OBJ_PRIX = M.MAC_PRIX
ORDER  BY NOM, PRIX

NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0

-- union à base de jointure avec utilisation de CASE
SELECT CASE 
          WHEN OBJ_NOM IS NULL
               THEN MAC_NOM
               ELSE OBJ_NOM
       END AS NOM,
       CASE
          WHEN OBJ_PRIX IS NULL
               THEN MAC_PRIX
               ELSE OBJ_PRIX
       END AS PRIX
FROM   T_OBJET O
       FULL OUTER JOIN T_MACHINE M
            ON O.OBJ_NOM = M.MAC_NOM
               AND O.OBJ_PRIX = M.MAC_PRIX
ORDER  BY NOM, PRIX

NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0

CREATE TABLE T_CLIENT
(CLI_ID  INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_NOM VARCHAR(32))

CREATE TABLE T_ADRESSE
(ADR_ID    INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_ID    INTEGER FOREIGN KEY REFERENCES T_CLIENT (CLI_ID),
 ADR_VOIE  VARCHAR(64) NOT NULL,
 ADR_CP    CHAR(5) NOT NULL,
 ADR_VILLE VARCHAR(32) NOT NULL)

SELECT * 
FROM T_CLIENT

SELECT * 
FROM T_ADRESSE

CLI_ID      CLI_NOM 
----------- --------
2           DUPONT

ADR_ID      CLI_ID      ADR_VOIE                                ADR_CP ADR_VILLE
----------- ----------- --------------------------------------- ------ -------------------
2           2           25 chemin des vendanges                 84190  BEAUMES DE VENISE

CREATE FUNCTION PI()
LANGUAGE SQL
RETURNS DECIMAL(16,15)
CONTAINS SQL
RETURN 3.1415926535 89793

SELECT DIAMETRE * PI()
FROM   T_CERCLE

CREATE function franc_euros (VALEUR FLOAT)
LANGUAGE SQL
RETURNS DECIMAL (16, 2)
RETURNS NULL ON NULL 
DECLARE VALEUROS DECIMAL (16, 2)
SET VALEUROS = CAST(VALEUR as decimal(16,2) ) / 6.55957
RETURN CAST(VALEUROS as FLOAT)

SELECT franc_euros(MONTANT_TTC)
FROM   T_FACTURE

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN wordcount(
   SQLUDF_CLOB     *in1,
   SQLUDF_INTEGER  *out,
   SQLUDF_NULLIND  *in1null,
   SQLUDF_NULLIND  *outnull,
   SQLUDF_TRAIL_ARGS) {

   SQLUDF_INTEGER count = 0;
   SQLUDF_INTEGER ind;
   SQLUDF_SMALLINT blank = 0;

   for (ind = 0; ind < in1->length; ind++) {
      if (blank == 0 && in1->data[ind] != ' ') {
         blank = 1;
         count++;
      } else if (blank == 1 && in1->data[ind] == ' ') {
         blank = 0;
      } /* endif */
   } /* endfor */

   *out = count;
   *outnull = 0;
}

CREATE FUNCTION WORD_COUNT (MyText CLOB)
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NO SQL
EXTERNAL NAME "UDFbibC/Wordcount"
RETURNS INTEGER
DECLARE N_WORDS INTEGER
SET N_WORDS = Wordcount(MyText)
RETURN (N_WORDS)

//fichier projet delphi "UdfIBsqlPro.dpr"

library UDFibSQLpro;

uses
  SysUtils,
 Classes,
 UudfIB in 'UudfIB.pas';

{$R *.res}

// function flip (s : pchar; pivot : integer) : pchar;
// function reverse ( s : pchar) : pchar;

begin

end.

// fichier d'unité delphi "UudfIB.pas"

unit UudfIB;

interface

Uses
  IBexternals, IBheader, IBIntf;

// intervertit le début et la fin d'une chaîne 
// par rapport au caractère pivot
function flip (s : pchar; pivot : integer) : pchar; cdecl;
// inverse les lettres d'un mot
function reverse ( s : pchar) : pchar; cdecl;

Exports
  flip, reverse;

implementation

function flip (s : pchar; pivot : integer) : pchar; cdecl;
var
  sd : string;
  sout : string;
begin
if length(s) = 0
then
begin
  flip := s;
  exit;
end;
sd := string(s);
if pivot >= length(sd)
then
begin
  flip := s;
  exit;
end;
sout := copy(sd, 1, pivot) + copy(sd, pivot+1, length(sd) - pivot);
flip := PChar(sd);
end;

function reverse ( s : pchar) : pchar; cdecl;
var
  sd : string;
  sout : string;
  i : integer;
begin
sd := string(s);
if length(sd) = 0
then
begin
  sout := s;
  exit;
end;
sout := '';
for i :=1 to length(sd)
do
  sout := sout + sd[i];
reverse := PChar(sout);
end;

Initialization
  IsMultithread := true;
  CheckIBloaded;

end.

/-- liaison entre les UDF et les modules externes

DECLARE EXTERNAL FUNCTION FLIP
   CSTRING(256), DOUBLE PRECISION
   RETURNS CSTRING(256)
   ENTRY_POINT "flip"
   MODULE_NAME "C:\IB\UDF\UdfIBsqlPro.dll"
DECLARE EXTERNAL FUNCTION REVERSE
   CSTRING(256)
   RETURNS CSTRING(256)
   ENTRY_POINT "reverse"
   MODULE_NAME "C:\IB\UDF\UdfIBsqlPro.dll"

COMMIT

-- exemple d'utilisation

SELECT FLIP(COL1, 3), REVERSE(COL2)
FROM TEST

-- Fred BROUARD - 2002-10-11 
-- extrait d'une chaine de caractères uniquement les chiffres sans aucun espace

CREATE  FUNCTION FN_FORMATE_CHIFFRE_SEUL (@VALUE VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
-- effets de bord : NULL
   IF @VALUE IS NULL
   RETURN @VALUE
-- effet de bord : chaine vide
   IF @VALUE = ''
   RETURN @VALUE
-- cas courant
   DECLARE @NEWVALUE VARCHAR(8000)
   SET @NEWVALUE = ''
   DECLARE @I INTEGER
   SET @I = 1
   WHILE @I <= LEN(@VALUE)
   BEGIN
      IF SUBSTRING(@VALUE, @I, 1) BETWEEN '0' AND '9'
      SET @NEWVALUE = @NEWVALUE + SUBSTRING(@VALUE, @I, 1)
      SET @I = @I + 1
   END 
   RETURN @NEWVALUE
END

-- Fred BROUARD - 2002-10-11 
-- extrait d'une chaine de caractères uniquement des lettre majuscules non diacritqiues,
-- des chiffres et des espaces non doublonnés (conversion des caractères diacritiques)

CREATE  FUNCTION FN_FORMATE_MAJ_SANS_DIAC (@VALUE VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
-- effets de bord : NULL
   IF @VALUE IS NULL
   RETURN @VALUE
-- effet de bord : chaine vide
   IF @VALUE = ''
   RETURN @VALUE
-- cas courant
   DECLARE @NEWVALUE VARCHAR(8000)
   SET @NEWVALUE = ''
   DECLARE @I INTEGER
   SET @I = 1
   DECLARE @C CHAR(1)
   DECLARE @LETTRE BIT
   SET @VALUE = UPPER(@VALUE)
   WHILE @I <= LEN(@VALUE)
   BEGIN
      SET @LETTRE = 0
      SET @C = SUBSTRING(@VALUE, @I, 1)
      IF  CAST(@C AS VARBINARY(32)) BETWEEN CAST('0' AS VARBINARY(32)) 
                                        AND CAST('9' AS VARBINARY(32))
       OR CAST(@C AS VARBINARY(32)) BETWEEN CAST('A' AS VARBINARY(32))
                                        AND CAST('Z' AS VARBINARY(32))
      BEGIN 
          SET @NEWVALUE = @NEWVALUE + SUBSTRING(@VALUE, @I, 1)
          SET @LETTRE = 1
      END
      ELSE
      BEGIN
         IF @C='à' OR @C='À' OR @C='â' OR @C='Â' OR @C='ä' OR @C='Ä' OR @C='Á' 
         OR @C='á' OR @C='Ã' OR @C='ã' OR @C='å' OR @C='Å'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'A'
            SET @LETTRE = 1
         END
         IF @C='æ' OR @C='Æ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'AE'
            SET @LETTRE = 1
         END
         IF @C='Ç' OR @C='ç'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'C'
            SET @LETTRE = 1
         END
         IF @C='é' OR @C='É' OR @C='è' OR @C='È' OR @C='ê' OR @C='Ê' OR @C='ë' OR @C='Ë'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'E'
            SET @LETTRE = 1
         END
         IF @C='î' OR @C='Î' OR @C='ï' OR @C='Ï' OR @C='ì' OR @C='Ì' OR @C='í' OR @C='Í'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'I'
            SET @LETTRE = 1
         END
         IF @C='ñ' OR @C='Ñ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'N'
            SET @LETTRE = 1
         END
         IF @C='ô' OR @C='Ô' OR @C='ö' OR @C='Ö' OR @C='ò' OR @C='ó'
         OR @C='Ò' OR @C='Ó' OR @C='Õ' OR @C='õ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'O'
            SET @LETTRE = 1
         END
         IF @C='œ' OR @C='Œ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'OE'
            SET @LETTRE = 1
         END
         IF @C='ß'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'SS'
            SET @LETTRE = 1
         END
         IF @C='Ù' OR @C='Ú' OR @C='Û' OR @C='Ü' OR @C='ù' OR @C='ú' OR @C='û' OR @C='ü'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'U'
            SET @LETTRE = 1
         END
         IF @C='Ý' OR @C='ý' OR @C='ÿ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'Y'
            SET @LETTRE = 1
         END
         IF @LETTRE = 0
            SET @NEWVALUE = @NEWVALUE + ' '
      END
      SET @I = @I + 1
   END 
-- dédoublonnage des espaces parasites
   SET @VALUE = @NEWVALUE
   SET @NEWVALUE = ''
   SET @I = 1
   SET @LETTRE = 0
   WHILE @I <= LEN(@VALUE)
   BEGIN
      SET @C = SUBSTRING(@VALUE, @I, 1)
      IF  CAST(@C AS VARBINARY(32)) = CAST(' ' AS VARBINARY(32))
      BEGIN
         IF @LETTRE = 1
            SET @NEWVALUE = @NEWVALUE + @C
         SET @LETTRE = 0
      END
      ELSE
      BEGIN
         SET @NEWVALUE = @NEWVALUE + @C 
         SET @LETTRE = 1 
      END
      SET @I = @I + 1 
   END
   RETURN LTRIM(@NEWVALUE)
END

/* obtention de la liste des colonnes d'un table */

-- F. BROUARD - 2002-10-10

CREATE FUNCTION FN_LIST_COLUMNS (@TABLE_NAME VARCHAR(128)) 
RETURNS VARCHAR (8000)
AS
BEGIN
   DECLARE @LIST_COLS VARCHAR(8000)
   SET @LIST_COLS ='('
   SELECT @LIST_COLS = @LIST_COLS + COLUMN_NAME + ', '
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = @TABLE_NAME
   SET @LIST_COLS = SUBSTRING(@LIST_COLS, 1, LEN(@LIST_COLS)-1) + ')'
   RETURN(@LIST_COLS)
END

CREATE TABLE T_PRIX
(PRX_ID INTEGER NOT NULL PRIMARY KEY,
 PRX_PRIX FLOAT NOT NULL)

INSERT INTO T_PRIX VALUES (1, 254.15)
INSERT INTO T_PRIX VALUES (2, 98541.24)
INSERT INTO T_PRIX VALUES (3, 8741.99)

BEGIN TRANSACTION 056512300651324568456521

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
2, 98541.24

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10
3, 9615,10
END FUTURE INFORMATION

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10
3, 9615,10
END FUTURE INFORMATION
TRANSACTION COMPLETE
WORK COMMITED