Tipy a triky pro Oracle I. – jak na automatické přidělování ID?

Autor: David Krch (redakce@dbsvet.cz), Téma: Tipy - triky
Vydáno dne 05. 05. 2006




V prvním pokračování nového seriálu věnovaného praktickým tipům a trikům pro databázovou platformu Oracle Database se podíváme na to, jak se vytvářejí sloupce s automaticky přidělovanými čísly. Cílem seriálu je přinášet vám nejen praktické informace, které vám mohou pomoci při správě či vývoji na této platformě, ale také upozorňovat na zajímavé funkce. O kterých možná ani nevíte, že existují.

Pokud se s databázovou platformou Oracle setkáváte poprvé, asi budete překvapeni tím, jakým způsobem se v ní vytvářejí sloupce s automaticky přidělovanými čísly. Na rozdíl od jiných databází totiž v Oracle nenajdete datový typ IDENTITY, SERIAL, AUTONUMBER ani žádný podobný. Přesto je však řešení velmi jednoduché, když ovšem víte jak na to.

Oracle má pro tyto účely speciální typ databázového objektu – sekvence. Ty po vytvoření postupně vrací čísla z definované číselné řady. Mechanismus je navržen tak, aby přidělené číslo již nemohla získat jiná transakce, ale aby zároveň bylo minimalizováno vzájemné blokování transakcí i počet I/O operací.

Sekvenci vytvoříte podobně jako jiné databázové objekty, a to příkazem CREATE. I když je možné specifikovat řadu různých parametrů jako například počáteční a maximální hodnotu sekvence, velikost kroku nebo parametry pro využití cache, obvykle vystačíte s touto prostou podobou příkazu:

CREATE SEQUENCE zakaznici_seq;

Od chvíle kdy sekvenci vytvoříte, můžete kdykoliv přidělit políčku další hodnotu ze sekvence například pomocí příkazu:

INSERT INTO zakaznici (zakaznik_id,nazev)
   VALUES (zakaznici_seq.nextval, 'Šroubek a Vroubek');

Tímto příkazem vytvoříte nový záznam v tabulce ZAKAZNICI. Pole ZAKAZNIK_ID bude naplněno hodnotou ze sekvence (to zajišťuje zápis <jménoSeqvence>.nextval). Pokud se nechcete o vyplňování hodnot ze sekvence starat v aplikaci, stačí definovat trigger nad danou tabulkou:

CREATE OR REPLACE TRIGGER zakaznici_ins
  BEFORE INSERT ON zakaznici
  FOR EACH ROW
  BEGIN
    SELECT zakaznici_seq.nextval 
           INTO :new.zakaznik_id
      FROM dual;
  END;
/

Tento trigger se automaticky spouští před vložením každého řádku (BEFORE INSERT, FOR EACH ROW). Protože sekvence je databázový objekt, nelze v Oracle provést prosté přiřazení, ale hodnotu je třeba načíst v dotazu. Ideální je použít speciální tabulku DUAL, která vždy vrací jediný záznam. Hodnota se v tomto případě načte do sloupce ZAKAZNIK_ID.

Od této chvíle se vám budou při vkládání záznamů do tabulky ZAKAZNICI automaticky přiřazovat do pole ZAKAZNIK_ID hodnoty 1, 2, 3... atd.

Vypadá to složitě? Možná. Nové tabulky ale obvykle nedefinujete každý den a použití sekvence v Oracle nabízí více možností, než jen prosté přidělování čísel po jedné:

  1. Pokud již před definicí automatického číslování máte v tabulce záznamy, jejichž ID chcete ponechat, stačí prostě nadefinovat sekvenci tak, aby začínala s hodnotou vyšší, než je nejvyšší stávající hodnota v tabulce. Pokud jsou tedy všechny hodnoty daného sloupce v tabulce nižší než 1000, stačí sekvenci nadefinovat takto:

    CREATE SEQUENCE zakaznici_seq START WITH 1000;
    

  2. Pokud importujete data do více tabulek a chcete, aby si importovaná data kvůli vzájemným vazbám ponechala původní identifikátory, deaktivujte před importem trigger:

    ALTER TRIGGER zakaznici_ins DISABLE;
    

    Po importu pak již stačí posunout sekvenci tak, aby nové hodnoty byly vyšší než ty importované (v uvedeném případě o 2000) a samozřejmě opětovně aktivovat trigger:

    SELECT zakaznici_seq.nextval
      FROM ALL_OBJECTS WHERE ROWNUM<=2000;
    
    ALTER TRIGGER zakaznici_ins ENABLE;
    

  3. Pokud potřebujete zajistit jednoznačnou identifikaci objektů umístěných ve více tabulkách, použijete pro všechny tabulky stejnou sekvenci.
  4. Hodnota v tabulce nemusí být tvořena jen hodnotou získanou ze sekvence, ale může být vytvořena výpočtem – například jmeno_lokality||zakaznici_seq.nextval.
  5. Sekvenci nemusíte používat jen pro generování jednoznačných identifikátorů záznamů, ale třeba i pro jména generovaných souborů či libovolných jiných objektů.
  6. Sekvenci můžete použít i jako logické hodiny – například při optimistickém zamykání záznamů ve webové aplikaci. V tom případě trigger definujete na INSERT i UPDATE a data ukládáte do samostatného sloupce v tabulce (samozřejmě již nejde o primární klíč). Před ukládáním hodnot z formuláře pak prostě porovnáte aktuální hodnotu uloženou v tabulce s hodnotou získanou při načítání hodnot do formuláře. Pokud po načtení záznamu do formuláře došlo ke změně dat, bude aktuální hodnota v tabulce vyšší než hodnota načtená do formuláře. (V 10g ale pro tyto účely spíše použijete pseudosloupec ORA_ROWSCN, který za vás naplňuje databáze sama. O tom ale až někdy příště).
Dnešní ukázky můžete vyzkoušet na libovolné edici Oracle Database, včetně volně dostupné Express Edition. V případě potřeby si Oracle Database můžete stáhnout na webu společnosti Oracle.

O autorovi
David Krch (*1976) pracuje ve společnosti Oracle Czech na pozici Technology Sales Consultant. Vystudoval obor Informační technologie na Vysoké škole ekonomické. Od roku 2001 působí ve firmě Oracle Czech na pozici Technology Sales Consultant, ve které je zodpovědný za podporu prodeje technologií Oracle. Z počátku se zaměřoval na portálová řešení. Od roku 2002 se specializuje na podporu prodeje databáze Oracle a témata související se zajištěním provozu, jako je bezpečnost a vysoká dostupnost. Jako nezávislý vývojář vyvíjel na zakázku databázové aplikace s využitím desktopových databází.