Wprowadzenie do programowania na MS SQLServer

Wprowadzenie do programowania
na MS SQLServer 2005 i 2008
Kalen Delaney, Inside MS SQL Server 2000, Wydawnictwo RM,
2001
Kalen Delaney, Inside MS SQL Server 2005, The storage engine,
Microsoft Press, 2007
MS SQL Server 2005, 2008 Books Online:
http://msdn.microsoft.com/en-us/library/ms130214.aspx
Paweł Lenkiewicz, Administrowanie bazami danych na przykładzie
Microsoft SQL Server 2005, Wyd.PJWSTK i materiały do zajęć
SQLServer, L.Banachowski
1
Pojęcie bazy danych
• W odróżnieniu od Oracle, jedna instalacja (instancja) serwera
zawiera wiele baz danych.
• Bazy danych są przechowywane w plikach, które odpowiadają
fizycznym plikom na dysku. Domyślnie przy tworzeniu bazy danych
jest tworzony jeden plik dla danych oraz jeden dla dziennika (logu)
transakcji.
• Bazy danych dzielą się na systemowe (master, model, tempdb,
msdb) i użytkownika.
• Obiekty bazy danych są podzielone na schematy. Pełna referencja
do obiektu na serwerze: bazadanych.schemat.obiekt (domyślny
schemat dbo).
SQLServer, L.Banachowski
2
Struktura serwera w Object Explorer
SQLServer, L.Banachowski
3
Bazy systemowe
Master – podstawowa systemowa baza danych, zawierająca
metadane np. informacje o kontach użytkowników, bazach danych
i innych obiektach serwera. Korzystamy za pomocą odpowiednich
narzędzi oraz procedur systemowych.
Model – jest szablonem bazy danych. Gdy tworzymy nową bazę
danych użytkownika, jest ona kopią bazy model. Dzięki niej
możemy uprościć tworzenie wielu identycznych baz danych.
Jeżeli w bazie Model utworzymy tabele, procedury składowane i
inne obiekty, to każda nowa baza danych będzie te obiekty
zawierać.
SQLServer, L.Banachowski
4
Bazy systemowe
Tempdb – baza w której serwer trzyma informacje tymczasowe
np. tabele i procedury tymczasowe użytkowników.
Msdb – przechowuje informacje na temat zaplanowanych
zadań. Korzystamy za pomocą odpowiednich narzędzi oraz
procedur systemowych.
SQLServer, L.Banachowski
5
SQL Server Management
Studio – graficzny interfejs
do baz danych na serwerze
• Tworzenie
i
administrowanie bazami
danych.
• Łatwiejsze niż z linii
poleceń tworzenie i
modyfikowanie tabel,
procedur, wyzwalaczy,
perspektyw, indeksów,
typów
danych,
użytkowników,
uprawnień.
• Wprowadzanie,
wyświetlanie, import i
eksport danych.
SQLServer, L.Banachowski
6
Widok projekt
• Można tworzyć nowe tabele i wprowadzać modyfikacje do
istniejących tabel.
SQLServer, L.Banachowski
7
Tworzenie zapytania do bazy danych
z paska narzędzi New Query - edytor tekstowy SQL
a z menu Query -> Design Query in Editor – edytor graficzny
Aby wykonać zapytanie: z paska narzędzi Execute
SQLServer, L.Banachowski
8
Tworzenie wyzwalaczy
Wyświetlony jest
szablon, który należy
wypełnić odpowiednim
kodem SQL.
SQLServer, L.Banachowski
9
Tworzenie procedur składowanych
SQLServer, L.Banachowski
10
Projekty
Tworzone skrypty można zapisywać lokalnie w plikach
korzystając z podziału na projekty (Projects), które z kolei są
grupowane w rozwiązania (Solutions)
SQLServer, L.Banachowski
11
• SQL Server Books Online
• SQL Server Service Manager – do
uruchamiania i zatrzymywania serwera.
SQLServer, L.Banachowski
12
Data Transformation Services
Import/Export Wizard
Import i export danych do i ze źródeł OLE DB lub ODBC.
Mogą być połączone z transformacjami danych za pomocą
instrukcji SQL i procedur.
SQLServer, L.Banachowski
13
SQL
W oprogramowaniu Microsoft SQL Server zaimplementowano
język SQL w oparciu o standard SQL-92. Większość instrukcji
omówionych w wykładzie "Systemy baz danych", działa
również na serwerze SQL Server.
Podstawowe różnice:
• Operatorem konkatenacji jest + a nie ||
• Nie ma typu VARCHAR2 – jest VARCHAR
• Średnik jest opcjonalny.
SQLServer, L.Banachowski
14
CREATE TABLE
Zamiast sekwencji jak w Oracle, jest właściwość IDENTITY dla
kolumn. Np.
CREATE TABLE Osoby
(IdOsoby INT PRIMARY KEY IDENTITY,
Imie VARCHAR(20),
Nazwisko VARCHAR(30));
W tak utworzonej tabeli, wartości klucza głównego IdOsoby są
generowane automatycznie.
INSERT INTO Osoby VALUES ('Jan', 'Kowalski');
SELECT * FROM Osoby;
SQLServer, L.Banachowski
15
SQLServer, L.Banachowski
16
CREATE SCHEMA
CREATE SCHEMA myschema;
Dodanie nowej tabeli do schematu:
CREATE TABLE myschema.mytable1(x int);
Odwołanie do tabeli w schemacie:
SELECT * FROM myschema.mytable;
Usunięcie schematu (najpierw obiekty):
DROP myschema.mytable;
DROP myschema;
Domyślny schemat dbo
SQLServer, L.Banachowski
17
Tabele tymczasowe
• Lokalne tabele tymczasowe - nazwa takiej tabeli ma postać
#nazwa_tabeli. Jest ona widoczna tylko w sesji, w której została
utworzona. Po zakończeniu sesji tabela jest usuwana.
• Globalne tabele tymczasowe różnią się tym, że są widoczne
również spoza sesji, w której zostały utworzone. Nazwa takiej tabeli
ma postać ##nazwa_tabeli. Po zakończeniu sesji wszystkich
użytkowników korzystających z tabeli, jest ona usuwana.
• Tworzone w systemowej bazie tempdb
• Do tworzenia tabel tymczasowych używamy polecenia CREATE
TABLE w sposób analogiczny do normalnych tabel.
SQLServer, L.Banachowski
18
Tabele tymczasowe
Przykład
CREATE TABLE #MyTempTable
(cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
• Można używać wszystkich deklaratywnych więzów spójności z
wyjątkiem FOREIGN KEY.
SQLServer, L.Banachowski
19
Użycie dziennika transakcji dla tabel
tymczasowych
Operacje na tabelach tymczasowych są wpisywane do dziennika
transakcji w bazie tempdb w sposób uproszczony, więc mogą
zostać wycofane w razie potrzeby. Uproszczenie oznacza brak
możliwości odtworzenia przy awarii ale jednocześnie istotnie
zwiększoną wydajność operacji na nich (do 4 razy szybciej).
SQLServer, L.Banachowski
20
Transakcje
BEGIN TRAN - rozpoczęcie transakcji
COMMIT TRAN - zatwierdzenie transakcji
ROLLBACK TRAN - wycofanie transakcji
• Domyślnie ustawiona jest opcja IMPLICIT_TRANSACTIONS na
OFF. Przy takim ustawieniu, jeśli nie zastosujemy BEGIN TRAN,
system traktuje każdą instrukcję DML jako osobną transakcję i
zatwierdza ją (jak autocommit w Oracle). Aby to wyłączyć, należy
użyć (wtedy BEGIN TRAN przestaje być konieczne):
SET IMPLICIT_TRANSACTIONS ON
• Użytkownik powinien wtedy sam wykonać COMMIT lub
ROLLBACK, inaczej system wycofa transakcję przy zamknięciu
połączenia przez użytkownika.
SQLServer, L.Banachowski
21
TransactSQL, wsad (batch)
• TransactSQL – połączenie możliwości SQL*Plus i PL/SQL
• Zmiana bazy danych: use baza_danych np. use pubs
• Wsad (batch) - ciąg instrukcji kierowany jako całość do
wykonania przez serwer. W kodzie jest rozpoznawany koniec wsadu
przez słowo kluczowe go (służy więc jako separator wsadów w
skryptach). W edytorze wsad można wyróżnić przez podświetlenie
myszką. Jego realizacja następuje przez naciśnięcie F5 (Execute).
• Nie ma odpowiednika bloku anonimowego w PL/SQL
• W Transact-SQL można, ale nie trzeba, kończyć każdej instrukcji
średnikiem. Instrukcje mogą znajdować się jedna za drugą. Dla
większej czytelności kodu zalecane jest jednak umieszczanie każdej
instrukcji w oddzielnej linii i używanie wcięć.
SQLServer, L.Banachowski
22
Deklaracje zmiennych
• Zmienne użytkownika są zawsze poprzedzone znakiem @ i mają
charakter lokalny dla wsadu:
DECLARE @zmienna typdanych
• W jednej instrukcji DECLARE można deklarować wiele
zmiennych. W odróżnieniu od PL/SQL, deklaracje mogą występować
w dowolnym miejscu.
DECLARE @Imie VARCHAR(9), @Nazwisko VARCHAR(20);
• Wszystkie zadeklarowane lokalnie zmienne są widoczne tylko w
ramach wsadu.
SQLServer, L.Banachowski
23
Zmienne systemowe
Zmienne systemowe oznaczone są dwoma znakami "@". Często
używane zmienne systemowe:
• @@ERROR - numer ostatniego błędu
• @@FETCH_STATUS - czy kursor pobrał wiersz (0 gdy pobrał)
• @@IDENTITY - zawiera ostatnio wygenerowaną wartość
IDENTITY (przydatne, gdy chcemy użyć ID wstawionego przez
INSERT wiersza)
• @@ROWCOUNT - zwraca liczbę wierszy, na których operowała
ostatnia instrukcja SQL
• @@VERSION - zwraca informację o wersji SQL Serwera
SQLServer, L.Banachowski
24
SELECT
• Odpowiednikiem konstrukcji w Oracle:
SELECT wyrażenie INTO zmienna ... FROM …
a zarazem konstrukcją umożliwiającą wykonanie przypisania
wartości do zmiennej jest:
SELECT @zmienna = wyrażenie [FROM ….]
• Tej konstrukcji możemy użyć do przypisania pewnych wartości
do zmiennych:
SELECT @nazwisko = 'Kowalski', @imie = 'Jan';
SQLServer, L.Banachowski
25
SELECT
• Ten sam efekt możemy uzyskać przy pomocy instrukcji SET, z tym
że w jednej instrukcji można dokonać tylko jednego przypisania:
SET @nazwisko = 'Kowalski‘;
SET @imie = 'Jan‘;
• Możemy również przypisać zmiennym wartości zwrócone przez
zapytanie SQL:
SELECT @nazwisko = Nazwisko, @imie = Imie
FROM Osoby WHERE IdOsoby = 1;
Zapytanie powinno zwracać dokładnie jeden wiersz, jednak:
• gdy zapytanie zwróci więcej wierszy, do zmiennych zostaną
przypisane wartości z ostatniego wiersza;
• gdy zapytanie nie zwróci żadnego wiersza, wtedy zmienna
zachowuje swoją dotychczasową wartość (np. NULL).
SQLServer, L.Banachowski
26
PRINT wyrażenie
SQLServer, L.Banachowski
27
SELECT
W następujący sposób można etykietować kolumny w celu
wypisania wyniku zapytania:
SELECT Ename AS Nazwisko FROM Emp;
SELECT Ename Nazwisko FROM Emp;
SELECT 'Nazwisko'=Ename FROM Emp;
SELECT Nazwisko=Ename FROM Emp;
SQLServer, L.Banachowski
28
Instrukcja warunkowa
IF warunek
instrukcja lub blok
[ELSE
instrukcja lub blok]
np.:
IF @pensja > 0
BEGIN
INSERT INTO Emp (Ename, Sal, Deptno)
VALUES (@nazwisko, @pensja, @deptno);
PRINT 'Wstawiono pracownika';
END
ELSE
PRINT 'Niepoprawna pensja';
SQLServer, L.Banachowski
29
Instrukcja iteracji
WHILE warunek
instrukcja lub blok
Istnieje możliwość wyjścia z pętli używając instrukcji BREAK.
Instrukcja CONTINUE powoduje, że reszta instrukcji w pętli jest
ignorowana i następuje wykonanie kolejnej iteracji:
WHILE
(SELECT AVG(Sal) FROM Emp) < 200
BEGIN
UPDATE Emp SET Sal = Sal * 1.2;
IF (SELECT MAX(Sal) FROM Emp) > 400 BREAK;
ELSE CONTINUE;
END;
SELECT * FROM Emp;
SQLServer, L.Banachowski
30
Kursor
DECLARE nazwa_kursora
CURSOR FOR instrukcja_SELECT
OPEN nazwa_kursora
Pobranie kolejnego wiersza (w Transact SQL nie istnieją zmienne
typu wierszowego, więc trzeba wcześniej zadeklarować tyle
zmiennych, ile zwróci wartości instrukcja SELECT kursora):
FETCH NEXT FROM nazwa_kursora INTO zmienne
Do sprawdzenia, czy instrukcja FETCH zwróciła wiersz, służy
zmienna systemowa @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0 ….
CLOSE nazwa_kursora
DEALLOCATE nazwa_kursora
SQLServer, L.Banachowski
31
Przykład
DECLARE kursor CURSOR FOR
SELECT Ename, Sal FROM Emp WHERE Sal > 200;
DECLARE @nazwisko VARCHAR(50), @pensja MONEY;
PRINT 'Pracownicy o pensji wyższej niż 200:' ;
OPEN kursor;
FETCH NEXT FROM kursor INTO @nazwisko, @pensja;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @nazwisko + ' ' + convert(varchar(50),@pensja);
FETCH NEXT FROM kursor INTO @nazwisko, @pensja;
END;
CLOSE kursor;
DEALLOCATE kursor;
SQLServer, L.Banachowski
32
Procedury
CREATE PROC[EDURE] nazwa_procedury lista parametrów
AS instrukcje
• Definiując listę parametrów podajemy nazwy poprzedzone znakiem
"@" oraz typ danych i rozdzielamy specyfikacje parametrów
przecinkami.
• Możemy podać wartość domyślną oraz określić, że jest to parametr
wyjściowy OUTPUT
• Definicja parametru procedury ma postać:
@nazwa_parametru typdanych [=wartość_domyślna]
[OUTPUT]
• ALTER PROCEDURE pozwala powtórnie wprowadzić tekst
procedury.
SQLServer, L.Banachowski
33
Przykład
CREATE PROC pensja
@Empno INT, @Nowe_zarobki DECIMAL(6,2) OUTPUT,
@Procent INT = 20
AS
BEGIN
SELECT @Nowe_zarobki = Sal + Sal * @Procent /100
FROM Emp WHERE Empno = @Empno;
UPDATE Emp SET Sal = @Nowe_zarobki
WHERE Empno = @Empno;
END;
Wywołanie procedury:
DECLARE @Nowe_zarobki DECIMAL(6,2);
EXEC pensja 1, @Nowe_zarobki OUTPUT;
PRINT @Nowe_zarobki;
SQLServer, L.Banachowski
34
SQLServer, L.Banachowski
35
RETURN w procedurze
CREATE PROCEDURE Ilu_pracownikow AS
BEGIN
DECLARE @ile INT;
SELECT @ile = COUNT(*) FROM Emp;
RETURN @ile;
END;
Procedura zliczy wszystkie wiersze z tabeli Emp i zwróci wartość
poprzez RETURN. Do wartości zwróconej w ten sposób możemy
się odwołać w następujący sposób:
DECLARE @zmienna int;
EXEC @zmienna = Ilu_pracownikow;
PRINT @zmienna;
Stosuje się ją zwykle do przekazania stanu obliczeń wywołania
procedury, np. czy i jaki wystąpił błąd.
SQLServer, L.Banachowski
36
SELECT w procedurze
Inaczej niż w Oracle:
USE scott
CREATE PROC Display AS
BEGIN
SELECT * FROM Emp;
SELECT * FROM Dept;
END;
GO
EXEC Display;
SQLServer, L.Banachowski
37
SQLServer, L.Banachowski
38
Funkcje skalarne
CREATE FUNCTION AveragePrice(@booktype varchar(12))
RETURNS money AS
BEGIN
DECLARE @av money;
SELECT @av = avg(price)
FROM titles WHERE type=@booktype;
RETURN @av;
END;
SELECT title_id, price
FROM titles
WHERE price > dbo.AveragePrice('business') AND type = 'business'
Wymagana jest nazwa schematu.
SQLServer, L.Banachowski
39
Funkcje tabelowe
CREATE FUNCTION SalesByStore(@storid varchar(30))
RETURNS TABLE AS
RETURN (SELECT title, qty FROM sales s,titles t
WHERE s.stor_id=@storid AND t.title_id=s.title_id);
GO
SELECT * FROM SalesByStore('8042');
• Nie jest wymagana nazwa schematu.
• Przypomina perspektywę z parametrami.
SQLServer, L.Banachowski
40
SQLServer, L.Banachowski
41
Wyzwalacze typu after i instead of
• Nie istnieją wyzwalacze ani typu before ani for each row
• Wyzwalacz jest zawsze uruchamiany dla całej instrukcji DML
po jej wykonaniu.
• Odwołania do starych i nowych wartości modyfikowanych
danych są możliwe dzięki wirtualnym tabelom o nazwach inserted
i deleted, zawierających odpowiednio nowe i stare wartości. Aby
się do nich odwołać, trzeba użyć instrukcji SELECT tak, jakby
były to normalne tabele bazy danych. Modyfikacja danych
bezpośrednio w tych tabelach nie jest możliwa.
SQLServer, L.Banachowski
42
CREATE TRIGGER
CREATE TRIGGER nazwa_wyzwalacza
ON tabela
FOR [INSERT|UPDATE|DELETE]
AS instrukcje
Po słowie FOR (równoważnie AFTER) możemy napisać jedną,
dwie lub wszystkie trzy nazwy (oddzielone przecinkami).
• ALTER TRIGGER pozwala powtórnie wprowadzić
zmodyfikowany tekst wyzwalacza.
SQLServer, L.Banachowski
43
Przykład
Poniższy wyzwalacz nie pozwoli usunąć wierszy z tabeli:
SET IMPLICIT_TRANSACTIONS ON
CREATE TRIGGER wyzw1 ON Emp
FOR DELETE AS
BEGIN PRINT 'ROLLBACK';
SELECT * FROM EMP;
ROLLBACK;
END;
Wykonujemy kolejno:
DELETE EMP;
-- -- pusta tabela
SELECT * FROM EMP; -- znowu pełna tabela
• W wyzwalaczu można używać COMMIT i ROLLBACK (w
Oracle nie można).
SQLServer, L.Banachowski
44
Uwaga: ROLLBACK w
wyzwalaczu kończy zarówno
transakcję jak i aktualny wsad.
SQLServer, L.Banachowski
45
RAISERROR
RAISERROR(message, severity, state)
gdzie:
• message - dowolny tekst (komunikat błędu),
• severity - liczba z przedziału 0-25 (przy czym użytkownik może
używać wartości z przedziału 0-18),
• state - liczba z przedziału 1-127
Wartości parametrów "severity" i "state" są przekazywane do
aplikacji klienta, dzięki czemu różne błędy mogą być obsługiwane
w różny sposób.
Błędy severity>=20 powodują zamknięcie połączenia (sesji).
SQLServer, L.Banachowski
46
Przykład
Gdy pracownik z zarobkami zero zostanie wprowadzony, wyzwalacz
zgłosi błąd oraz wycofa całą aktualną transakcję:
CREATE TRIGGER wyzw ON Emp FOR INSERT AS
BEGIN
DECLARE @sal DECIMAL(6,2);
SET @sal=-1;
SELECT @sal = sal FROM inserted WHERE sal=0;
IF @sal = 0
BEGIN
RAISERROR('Pensja nie może być zero!' ,1,2);
ROLLBACK; -- porzucenie transakcji i wsadu
END;
END;
SQLServer, L.Banachowski
47
Wybrane funkcje wbudowane
CONVERT(typ_danych, wyrażenie [, styl]) - dokonuje konwersji typów
danych (styl jest używany przy konwersji do daty, typów walutowych itp.)
GETDATE() - zwraca aktualną datę systemową
LEFT(napis, ile_znaków) - zwraca określoną liczbę znaków napisu
rozpoczynając od lewej
LEN(napis) - zwraca długość napisu
REPLACE(napis, wzorzec, napis_do_zamiany) - wyszukuje i zamienia
fragment napisu
RIGHT(napis, ile_znaków) - zwraca określoną liczbę znaków napisu
rozpoczynając od prawej
SUBSTRING(napis, od, do) - zwraca określoną część napisu
SQLServer, L.Banachowski
48
Wybrane procedury systemowe
• Można ich używać z poziomu każdej bazy danych - operują na
bazach i tabelach systemowych.
• Do ich wykonania nie jest wymagane użycie polecenia EXEC
• Przy pomocy procedur systemowych możemy łatwo wyciągnąć
informację z tabel systemowych.
Sp_tables - tabele bazy danych
Sp_help nazwa_obiektu - informacje na temat obiektu (np. tabeli,
perspektywy, procedury)
Sp_helptext nazwa_obiektu - tekst obiektu (np. procedury)
Sp_helpdb nazwa_bazy - informacje na temat bazy danych
Sp_helpindex nazwa_tabeli - indeksy założone na tabeli
Sp_helpconstraint nazwa_tabeli – więzy spójności na tabeli
Sp_spaceused nazwa_obiektu - ilość miejsca zajętego przez obiekt
SQLServer, L.Banachowski
49
Sp_tables
SQLServer, L.Banachowski
50
Sp_help Emp
SQLServer, L.Banachowski
51
Schematy zawierające perspektywy systemowe
(w każdej bazie danych)
sys
sys.all_objects, sys.objects, sys.system_objects
sys.tables, sys.views, sys.schemas, sys.columns
INFORMATION_SCHEMA (zgodne z SQL’92)
SQLServer, L.Banachowski
52
Niektóre rozszerzenia wprowadzone w
wersji 2005
v
v
v
v
v
v
v
v
Wprowadzenie schematów i tworzenie obiektów
bazodanowych w ramach schematów.
Obiekty (ich metody) .NET dostępne w kodzie Transact
SQL – tak jak Java dostępna na serwerze Oracle.
Bezpośrednia obsługa zleceń HTTP na serwerze bazy
danych.
Zapytania rekurencyjne – WITH
Operator PIVOT (zapytanie krzyżowe).
Wyzwalacze dla instrukcji DDL
Obsługa wyjątków (TRY … CATCH…)
Nowy typ danych XML – odpowiednik XMLType w
Oracle.
SQLServer, L.Banachowski
53
Rozszerzenia w wersji 2008
v
Typy danych:
– DATE (sama data)
– TIME (sam czas)
– FILESTREAM (LOBy przechowywane w systemie
plików)
– HIERARCHY ID – węzeł w hierarchii
– GEOMETRY (typ obiektów geometrycznych)
– GEOGRAPHY (typ obiektów geograficznych)
v
Specyfikacja kolumny jako SPARSE
(zoptymalizowane przechowywanie NULL)
v
Instrukcja MERGE … USING … ON …
WHEN MATCHED … WHEN NOT MATCHED …
(połączenie INSERT, DELETE i UPDATE na docelowej
tabeli w oparciu o porównanie jej z inną tabelą)
SQLServer, L.Banachowski
54
Przykład MERGE
SQLServer, L.Banachowski
55
Rozszerzenia w wersji 2008 c.d.
• Klauzula WHERE w CREATE INDEX – filtr.
• Typ tabelowy, parametr tabelowy
Najpierw definiujemy typ tabelowy:
CREATE TYPE myTableType AS
TABLE( id INT, name NVARCHAR(100), qty INT)
Następnie procedurę:
CREATE PROCEDURE myProc(@tvp myTableType
READONLY) AS
UPDATE Inventory SET qty += s.qty
FROM Inventory AS i INNER JOIN @tp AS s ON i.id=s.id
• INSERT wielowierszowy
INSERT INTO contacts VALUES (‘John Doe’,’123-456-7890’),
(‘Jane Smith’,’6667-090-2222’);
SQLServer, L.Banachowski
56