Discussion :

[Cyrano]

Bonjour,
Je fouille depuis un moment pour trouver comment récupérer une valeur de clé primaire sous Oracle (10g me semble-t-il) et je ne suis pas un vieil habitué d'Oracle en général.

Chacune de mes tables a une clé primaire assortie d'une séquence et d'un trigger pour une auto-incrémentation automatique.

Mes insertions ne mentionnent donc pas la colonne de clé primaire. Ce faisant, je n'ai nul besoin d'indiquer de valeur de clé primaire avec <nom_sequence.nextval>. Ça fonctionne fort bien, mais je me heurte à un problème : mes insertions se font via des requêtes paramétrées dans des packages. J'ai donc dans mon package une procédure pour l'insertion. J'ai des paramètres de retour (OUT) ERRCODE et ERRMSG, mais je cherche surtout comment récupérer la valeur de clé primaire générée en étant sûr que, si une insertion concurrente à eu lieu en parallèle par un autre utilisateur, j'ai bien la clé correspondant à ma requête.

Toute indication ou piste vers une doc appropriée sera grandement bienvenue.

Merci d'avance.

[mnitu]

insert into ... returning ... ou sequence.currval.

[Cyrano]

Merci pour la rapidité mnitu,
Corrige moi si je me trompe :

1. déclarer une variable OUT pour recevoir la valeur;
2. Après la requête d'insertion, ajouter ce second INSERT pour récupérer seq.currval avant le END nom_procedure;

Quelque chose dans ce goût-là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  -- -----------------------------------
  -- Ajout d'un centre
  -- -----------------------------------
  PROCEDURE insert_centre(
    ERRCODE             IN OUT NUMBER,
    ERRMSG              IN OUT NOCOPY VARCHAR2,
    ID_CENTRE           OUT NUMBER,
    -- reste de mes parametres ....
  ) AS
  BEGIN
    INSERT INTO LOG_CENTRE_CTR (
        -- colonnes à remplir
    )
    VALUES(
        -- valeurs correspondantes
    );
    ID_CENTRE := SEQ_PK_CTR.CURRVAL;
    RETURN ID_CENTRE;
  END insert_centre;

?

[mnitu]

Plutôt ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
INSERT INTO employees 
      (employee_id, last_name, email, hire_date, job_id, salary)
   VALUES 
   (employees_seq.nextval, 'Doe', 'john.doe@oracle.com', 
       SYSDATE, 'SH_CLERK', 2400) 
   RETURNING salary*12, job_id INTO l_bnd1, l_bnd2

[Cyrano]

Ben alors mon trigger ne sert plus à rien si je dois indiquer explicitement la valeur de la clé primaire dans la requête. Or comme expliqué en préambule, je ne l'indique pas :
-1- Pour ma table, j'ai une séquence pour la clé primaire, comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE SEQUENCE  "SEQ_PK_CTR"  
MINVALUE 1 
MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 
START WITH 1 
CACHE 20 
NOORDER  
NOCYCLE ;

-2- Pour la table, j'ai un trigger comme celui-ci par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
create or replace TRIGGER "TIB_LOG_PK_CTR" 
before insert on "LOG_CENTRE_CTR"   
for each row 
begin    
    if inserting then      
        if:NEW."CTR_ID" is null then         
            select SEQ_PK_CTR.nextval
            into :NEW."CTR_ID"
            from dual;      
        end if;   
    end if; 
end;

-3- ma requête d'insertion comprendra donc les noms des colonnes autres que la clé primaire et je n'indiquerai pas de valeur non plus, le trigger s'en charge.

Donc le problème lors de l'appel de ma procédure consiste à récupérer la valeur générée. Voilà pourquoi j'ai songé à cette syntaxe pour le faire en assignant la valeur SEQ_PK_CTR.currval à ma variable déclarée OUT en début de procédure, un équivalent en quelque sorte d'un last_insert_id

Faut que je fasse des tests, pour l'instant j'en suis à l'écriture des packages, je n'ai pas attaqué le code appelant qui enverra les données. Après réflexion, la formule serait probablement davantage sous la forme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
-- ... code de la procédure ...
  SELECT SEQ_PK_CTR.CURRVAL 
  INTO ID_CENTRE 
  FROM DUAL;
END;

Ce que je ne sais pas, c'est de savoir si ça me récupèrera la bonne valeur, ce qui impliquerait que ça prendrait en compte l'insertion juste avant et donc que l'incrémentation par le trigger aurait correctement été effectuée, et encore une fois qu'aucune insertion concurrente par un autre utilisateur ne risquerait de fausser le résultat.

[mnitu]

L'exemple c'était pour la syntaxe du insert ... returning et non pas pour supprimer le trigger.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
create table tutu (
  id    number primary key,
  txt   varchar2(10)
)
/
create sequence seq_tutu_id
/
CREATE OR REPLACE TRIGGER "BI_TUTU" 
before INSERT ON "TUTU"   
FOR each row 
begin
  if :new.id is null then
   select seq_tutu_id.nextval into :new.id from dual;
  end if;
end;
/
SQL> variable n number;
SQL> insert into tutu (txt) values ('test') returning id into :n;
 
1 row inserted
n
---------
1
 
SQL> print n
n
---------
1

Bien sûr qu'à la place du n tu peux utiliser une variable pl_sql.