SQL – język baz danych – przegląd

SQL - historia
• SQL - "Structured Query Language" - język czwartej
generacji (4GL) wymowa: "es-kju-ell" lub "sequel [si:kwl]"
• SQL – język baz danych, strukturalny język zapytań, język
kwerend.
SQL – język baz danych –
przegląd
Język do definiowania, wyszukiwania i
administrowania danymi w relacyjnej bazie danej.
Tadeusz Pankowski
www.put.poznan.pl/~tadeusz.pankowski
(c) T. Pankowski, SQL - przegląd
1
(c) T. Pankowski, SQL - przegląd
Standaryzacja - zalety
SQL - historia
1.
2.
3.
4.
5.
6.
7.
2
1970, E.F.Codd, "A relational model of data for large shared data
banks", Communications of the ACM, Vol.13, No.6, 1970, podstawy
relacyjnego modelu danych
1974, SEQUEL "Structured English Query Language", D. Chamberlin
i in., IBM San Jose Research Laboratory, implementacja: SEQUELXRM (1974-75)
1976-77, SEQUEL/2, implementacja i rozwój w ramach projektu
System R, IBM San Jose RL
1980-83, główne implementacje: ORACLE, DB2 (SQL/DS),
1986, SQL/86 - oficjalny standard ANSI (American National
Stanmdards Institute) oraz ISO (International Organization for
Standardization)
1992, SQL2 (lub SQL/92) - "International Standard ISO/IEC
9075:1992, Database Language SQL"
dalszy rozwój: SQL3 (SQL:1999), włącza idee podejścia
obiektowego, dane wielowymiarowe, heterogeniczne, XML, ...
(c) T. Pankowski, SQL - przegląd
3
•
•
•
•
•
•
redukcja kosztów szkolenia
przenośność aplikacji
wydłużenie żywotności aplikacji
komunikacja międzysystemowa, interoperacyjność
wygoda dla użytkownika, co robi system, a nie w jaki
sposób
wspólny interfejs do bazy danych
(c) T. Pankowski, SQL - przegląd
4
Standaryzacja - wady
•
•
•
•
ograniczenie kreatywności
SQL jest dość daleki od idealnego języka relacyjnego
braki samego SQL – wiele problemów pozostawiono jako
zależnych od implementacji "implementation
dependent", co utrudnia przenośność
złudzenie istnienia standardu (?)
Oprogramowanie standardowe
zapewniające wymianę danych
między serwerem i klientem:
ADO
(ActiveX Data Objects)
Architektura aplikacji baz
danych
ODBC
(Open Database Connectivity)
Warstwa
oprogramowania
serwera
MS SQL
Server
(c) T. Pankowski, SQL - przegląd
Warstwa
oprogramowania
klienta
Oprogramowanie klienta
ORACLE
Access
5
SQL – przeznaczenie
SQL – postać poleceń
SQL = SQL2 = SQL/92
Uwaga: Używać będziemy notacji stosowanej w dialekcie języka SQL
systemu MS SQL Server, a więc języka Transact-SQL.
1.
Główne funkcje zarządzania danymi:
• definiowanie
• manipulowanie,
• administrowanie
2.
Przeznaczony dla:
• interakcyjnych użytkowników końcowych,
• twórców programów użytkowych (aplikacji),
• administratorów baz danych,
• twórców oprogramowania specjalizowanego (narzędziowego,
systemów współpracujących z bazami danych, ...)
7
Zapytania w języku SQL mogą przybierać różną postać. Postać ta może
zależeć od:
1.
Miejsca wydawania polecenia:
•
•
•
2.
bezpośrednio w trybie interakcyjnym z terminala,
w oprogramowaniu serwera,
w oprogramowaniu kienta.
Kompletności posiadanej informacji w momencie formułowania
polecenia:
•
•
•
posiadamy pełną informację (w zapytaniu nie ma potrzeby używania
zmiennych),
używamy zmiennych do przekazywania i pobierania do/z zapytania
pewnych parametrów, ale postać zapytania jest ustalona,
używamy zmiennych do dynamicznego tworzenia postaci zapytania, a
więc zmienne mogą być zarówno parametry zapytania, jak również jego
struktura.
8
Pełne określenie zapytania
Zapytania sparametryzowane
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Dochod(IDPrac, Rok, Kwota)
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Dochod(IDPrac, Rok, Kwota)
Wykorzystanie zmiennych wyjściowych w T-SQL (Transact-SQL):
W trybie interakcyjnym, bezpośrednio z terminala
select Nazwisko, Placa
from
Pracownik
where Placa >= 2000
Wynikiem jest tabela o dwóch kolumnach: Nazwisko i Placa.
Do tabeli wynikowej należą wiersze, w których atrybut Placa
ma wartość większą lub równą 2000.
declare
select
from
where
@zm_placa money
@zm_placa = Placa
Pracownik
IDPrac = '00123'
W pierwszym wierszu deklarowana jest zmienna @zm_placa (nazwa
każdej zmiennej w języku Transact-SQL rozpoczyna się od znaku @) typu
money.
W drugim wierszu pod zmienną @zm_placa podstawiana jest wartość
kolumny Placa z ostatniego wiersza stanowiącego zbiór odpowiedzi na
zapytanie.
Z wykorzystaniem zmiennnych wierszowych (aliasów):
select P.Nazwisko, D.Rok, D.Kwota
from
Pracownik P, Dochod D
where P.IDPrac = D.IDPrac AND Rok > 1980
9
10
Zapytania sparametryzowane (c.d.)
Zapytania dynamiczne (T-SQL)
declare @zm_zapytanie varchar(100)
declare @zm_tabela varchar(100)
declare @zm_kolumny varchar(100)
Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)
Dochod(IDPrac, Rok, Kwota)
Wykorzystanie zmiennych wejściowych i wyjściowych (T_SQL):
set @zm_tabela = 'Pracownik'
set @zm_kolumny='Nazwisko, Placa'
set @zm_zapytanie='select ' + @zm_kolumny + ‘ from '+@zm_tabela
declare @IDPrac varchar(30)
declare @zm_placa money
set @IDPrac='00123'
select @zm_placa = Placa
from
Pracownik
where IDPrac = @IDPrac
execute sp_sqlexec @zm_zapytanie
•
•
Wartość zmienna @IDPrac określana jest przed wykonaniem zapytania.
Zauważmy, że dzięki znakowi @ system rozróżnia czy chodzi o nazwę
zmiennej @IDPrac, czy o nazwę kolumny IDPrac.
W obydwu powyższych przypadkach struktura zapytania jest znana.
11
•
trzy pierwsze linie zawierają deklaracje zmiennych
w trzech kolejnych liniach następuje określenie wartości zmiennych,
tworzymy:
”select Nazwisko, Placa from Pracownik”
w ostatniej linii następuje wykonanie zapytania, procedury pamiętanej
sp_sqlexec, ktrórej parametr @zm_zapytanie przekazuje tekst
polecenia (zapytania, procedury)
12
Zapytania dynamiczne (T-SQL)
Zapytania dynamiczne (VB - ADO)
•
W przypadku zapytań dynamicznych postać zapytania nie
jest znana na etapie kompilacji, w szczególności więc
system nie jest w stanie ani sprawdzić poprawności
składniowej zapytania, ani tym bardziej opracować
optymalnej strategii jego wykonania.
•
Czynności te realizowane są dopiero na etapie wykonania,
co istotnie zmniejsza efektywność przetwarzania.
13
14
Definiowanie tabel - przykład
Zapytania dynamiczne (VB - ADO)
‘Podprogram tworzenia tabeli w bieżącej bazie danych
‘nazwa tabeli podawana jest w polu tekstowym NazwaTabeli i jest wykorzystana
‘do dynamicznego utworzenia polecenia SQL-owego CREATE TABLE
----------------------------------------------------------------------------------------------------------Private Sub TworzTabele_Click()
Dim c As ADODB.Command
On Error Resume Next
Set c = New ADODB.Command
Set c.ActiveConnection = db
c.CommandText = "Create Table " & NazwaTabeli.Text & " (KolA Char (2),KolB Int)"
c.CommandType = adCmdText
db.Errors.Clear
c.Execute
If db.Errors.Count > 0 Then
WriteError
Else
StatusBar1.SimpleText = "Tabela utworzona."
End If
End Sub
15
Definiowanie tabel – przykład (c.d.)
Definiowanie tabel - przykład
create table Dostawa (
IdDost
int
identity(1,1),
NrDcy
char(3),
NrMag
int,
NrTow
char(5),
Data
datetime,
Ilosc
decimal(5),
constraint Kg_Dostawa primary key (IdDost),
constraint Ko_Dostawa_Dostawca
foreign key (NrDcy) references Dostawca,
constraint Ko_Dostawa_Towar
foreign key (NrTow) references Towar ,
constraint Ko_Dostawa_Magazyn
foreign key (NrMag) references Magazyn)
create table Dostawca (
NrDcy
char(3) primary key,
Nazwa
char(20),
Adres
char(20))
create table Magazyn (
NrMag
INT primary key,
Adres
char(20))
create table Towar (
NrTow
char(5) not null primary key,
Nazwa
char(20),
GrupaTow
char(3) not null,
CenaZak
money,
CenaSprz
money)
17
create table TowMag (
NrMag
int,
NrTow
char(5),
Stan
decimal(5),
primary key (NrMag, NrTow),
foreign key (NrMag) references Magazyn,
foreign key (NrTow) references Towar)
18
Operowanie danymi
Definiowanie tabel – przykład (c.d.)
1. W powyższych definicjach:
• klucz główny składa się z kilku kolumn dlatego jego definicja
podana jest po definicji wszystkich kolumn, np: primary key
(NrMag, NrTow);
• warunkom spójności bazy danych można nadać nazwy,
umieszczamy je wtedy we frazie constraint,
• definiujemy zależności referencyjne przez określenie klucza
obcego foreign key oraz tabeli, od której klucza głównego ten
klucz obcy zależy, np: foreign key (NrMag) references Magazyn
oznacza, że każda wartość wprowadzana do kolumny
TowMag.NrMag musi występować jako wartość klucza głównego
w tabeli Magazyn.
2. Za pomocą create table tworzone są tablice bazowe
(ekstensjonalne), mające fizyczną reprezentację w bazie danych oraz
określamy niektóre warunki spójności bazy dnaych.
3. Tablice wirtualne (intencjonalne), tzw. widoki lub perspektywy (ang.
view) tworzone są za pomocą CREATE VIEW
19
Cztery podstawowe operacje:
select
insert
update
delete
- wyszukiwanie,
- dołączanie,
- aktualizacja,
- usuwanie.
Przykłady operacji modyfikujących:
insert into Dostawa
values('10','100',1,'1996.11.26',3)
update Towar
set
CenaSprz = 1.1 * CenaSprz
where GrupaTow = 'AGD'
delete from Towar
whereCenaSprz < CenaZak
(c) T. Pankowski, SQL - przegląd
20
SELECT *
Problemy z SELECT *:
Co zadecydowało o sukcesie systemów relacyjnych baz
danych?
1. Wybiera wszystkie kolumny, ale w jakiej
kolejności? Kolejność kolumn w modelu
relacyjnym nie jest określona. Musimy się
więc odwoływać do poziomu fizycznego
określającego sposób pamiętania tabeli.
I think there are three elements of the System R prototype and its
deployment in test environments that had an effect on the eventual adoption
of relational data storage systems.
2. Jeśli za pomocą SELECT * został
zdefiniowany widok, to co się stanie jeśli do
bazowej tabeli dodam nową kolumnę? Ma
być ona włączana do widoku czy nie?
(c) T. Pankowski, SQL - przegląd
21
(c) T. Pankowski, SQL - przegląd
22
2.
1.
The first one is the obvious one that everybody suggests: the invention of
a nonprocedural query specification was a tremendous simplification that
made it much easier to specify applications. No longer did you have to say
which index to use and which join method to use to get the job done.
This was a tremendous boon to application development.
But the real reasons that the relational system won have nothing to do with the
relational model; they have to do with the fact that the early prototypes,
both System R and Ingres, supported ad hoc queries and online data
definition.
You could try out a query right away, rather than typing it into your application,
compiling the application, running it, and having a failure, usually a syntax error.
Even if there were no syntax errors, you still had the advantage that you could
look at the query answers and see if you had the right tuples.
Ad hoc online query execution was something that was unheard of in the
database community at that time, so this was a tremendous boon to
application developers and to people who were browsing the data.
(c) T. Pankowski, SQL - przegląd
23
(c) T. Pankowski, SQL - przegląd
24
Operowanie danymi
Operacja wyszukiwania:
3.
Maintaining the coherence between the metadata that the system
uses at run time to figure out where things are and which way's up and
which way's down and the actual use of the system.
So a difficult and rather successful effort in the System R project was to figure
out ways to synchronize the modification of metadata without
impacting or interrupting the ongoing work involving other data
objects.
select distinct W.NrTow, W.NrMag
from
Dostawa W
where NrDcy = '10' AND Ilosc > 2
distinct - usuwanie duplikatów
select W.NrDcy, D.Nazwa, T.Nazwa, T.CenaSprz
from
Dostawa W, Dostawca D, Towar T
where W.NrDcy = D.NrDcy AND W.NrTow = T.NrTow
(c) T. Pankowski, SQL - przegląd
25
(c) T. Pankowski, SQL - przegląd
Funkcje agregujące (c.d.)
Funkcje agregujące
count, sum, avg, max, min, count(*)
1.
2.
Liczba wszystkich dostawców:
select count(*) as Ld
from Dostawca
Ld
4
Liczba dostawców realizujących dostawy
select count(distinct W.NrDcy) as Ldd
from Dostawa W
Ldd
3
bez distinct:
select count(W.NrDcy) AS Ldd
from Dostawa W
Ldd
5
26
3. Sumaryczny wartość towaru 100 w cenach zakupu:
select
sum(T.CenaZak * TM.Stan) as W100
from
Towar T, TowMag TM
where
T.NrTow = TM.NrTow AND T.NrTow = '100'
W100
45 000
(c) T. Pankowski, SQL - przegląd
28
Operacje z wykorzystaniem kursora
Funkcje agregujące (c.d.)
4.
Wartości towarów w poszczególnych grupach towarowych:
select
from
where
group by
having
select
from
where
Kursor (ang. cursor) dostarcza mechanizmu dostępu do pojedynczych
wierszy wg zasady "jeden po drugim".
T.GrupaTow, sum(T.CenaZak * TM.Stan) as Wartość
Towar T, TowMag TM
GrupaTow Wartość
T.NrTow = TM.NrTow
----------------- -------------KOM
24 000.00
T.GrupaTow
RTV
45 000.00
GrupaTow <> 'AGD'
*
Towar T, TowMag TM
T.NrTow = TM.NrTow
(c) T. Pankowski, SQL - przegląd
29
Operacje z wykorzystaniem kursora
30
Operacje pozycyjne: UPDATE i DELETE
Aby przetwarzać dane za pomocą kursora należy:
• zadeklarować kursor DECLARE ... CURSOR i zdefiniować jego zakres
FOR SELECT,
• otworzyć kursor, OPEN - wykonywana jest operacja SELECT, kursor
wskazuje na miejsce bezpośrednio przed pierwszym wierszem w
utworzonym zbiorze wierszy (zakresie kursora),
• wykonać operację pobrania danych na kolejnym wierszu FETCH NEXT
FROM, pobierane dane można zapamiętać pod podanymi zmiennymi,
• pobieranie danych może być realizowane w pętli, różna od zera wartość
zmiennej (funkcji) globalnej @@FETCH_STATUS może kończyć
wykonywanie operacji pobierania danych,
• zmienne mogą być wykorzystywane w dowolnych operacjach,
wyrażenie SELECT @zmNazwa, @zmCenaSprz oznacza wypisanie
danych,
• w końcu kursor należy zamknąć CLOSE i dealokować DEALLOCATE.
(c) T. Pankowski, SQL - przegląd
declare @zmNazwa char(20)
declare @zmCenaSprz Money
declare kurAGD cursor
for select Nazwa, CenaSprz
from Towar
where GrupaTow = 'AGD'
open kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
while @@FETCH_STATUS = 0
begin
select @zmNazwa, @zmCenaSprz
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
end
close kurAGD
deallocate kurAGD
31
declare @zmNazwa char(20)
declare @zmCenaSprz money
declare kurAGD cursor
for select Nazwa, CenaSprz
from Towar
where GrupaTow = 'AGD'
open kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
while @@FETCH_STATUS = 0
begin
select @zmNazwa, @zmCenaSprz
update Towar set CenaSprz = CenaSprz + 1 where current of kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
end
close kurAGD
deallocate kurAGD
(c) T. Pankowski, SQL - przegląd
32
Widoki (views)
Widoki (views)
"Towary RTV w cenach sprzedaży "
• Widoki są tabelami wirtualnymi (intencjonalnymi), nie mającymi
bezpośredniej reprezentacji w bazie danych. Zapamiętana jest jedynie
ich definicja.
• Definicja widoku wykorzystywana jest w momencie odwoływania się
do niego jak do każdej innej tabeli.
create view RTV (NrTow, Nazwa, CenaSprz)
as select T.NrTow, T.Nazwa, T.CenaSprz
from Towar T
where T.GrupaTow = 'RTV‘
Definicja widoku wykorzystywana jest w momencie odwoływania się do
niego:
select
RTV.NrTow, RTV.Nazwa, RTV.CenaSprz
from
RTV
where
RTV.CenaSprz < 2000
Przekształcone jest do zapytania względem tabeli TOWAR:
select
RTV.NrTow, RTV.Nazwa, RTV.CenaSprz
from (select T.NrTow, T.Nazwa, T.CenaSprz
from Towar T
where T.GrupaTow = 'RTV' ) RTV
where RTV.CenaSprz < 2000
lub równoważnie:
select
T.NrTow, T.Nazwa, T.CenaSprz
from
Towar T
where
T.GrupaTow = 'RTV' and T.CenaSprz < 2000
(c) T. Pankowski, SQL - przegląd
33
(c) T. Pankowski, SQL - przegląd
34
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków
W przypadku widoku RTV, jego definicja
1. Jeśli widok jest tak zdefiniowany, że można
przetransformować operacje jego modyfikowania na
modyfikację tabel bazowych, to można go aktualizować.
2. Sytuacja taka występuje na przykład wtedy, gdy w
definicji kursora zawarto klucz główny tabel bazowych,
nad którymi jest definiowany i nie zawiera funkcji
agregujących ani słowa DISTINCT.
create view RTV (NrTow, Nazwa, CenaSprz)
as
select T.NrTow, T.Nazwa, T.CenaSprz
from
Towar T
where T.GrupaTow = 'RTV'
zawiera klucz główny tabeli Towar. Można go więc modyfikować.
Wówczas polecenie:
update RTV
set CenaSprz = CenaSprz + 10
zostanie wykonane i spowoduje odpowiednią modyfikację tabeli Towar.
(c) T. Pankowski, SQL - przegląd
35
(c) T. Pankowski, SQL - przegląd
36
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków (c.d.)
Bardzo często widoki są niemodyfikowalne.
Przykłady niemodyfikowalnych widoków:
2. Widok Miasta definiuje pary (DAdres, MAdres), gdzie DAdres jest
adresem dostawcy, a MAdres jest adresem magazynu, do którego
dostawca dostarcza towary:
1. Widok zawierający łączną ilość poszczególnych towarów we
wszystkich dostawach:
create view Miasta (DAdres, MAdres)
as select distinct D.Adres, M.Adres
from Dostawca D, Magazyn M, Dostawa W
where D.NrDcy = W.NrDcy and M.NrMag = W.NrMag
create view TD(NrTow, IlDost)
as select W.NrTow, SUM(W.Ilosc)
from Dostawa W
group by W.NrTow
Widok jest niemodyfikowalny, gdyż zawiera funkcję agregującą.
(c) T. Pankowski, SQL - przegląd
Widok ten jest niemodyfikowalny, gdyż zawiera słowo distinct.
37
(c) T. Pankowski, SQL - przegląd
Modyfikowalność widoków (c.d.)
Modyfikowalność widoków (c.d.)
Rozważmy widok jak poprzednio, ale bez słowa distinct:
create view Miasta1 (DAdres, MAdres)
as select D.Adres, M.Adres
from Dostawca D, Magazyn M, Dostawa W
where D.NrDcy = W.NrDcy and M.NrMag = W.NrMag
Miasta1
Dostawca
update Miasta1
set DAdres = 'Kraków'
where DAdres = 'Poznań'
39
Widok Miasta1 jest modyfikowalny, gdyż zawarte w jego definicji warunki na kluczach głównych
pozwalają dokonać odpowiednich modyfikacji na wyjściowych tabelach
38
Bezpieczeństwo baz danych (c.d.)
Bezpieczeństwo baz danych
1.
Przykład pokazuje, w jaki sposób tworzy się nowego użytkownika i nową rolę w systemie
SQL Server oraz w jaki sposób dodaje się użytkownika do istniejącej roli.
Mechanizmy zapewnienia bezpieczeństwa:
• uwierzytelnianie i autoryzacja:
(nazwa logowania, hasło) →
użytkownik bazy danych →
uprawnienia
• operacje GRANT/REVOKE/DENY – przyznawanie,
odbieranie, odmawiane uprawnień,
• wykorzystanie widoków (selektywne udostępnianie
danych),
• warunki spójności bazy danych
• archiwowanie i odtwarzanie
(c) T. Pankowski, SQL - przegląd
1.
2.
3.
4.
5.
6.
41
(c) T. Pankowski, SQL - przegląd
2.
3.
4.
Spójność, niesprzeczność, integralność (ang. consistency, integrity
constraints).
Spójność oznacza poprawność, niesprzeczność danych w bazie
danych. Standard SQL zawiera środki do definiowania warunków
spójności (lub więzów integralności), np. w obrębie zdania CREATE
TABLE.
Każda próba naruszenia zdefiniowanych warunków spójności
(podczas modyfikacji bazy danych) zostaje udaremniona, operacja
ją podejmująca jest odrzucana, a baza danych pozostaje nie
zmieniona.
W systemie pamiętana jest więc baza danych (dane podstawowe)
oraz wiedza o bazie danych (dane opisujące struktury i warunki
spójności).
(c) T. Pankowski, SQL - przegląd
42
Rodzaje warunków spójności
Spójność bazy danych
1.
Tworzenie nowego konta logowania do systemu MS SQL Server umożliwiającego
użytkownikowi przyłączenie się do serwera:
EXEC sp_addlogin 'Piotr', 'piotr', ‘sklep'
'Piotr' jest nazwą użytkownika, 'piotr' jest hasłem, a ‘sklep' bazą danych.
Tworzenie nowego użytkownika (w tym przypadku nazwa użytkownika jest
identyczna z nazwą konta logowania):
EXEC sp_adduser 'Piotr', 'Piotr'
Tworzenie nowej roli:
EXEC sp_addrole 'Klient'
Dodanie użytkownika do roli:
EXEC sp_addrolemember 'Klient', 'Piotr'
Zdefiniowanie uprawnień:
grant SELECT on Pracownik to Klient
deny SELECT on Pracownik to Piotr
Odczytanie informacji o kontach logowania, użytkownikach i rolach (dostępne po
ponownym uruchomieniu SQL Servera):
sp_helplogins
sp_helpuser
sp_helprole
43
1.
2.
3.
4.
5.
6.
Typ kolumny – typ może być standardowy lub pochodny zdefiniowany
przez użytkownika.
UNIQUE – dla określenia, że kolumna lub zestaw kolumn ma mieć
unikalną wartość w tabeli (jest kluczem potencjalnym - alternatywnym,
kandydującym – w tabeli), taki klucz może przyjmować wartości NULL.
PRIMARY KEY – specjalny przypadek UNIQUE dla zdefiniowania
klucza głównego – nie może przyjmować wartości NULL.
FOREIGN KEY - dla zdefiniowania zależności referencyjnych
(zależności odniesień). Wartość klucza obcego musi występować jako
wartość klucza w powoływanej tabeli.
CHECK - dla określenia, że kolumna lub zestaw kolumn (z jednej krotki)
mają mieć wartości spełniające określony warunek. Taka spójność
może być zdefiniowane jako reguła w wyrażeniu CREATE RULE.
Bardziej złożone warunki spójności można definiować za pomocą
procedur wyzwalanych w wyrażeniach CREATE TRIGGER. Procedury
te są automatycznie wyzwalane przy aktualizacji tabeli.
(c) T. Pankowski, SQL - przegląd
44
Programowanie SQL Servera
Systemowe procedury pamiętane
¾ Dla wykonywania obliczeń, których nie można wyrazić za pomocą
pojedynczego wyrażenia w języku Transact-SQL (T-SQL), stosuje
się:
• systemowe procedury pamiętane (system stored procedures),
• paczki (wsady) (batches) i skrypty (scripts),
• funkcje (functions),
• procedury pamiętane (składowane) (stored procedures),
• procedury wyzwalane (trigery) (triggers),
• kursory (cursors).
(c) T. Pankowski, SQL - przegląd
45
Systemowe procedury pamiętane przykłady
(c) T. Pankowski, SQL - przegląd
46
Systemowe procedury pamiętane przykłady
¾ Tworzenie typu PESEL opartego na standardowym typie danych varchar. Typ
nie może przyjmować wartości NULL, varchar(11) jest ujęte w apostrofy, gdyż
zawiera nawiasy:
USE master
EXEC sp_addtype PESEL, 'VARCHAR(11)', 'NOT NULL‘
¾ Odczytanie tekstu procedury wyzwalanej employee_insupd zawartej w bazie
danych pubs:
USE pubs
EXEC sp_helptext 'employee_insupd'
¾ Wykonanie prostego wyrażenia SELECT zawierającego wbudowany parametr
@level:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
(c) T. Pankowski, SQL - przegląd
¾ Nazwy wszystkich systemowych procedur pamiętanych rozpoczynają
się od „sp_” lub „xp_”.
¾ Systemowe procedury pamiętana są prekompilowanymi zbiorami
wyrażeń SQL-owych („sp_”) lub języka programowanie („xp_”).
¾ Procedury typu „xp_” (extended stored procedures) są to funkcje
zdefiniowane w bibliotekach dołączanych dynamicznie (bibliotekach
DLL) i służą do rozszerzenia funkcji SQL Servera. Aby zainstalować
taką procedurę należy najpierw skopiować plik DLL do katalogu
zawierającego SQL Server – najczęścij jest to C:\Mssql\Binn, a
następnie wykonać systemową procedurę pamiętaną
sp_addextendedproc.
47
¾ Dodanie rozszerzonej procedury pamiętanej:
USE master
EXEC sp_addextendedproc xp_hello, 'xp_hello.dll'
¾ Wykonuje podaną komendę systemu operacyjnego i zwraca wyjście jako zbiór
wierszy tekstowych:
xp_cmdshell 'dir *.exe'
¾ Tworzenie wyrażenia INSERT:
DECLARE @ret_string varchar (255)
EXEC xp_sprintf @ret_string OUTPUT,
'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'
PRINT @ret_string
Wynik:
INSERT INTO table1 VALUES (1, 2)
48
Paczki (batches)
Paczki i skrypty
• Paczka stanowi grupę złożoną z jednej lub z wielu wyrażeń
Transact-SQLa. Standardowym końcem paczki jest polecenie GO
lub koniec pliku. Paczkę może też stanowić grupa „podświetlonych”
wyrażeń (przy korzystaniu z Query Analysera).
• SQL Server kompiluje paczkę w pojedynczą jednostkę wykonywalną
zwaną planem wykonania (execution plan).
• Wyrażenia tworzące plan wykonania wykonywane są jednorazowo
w jednym czasie. W przypadku wystąpienia błędu kompilacji, żadne
z wyrażeń w paczce nie zostanie wykonane.
• Zmienna zdefiniowana w paczce nie jest dostępna poza nią –
zmienne są lokalne w paczce.
• Nie wszystkie wyrażenia mogą występować w jednej paczce (np.
definiowanie i wywoływanie procedury).
(c) T. Pankowski, SQL - przegląd
¾ Przykład skryptu złożonego z dwóch paczek:
USE sklep
GO
-- wykonanie paczki, wybór bazy danych sklep
DECLARE @Powitanie VARCHAR(50)
SET @Powitanie = 'Witaj w sklepie!'
print @Powitanie
GO
-- wykonanie paczki,
-- zmienna @Powitanie po GO nie jest już dostępna.
49
(c) T. Pankowski, SQL - przegląd
50
Funkcje skalarne
Funkcje
Funkcja zwraca największy numer pracownika z podanego działu:
¾ Rodzaje funkcji:
• funkcje skalarne (scalar user-defined functions) – zwracaną
wartością jest pojedyncza liczba lub tekst, tj. wartość dowolnego
typu z wyjątkiem typów: text, ntext i image;
• proste funkcje tablicowe (inline table-valued functions) – zwracają
wartość typu TABLE, przy czym zwracana tabela jest określona za
pomocą pojedynczego wyrażenia SELECT (funkcje te nazywane są
sparametryzowanymi widokami);
• złożone funkcje tablicowe (multi-statement table-valued functions) –
zwracają zmienną typu TABLE, przy czym zwracana tabela jest
dowolną tabelą utworzoną w ciele funkcji.
CREATE FUNCTION ostatni_prac_dzialu (@id int)
RETURNS int
BEGIN
declare @nr int
set @nr = (select max(IdPrac) from pracownik
where IdDzialu=@id)
RETURN @nr
END
Wykorzystanie funkcji:
select * from pracownik
where IdPrac = dbo.ostatni_prac_dzialu(1)
(c) T. Pankowski, SQL - przegląd
51
(c) T. Pankowski, SQL - przegląd
52
Proste funkcje tablicowe
Złożone funkcje tablicowe
select * from dbo.prac_dzialu(2)
Funkcja zwraca listę dobrze zarabiających pracowników:
create function krezus(@z int)
RETURNS @lista table (id int PRIMARY KEY,
nazwisko varchar(22), dochod int)
as
begin
if @z > 5000
insert @lista select id_prac,nazwisko,zarobki
from pracownik1 where zarobki > @z
RETURN
end
lub bez dbo
Wykorzystanie funkcji:
select * from prac_dzialu(2)
select * from krezus(5500)
select * from krezus(1000) –- tabela pusta
Funkcja zwraca zbiór pracowników z działu o podanym identyfikatorze:
create function prac_dzialu(@id int)
RETURNS TABLE
as
RETURN(select * from pracownik where IdDzialu=@id)
go
Wykorzystanie funkcji (wywołanie funkcji występuje w roli tabeli):
(c) T. Pankowski, SQL - przegląd
Procedury pamiętane
Procedura oblicza dochód w podanym okresie:
CREATE PROC dochod
@idprac int,
@czas int,
@wynik int OUTPUT
-- parametr wyjsciowy
as
select @wynik = (pensja + premia)*@czas
from pracownik where IdPrac = @idprac
go
Wywołanie:
declare @c int, @w int
select @c = 12
exec dochod 1,@c,@w output
select
cast(@c as varchar(4))+' miesiecy' as [dochod za okres],
cast(@w as varchar(11))+' zl' as wynosi
go
Wynik:
dochod za okres
wynosi
-------------------- -------12 miesiecy
14400 zl
53
(c) T. Pankowski, SQL - przegląd
54
Procedury wyzwalane (trigery)
¾ Procedury wyzwalane (triggery):
• rozszerzają możliwości definiowania warunków spójności,
• wywoływane są automatycznie w wyniku wykonania operacji:
INSERT, UPDATE lub DELETE,
• może specyfikować operację, która wykona się zamiast operacji
głównej – wariant INSTEAD OF,
• wykorzystują systemowe tabele INSERTED i/lub DELETED
(INSERTED zawiera dołączane krotki lub nową wersję krotek
zmienianych; DELETED zawiera usuwane krotki lub starą wersję
krotek zmienianych),
• mogą być przypisane do tabeli lub widoku,
• mogą być wywoływane rekurencyjnie.
(c) T. Pankowski, SQL - przegląd
56
Procedury wyzwalane – INSTEAD OF
Procedury wyzwalane - przykład
Trigger zaznacza w kolumnie del elementy przeznaczone do usunięcia:
Zakaz dopisania pracownika, który zarabia więcej niż jego kierownik:
create trigger zaznacz
on Pracownik
INSTEAD OF delete as
update Pracownik
set del='*'
where nazwisko in (select nazwisko from deleted)
go
delete from Pracownik
where nazwisko like 'K%‘
create trigger maks
on pracownik
for insert as
if (select zarobki from inserted) >
(select k.zarobki from pracownik k, inserted i
where k.IdPrac = i.IdKier)
begin
rollback
end
(c) T. Pankowski, SQL - przegląd
57
Wynik:
premia
----------200
200
200
200
del
---*
NULL
*
NULL
58
Kursory
Kursory
Składnia SQL-92:
Kursor (ang. cursor) dostarcza mechanizmu dostępu do pojedynczych
wierszy wg zasady "jeden po drugim".
DECLARE nazwa_kursora [INSENSITIVE] [SCROLL] CURSOR
FOR wyrażenie_select
[FOR {READ ONLY | UPDATE [OF nazwa_kolumny [,...n]]}]
Składnia Transact-SQL:
DECLARE nazwa_kursora CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR wyrażenie_select
[FOR UPDATE [OF nazwa_kolumny [,...n]]]
nazwisko
-------------------Kowalski
Lipski
Kowal
Pawlak
59
declare @zmNazwa char(20)
declare @zmCenaSprz Money
declare kurAGD cursor
for select Nazwa, CenaSprz
from Towar
where GrupaTow = 'AGD'
open kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
while @@FETCH_STATUS = 0
begin
select @zmNazwa, @zmCenaSprz
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
end
close kurAGD
deallocate kurAGD
60
Kursory (c.d.)
Kursory a ADO Recordset
Aby przetwarzać dane za pomocą kursora należy:
• zadeklarować kursor DECLARE ... CURSOR i zdefiniować jego zakres
FOR SELECT,
• otworzyć kursor, OPEN - wykonywana jest operacja SELECT, kursor
wskazuje na miejsce bezpośrednio przed pierwszym wierszem w
utworzonym zbiorze wierszy (zakresie kursora),
• wykonać operację pobrania danych na kolejnym wierszu FETCH NEXT
FROM, pobierane dane można zapamiętać pod podanymi zmiennymi,
• pobieranie danych może być realizowane w pętli, różna od zera wartość
zmiennej (funkcji) globalnej @@FETCH_STATUS może kończyć
wykonywanie operacji pobierania danych,
• zmienne mogą być wykorzystywane w dowolnych operacjach,
wyrażenie SELECT @zmNazwa, @zmCenaSprz oznacza wypisanie
danych,
• w końcu kursor należy zamknąć CLOSE i dealokować DEALLOCATE.
(c) T. Pankowski, SQL - przegląd
61
Kursory a ADO Recordset
(c) T. Pankowski, SQL - przegląd
63
¾ Obiekt Recordset representuje zbiór rekordów z tabeli bazowej lub
wynik wykonania polecenia. W każdej chwili czasu obiekt Recordset
wskazuje jeden rekord w zbiorze zwany rekordem bieżącym.
¾ Tworzenie nowego obiektu Recordset:
Set Klient = New ADODB.Recordset
Set Klient.ActiveConnection = db
If Len(StanUSA.Text) = 0 Then
Klient.Source = "Select CustomerId, Name, State From
Customers"
Else
Klient.Source = "Select CustomerId,Name,State From
Customers Where State = '" _
& StanUSA.Text & "'"
End If
(c) T. Pankowski, SQL - przegląd
62