Nawet SQL ma pętle

2009-01-12 12:35

Stwierdzenie ‘programować w HTML’ jest rzecz jasna nadużyciem, ale istnieją przecież inne języki, dla których określenie ‘programistyczne’ (lub jego brak) nie jest wcale takie oczywiste. Weźmy choćby SQL, teoretycznie pretendujący do miana języków deklaratywnych. Charakteryzują się one tym, że pisząc w nich określamy tylko to, co ma zostać zrobione – nie zaś jak. Na pierwszy rzut oka ma to sens: w końcu pisząc proste lub nawet całkiem skomplikowane zapytanie SELECT w ogóle nas nie interesuje to, przy pomocy jakich struktur danych zostanie ono wykonane i jaka pętla będzie się za nim kryć.
Bo przecież SQL nie ma pętli, prawda? :)

Ano właśnie nieprawda. Ponadto czasami są one jedynym wyjściem, jeśli mamy do czynienia z nieco bardziej skomplikowanymi danymi – jak choćby z jakąś hierarchią drzewiastą. Zwykle zapisuje się ją w relacyjnej bazie danych tak, że każdy element zawiera informacje o identyfikatorze elementu do niego nadrzędnego. Jest to całkowicie wystarczającą informacją do odtworzenia ich hierarchii.
Do takiego drzewka łatwo dodawać nowego elementy, ale ich usuwanie może być już problemem. Wyrzucenie jednej pozycji powinno bowiem oznaczać odcięcie całego poddrzewa – zwłaszcza że na pozostawienie osieroconych potomków często nie pozwoli sam silnik bazy danych, jeśli sprawdza poprawność relacji. Musimy zatem usuwać od dołu, a następnie przesuwać się w górę… Tylko jak niby zapisać to w postaci zapytania SQL?
Okazuje się, że nic prostszego. No, a przynajmniej okazuje się, że da się to zrobić:

  1. CREATE PROCEDURE DeleteItem
  2.     @ID int
  3. AS
  4. BEGIN
  5.     DECLARE @cur CURSOR
  6.     DECLARE @ChildID int
  7.    
  8.     SET @cur = CURSOR FOR (SELECT ID FROM Items WHERE ParentID = @ID)
  9.     OPEN @cur
  10.     FETCH NEXT FROM @cur INTO @ChildID
  11.     WHILE @@FETCH_STATUS = 0
  12.     BEGIN
  13.         EXECUTE DeleteItem @ChildID
  14.         FETCH NEXT FROM @cur INTO @ChildID
  15.     END
  16.    
  17.     DELETE FROM Items WHERE ID = @ID
  18. END

Nawet bez specjalnej znajomości składni można się domyślić, co tutaj jest wykonywane. Oto używamy kursora (coś w stylu iteratora), żeby najpierw usunąć elementy podrzędne do tego, który zamierzamy wykasować. W tym celu dla każdego z nich wywołujemy po prostu tę samą procedurę. Na koniec dokonujemy usunięcia pierwotnego elementu, który teraz na pewno jest już liściem (nie ma żadnych potomków), więc może być wyrzucony bez przeszkód.

Całkiem proste, czyż nie? Można powiedzieć, że w każdym języku programowania algorytm ten wyglądałby podobnie… Sęk w tym, że tu właśnie tkwi problem. Bo skoro w rzekomo deklaratywnym języku SQL można (a w tej sytuacji nawet trzeba) używać takich narzędzi jak pętle czy rekurencja, to przecież nie różni się on wtedy niczym od “normalnych” języków programowania. Jeśli całą operację trzeba zakodować krok po kroku, to nie mamy już żadnej korzyści z filozofii polegającej na określaniu ‘co’, a nie ‘jak’.
Może więc znaczy to, że inaczej programować po prostu się nie da? :)

Tags: ,
Author: Xion, posted under Programming »


7 comments for post “Nawet SQL ma pętle”.
  1. Kos:
    January 12th, 2009 o 13:46

    SQL sam z siebie AFAIK nie ma takich bajerów – jeśli coś wyniosłem z wykładów, to to, że takie bajery są zapewnione w np. microsoftowym TransactSQL, nieraz określanym mianem “funkcyjnego rozszerzenia SQLa”. :)

  2. Force:
    January 12th, 2009 o 18:20

    W MySQL-u procedura? No i przecież wystarczy tworząc tabele jak są relacja dopisać np.
    FOREIGN KEY(ParentUID)
    REFERENCES Object(UID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    i będzie to samo co Twoja procedura, a i pewnie szybsze, bez rekurencji i pewnie silnik jakoś tam optymalizuje tabele aby szybko móc relacje wyciągać między wierszami

  3. Xion:
    January 13th, 2009 o 0:12

    O, dobrze wiedzieć :) Akurat część definicyjna SQL-a jest mi znacznie mniej znana, więc tego akurat nie wiedziałem.

  4. Kauach:
    January 14th, 2009 o 9:02

    Powiem Ci, że zastosowanie hierarchii drzewiastej jest w bazach danych raczej średnim pomysłem. Szczególnie jeśli są to drzewa często modyfikowane. T-SQL o ile wiem nie przepada za kursorami i ta procedura usuwania dla wielu rekordów będzie koszmarnie wolna. W takich wypadkach chyba lepiej przemyśleć strukturę danych i nie używać drzewa.
    Co innego jeśli to np. rzadko modyfikowane dane. Wtedy nie ma to znaczenia =).

    Pozdrawiam

  5. czoper:
    January 15th, 2009 o 12:54

    W tym przypadku rzeczywiście ustawienie ON DELETE CASCADE jest rozwiązaniem czysto intuicyjnym. Co innego, gdy chcemy mieć całkowitą kontrolę nad wykonywanym kodem i usuwanymi elementami – np. wyświetlać ostrzeżenia/przerywać usuwanie w zależności od przetwarzanych danych.

    W PL/SQL (rozszerzenie SQL-a do baz danych Oracle) rekurencję da się stosować bez problemu, a korzystanie z kursorów to czysta przyjemność.

  6. Kauach:
    January 15th, 2009 o 13:05

    panie – przyjemność przyjemnością, a szybkość szybkością =). Jak w T-SQL walniesz kursor na milion rekordów to Ci się baza z**ra. Podobno Oracle jest pod tym względem bardziej cursor friendly =)

  7. skowronkow:
    January 28th, 2009 o 2:51

    Oracle to nieco inna liga. Jest glownie przeznaczony do hurtowni danych, ma swietnie dzialajace CBO i coz, jest naprawde szybki. Co do artykulu to nie jest SQL (jesli juz to PL/SQL lub siakis inny PL/pgSQL czy T-SQL).

Comments are disabled.
 


© 2017 Karol Kuczmarski "Xion". Layout by Urszulka. Powered by WordPress with QuickLaTeX.com.