poniedziałek, 8 listopada 2010

Kurs Oracle SQL. Sekwencje

Tworzenie sekwencji


Sekwencja jest obiektem podającym kolejne wartości wg ustalonych kryteriów. Sekwencje stosujemy często w celu tworzenia kluczy głównych tabel. 
Umyślnie daję nieco bardziej "zakręcony" temat przed dodawaniem danych, ponieważ przy insercie będziemy korzystali właśnie z sekwencji. Po co przy wstawianiu kolejnych wierszy  podawać  "z palca" kolejny numerek którym po którym dany wiersz będziemy identyfikować? Łatwo się przy tym pomylić i wstawić numer który już istnieje, a poza tym wiąże się to z dość upierdliwym sprawdzaniem ostatniego numerka. Niech zrobi to za nas automat!




Na podstawie wyżej przytoczonego przykładu:

create sequence

Po tym następuje podanie nazwy sekwencji. W nazwie sekwencji nie może być spacji.

Minvalue

Wartość minimalna jaką może przybrać sekwencja.

 

Maxvalue

Wartość maksymalna jaką może przybrać sekwencja.

 

Start with

Określa wartość od jakiej ma się rozpoczynać sekwencja.

Increment by

Określa wartość o jaką ma się zmieniać aktualny stan sekwencji po każdym pobraniu danych.
Jako parametr tego polecenia możemy również podać liczbę ujemną. Wartość sekwencji będzie wtedy maleć.

Rzadziej używanymi opcjami sekwencji są cycle oraz cache.

Cycle / nocycle

Parametr ten określa czy sekwencja może się przekręcić (jak licznik w samochodzie, nie jak teściowa) i rozpocząć naliczanie od początku.


Cache / nocache

klauzula CACHE włącza wykonywanie pre-alokacji numerów sekwencji i przechowywanie ich w pamięci, co skutkuje zwiększeniem szybkości generacji kolejnych liczb. Klauzula NOCACHE wyłącza tę możliwość. Domyślnie przyjmowane jest CACHE 20. Wartość podana w CACHE musi być mniejsza niż MAXVALUE - MINVALUE


Order / noorder
Klauzula ORDER gwarantuje, że kolejne liczby będą generowane w porządku jakim otrzymane zostały przez system polecenia ich generacji. Klauzula NOORDER wyłączą tę własność.


Pobieranie wartości z sekwencji


Do pobierania danych z sekwencji, wykorzystywane są dwie pseudokolumny currval i nextval.

Nextval

Służy do pobierania następnej wartości z sekwencji.

Currval

Służy do pobierania aktualnej wartości sekwencji. Przed wykorzystaniem currval należy wykonać przynajmniej raz nextval na danej sekwencji. W przypadku braku takiej inicjacji, zarówno currval jak i nextval będą miały tą samą wartość odpowiadającą parametrowi start with dla danej sekwencji.
Pobierać wartości z sekwencji możemy na dwa sposoby. Wykorzystywać do tego select na tabeli (np. dual) lub w instrukcjach DML. O tym drugim sposobie będziemy mówić nieco później.Poniżej przytaczam przykłady pobrania wartości currval i nextval oraz różnice pomiędzy nimi.


wykorzystanie sekwencji w instrukcjach DML


Dodając dane do tabeli często zachodzi konieczność wygenerowania dla każdego wpisu unikalnego id dla każdego wiersza. Zdarza się że jest to wymagane z powodu założenia na danej tabeli klucza głównego. W takiej sytuacji najwygodniej jest skorzystać z sekwencji. Sekwencje mogą produkować tylko wartości liczbowe.



Usuwanie sekwencji

Aby usunąć stworzoną sekwencję należy wykorzystać polecenie drop o następującej kontrukcji:



DROP sequence nazwa__sekwencji;

jak to widać poniżej.






Modyfikacja sekwencji

Aby zmienić lub nadać jakiś parametr istniejącej sekwencji stosujemy konstrukcję:

ALTER SEQUENCE NAZWA_SEKWENCJI NAZWA PARAMETRU {WARTOŚĆ};

Nie możemy tylko zmienić parametru START WITH co widać wraz z przykładami prawidłowego zastosowania ALTER SEQUENCE na poniższym obrazku:






Ćwiczenia

  1. Stwórz sekwencję która rozpoczyna się od 1, o wartości maksymalnej 9999 postępującą o 1.
  2. Stwórz sekwencję która rozpoczyna się od 9999, o wartości minimalnej 1 postępującą o 4.
  3. Korzystając z widoku stworzonego w czasie ćwiczeń w dziale „Widoki” zdubluj dane w tabeli regions uzupełniając pole region_id wartością pobraną z sekwencji.
  4. Sprawdź wartość sekwencji nie zmieniając jej wartości.


Ten temat omawiam na poniższych szkoleniach:
• Podstawy Oracle SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

8 komentarzy:

  1. Rety, ale to spowalnia pisanie kodu ręcznie... I dodaje kolejny element, o którego konfiguracji należy pamiętać przy utrzymaniu kodu... Nie mogliby zrobić jakiegos skrótu, domyślnego zachowania, jak to jest w dialekcie MySQL (AUTO_INCREMENT) albo MS SQL (IDENTITY)? W ogóle to fajnie móc skonfigurować właściwości sekwencji, ale żeby tak trzeba było zawsze?

    OdpowiedzUsuń
  2. znowu ten sam błąd.....

    Error starting at line 1 in command:
    create sequence moja_sekwencja
    minvalue 0
    maxvalue 99999
    start with 0
    increment by 1
    Error at Command Line:1 Column:16
    Error report:
    SQL Error: ORA-00955: name is already used by an existing object
    00955. 00000 - "name is already used by an existing object"
    *Cause:
    *Action:

    OdpowiedzUsuń
    Odpowiedzi
    1. Masz już sekwencję o tej nazwie. Podaj inną nazwę sekwencji lub usuń tę sekwencję (DROP SEQUENCE moja_sekwencja;)

      Usuń
  3. czy mógłby ktoś podać pełne rozwiązanie do zadania nr 3 bo już się pogubiłem :(

    OdpowiedzUsuń
  4. Oto moje propozycje rozwiązania zadań (mam wątpliwości co do pkt. 3):

    1) Create sequence sekwencja_1
    minvalue 1
    maxvalue 9999
    start with 1
    increment by 1

    2) Create sequence sekwencja_2
    minvalue 1
    maxvalue 9999
    start with 9999
    increment by -4

    3)
    a) Create or replace view dodawanie_wierszy as Select * from regions
    b) Insert into dodawanie_wierszy select sekwencja_1.nextval, region_name from regions

    4) Select sekwencja_1.currval from dual

    OdpowiedzUsuń
    Odpowiedzi
    1. Ad. 3b) Powinno być:
      Insert into regions select sekwencja_1.nextval, region_name from dodawanie_wierszy

      Usuń
  5. A ja mam pytanie jeśli ktoś czyta tego bloga. Próbuje zrobić sekwencje która będzie uzupełniać dane w kombinacji nr/rok wszystko uzupełnia triger i jak to zrobić żeby sekwencja przekręciła się w nowym roku?

    OdpowiedzUsuń
  6. Ad 3 poprawnie moim zdaniem powinno być tak
    a)Create or replace view testowa as Select * from regions
    b)Insert into regions values (test2.nextval, 'ggg');
    u mnie działa

    OdpowiedzUsuń