SQL ELEMENTY ZAAWANSOWANEmwisla.home.amu.edu.pl/BAD210/W05 SQL 2.pdfZasady tworzenia podzapytań...

050 SQL – ELEMENTY

ZAAWANSOWANE

Prof. dr hab. Marek Wisła

Deklarowanie zmiennych

• DECLARE @nazwa-zmiennej typ-danych {, @nazwa-

zmiennej typ-danych};

deklaruje nazwy zmiennych lokalnych (definiowanych

przez użytkownika) oraz określa typy tych zmiennych

• SET @nazwa-zmiennej = wyrażenie;

• podstawia obliczoną wartość wyrażenia pod zmienną o

podanej nazwie

Instrukcja warunkowa

IF wyrażenie-logiczne polecenie-SQL

[ELSE polecenie-SQL];

• UWAGI:

• Brak słowa THEN przed pierwszym poleceniem SQL,

• Brak słowa END na końcu instrukcji IF.

• Po IF wyrażenie-logiczne wykonywane jest tylko jedno

polecenie SQL

• Po ELSE wykonywane jest tylko jedno polecenie SQL.

Instrukcja WHILE

WHILE wyrażenie-logiczne polecenie-SQL;

• UWAGI:

• Brak słowa END na końcu instrukcji WHILE.

• Po WHILE wyrażenie-logiczne wykonywane jest tylko

jedno polecenie SQL

Bloki instrukcji

BEGIN

polecenie-SQL;

{polecenie-SQL;}

END;

• Blok BEGIN … END pozwala na wykonywanie kilku

zapytań w jednej instrukcji warunkowej IF lub pętli WHILE

Bloki instrukcji

IF wyrażenie-logiczne

BEGIN

polecenie-SQL;

{polecenie-SQL;}

END [ELSE BEGIN

polecenie-SQL;

{polecenie-SQL;}

END];

WHILE wyrażenie-logiczne

BEGIN

polecenie-SQL;

{polecenie-SQL;}

END

Wypisywanie tekstu

• PRINT 'tekst-ASCII' | @zmienna-lokalna | @@zmienna-

globalna;

• UWAGA:

• Argumentami komendy PRINT muszą być zmienne typu

znakowego. Z tego powodu wszystkie zmienne innych

typów muszą zostać w jawny sposób skonwertowane na

typ znakowy np. funkcją CAST lub CONVERT.

Przykład

• Tworzenie prostej strony HTML wyświetlającej wynik

zapytania SQL

Podzapytania

SELECT arg = (SELECT...), arg2, ...

FROM (SELECT ...) a

INNER JOIN (SELECT...) b ON a.pole = b.pole

WHERE arg2 IN (SELECT arg3 FROM...)

Zasady tworzenia podzapytań

• Zdecydowanie unikaj wstawiania podzapytań do listy

argumentów.

• Podzapytanie w klauzuli WHERE musi zwracać

„rozsądną” ilość wartości. Np. sprawdzenie dla 1000

rekordów 1000 możliwych wartości może dać w wyniku

milion sprawdzeń.

• Podzapytania w klauzuli WHERE muszą zawierać tylko

jeden atrybut (kolumnę).

• Nadmierne rozbudowanie zapytania poprzez wstawianie

wielu podzapytań może doprowadzić do znacznego

spowolnienia wykonywania całego zapytania.

Operatory w warunku WHERE

• Operatory porównania

argument operator-porównania [ANY|ALL] (SELECT …)

• ANY – wystarczy, że jeden z wierszy wyniku spełnia warunek porównania

• ALL – wszystkie wiersze wyniku muszą spełniać warunek porównania

• Operator występowania

argument [NOT] IN (SELECT …)

• sprawdza, czy podana wartość [nie] jest w zbiorze wierszy wyniku

• Operator istnienia

[NOT] EXISTS (SELECT …)

sprawdza, czy [nie] istnieje co najmniej jeden wiersz wyniku

Podzapytania skorelowane

• Zapytania wewnętrzne są wykonywane w kolejności od

najgłębiej zagnieżdżonego do najbardziej zewnętrznego.

Wyjątek stanowią tzw. podzapytania skorelowane.

• Podzapytanie skorelowane operuje na wartościach

kolumn przekazywanych przez zapytanie zewnętrzne

