Oracle: Sequenzen

Sequenzen sind Generatoren für numerische Werte, die automatisch hochgezählt werden und üblicherweise für Primärschlüsselwerte verwendet werden. Sinn einer Sequenz ist die Vermeidung des beliebten Anfängerfehlers "select max(id)+1 from xyz" zur Erzeugung des nächsten Primärschlüsselwertes.

Syntax zum Erzeugen einer Sequenz

 CREATE SEQUENCE SEQUENCE_NAME
 INCREMENT BY 1  -- Schrittgröße beim Hochzählen
 START WITH 1    -- Startwert
 MINVALUE 1      -- Kleinster Wert
 MAXVALUE 999999 -- Größter Wert
 NOCYCLE / CYCLE -- wieder bei MINVALUE starten wenn MAXVALUE überschritten wurde
 CACHE 20
 NOORDER;

Verwenden von Sequenzen

 select SEQUENCE_NAME.nextval from dual

liefert den nächsten Wert der Sequenz

 select SEQUENCE_NAME.currval from dual

liefert den aktuellen Wert der Sequenz, das heißt, genau den Wert, der beim letzten Aufruf von SEQUENCE_NAME.nextval zurückgeliefert wurde. SEQUENCE_NAME.currval kann erst aufgerufen werden, wenn vorher mindestens einmal SEQUENCE_NAME.nextval aufgerufen worden ist. Mit Currval kann man jedoch nur Werte abfragen, die in der eigenen Session erzeugt wurden. Selbst wenn in anderen parallel laufenden Sessions von der selben Sequence bereits weitere Werte generiert wurden, dann liefert Currval immer noch den zuletzt für die eigene Session generierten Wert. Wenn man in einer Session noch nicht mit Nextval einen Wert generiert hat, dann kann man auch nicht mit Currval den zuletzt generierten Wert abfragen.

Anwendungsbeispiel

 INSERT INTO tabelle1 (num, name) 
 VALUES (SEQUENCE_NAME.nextval, 'Test');

Oft steht man vor dem Problem, dass man den Wert, der soeben in die Datenbank geschrieben wurde weiterverwenden will, z.B. um einen Detaildatensatz in einer Untergeordneten Tabelle anzulegen, der über den Fremdschlüssel num verbunden ist. Hierzu gibt es 2 Möglichkeiten:

Referenzieren über SEQUENCE_NAME.currval. Nachteil hierbei ist, dass insbesondere bei längeren Programmen oftmals nicht sichergestellt werden kann, dass der Wert der Sequenz unverändert ist.
Innerhalb eines PL/SQL-Programms kann man den Wert über die RETURNING-Klausel direkt in eine Variable speichern:

 INSERT INTO tabelle1 (num, name) 
 VALUES (SEQUENCE_NAME.nextval, 'Test')
 RETURNING num INTO v_aktuellerSequenzwert;

Alternativ Speicherung in einer PL/SQL-Variablen:

 SELECT SEQUENCE_NAME.nextval INTO v_aktuellerSequenzwert FROM DUAL;

Beim Verwenden einer Sequenz kann nicht sichergestellt werden, dass die eingetragenen Werte lückenlos aufeinanderfolgen. Das liegt schon daran, dass immer mehrere Werte im Voraus generiert werden und in einem Cache gespeichert werden. Wenn die Datenbank heruntergefahren wird, dann gehen die im Cache gespeicherten Werte verloren.

Dictionary-View zu Sequenzen

 select * from user_sequences;

Trigger (Vorlage) für eine Sequenz

====(ersetze Platzhalter <% %>. ====

==== Zusätzlich (& nützlich): Timestamp's im Trigger. Siehe z.B.: --SELECT SYSDATE INTO :NEW.CHG_DATE FROM DUAL; // ggf. entfernen.====

 CREATE SEQUENCE SQ_<%tableName%> START WITH 1 INCREMENT BY 1 MINVALUE 1;
 / 
 CREATE OR REPLACE TRIGGER TS_<%tableName%> 
 BEFORE INSERT OR UPDATE 
 ON <%tableName%>
 REFERENCING NEW AS NEW OLD AS OLD 
 FOR EACH ROW 
 BEGIN
   IF (INSERTING) THEN 
     --SELECT SYSDATE INTO :NEW.CHG_DATE FROM DUAL; 
     --SELECT SYSDATE INTO :NEW.CRE_DATE FROM DUAL; 
     IF (:NEW.<%pkFieldName%> IS NULL) THEN 
       SELECT SQ_<%tableName%>.NEXTVAL INTO :NEW.<%pkFieldName%> FROM DUAL; 
     END IF; 
 
   ELSIF (UPDATING) THEN
     --SELECT SYSDATE INTO :NEW.CHG_DATE FROM DUAL;
   END IF;
 END;
 /

-- Kein Setzen von SEQUENCE_NAME.nextval in der Abfrage ist nun notwendig, Trigger z.B. TS_tabelle1 macht es nun beim Insert. 
-- Bsp.:
 INSERT INTO tabelle1 (name) VALUES ('Test'); 

-- Nachträgliches holen von gerade erstellten Id mit:
 SELECT  SQ_<%tableName%>.currval as value FROM dual;