Wykład 10

Transact-SQL - wstęp

1. Wstęp

Z poprzednich rozdziałów dowiedzieliśmy się, jak wykonywać pojedyncze instrukcje SQL. W praktyce jednak często zdarza się, że zadanie, które musimy wykonać na naszej bazie danych, wymaga większej liczby instrukcji, np. sprawdzenia warunków i w zależności od nich wykonanie modyfikacji lub nie. Modyfikacje często dotyczą więcej niż jednej tabeli.

Serwery baz danych oferują rozszerzenie języka SQL o konstrukcje typowe dla języków programowania. Rozszerzenie to jest zdefiniowane w standardzie SQL już od wersji z roku 1999 i nosi nazwę SQL/PSM. Producenci serwerów baz danych implementują jednak własne rozwiązania i tak, Microsoft SQL Server oferuje język Transact-SQL, a Oracle posiada swój PL/SQL.

Ten i kolejne wykłady omawiają język Transact-SQL. W każdym rozdziale znajdziemy też krótkie omówienie i opis różnic w języku PL/SQL. Niewątpliwie zrozumienie tego wykładu będzie łatwiejsze dla osób, które miały do czynienia z programowaniem i nie są im obce pojęcia takie jak: deklaracja zmiennej, instrukcja warunkowa, pętla. Pojęcia te będą krótko omówione, lecz w przypadku problemów z ich zrozumieniem zalecane jest sięgnięcie do literatury uzupełniającej.

2. Aplikacja bazodanowa

Tworząc aplikację korzystającą z bazy danych, należy podjąć decyzję, jakie jej części będą znajdować się na:

• serwerze baz danych,
• na komputerze użytkownika (czyli po stronie klienta).

Generalna zasada mówi, że składowanie danych oraz mechanizm wykonywania instrukcji SQL powinny znajdować się po stronie serwera. Po stronie klienta natomiast powinien znajdować się „przyjazny” interfejs użytkownika. Większość operacji przetwarzania danych powinniśmy przenosić na serwer, gdyż oferuje on mechanizmy pozwalające przetwarzać dane w sposób wydajny i bezpieczny. Na serwerze powinna być realizowana centralna kontrola nad spójnością danych. Na stacji klienckiej natomiast powinna być wykonywana precyzyjna i przyjazna dla użytkownika diagnostyka i obsługa błędów.

Język Transact-SQL będzie służył nam do oprogramowania strony serwera. Pozwoli nam pisać kod, który będzie wykonywany bezpośrednio na serwerze baz danych.

Elementy aplikacji umieszczane po stronie serwera to:

• deklaratywne więzy spójności (omówione w wykładzie 9),
• procedury składowane (omówione w wykładzie 12),
• wyzwalacze bazy danych (omówione w wykładzie 14).

3. Blok anonimowy

Podstawową konstrukcją programu w językach omawianego typu jest blok anonimowy. Jest to program, którego kod nie będzie trwale zapisany w bazie danych w odróżnieniu od omówionych w kolejnych wykładach procedur składowanych. Program tego typu może być zapisany w pliku tekstowym do ponownego użycia.

W języku Transact-SQL konstrukcja bloku anonimowego jest bardzo prosta. Pisząc tego typu program umieszczamy kolejne linie kodu jedna po drugiej. Blok kończymy słowem GO. Wszystkie zadeklarowane lokalnie zmienne są widoczne do najbliższego wystąpienia GO. W praktyce słowo GO jest często pomijane. Jeśli na przykład napiszemy kod w narzędziu Management Studio i zapomnimy zakończyć go słowem GO, program zrobi to za nas niejawnie. GO będzie istotne wtedy, gdy jednocześnie będziemy chcieli uruchomić kilka niezależnych od siebie programów.

W Transact-SQL nie istnieje obowiązek kończenia każdej linii kodu średnikiem, jak w wielu innych językach programowania. Teoretycznie, możliwe jest pisanie kodu nawet w jednej linii, nie używając przycisku enter. Dla zwiększenia czytelności kodu zalecane jest jednak umieszczanie każdej instrukcji w osobnej linii i używanie wcięć (tabulator).

4. Komentarze

Komentarz pozwala nam „wyłączyć” fragment kodu, a więc spowodować, że część kodu będzie ignorowana w trakcie wykonania programu. W Transact-SQL mamy dwie możliwości:

Komentarz blokowy, między nawiasami (dowolnie wiele linii):

/* komentarz */

Komentarz jednoliniowy (do końca bieżącej linii):

-- komentarz

5. Deklaracje zmiennych

Zmienna jest jedną z podstawowych konstrukcji niezbędnych przy programowaniu. Deklarując zmienną informujemy serwer, że ma przygotować w pamięci RAM miejsce w celu przechowywania wartości określonego typu. Na zmienne będziemy mogli przypisywać wartości pobrane z bazy danych, wykonywać na nich obliczenia, używać przy aktualizacji danych itp.

W języku Transact-SQL zmienne deklarowane przez użytkownika muszą być poprzedzone znakiem @. W celu zadeklarowania zmiennej używamy instrukcji DECLARE podając nazwę zmiennej i typ danych. Możemy używać tych samych typów, których używaliśmy przy tworzeniu tabel. Przykład:

DECLARE @nazwisko VARCHAR(30)

W jednej instrukcji DECLARE możemy zadeklarować wiele zmiennych, np.:

DECLARE @imie VARCHAR(20), @nazwisko VARCHAR(30), @wiek INT

Instrukcja DECLARE może występować wielokrotnie, w dowolnym miejscu kodu.

