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

Date de publication : 08/10/2007

Par Stéphanie Tirtiat (Intl Tech Support)

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

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;

A 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 :

code 4D


  ` [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 :

code 4D


  ` [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 :

code 4D


  ` [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 SGBDRs ou les astuces 4D for OCI de la rubrique Trucs & Astuces.

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'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.