Bazy Danych - poniedziałek, 15.00 - 18.00 - zadania zestaw 10.

PL/SQL (Oracle) - drzewa, procedury składowane, CTE, triggery

Do łączenia się z bazą Oracle używamy SQL*PLUS (na maszynach wirtualnych) lub SQL Command Line (jesli mamy u siebie bazę Oracle 11gR2 XE).

Drzewa


SELECT level, * FROM drzewo
WHERE standardoweWarunkiWyboru
CONNECT BY PRIOR poleOjca = poleDziecka
START WITH poleOjca = wartosc
  • CONNECT BY oznacza krawędzie w drzewie, słowo PRIOR stawiamy przy nazwie pola, które jest identyfikatorem ojca (np. idPracownika, gdzie pracownik-ojciec jest szefem szukanych pracowników). Drugi element w równości oznacza pole w dziecku, które identyfikuje id w polu poleOjca, które jest ojcem (zobacz rysunek). Jest to odpowiednikiem ... UNION ALL cte JOIN tabela on cte.poleOjca = tabela.poleDziecka w rekurencyjnym CTE w MS SQL Server
  • START WITH oznacza warunek, który rozpoczyna rekurencje, odpowiednik SELECT * FROM tabela WHERE warunek UNION ALL ... w CTE w MS SQL Server
  • level psaudokolumna pamiętająca aktualny poziom w drzewie
  • WHERE standardoweWarunkiWyboru w tym miejscu można użyć pseudokolumny level (i innych też - patrz dalej)
  • Nalezy pamiętać, że warunki definiujące zarówno w START WITH jak i CONNECT BY nie muszą być równościami - mogą być dowolnymi warunkami logicznymi, dodatkowo mogą powodowac pętle - Oracle rzuca błąd, chyba że użyjemy odpowiedniej skłądni (nalezy zabezpieczyć przed tym kod).
  • Inne przydatne konstrukcje:

    ORDER SIBLINGS BY pole [, pole2 ..] - zmień kolejność wśród wyświetlanych dzieci

    CONNECT_BY_ROOT pole - umieść w miejscu wyboru pól: SELECT CONNECT_BY_ROOT pole FROM tabela aby w miejscu pole w tabeli wynikowej pojawiła się wartośc z rodzica, a nie z dziecka

    CONNECT_BY_ISCYCLE - pseudokolumna, która mówi, że został znaleziony wierzchołek, który ma dziecko będące wyżej od niego w hierarchi (będące poprzednikiem), aby zadziałało bez błędu nalezy w klauzuli CONNECT BY wpisać NOCYCLE np. CONNECT BY NOCYCLE PRIOR id = parentId

    SYS_CONNECT_BY_PATH(pole, znak) - pozwala budować zapis ścieżki do danego wierzchołka, przykład: SELECT level, SYS_CONNECT_BY_PATH(id, "|") as sciezka FROM ...

Triggery


CREATE [OR REPLACE] TRIGGER nazwa_procedury_wyzwalanej
	<moment uruchomienia> <zdarzenie uruchamiające> [OF <lista pól tabeli tabela>] ON tabela	
	[ FOR EACH ROW ]
	[ WHEN warunek ]
	[ DECLARE <deklaracje zmiennych> ]
BEGIN
	<ciało procedury wyzwalanej>