(nadrzędne).

• Z syntaktycznego punktu widzenia podzapytanie

skorelowane różni się od podzapytania nieskorelowanego

tym, że w podzapytaniu występuje odwołanie do nazwy

kolumny wiersza, którego dotyczy zapytanie zewnętrzne.

Przykład

Wyświetlić nazwiska, imiona i płace tych pracowników, którzy zarabiają więcej niż średnia płaca pracowników na tym samym stanowisku.

SELECT p.nazwisko, p.imie, p.placa

FROM pracownicy p

WHERE p.placa > (SELECT AVG (placa)

FROM pracownicy

WHERE stanowisko = p.stanowisko)

ORDER BY p.nazwisko, p.imie;

• Odwołanie do nazwy kolumny zapytania zewnętrznego musi wykorzystać alias tabeli zdefiniowanej w zapytaniu zewnętrznym.

Zasady programowania

• Unikaj podzapytań skorelowanych! Znacznie obniżają one efektywność zapytania. W większości przypadków można żądane wyniki uzyskać bez stosowania zapytań skorelowanych.

SELECT p.nazwisko, p.imie, p.placa, s.srednia

FROM pracownicy p

INNER JOIN (SELECT stanowisko, srednia=AVG (placa)

FROM pracownicy

GROUP BY stanowisko

) s ON s.stanowisko = p.stanowisko

WHERE p.placa > s.srednia

ORDER BY p.nazwisko, p.imie;

OPERACJE

MNOGOŚCIOWE

Suma - Union

SELECT (…)

UNION

SELECT (…)

• Daje w wyniku sumę (bez powtórzeń) relacji będących

wynikami pierwszego i drugiego polecenia SELECT.

• Wyniki obydwu poleceń SELECT muszą zawierać tę

samą liczbę kolumn o tych samych typach.

• Fraza ORDER BY może wystąpić tylko w ostatnim

poleceniu SELECT.

Suma – Union All

SELECT (…)

UNION ALL

SELECT (…)

• Daje w wyniku sumę (z powtórzeniami) relacji będących

wynikami pierwszego i drugiego polecenia SELECT.

• Pod względem formalnym wynik zapytania z UNION ALL

może nie być relacją, ponieważ nie musi ona posiadać

unikalnego klucza własnego.

Przykład

SELECT Nazwisko, Imie FROM Studenci

WHERE Kierunek = 'matematyka'

UNION

SELECT Nazwisko, Imie FROM Studenci

WHERE Kierunek = informatyka';

Różnica

SELECT (…)

EXCEPT

SELECT (…)

• Daje w wyniku różnicę relacji będących wynikami poleceń

SELECT.


• Zamiast EXCEPT stosuj odpowiednie złączenie tabel. Na

przykład różnicę Wydawców między tabelami

NazwaWydawcy a Ksiazki można uzyskać następująco:

SELECT NazwaWydawcy

FROM Wydawcy w

LEFT JOIN (SELECT DISTINCT NazwaWydawcy

FROM Ksiazki

) k ON k.NazwaWydawcy = w.NazwaWydawcy

• WHERE k.NazwaWydawcy IS NULL

Iloczyn

SELECT (…)

INTERSECT

SELECT (…)

• Daje w wyniku iloczyn (przekrój) relacji będących

wynikami poleceń SELECT

• Uwagi analogiczne, jak dla operacji sumy.


• Zamiast INTERSECT stosuj odpowiednie złączenie tabel.

Na przykład wszystkich Wydawców, którzy opublikowali

książkę można uzyskać następująco:

SELECT NazwaWydawcy

FROM Wydawcy w

INNER JOIN (SELECT DISTINCT NazwaWydawcy

FROM Ksiazki

) k ON k.NazwaWydawcy = w.NazwaWydawcy

OPERACJE RELACYJNE

Projekcja i selekcja

• Projekcja: (wyspecyfikowanie kilku kolumn mające na

celu pominięcie informacji z pozostałych kolumn)

SELECT nazwa-kolumny {, nazwa-kolumny}

FROM nazwa-tabeli;

• Selekcja: wybranie z tabeli pewnego podzbioru wierszy

spełniających warunek podany w klauzuli WHERE

SELECT * FROM nazwa-tabeli

WHERE warunek;