6. Zmienne systemowe

Nazwy zmiennych systemowych rozpoczynają się od dwóch znaków @. Nie musimy ich deklarować. Mogą służyć do pobierania informacji przydatnych dla naszego programu. Kilka najczęściej używanych:

@@ERROR – numer ostatniego błędu

@@FETCH_STATUS – czy kursor pobrał wiersz (0 gdy pobrał)

@@IDENTITY – zawiera ostatnio wygenerowaną wartość IDENTITY (bardzo przydatne, gdy chcemy sprawdzić identyfikator wstawionego przez INSERT wiersza)

@@ROWCOUNT – zwraca liczbę wierszy, na których operowała ostatnia instrukcja (przydatne aby sprawdzić np. ile wierszy zmieniła instrukcja UPDATE lub czy DELETE usunął wiersz).

@@VERSION – zwraca informację o serwerze SQL

Aby wyświetlić wartość zmiennej systemowej możemy posłużyć się instrukcją SELECT lub PRINT, np.:

PRINT @@VERSION

lub:

SELECT @@VERSION

7. Instrukcja SELECT w Transact-SQL

Instrukcja SELECT w bloku Transact-SQL może służyć do przypisania stałych wartości na zmienne. Nie używamy wtedy FROM ani innych klauzul typowych dla tej instrukcji.

SELECT @zmienna = wyrażenie

Przykład:

SELECT @imie = 'Jan', @nazwisko = 'Kowalski'

Ten sam efekt można osiągnąć przy pomocy instrukcji SET z tą różnicą, że w jednej instrukcji SET możemy dokonać tylko jednego przypisania.

SET @nazwisko = 'Kowalski'
SET @imie = 'Jan'

Instrukcja SELECT może też służyć do przypisania wartości z bazy danych na zmienne.

SELECT @nazwisko = Nazwisko, @imie = Imie
FROM Osoba 
WHERE IdOsoby = 1234

Należy pamiętać, że zapytanie powinno zwracać jeden wiersz. Gdy jednak zapytanie zwróci więcej wierszy, do zmiennych zostaną przypisane wartości z ostatniego rekordu. Gdy zapytanie nic nie zwróci, przypisanie nie dokona się i na zmiennej pozostanie poprzednia wartość.

8. Przykłady prostych programów w Transact-SQL

Przykład 1:

Przykład kodu Transact-SQL usuwającego wiersz i rejestrującego w tabeli „dziennik” liczbę usuniętych wierszy:

DECLARE @usunięte INT
DELETE FROM Dept WHERE Deptno = 50
SET @usuniete = @@ROWCOUNT
INSERT INTO Dziennik 
VALUES ('Dział', @usuniete, GETDATE())
GO

Przykład 2:

Prosty program w Transact-SQL, w którym deklarujemy zmienną, a następnie na tą zmienną pobieramy liczbę rekordów z tabeli „emp”. Wynik wypisujemy używając instrukcji PRINT, w postaci zdania np. "W tabeli jest 10 osób".

DECLARE @Ile INT
SELECT @Ile = COUNT(*) FROM emp
PRINT 'W tabeli jest ' + CONVERT(VARCHAR, @ile) + ' osób'

Pamiętajmy, że konkatenacja (łączenie napisów) w Transact-SQL wymaga dokonania konwersji, gdy zmienna jest typu liczbowego.

9. PL/SQL - różnice

Jak wspomniano na początku tego wykładu, odpowiednikiem Transact-SQL w serwerze Oracle jest język PL/SQL. Jego składnia dosyć istotnie różni się w stosunku do języka używanego w serwerze baz danych firmy Microsoft.

Podstawową różnicą jest inna konstrukcja bloku anonimowego. Nie występuje tutaj słowo GO, lecz wymagana jest określona konstrukcja bloku:

DECLARE
   deklaracje
BEGIN
   ciąg instrukcji do wykonania
EXCEPTION
   obsługa wyjątków (błędów)
END;

W PL/SQL każdą linię kończącą fragment kodu kończymy średnikiem.

Inna jest też konstrukcja pozwalająca na dokonanie przypisania wartości na zmienną. Nie możemy w tym celu użyć SET, ani SELECT. Piszemy po prostu:

zmienna := wyrażenie;

Nazwy zmiennych nie muszą rozpoczynać się znakiem @.

Użycie instrukcji SELECT w celu przypisania wartości z bazy danych także się różni. W PL/SQL używamy dodatkowej klauzuli INTO, np.:

SELECT imie, nazwisko INTO zm_imie, zm_nazwisko
FROM Osoba
WHERE IdOsoby = 1234;

Gdy używamy konstrukcji SELECT ... INTO ... zapytanie musi zwracać dokładnie jeden wiersz. Gdy nie zwróci nic lub zwróci wiele wierszy wystąpi błąd.

Rozwiązania przykładów z punktu 7, wykonane w PL/SQL:

Przykład 1:

DECLARE
  usuniete INTEGER;
BEGIN
  DELETE FROM Dept WHERE Deptno = 50;
  usuniete := SQL%ROWCOUNT;
  INSERT INTO Dziennik 
  VALUES ('Dział', @usuniete, SYSDATE);
END;

Przykład 2:

DECLARE
  ile INTEGER;
BEGIN
  SELECT COUNT(*) INTO ile FROM emp;
  dbms_output.put_line('W tabeli jest ' || ile || ' osob');
END;

Uwaga: Aby komunikat wypisany przez dbms_output.put_line był widoczny na ekranie, konieczne jest wcześniejsze wykonanie:

SET SERVEROUTPUT ON