Retourner le dernier enregistrement créé dans une table Oracle (INSERT INTO...RETURNING)

Stéphanie Tirtiat

Retourner l'identifiant d'un INSERT dans une table Oracle avec 4D for OCI et une requête de type INSERT INTO...RETURNING. ♪

- Titre : Retourner le dernier enregistrement créé dans une table Oracle (INSERT INTO...RETURNING)

- Auteur : Stéphanie Tirtiat

- Parution : 8 octobre 2007

- Licence : Copyright ® 2007 4D s.a. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

Produit : 4D for OCI, versions 2004 et v11 SQL · Plateforme : Mac & Win

Dans une table Oracle, vous avez une colonne définie en primary key et incrémentée automatiquement par un numéro séquentiel.
Comment obtenir cette primary key ou l'identifiant du dernier INSERT ?

Plusieurs possibilités de retourner la clé correspondant à l'enregistrement le plus récent :

La fonction Max :

WHERE primary_key = SELECT (max(primary_key) FROM my_table);

La variable rowid et la fonction Max :

WHERE rowid in (SELECT max(rowid) FROM my_table);

La variable currval :

SELECT table_sequence.currval FROM dual;

Ces trois solutions retournent un identifiant de la dernière row insérée pour toute la table Oracle. C'est-à-dire que si Pierre insère un enregistrement après vous, le rowID sera celui de l'enregistrement de Pierre.

Or, vous voulez retourner le ROWID de votre dernier INSERT.

La solution est d'utiliser une requête de type INSERT INTO...RETURNING.

Exemple :

Description de notre table de test "test_autoid" dans Oracle :

Name

Null

Type

AUTOID

NOT NULL

NUMBER

ZENAME

VARCHAR2(100)

La colonne AUTOID étant auto-incrémentée de n+1 à chaque INSERT.

Utilisation d'une requête INSERT...RETURNING dans une procédure stockée :

Cette procédure Oracle retourne la pseudo-colonne ROWID :

create or replace PROCEDURE TROWID
(vRowID OUT rowid) IS
BEGIN
insert into test_autoid (zename) values ('xxxxx') returning rowid into vRowID;
END;

À l'exécution, le code retourne :

VROWID = AAAOLfAAEAAAEZmAAA

Ce qui correspond à la ligne que l'on vient d'insérer.

Vous pouvez également retourner une colonne de votre table Oracle.

Intégration de la requête avec 4D for OCI :

En utilisant une procédure stockée et ROWID :

` [déclarations, initialisations...]
` [connexion...]

`WRITE PROC
$vlStatus:=OCIHandleAlloc (<>envhp;$vlstmthp;OCI_HTYPE_STMT )
$vlRequeteSql:=$vlRequeteSql+"create or replace PROCEDURE TROWID"+Char(LF ASCII code )
$vlRequeteSql:=$vlRequeteSql+"(vRowID OUT ROWID) IS"+Char(LF ASCII code )
$vlRequeteSql:=$vlRequeteSql+"BEGIN"+Char(LF ASCII code )
$vlRequeteSql:=$vlRequeteSql+"insert into test_autoid (zename) values ('abcdefgh')
returning rowid into vRowID;"+Char(LF ASCII code )
$vlRequeteSql:=$vlRequeteSql+"END;"
$vlStatus:=OCIStmtPrepare ($vlstmthp;vperrhp;$vlRequeteSql;OCI_DEFAULT )
$vlStatus:=OCIStmtExecute (<>svchp;$vlstmthp;vperrhp;1;0;0;0;OCI_DEFAULT )

`EXECUTE PROC
$vlStatus:=OCIHandleAlloc (<>envhp;$vlstmthp;OCI_HTYPE_STMT )
$vlRequeteSql:=""
$vlRequeteSql:=$vlRequeteSql+"BEGIN"+Char(LF ASCII code )
$vlRequeteSql:=$vlRequeteSql+"TROWID(:vrowid);"+Char(LF ASCII code )
$vlRequeteSql:=$vlRequeteSql+"END;"
C_POINTER(pnull1;pnull2;pnull3)
ARRAY STRING(255;vt_RowID;1)
C_LONGINT(vl_Bind)
$vlStatus:=OCIStmtPrepare ($vlstmthp;vperrhp;$vlRequeteSql;OCI_DEFAULT )
$vlStatus:=OCIBindByName ($vlstmthp;vl_Bind;vperrhp;":vrowid";->vt_RowID;SQLT_STR ;
pnull1;pnull2;pnull3;OCI_DEFAULT ;BIND_OUT )
$vlStatus:=OCIStmtExecute (<>svchp;$vlstmthp;vperrhp;1;0;0;0;OCI_DEFAULT )

` [déconnexion, cleanup...]

En envoyant une requête directement, toujours avec le ROWID :

` [déclarations, initialisations...]
` [connexion...]

$vlStatus:=OCIHandleAlloc (<>envhp;$vlstmthp;OCI_HTYPE_STMT )
$vlRequeteSql:="insert into test_autoid (zename) values ('toto')
returning rowid into :vrowid"
C_POINTER(pnull1;pnull2;pnull3)
ARRAY STRING(255;vt_RowID;1)
C_LONGINT(vl_Bind)
$vlStatus:=OCIStmtPrepare ($vlstmthp;vperrhp;$vlRequeteSql;OCI_DEFAULT )
$vlStatus:=OCIBindByName ($vlstmthp;vl_Bind;vperrhp;":vrowid";->vt_RowID;SQLT_STR ;
pnull1;pnull2;pnull3;OCI_DEFAULT ;BIND_OUT )
$vlStatus:=OCIStmtExecute (<>svchp;$vlstmthp;vperrhp;1;0;0;0;OCI_DEFAULT )

` [déconnexion, cleanup...]

Et une variante du code ci-dessus qui retourne la valeur de notre colonne auto-incrémentée AUTOID :

` [déclarations, initialisations...]
` [connexion...]

$vlStatus:=OCIHandleAlloc (<>envhp;$vlstmthp;OCI_HTYPE_STMT )
$vlRequeteSql:="insert into test_autoid (zename) values ('toto')
returning autoid into :vautoid"
C_POINTEUR(pnull1;pnull2;pnull3)
TABLEAU ALPHA(255;vt_AutoID;1)
C_ENTIER LONG(vl_Bind)
$vlStatus:=OCIStmtPrepare ($vlstmthp;vperrhp;$vlRequeteSql;OCI_DEFAULT )
$vlStatus:=OCIBindByName ($vlstmthp;vl_Bind;vperrhp;":vautoid";->vt_AutoID;SQLT_STR ;
pnull1;pnull2;pnull3;OCI_DEFAULT ;BIND_OUT )
$vlErreur:=OCIErrorGet (vperrhp;1;$Numer;$Contents)
$vlStatus:=OCIStmtExecute (<>svchp;$vlstmthp;vperrhp;1;0;0;0;OCI_DEFAULT )

` [déconnexion, cleanup...]

Pour d'autres exemples de code 4D for OCI, voir les ressources de la rubrique 4D & autres SGBDR ou les astuces 4D for OCI de la rubrique Trucs & Astuces.