Złączenie – Cross join

• Złączenie CROSS JOIN jest to tzw. złączenie krzyżowe,

którego wynikiem jest iloczyn kartezjański wartości

wybranych z łączonych tabel.


FROM nazwa-tabeli-1

CROSS JOIN nazwa-tabeli-2


FROM nazwa-tabeli-1 t1, nazwa-tabeli-2 t2


• Złączenie CROSS JOIN może zwrócić bardzo dużą ilość

rekordów. Np. złączenie tabel, w których każda ma po

1 000 wierszy zwraca 1 000 000 rekordów!

• Z tego względu należy korzystać z CROSS JOIN w

wyjątkowych przypadkach i z pełną świadomością.

Złączenie – Equi join

• W warunku złączenia (w klauzuli ON lub warunku WHERE) występuje zwykły znak równości. Warunek złączenia może być również koniunkcją kilku równości.


FROM nazwa-tabeli-1 t1

INNER JOIN nazwa-tabeli-2 ON t1.kolumna-1 = t2.kolumna-2


FROM nazwa-tabeli-1 t1, nazwa-tabeli-2 t2

WHERE t1.kolumna-1 = t2.kolumna-2

Złączenie Natural join

• Złączenie natural join jest to szczególny typ złączenia equi-join, w którym kolumny łączonych tabel użyte w warunku łączenia mają tę samą nazwę.


FROM nazwa-tabeli-1

INNER JOIN nazwa-tabeli-2 ON t1.kolumna = t2.kolumna

SELECT nazwa-kolumny {, nazwa-kolumny} FROM nazwa-tabeli-1 t1, nazwa-tabeli-2 t2

WHERE t1.kolumna = t2.kolumna

Złączenie Theta join

• Złączenia w których w warunku występuje inny symbol

porównania wartości niż =, np. >, BETWEEN, <>


FROM nazwa-tabeli-1 t1

INNER JOIN nazwa-tabeli-2 ON t1.kolumna-1 >=

t2.kolumna-2

SELECT nazwa-kolumny {, nazwa-kolumny} FROM

nazwa-tabeli-1 t1, nazwa-tabeli-2 t2

WHERE t1.kolumna-1 >= t2.kolumna-2

Złączenie LEFT OUTER JOIN

• Złączenie typu LEFT OUTER JOIN pozwala nam na

uwzględnienie w wyniku danych z głównej tabeli, które nie

posiadają swoich odpowiedników w złączanych tabelach.

Oznacza to, że jeśli w pierwszej tabeli pojawiają się

wiersze, które nie posiadają odpowiedników w drugiej

tabeli to zostaną wzięte pod uwagę podczas złączenia ale

puste kolumny zostaną wypełnione wartościami NULL.


FROM nazwa-tabeli-1

LEFT OUTER JOIN nazwa-tabeli-2 ON warunek-złączenia

Przykład

Złączenie RIGHT OUTER JOIN

• Złączenie typu RIGHT OUTER JOIN działa analogicznie

do LEFT OUTER JOIN ale w tabeli wynikowej uwzględnia

wiersze z łączonej tabeli, które nie posiadają

odpowiedników w tabeli głównej.


FROM nazwa-tabeli-1

RIGHT OUTER JOIN nazwa-tabeli-2 ON warunek-

złączenia

Przykład

Złączenie FULL OUTER JOIN

• Złączenie obustronne FULL OUTER JOIN jest sumą

złączenia lewostronnego i prawostronnego. Zawiera

wszystkie wiersze obu złączonych tabel, w tym również te

które nie mają swoich odpowiedników.


FROM nazwa-tabeli-1

FULL OUTER JOIN nazwa-tabeli-2 ON warunek-

złączenia

Przykład

WIDOKI

Widoki

• Widoki, nazywane także perspektywami albo

projekcjami pozwalają:

• ułatwić odczytywanie danych pochodzących z kilku tabel

lub danych obliczanych,

• ograniczyć dostęp do danych poufnych (ich „widzenie”),

• ukryć strukturę tabel bazy danych,

• ułatwić zarządzanie uprawnieniami użytkowników.

Tworzenie widoków

• IF OBJECT_ID('nazwa-widoku','V') IS NULL

CREATE VIEW nazwa-widoku

