Im letzten Post habe ich beschrieben, mit welchem einfachen Befehl man Sequenzen auf 1 resetten kann.
\nDas ist in der Regel nur dann sinnvoll, wenn alle Tabellen eines Schema geleert wurden. Auf Basis der Erkenntnisse des Posts w\u00fcrde ich jetzt ein sinnvolleres Szenario betrachten.<\/p>\n
Oftmals legt man nur bestimmte Testdaten ggf. \u00fcber Sequenzen an und m\u00f6chte nach Bereinigung der Testdaten dann die Sequenzen auf den Maximalwert der ID(+1) der entsprechenden Tabelle zu setzen.<\/p>\n
Eine Art, ID\u2019s und Sequenzen zu koppeln, ist \u00fcber eine einfache \u201cMETA\u201d-Tabelle. \u00dcber ein kleines PL\/SQL-Script kann dann die passende Sequenz aus der Tabelle ermittelt und z.B. \u00fcber ein Trigger abgerufen werden.<\/p>\n
Hier beschreibe ich mal, wie das geht.<\/p>\n
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\u00fcllt werden m\u00fcssen, k\u00f6nnte noch eine Spalte COLUMN_NAME hinzugef\u00fcgt werden, da in den meisten F\u00e4llen die Sequenzen nur f\u00fcr die ID-Generierung genutzt wird, nutze ich erst einmal die einfachere Variante.<\/p>\n
Als erstes wird die Tabelle angelegt:<\/p>\n
--\u00a0Erst\u00a0mal\u00a0die\u00a0Tabelle\u00a0anlegen\r\nCREATE\u00a0TABLE\u00a0META_SEQUENCES(\r\n\u00a0\u00a0\u00a0TABLE_NAME\u00a0VARCHAR2(40)\u00a0NOT\u00a0NULL,\r\n\u00a0\u00a0\u00a0SEQUENCE_NAME\u00a0VARCHAR2(40)\u00a0NOT\u00a0NULL\r\n)\r\n\/\r\n\r\n--\u00a0ein\u00a0unique\u00a0Index\u00a0auf\u00a0die\u00a0Tabelle\u00a0-\u00a0pro\u00a0Tabelle\u00a0soll\u00a0es\u00a0nur\u00a0eine\u00a0Sequenz\u00a0geben\r\nCREATE\u00a0UNIQUE\u00a0INDEX\u00a0UC_META_TAB_IDX\u00a0ON\u00a0META_SEQUENCES(TABLE_NAME)\r\n\/\r\n\r\n--\u00a0auch\u00a0noch\u00a0einen\u00a0Unique-Index\u00a0auf\u00a0der\u00a0Sequence\u00a0anlegen,\u00a0Sequenzen\u00a0sollten\u00a0auch\u00a0nicht\u00a0mehrfach\u00a0verwendet\u00a0werden\r\n\r\nCREATE\u00a0UNIQUE\u00a0INDEX\u00a0UC_META_SEQ_IDX\u00a0ON\u00a0META_SEQUENCES(SEQUENCE_NAME)\r\n\/\r\n<\/pre>\nNun legen wir eine kleine PL\/SQL-Funktion an, die anhand des \u00fcbergebenen Tabellen-Namens den n\u00e4chsten Wert der Sequenz ermittelt.<\/p>\n
CREATE\u00a0OR\u00a0REPLACE\u00a0FUNCTION\u00a0GET_SEQUENCE_BY_TABLENAME(V_TABLE_NAME\u00a0VARCHAR2)\u00a0RETURN\u00a0NUMBER\u00a0AS\r\n\u00a0\u00a0v_sequence_val\u00a0number;\r\n\u00a0\u00a0v_sequence_name\u00a0varchar2(40);\r\nBEGIN\r\n\u00a0\u00a0SELECT\u00a0sequence_name\u00a0INTO\u00a0v_sequence_name\u00a0FROM\u00a0META_SEQUENCES\u00a0where\u00a0TABLE_NAME=V_TABLE_NAME;\r\n\u00a0\u00a0execute\u00a0immediate\u00a0'SELECT\u00a0'||v_sequence_name||'.nextval\u00a0FROM\u00a0DUAL'\u00a0INTO\u00a0v_sequence_val;\r\n\u00a0\u00a0return\u00a0v_sequence_val;\r\nEND;\r\n\/\r\n<\/pre>\nDiese Methode kann dann z.B. in Triggern genutzt werden, um die n\u00e4chste ID zu erzeugen.<\/p>\n
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\u00f6schung die Sequenz auf den letzten Wert resettet.<\/p>\n
CREATE\u00a0OR\u00a0REPLACE\u00a0PROCEDURE\u00a0RESET_SEQUENCES\u00a0AS\r\n\u00a0\u00a0Cursor\u00a0v_cursor\u00a0is\u00a0select\u00a0table_name,\u00a0sequence_name\u00a0from\u00a0META_SEQUENCES;\r\n\u00a0\u00a0v_max_id\u00a0number;\r\nBEGIN\r\n\u00a0\u00a0for\u00a0v_dataset\u00a0in\u00a0v_cursor\u00a0loop\r\n\u00a0\u00a0\u00a0\u00a0--Abruf\u00a0der\u00a0letzten\u00a0ID\u00a0der\u00a0entsprechenden\u00a0Tabelle\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0execute\u00a0immediate\u00a0'select\u00a0nvl(max(id),0)+1\u00a0from\u00a0'||v_dataset.table_name\u00a0into\u00a0v_max_id;\r\n\u00a0\u00a0\u00a0\u00a0--\u00a0Reset\u00a0der\u00a0Sequenz\r\n\u00a0\u00a0\u00a0\u00a0execute\u00a0immediate\u00a0'ALTER\u00a0SEQUENCE\u00a0'||v_dataset.sequence_name||'\u00a0RESTART\u00a0START\u00a0WITH\u00a0'||v_max_id;\r\n\u00a0\u00a0end\u00a0loop;\r\nEND;\r\n\/\r\n<\/pre>\nDas war es schon. Werden jetzt Testdaten angelegt und sp\u00e4ter gel\u00f6scht, kann nach der Testdaten-L\u00f6schung diese Routine ausgef\u00fchrt 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u00fcrde ich jetzt ein sinnvolleres Szenario betrachten. Oftmals legt man nur bestimmte Testdaten ggf. \u00fcber Sequenzen an und […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,8],"tags":[],"_links":{"self":[{"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/posts\/133"}],"collection":[{"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/comments?post=133"}],"version-history":[{"count":1,"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":134,"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions\/134"}],"wp:attachment":[{"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/media?parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/categories?post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cssec.de\/blog\/wp-json\/wp\/v2\/tags?post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}