END;
  • <moment uruchomienia> = AFTER, BEFORE, INSTEAD OF
  • <zdarzenie uruchamiające> = INSERT, UPDATE, DELETE (dla DML, mozliwe są też triggery DDL i Logon), jesli chcemy kilka operacji to łączymy OR: AFTER UPDATE OR INSERT OR DELETE
  • <ciało procedury wyzwalanej> - kod w PL/SQL (zaawansowane mozliwości PL/SQL podobnie jak TSQL podane u dra Telegi w materiałach dla studentów)
  • FOR EACH ROW - ogranicza nieco moc triggera, powoduje wywołanie dla każdego wiersza a nie dla całego zestawu (omówione na ćwiczeniach). Mamy dostęp do flag :OLD i :NEW, które dodajemy przed nazwą kolumny np. IF :OLD.pole != :NEW.pole THEN ...
  • WHEN - wymaga FOR EACH ROW, oznacza kiedy wykonać ten trigger dla wiersza (warunek musi być dla kolumn wiersza tabeli), można użyć :OLD i :NEW, ale bez dwukropka (proszę zapytać twórców bazy danych why;) ja nie mam zielonego pojęcia)
  • w triggerze 'for each statement' (domyślnym) nie mamy dostępu do starych i nowych wartości wierszy (?) - przynajmniej nie udało mi sie tej informacji wyciągnąć z dokumentacji Oracle'a...
  • mozna tworzyć zależności czasowe triggerów - który po którym zostanie wykonany (szczegóły w dokumentacji)

Dla zainteresowanych:

Zadania

Masz dany schemat lotów jak w zestawie 08 (dla ułatwienia radzę wszelkie pola typu VARCHAR(256) zamienić na VARCHAR(8) - inaczej będzie to paskudnie wyglądać w SQL*PLUS). Wykonaj poniższe polecenia wykorzystując język PL/SQL w systemie Oracle (każda osoba, która zaprezentuje rozwiązanie +1 punkt z aktywności)

  1. Stwórz trigger, który po usunięciu wierszy z tabeli bilety zmieni aktualną cenę na niższą o 10% jeśli ilośc biletów na dany lot spadnie poniżej 3. Załóż, że zawsze usuwamy bilety tylko na jeden lot w jednym zapytaniu delete.

  2. Stwórz trigger, który po wstawieniu wierszy do tabeli bilety zmieni ich ceną na aktualną (może być FOR EACH ROW)

  3. Stwórz trigger (może być FOR EACH ROW), który podczas wstawienia biletu sprawdzi czy w samolocie jest jeszcze wolne miejsce.

  4. Stwórz dwa triggery na tabelach Samoloty i Helikoptery, które sprawdzą czy dodawany pojazd ma poprawne id. Jedno id nie może powtarzać się w dwóch tabelach.

  5. Stwórz trigger na tabeli loty, które umozliwi wstawianie tylko takich lotów, których id samolotu jest w tabeli Samoloty lub Helikoptery.

Masz dany schemat:


create table T1(
	id INT NOT NULL,
	name VARCHAR(8),
	charge VARCHAR(4),
	reportsTo INT
)

insert into T1 values (1, 'Smith', 'SGM', NULL);
insert into T1 values (2, 'Celko', 'MSG', NULL);
insert into T1 values (3, 'Ramirez', 'SFC', 2);
insert into T1 values (4, 'Kovalsky', 'SSG', 1);
insert into T1 values (5, 'Plateu', 'SGT', 1);
insert into T1 values (6, 'Gore', 'CPL', 5);
insert into T1 values (7, 'Ryan 1', 'PVT', 5);
insert into T1 values (8, 'Ryan 2', 'PVT', 6);
insert into T1 values (9, 'Ryan 3', 'PVT', 6);
insert into T1 values (10, 'Ryan 4', 'PVT', 3);
insert into T1 values (11, 'Ryan 5', 'PVT', 3);
Pole reportsTo to id przełożonego (opuściłem więzy integralności dla łatwiejszych modyfikacji). Wykonaj poniższe polecenia wykorzystując język PL/SQL w systemie Oracle (każda osoba, która zaprezentuje rozwiązanie +1 punkt z aktywności)
  1. Napisz zapytanie, które zwróci wysokośc drzewa zależności

  2. Napisz zapytanie, które zwróci wszystkich przełożonych nad osobą 'Gore'.

  3. Napisz zapytanie, które zwróci wszystkich podwładnych osoby 'Celko'

  4. Napisz zapytanie, te osoby które są najniżej w hierarchii, wraz z ich poziomem w tej hierarchii

  5. Podaj jakie stopnie i ile jest osób dla danego stopnia, które znajdują się na 3 poziomie zagłębienia w drzewie hierarchii (1 = szef wszystkich szefów (reportsTo is NULL))