[(nazwa-kolumny

{, nazwa-kolumny})]

[WITH ENCRYPTION] AS

polecenie-SELECT [WITH CHECK OPTION]

GO

• WITH ENCRYPTION – zabezpiecza widok przed możliwością obejrzenia jego definicji.

• WITH CHECK OPTION – wymusza zgodność wszelkich poleceń modyfikujących dane widoku z jego definicją.

Zmiana widoku

• IF OBJECT_ID('nazwa-widoku','V') IS NOT NULL

• ALTER VIEW nazwa-widoku

[(nazwa-kolumny

{, nazwa-kolumny})]

[WITH ENCRYPTION]

AS

polecenie-SELECT

[WITH CHECK OPTION];

GO

Usuwanie widoku

• IF OBJECT_ID('nazwa-widoku','V') IS NOT NULL

DROP VIEW nazwa-widoku;

Widoki

• Widok nie ma własnych danych, tworzy je wirtualna

tabela będąca wynikiem działania polecenia SELECT

podanego po słowie AS.

• W komendzie SELECT nazwa widoku może występować

w miejscu nazwy tabeli, np. w frazie FROM.

• Lista nazw kolumn może nie wystąpić, nazwy pobiera się

wówczas z tabeli będącej wynikiem polecenia SELECT.

• Polecenie SELECT nie może zawierać frazy ORDER BY.

Modyfikowanie danych

• Widok może służyć do modyfikowania danych (działają

dla niej polecenia INSERT, UPDATE i DELETE), ale

wówczas nie może zawierać:

• więcej niż jednej tabeli we frazie FROM,

• słowa DISTINCT,

• fraz GROUP BY i HAVING,

• kolumn zdefiniowanych wyrażeniami i funkcjami.

Zasady programisty

• W zdecydowanej większości przypadków widoki nie są obiektami indeksowanymi (jedynie przy dosyć restrykcyjnych założeniach można zdefiniować indeks na widoku).

• Z tego powodu pobieranie danych z widoku wydłuża (może również znacznie!) czas pobierania danych z bazy.

• Dlatego należy ostrożnie korzystać z widoków. W przypadku małych tabel, pobieranie danych za pomocą widoku będzie efektywne. Natomiast oparcie widoku o kilka dużych połączonych ze sobą tabel może znacznie spowolnić pracę aplikacji, a nawet całkowicie zablokować aplikację. W takich przypadkach zamiast widoków należy definiować procedury składowane.

PROCEDURY

SKŁADOWANE I FUNKCJE

Procedury składowane

• Procedura składowana to nazwany i prekompilowany zestaw poleceń (instrukcji) przechowywany na serwerze, zapewniający szybszą i wydajniejszą realizację obsługi bazy danych.

• Typy procedur

• katalogowe procedury składowane – pozwalają na pobieranie informacji ze zbioru tabel nazywanych katalogiem systemowym, np. informacje o bazach danych, tabelach, kluczach, indeksach, serwerze itp.,

• systemowe procedury składowane – narzędzia do zarządzania serwerem SQL, np. konfiguracja serwera, pomoc (sp_help), blokady,

• rozszerzone procedury składowane – zaimplementowane jako oddzielne biblioteki poza serwerem SQL, interfejs dla innych aplikacji lub systemów.

Procedury użytkownika

• Procedury składowane zdefiniowane przez użytkownika:

• mogą być tworzone tylko w bieżącej bazie danych,

• są prekompilowane,

• redukują obciążenie sieci.

Tworzenie procedury

• IF OBJECT_ID('nazwa-procedury', 'P') IS NOT NULL

DROP PROC[EDURE] nazwa-procedury

GO

• CREATE PROC[EDURE] nazwa-procedury

[@nazwa-parametru typ-danych [=wartość-domyślna] [OUTPUT]

{, @nazwa-parametru typ-danych [=wartość-domyślna]

[OUTPUT]}]

[WITH RECOMPILE|ENCRYPTION|

RECOMPILE, ENCRYPTION]

[FOR REPLICATION]

AS

polecenie-SQL

{polecenie-SQL}

GO

Opcje tworzenia procedury

• RECOMPILE – nakazuje serwerowi rekompilację tzw.

planu wykonania przy każdorazowym wywołaniu

procedury

• ENCRYPTION – zabezpiecza procedurę przed

