Im letzten Post habe ich beschrieben, mit welchem einfachen Befehl man Sequenzen auf 1 resetten kann.
Das ist in der Regel nur dann sinnvoll, wenn alle Tabellen eines Schema geleert wurden. Auf Basis der Erkenntnisse des Posts würde ich jetzt ein sinnvolleres Szenario betrachten.
Oftmals legt man nur bestimmte Testdaten ggf. über Sequenzen an und möchte nach Bereinigung der Testdaten dann die Sequenzen auf den Maximalwert der ID(+1) der entsprechenden Tabelle zu setzen.
Eine Art, ID’s und Sequenzen zu koppeln, ist über eine einfache “META”-Tabelle. Über ein kleines PL/SQL-Script kann dann die passende Sequenz aus der Tabelle ermittelt und z.B. über ein Trigger abgerufen werden.
Hier beschreibe ich mal, wie das geht.
Die META-Tabelle ist relativ einfach aufgebaut, besteht eigentlich nur aus dem Tabellen-Namen (TABLE_NAME) und der Sequenz (SEQUENCE_NAME). Sollten mehrere Spalten einer Tabelle gefüllt werden müssen, könnte noch eine Spalte COLUMN_NAME hinzugefügt werden, da in den meisten Fällen die Sequenzen nur für die ID-Generierung genutzt wird, nutze ich erst einmal die einfachere Variante.
Als erstes wird die Tabelle angelegt:
-- Erst mal die Tabelle anlegen CREATE TABLE META_SEQUENCES( TABLE_NAME VARCHAR2(40) NOT NULL, SEQUENCE_NAME VARCHAR2(40) NOT NULL ) / -- ein unique Index auf die Tabelle - pro Tabelle soll es nur eine Sequenz geben CREATE UNIQUE INDEX UC_META_TAB_IDX ON META_SEQUENCES(TABLE_NAME) / -- auch noch einen Unique-Index auf der Sequence anlegen, Sequenzen sollten auch nicht mehrfach verwendet werden CREATE UNIQUE INDEX UC_META_SEQ_IDX ON META_SEQUENCES(SEQUENCE_NAME) /
Nun legen wir eine kleine PL/SQL-Funktion an, die anhand des übergebenen Tabellen-Namens den nächsten Wert der Sequenz ermittelt.
CREATE OR REPLACE FUNCTION GET_SEQUENCE_BY_TABLENAME(V_TABLE_NAME VARCHAR2) RETURN NUMBER AS v_sequence_val NUMBER; v_sequence_name VARCHAR2(40); BEGIN SELECT sequence_name INTO v_sequence_name FROM META_SEQUENCES WHERE TABLE_NAME=V_TABLE_NAME; EXECUTE IMMEDIATE 'SELECT '||v_sequence_name||'.nextval FROM DUAL' INTO v_sequence_val; RETURN v_sequence_val; END; /
Diese Methode kann dann z.B. in Triggern genutzt werden, um die nächste ID zu erzeugen.
Mit Hilfe der Tabelle und unserem Wissen aus dem letzten Post kann man nun noch eine Reset-Prozedur erstellen, der z.B. nach Testdaten-Löschung die Sequenz auf den letzten Wert resettet.
CREATE OR REPLACE PROCEDURE RESET_SEQUENCES AS CURSOR v_cursor IS SELECT table_name, sequence_name FROM META_SEQUENCES; v_max_id NUMBER; BEGIN FOR v_dataset IN v_cursor LOOP --Abruf der letzten ID der entsprechenden Tabelle EXECUTE IMMEDIATE 'select nvl(max(id),0)+1 from '||v_dataset.table_name INTO v_max_id; -- Reset der Sequenz EXECUTE IMMEDIATE 'ALTER SEQUENCE '||v_dataset.sequence_name||' RESTART START WITH '||v_max_id; END LOOP; END; /
Das war es schon. Werden jetzt Testdaten angelegt und später gelöscht, kann nach der Testdaten-Löschung diese Routine ausgeführt werden und bei allen Tabellen, die in der META_SEQUENCES gepflegt sind, werden dann die entsprechenden Sequenzen auf den Maximalwert+1 der letzten ID gesetzt.