Oracle: Sequenzen

Aus Wikibooks

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.

[Bearbeiten] 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;

[Bearbeiten] 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:

1. 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.
2. 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 Cash gespeichert werden. Wenn die Datenbank heruntergefahren wird, dann gehen die im Cash gespeicherten Werte verloren.

Katalogtabelle zu Sequenzen:

select * from user_sequences;