możliwością obejrzenia jej definicji

• FOR REPLICATION – wykorzystywane przy replikacji

bazy

Zmiana procedury

• ALTER PROC[EDURE] nazwa-procedury

[@nazwa-parametru typ-danych [=wartość-domyślna] [OUTPUT]

{, @nazwa-parametru typ-danych [=wartość-domyślna] [OUTPUT]}]

[WITH RECOMPILE|ENCRYPTION|

RECOMPILE, ENCRYPTION]

[FOR REPLICATION]

AS

polecenie-SQL

{polecenie-SQL}

GO

UWAGA: Przed ALTER PROCEDURE nie może wystąpić żadne zapytanie. Z tego powodu przed zmianą procedury nie jest możliwe sprawdzenie, czy ta procedura istnieje. Alternatywnym rozwiązaniem jest usunięcie i ponowne utworzenie procedury.

Usuwanie procedury

• IF OBJECT_ID('nazwa-procedury', 'P') IS NOT NULL

DROP PROC[EDURE] nazwa-procedury

GO

Wywołanie procedury

• EXEC[UTE] nazwa-procedury

[@nazwa-parametru typ-danych [=wartość-domyślna]

[OUTPUT]

{, @nazwa-parametru typ-danych [=wartość-domyślna]

[OUTPUT]}]

[WITH RECOMPILE]

Przykład wywołania procedury SQL

../../Bazy danych - ćwiczenia/BAD210/Przyklad procedury.sql

Tworzenie funkcji

IF OBJECT_ID('nazwa-funkcji', 'FN') IS NOT NULL

DROP FUNCTION nazwa-funkcji

GO

CREATE FUNCTION nazwa-funkcji (

[@nazwa-parametru skalarny-typ-danych [=wartość-domyślna]

{, @nazwa-parametru skalarny-typ-danych [=wartość-domyślna]}]

) RETURNS skalarny-typ-danych

[AS] BEGIN

polecenie-SQL

{polecenie-SQL}

RETURN wyrażenie-skalarne;

END;

GO

Zmiana funkcji

ALTER FUNCTION nazwa-funkcji (

[@nazwa-parametru skalarny-typ-danych [=wartość-domyślna]

{, @nazwa-parametru skalarny-typ-danych [=wartość-domyślna]}]

) RETURNS skalarny-typ-danych

[AS] BEGIN

polecenie-SQL

{polecenie-SQL}

RETURN wyrażenie-skalarne;

END;

UWAGA: Przed ALTER FUNCTION nie może wystąpić żadne zapytanie. Z tego powodu przed zmianą funkcji nie jest możliwe sprawdzenie, czy ta funkcja istnieje. Alternatywnym rozwiązaniem jest usunięcie i ponowne utworzenie funkcji.

Usuwanie funkcji

• IF OBJECT_ID('nazwa-funkcji', 'FN') IS NOT NULL

DROP FUNCTION nazwa-funkcji

GO

Przykład wywołania funkcji

IF OBJECT_ID('Staz','FN') IS NOT NULL

DROP FUNCTION Staz

GO

CREATE FUNCTION Staz (@Data DATETIME)

RETURNS INT

BEGIN

RETURN(DATEDIFF(yy,@Data,GETDATE()))

END

GO

-- Test funkcji

SELECT Nazwisko, Zatrudniony, Staż=dbo.Staz(Zatrudniony)

FROM Zatrudnieni

WYZWALACZE I KURSORY

Wyzwalacze (Triggers)

• Procedury wyzwalane (wyzwalacze, triggery) to procedury

wywoływane przez system w momencie zajścia

odpowiedniego zdarzenia dotyczącego tabel w bazie

danych.

• Wyzwalacze pozwalają realizować zachowanie więzów

spójności i dotyczą operacji INSERT, UPDATE lub

DELETE.

Tworzenie wyzwalaczy

IF OBJECT_ID('nazwa-wyzwalacza','TR') IS NOT NULL

DROP TRIGGER nazwa-wyzwalacza

GO

CREATE TRIGGER nazwa-wyzwalacza

ON nazwa-tabeli

[WITH ENCRYPTION]

FOR|AFTER|INSTEAD OF INSERT|UPDATE|DELETE

AS

polecenie-SQL

{polecenie-SQL};

GO

Tworzenie wyzwalaczy

• Wyzwalacz definiuje procedurę o podanej nazwie, uruchamianą w momencie zajścia (FOR), po zajściu (AFTER) lub zamiast zajścia (INSTEAD OF) odpowiedniego zdarzenia o treści, którą tworzą polecenia SQL występujące po słowie AS.

• Podczas wykonywania wyzwalacza tworzone są dwie specjalne tabele: inserted i deleted. Ich zawartość można sprawdzać w treści procedury np. w celu wykrycia zmian: • gdy usuwamy wiersze, są one usuwane z tabeli głównej i przenoszone

do tablicy deleted, następnie wyzwalana jest procedura usuwająca,

• gdy wstawiamy wiersze, są one wstawiane do głównej tabeli i tabeli inserted, następnie wyzwalana jest procedura wstawiająca,

• gdy aktualizujemy tabelę, stare dane są wstawiane do tabeli deleted, a nowe do tabeli głównej i tabeli inserted, następnie wyzwalana jest procedura aktualizująca

• procedury wyzwalane mogą wywoływać inne procedury wyzwalane (wywołania kaskadowe).

Usuwanie wyzwalacza

IF OBJECT_ID('nazwa-wyzwalacza','TR') IS NOT NULL

DROP TRIGGER nazwa-wyzwalacza

GO

Zmiana wyzwalacza

ALTER TRIGGER nazwa-wyzwalacza

ON nazwa-tabeli

[WITH ENCRYPTION]

FOR|AFTER|INSTEAD OF INSERT|UPDATE|DELETE

AS

polecenie-SQL

{polecenie-SQL};

• GO

• UWAGA: Przed ALTER TRIGGER nie może wystąpić żadne zapytanie. Z tego powodu przed zmianą wyzwalacza nie jest możliwe sprawdzenie, czy wyzwalacz istnieje. Alternatywnym rozwiązaniem jest usunięcie i ponowne utworzenie wyzwalacza.

Przykład: Aktualizacja po usunięciu wartości

Z tabeli Oddzialy usuwamy oddział. Ponieważ oddziały są również zapisywane w tabeli Pracownicy, należy zamienić kod usuwanego oddziału wartością NULL.

CREATE TRIGGER Puste_Oddzialy

ON Oddzialy

FOR DELETE

AS UPDATE Pracownicy SET

NrOddzialu = NULL

WHERE NrOddzialu IN (SELECT NrOddzialu

FROM deleted);

GO

Przykład: Aktualizacja po zmianie wartości

W tabeli Oddzialy zmieniamy kodowanie oddziałów. Ponieważ kody oddziałów są również zapisywane w tabeli Pracownicy, należy uaktualnić kody oddziałów w tej tabeli.

CREATE TRIGGER Nowy_numer

ON Oddzialy

FOR UPDATE

AS IF UPDATE (NrOddzialu)

UPDATE Pracownicy SET

NrOddzialu = (SELECT NrOddzialu FROM inserted) -- nowy

WHERE NrOddzialu IN (SELECT NrOddzialu FROM deleted); -- stary

GO

Przykład

Z tabeli Pracownicy usuwamy pracownika. Można usunąć wiersz tabeli, ale pod warunkiem, że stanowisko usuwanego pracownika nie jest równe ‚Kierownik’.

CREATE TRIGGER sprawdz_usuwanie

ON Pracownicy

FOR DELETE

AS

IF (SELECT COUNT (*) FROM Pracownicy p

INNER JOIN deleted d ON p.ID = d.ID

WHERE p.Stanowisko = ’Kierownik’) > 0

BEGIN

PRINT 'Nie można usunąć kierownika';

ROLLBACK TRANSACTION;

END ELSE BEGIN

PRINT 'Pracownik został usunięty'

COMMIT TRANSACTION;

END;

GO

Zasady programisty

• Korzystaj z wyzwalaczy z wyjątkową ostrożnością.

• Nigdy nie modyfikuj danych w tabeli za pomocą wyzwalacza

zdefiniowanego na tej samej tabeli. Może to doprowadzić do

kaskadowego wywołania tego samego wyzwalacza i w

konsekwencji pojawi się dead-lock bazy danych.

• Wyzwalacze są dobrym narzędziem do monitorowania

czynności wykonywanych przez użytkownika i zapisywaniu ich

w dziennikach.

• Wyzwalacze nie są dobrym narzędziem do utrzymania więzów

integralności (spójności) bazy danych. Kaskadowe

wywoływanie wyzwalaczy może spowodować wystąpienie

blokad i w konsekwencji dead-lock bazy danych.

Kursory

• Kursory umożliwiają operowanie na zbiorze wyników uzyskanych w wyniku wykonania polecenia SELECT.

• 1. Kursor musi zostać zadeklarowany. Deklaracja kursora ma postać:

DECLARE nazwa-kursora

CURSOR FOR

polecenie-SELECT;

• 2. Kursor musi zostać otwarty. Otwarcie kursora ma postać:

OPEN nazwa-kursora;

Kursory

• 3. Pobieranie informacji z kursora realizuje polecenie:

FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n |RELATIVE n

FROM nazwa-kursora INTO @nazwa-zmiennej {, @nazwa-zmiennej}

• Gdzie:

• NEXT – określa następny rekord (wiersz) w stosunku do bieżącego

• PRIOR – określa poprzedni rekord w stosunku do bieżącego FIRST – określa pierwszy rekord w kursorze

• LAST – określa ostatni rekord w kursorze

• ABSOLUTE n – określa n-ty rekord w kursorze

• RELATIVE n – określa n-ty rekord w stosunku do bieżącego

Kursory

• 4. Ważną rolę pełni zmienna systemowa

@@FETCH_STATUS.

• Po pobraniu danych do kursora (np. przy pomocy FETCH

NEXT) warunek

@@FETCH_STATUS = 0

wskazuje, że rekordy (wiersze) zostały poprawnie

pobrane do kursora.

Kursory

• 5. Kursor musi zostać zamknięty. Zamknięcie kursora ma

postać:

CLOSE nazwa-kursora;

• 6. Polecenie

DEALLOCATE nazwa-kursora;

usuwa odwołanie do kursora i zwalnia pamięć

Przykład

W tabeli Oddzialy zmieniamy kodowanie oddziałów. Ponieważ kody oddziałów są również zapisywane w tabeli Pracownicy, należy uaktualnić kody oddziałów w tej tabeli.

CREATE TRIGGER Zmiany ON Oddzialy

FOR UPDATE

AS

BEGIN DECLARE @stary INT, @nowy INT;

DECLARE del CURSOR FOR SELECT ID_Oddzialu FROM deleted;

DECLARE ins CURSOR FOR SELECT ID_Oddzialu FROM inserted;

OPEN del;

OPEN ins;

FETCH FIRST FROM del INTO @stary;

FETCH FIRST FROM ins INTO @nowy;

Przykład c.d.

WHILE @@FETCH_STATUS = 0

BEGIN UPDATE Pracownicy SET

ID_Oddzialu = @nowy

WHERE ID_Oddzialu = @stary;

FETCH NEXT FROM del INTO @stary;

FETCH NEXT FROM ins INTO @nowy;

END;

CLOSE ins;

DEALLOCATE ins;

CLOSE del;

DEALLOCATE del;

END;

Zasady programisty

• Kursory przypominają programistom pętlę WHILE dobrze znaną z języków programowania. Jednak w przeciwieństwie do języków programowania, w T-SQL kursory należy stosować wyłącznie w ostateczności, gdy nie są dostępne inne narzędzia. Nadmierne wykorzystywanie kursorów jest jednym z najbardziej podstawowych błędów programistów T-SQL.

• Kursory są bardzo niewydajnym narzędziem. Czas wykonywania podobnego zapytania z wykorzystaniem kursora i bez kursora jest wielokrotnie (nawet ponad 100 razy) krótszy na korzyść zapytania bez kursora.

• W trakcie wykonywania pętli kursora nie należy modyfikować danych, które są zwracane komendą SELECT w definicji kursora.

SQL ELEMENTY ZAAWANSOWANEmwisla.home.amu.edu.pl/BAD210/W05 SQL 2.pdfZasady tworzenia podzapytań...

Documents

Transcript of SQL ELEMENTY ZAAWANSOWANEmwisla.home.amu.edu.pl/BAD210/W05 SQL 2.pdfZasady tworzenia podzapytań...