Rozdział 15
Delphi na sewerze SQL Microsoft
W tym rozdziale zapoznamy się z relacyjnym systemem baz danych (ang.
RDBMS) Microsoft SQL Server. Omówiono w nim problematykę współpracy
Delphi z bazą SQL Server oraz specyficzną odmianę języka SQL, z której
korzysta.
Uruchamianie serwera
Uruchomienie i zatrzymanie systemu Microsoft SQL Server umożliwia program
SQL Server Manager, znajdujący się grupie programów lub folderze SQL Server.
Te dwa podstawowe elementy zarządzania serwerem są także realizowane poprzez
program Services z Windows NT Control Panel. Ponieważ SQL Server
funkcjonuje jako usługa Windows NT, możemy też wykorzystać polecenie
Services z programu Windows NT Server Manager.
Zatrzymanie serwera umożliwia także instrukcja SHUTDOWN języka Transact -
SQL. Wykorzystanie opcji NOWAIT spowoduje natychmiastowe przerwanie jego
pracy (normalnie serwer jest zatrzymywany dopiero po zakończeniu
uruchomionych procesów).
A
ączenie się z serwerem
Warto odnotować, że wiele z poruszanych tutaj zagadnień odnosi się także do
Sybase SQL Server. Nie powinno to być dla nikogo zaskoczeniem, ponieważ
dopiero od wersji Microsoft SQL Server 6.0 produkty te istotnie się różnią.
Niska cena systemu Microsoft SQL Server i możliwość pracy w środowisku
Windows NT mogą stanowić zachętę do uruchamiania aplikacji
jednostanowiskowo (klient i serwer funkcjonują na tym samym komputerze).
Wiele małych przedsiębiorstw przyjmuje właśnie takie rozwiązanie. Aplikacja
wykorzystywana w taki sposób powinna być możliwie bezawaryjna. Jeśli bowiem
spowodujemy załamanie systemu, stracimy nie tylko proces klienta ale i serwera,
co może doprowadzić do uszkodzenia przetwarzanych danych. Uwaga ta odnosi
się także do każdego systemu operacyjnego i serwera SQL.
Natomiast niekwestionowaną zaletą instalacji jednostanowiskowej jest łatwość
konfigurowania i nawiązywania połączenia. Wybieramy wtedy protokół TCP/IP,
450 Część III
podając - zamiast rzeczywistego adresu IP komputera - adres pętli zwrotnej (ang.
loop-back address) 127.0.0.1.
Konfigurowanie połączeń klienta
Do skonfigurowania oprogramowania klienta posłużymy się narzędziem SQL
Client Configuration Utility (powinno się ono znajdować w folderze programu
Microsoft SQL Server). Wykonanie opisanych poniżej czynności pozwoli nam
utworzyć nowe połączenie klienta:
1. Uruchamiamy oprogramowanie SQL Server Client Configuration Utility.
2. Wybieramy fiszkę Advanced.
3. Wpisujemy nazwę serwera do pola tekstowego Server. W zasadzie nazwa może
być dowolna, jednak podanie takiej, jak zdefiniowana na komputerze serwera,
ułatwi konfigurację.
4. W części Net Library Configuration wybieramy domyślny sieciowy protokół
komunikacyjny. Prawdopodobnie będzie to albo komunikacja przez nazwane
potoki (ang. Named Pipes) albo TCP/IP.
5. Jeśli wybraliśmy komunikację przez potoki, wpisujemy - do pola tekstowego
Server - nazwę komputera, na którym pracuje serwer, i - aby dodać nowy alias
połączeniowy - klikamy Add/Modify, a następnie Done.
6. W przypadku innego protokołu komunikacyjnego, konieczne będą inne dane.
Ich rodzaj zależy od dokonanego wyboru. Np. TCP/IP wymaga adresu IP
komputera serwera oraz numeru nasłuchiwanego portu (powinny być one
oddzielone przecinkiem). Po ich wpisaniu klikamy Add/Modify a następnie
Done.
Teraz potrzebujemy jeszcze aliasu klienta, który umożliwi aplikacji w Delphi
dostęp do naszego serwera SQL.
OSTRZEŻENIE:
Powinniśmy upewnić się, czy posiadamy właściwą wersję oprogramowania SQL
Client Configuration. Klient uruchomiony w 16 bitowym środowisku wymaga 16-
bitowej wersji biblioteki procedur połączeniowych, a klient 32-bitowy - 32-bitowej
wersji biblioteki. Niektóre narzędzia innych producentów do administrowania (np.
DB Artisan) są jeszcze aplikacjami 16-bitowymi. Korzystają one z 16-bitowych
wersji klienta, nawet jeśli pracujemy w środowisku 32-bitowego systemu
operacyjnego.
Rozdział 15 Delphi na serwerze SQL Microsoft 451
Rozwiązywanie problemów połączeniowych z serwerem Microsoft SQL
Poniżej zebraliśmy kilka praktycznych porad:
1. Jeśli nasza, napisana w Delphi, aplikacja nie łączy się z serwerem, powinniśmy
spróbować nawiązać komunikację poprzez ISQL/w. Jeśli próba się powiedzie,
to prawdopodobnie z
le skonfigurowaliśmy alias BDE. W takiej sytuacji
powinniśmy powrócić do programu BDE Configuration i upewnić się, czy
podane ustawienia są poprawne ( w szczególności nazwa serwera).
2. Niemożność połączenia się poprzez ISQL/w pozwala przypuszczać, że
przyczyną problemów może być protokół sieciowy. Jeśli korzystamy z TCP/IP,
wtedy połączenie z serwerem możemy sprawdzić za pomocą programu PING,
dostarczanego wraz z Windows 95 i Windows NT. Powinniśmy spróbować
łączenia poprzez nazwę komputera (zapisaną w pliku HOSTS) oraz poprzez
jego adres IP. Jeśli komunikujemy się z serwerem przez potoki, możemy
skorzystać z instrukcji net view \\servername, gdzie servername
jest nazwą serwera NT, na którym realizowany jest serwer SQL. Po uzyskaniu
połączenia poprzez c wykonujemy instrukcję netuse \\
servername.\IPC$. Gdy obie zostaną wykonane poprawnie, skorzystamy
z procedur makepipe/readpipe. Uruchamiamy więc makepipe na serwerze oraz
readpipe /Sservername /Dteststring na komputerze klienta ( CTRL+C
lub CTRL+BREAK kończy realizację makepipe). Jeśli któraś z prób nie
wypadnie pomyślnie należy zwrócić się do Administratora NT. y
ródłem
zakłóceń może być wadliwa obsługa protokołu potoków na serwerze.
3. Jeśli łączymy się poprzez adres IP, a nie udaje się to poprzez nazwę serwera
powinniśmy sprawdzić plik HOSTS. Tymczasowo można zmienić odwołanie
sieciowe (programem konfiguracyjnym SQL Net), zastępując w nim nazwę
komputera jego adresem IP. Z uwagi na fakt, że odwołanie do nazwy jest
wygodniejsze, powinniśmy możliwie najszybciej usunąć przyczynę
uniemożliwiającą połączenie.
4. W przypadku protokołu IPX, warto rozpocząć od sprawdzenia, czy nasz
komputer ma dostęp do serwera. Można to zrobić uruchamiając isql /L z linii
poleceń systemu operacyjnego. Isql /L wywołuje funkcję dbserverenum,
która wyświetla listę dostępnych serwerów. Jeśli naszego serwera nie ma na
liście, powinniśmy zwrócić się do administratora sieci (prawdopodobnie sieć
nie funkcjonuje poprawnie).
5. Jeśli korzystamy z TCP/IP i w żaden sposób nie udało się nam połączyć
z serwerem, to prawdopodobną przyczyną może być uszkodzenie sieci.
Oczywiście powinniśmy jeszcze raz sprawdzić adres IP serwera oraz uruchomić
PING, podając adres 127.0.0.1 - aby przekonać się, czy stos protokołu
TCP/IP funkcjonuje poprawnie. Jeśli test się nie powiedzie, to prawdopodobnie
452 Część III
z
le skonfigurowano sam protokół. W takim przypadku ponownie proponujemy
zwrócić się do administratora sieci.
6. Jeśli nie uzyskaliśmy połączenia za pomocą ISQL /w , mimo iż PING
funkcjonuje poprawnie, powinniśmy sprawdzić - czy numer portu w odwołaniu
jest zgodny z ustawionym na serwerze bazy danych. Informacji o portach
przypisanych do serwera powinien nam udzielić administrator bazy danych.
7. Jeśli wszystko wydaje się być skonfigurowane poprawnie, możemy dla próby
wykorzystać inny protokół ( jeśli oczywiście mamy taką możliwość). Serwer
pozwala na jednoczesną obsługę kilku protokołów, więc może tymczasowo
wykorzystywać tylko ten, z którym funkcjonuje poprawnie, aż do momentu
przywrócenia do właściwej pracy drugiego protokołu.
WSKAZÓWKA:
Podstawowym narzędziem diagnostycznym w środowisku Windows NT jest
Windows NT Diagnostics (z folderu Administrative Tools), umożliwiający
przeglądanie zasobów komputera. Jest to adaptowana do NT wersja popularnego
programu MSD (Microsoft Diagnostic), znanego z systemów DOS i Windows 3.x.
Plik z programem MSD nazywa się WINMSD.EXE, i znajduje się kartotece
\WINNT\SYSTEM32.
Tworzenie aliasu BDE
Pokażemy teraz jak stworzyć alias BDE, dzięki któremu nasze aplikacje w Delphi
uzyskają dostęp do serwera bazy danych. Ten temat był już omawiany wcześniej,
dlatego tutaj ograniczymy się tylko do pokazania, jak utworzyć alias DBE
w zależności od ustawień oprogramowania sterującego obsługą bazy danych.
Aliasy BDE można tworzyć w oparciu o program BDE Administrator lub Delphi
Database Explorer. Poniższy opis odnosi się do programu BDE Administrator.
1. Uruchamiamy DBE Administrator, klikamy (prawym przyciskiem myszy)
fiszkę Databases i wybieramy New . W oknie dialogowym New Database Alias
wybieramy MSSQL z listy typów aliasów.
2. Po dodaniu nowego aliasu wpisujemy jego nazwę.
3. Konfigurujemy go, ustawiając parametry na stronie Definition.
4. Klikamy parametr SERVER NAME i ustawiamy w nim nazwę naszego serwera
SQL- tę, której użyliśmy poprzednio w programie SQL Client Configuration
Utility.
Rozdział 15 Delphi na serwerze SQL Microsoft 453
5. Dodatkowo możemy ustawić parametr USER NAME, podając nazwę
użytkownika, który będzie domyślnie logowany na serwerze. Będzie się ona
także ukazywała we wbudowanym w Delphi oknie dialogowym logowania.
Aby zapisać zmiany klikamy przycisk Apply i zamykamy sesję programu BDE
Administrator.
Specjalne ustawienia aliasu BDE
Omówione poniżej ustawienia wartości parametrów aliasu BDE mogą okazać się
istotne przy nawiązywaniu połączenia z Microsoft SQL Server. Chociaż możemy
ograniczyć się do ustawień domyślnych, to jednak w przypadku napotkania
problemów znajomość funkcji poszczególnych parametrów będzie z pewnością
bardzo przydatna. Warto zauważyć, że niektóre z nich określają sposób
funkcjonowania programów obsługi bazy i mogą być ustawiane na stronie
Configuration w programie BDE Administrator.
DRIVER FLAGS Setting and BLOB Handling
Czytanie lub zapis BLOB (Binary Large OBject) staje się czasami przyczyną
zawieszenia serwera Microsoft SQL. Jeśli występują tego rodzaju problemy,
proponujemy zmienić w aliasie ustawienie parametru DRIVER FLAG - z wartości
0 na 1. Powinno to być wystarczające dla usunięcia problemu, ale jednocześnie
może powodować naruszanie limitu czasu podczas zapisywania BLOB.
APPLICATION NAME (Nazwa aplikacji)
Wartością parametru APPLICATION NAME jest nazwa, pod którą nasz proces
będzie reprezentowany w tablicy procesów serwera SQL.
CONNECT TIMEOUT (Czas do zerwania połączenia)
Decyduje o tym, jak długo klient będzie ponawiał próby nawiązania połączenia
z serwerem SQL. Domyślnie przyjmowane jest 60 s. Zwiększenie tego parametru
może poprawić funkcjonowanie stacji klienckich (zwłaszcza, gdy łączymy się
przez sieć rozległą WAN).
HOST NAME (Nazwa hosta)
Parametr służy do nadania nazwy stacji roboczej , poprzez którą będzie ona
identyfikowana w tablicy procesów serwera SQL. Dzięki temu będzie można
odróżnić to połączenie od innych na różnych listach - takich jak np. tworzona
przez procedurę sp_who.
454 Część III
NATIONAL LANG NAME ( Wybrany język)
Parametr ten definiuje język, w jakim ukazują się komunikaty o błędach. Jeśli go
nie ustalimy przyjęte zostanie ustawienie domyślne.
TDS PACKET SIZE (Rozmiar pakietów TDS)
Stosowany do ustalenia rozmiaru pakietów Tabular Data Stream (TDS) jest
protokołem pakietowym wysokiego poziomu, wykorzystywanym przez SQL
Server do wymiany danych z klientami. Chociaż w programie BDE Administrator
można określić rozmiar od 0 do 65535, to jednak należy ograniczyć się do zakresu
obsługiwanego przez SQL Server: od 512 do 32767. Może się okazać, że
zwiększenie rozmiaru pakietu spowoduje wzrost przepustowości systemu
z powodu zmniejszenia liczby pakietów wymaganych do transmisji dużych ilości
danych (np. pól BLOB). Domyślny rozmiar wynosi 4096 i najlepszą wydajność
osiąga się dla wielkości pakietów w zakresie od 4096 do 8192.
Aby ustalić maksymalny rozmiar pakietu obsługiwanego przez serwer można użyć
pamiętanej procedury serwera SQL o nazwie sp_configure. Powinniśmy więc
- aby otrzymać bieżący rozmiar pakietu - wykonać instrukcję sp_configue
'network packet size'. Należy odpowiednio ustawić ten parametr przed
zmianą odpowiadających mu ustawień w programie DBE Administrator.
Niezgodność TDS PACKET SIZE w DBE Administrator i network packet
size w SQL Server może wywołać jeden z komunikatów błędów:
Error: unknown user name or password (nieznana nazwa
użytkownika lub hasło)
Server error-4002 Login failed (nieudane rejestracja
w systemie)
Server error - 20014 Login incorrect (niewłaściwa
rejestracja)
UWAGA:
Zmiana domyślnego rozmiaru pakietu jest możliwa dopiero od wersji 5 TDS
DATABASE NAME
Pozwala określić nazwę bazy danych SQL, z którą chcemy się połączyć. Zwykle
tworzy się osobny alias dla każdej wykorzystywanej bazy. Wtedy parametr
DATABASE NAME wskazuje tę, do której odnosi się dany alias. Brak wartości
parametru DATABASE NAME jest równoznaczny ze wskazaniem na domyślną
bazę użytkownika. Nie polecamy jednak takiej strategii. Jednym z powodów jest
Rozdział 15 Delphi na serwerze SQL Microsoft 455
fakt, że do ustalenia bazy trzeba wtedy tworzyć zapytanie do jednej z tabel
systemowych (ang. syslogins table) na serwerze.
BLOB EDIT LOGGING (Rejestracja zmian w BLOB)
Można zablokować rejestrację zmian w polach BLOB, ustawiając wartość tego
parametru na FALSE. Minimalizujemy wówczas wymagania pamięciowe,
poprawiając wydajność. Ustawienie FALSE powoduje, że dane BLOB
transmitowane są za pomocą programu narzędziowego SQL Server bulk copy .
Dlatego, jeśli zamierzamy użyć tego mechanizmu, powinniśmy ustawić opcję set
select into/bulk copy on w docelowej bazie - za pomocą procedury
pamiętanej sp_dboption.
MAX QUERY TIME
Ten parametr określa czas wyczekiwania przez SQL - do momentu zakończenia
asynchronicznego zapytania ( po upływie tego czasu jest ono usuwane). Microsoft
i Sybase SQL Links domyślnie wykonują zapytania synchronicznie. Wartość
parametru TIMEOUT na stronie sterownika MSSQL określa czas zakończenia
zapytania synchronicznego. Tryb asynchroniczny ustawimy zwiększając parametr
MSSQL DRIVER FLAG do 2048. Domyślnie SQL Links ogranicza czas
zakończenia zapytania asynchronicznego do 300 s.
Wstęp do SQL
SQL Server jest nazwą produktu firmy Microsoft, i rzeczywiście komunikujemy
się z nim poprzez SQL. Powinniśmy mieć jednak na uwadze, że SQL Server jest
serwerem bazy danych, a nie serwerem SQL. W tym paragrafie zapoznamy się
językiem Transact-SQL - odmianą SQL, z której korzystają serwery SQL Server.
Zwrócimy w nim szczególną uwagę na kilka cech, które różnią tę implementację
SQL od produktów dostarczanych przez innych sprzedawców. Aby móc poznawać
w praktyce prezentowane elementy języka, wystarczy zapewnić sobie dostęp do
serwera i umieć się z nim komunikować poprzez ISQL.
Tworzenie bazy danych
Zaczniemy od utworzenia bazy danych i umieszczenia w niej tymczasowych tabel,
które posłużą do analizy podanych dalej przykładów. Skorzystamy z instrukcji
CREATE DATABASE. Dokładna postać jej składni zależy od konkretnej
implementacji. Tutaj zaprezentujemy jej podstawową postać, rozpoznawaną przez
SQL Server:
456 Część III
CREATE DATABASE dbname ON datadevice=size LOG ON
logdevice=size
Planując umieszczenie tworzonej bazy danych korzystamy z urządzeń logicznych.
Koncepcja urządzeń logicznych pozwala uniknąć odwołań do fizycznego
położenia danych na dysku. Stanowią one warstwę pośrednią pomiędzy
sterownikami dysku i bazami danych.
Definiujemy je korzystając z instrukcji DISK INIT. Oto prosty przykład jej
użycia:
DISK INIT
name='saltrn00'
physname='c:\mssqlsql\data\saltrn00.dat',
vdevno=5,
size = 2048
Parametr name określa uchwyt logiczny (ang. logical handler), który występuje
w odwołaniach do danego urządzenia.
Parametr physname określa szczegółowo fizyczne położenie i nazwę pliku. Jeśli
tworzymy urządzenie logiczne na surowej partycji Windows NT, podajemy tylko
literę określającą napęd.
vdevno jest logicznym numerem urządzenia wirtualnego. Musi on być unikalny
wśród numerów urządzeń zdefiniowanych na serwerze. Może przyjmować
wartości z zakresu od 1 do 255. 0 jest zarezerwowane dla urządzenia głównego
(ang. master device).
Size określa liczbę stron o wielkości 2K przeznaczonych dla danego urządzenia.
Np. wielkość 4M (4096K) uzyskamy podając 2048.
Analogicznie tworzymy urządzenia do rejestracji zdarzeń (ang. log devices)
DISK INIT
name='sallog00'
physname='c:\mssql\data\sallog00.dat',
vdevno=6,
size = 512
WSKAZÓWKA:
Utworzenie urządzenia poprzez DISK INIT w systemie plikowym NTFS będzie
trwało krócej niż w FAT. W tym ostatnim bowiem nowe urządzenie plikowe musi
być inicjalizowane natychmiast po utworzeniu. Dla dużych urządzeń operacja ta
jest bardzo czasochłonna. Natomiast w NTFS inicjalizacja nie jest konieczna.
Rozdział 15 Delphi na serwerze SQL Microsoft 457
Po zdefiniowaniu urządzeń możemy przystąpić do utworzenia bazy. Instrukcja
CREATE DATABASE w poniższym przykładzie wykorzystuje zdefiniowane
wcześniej urządzenia:
CREATE DATABASE sales ON saldat00=4 LOG ON sallog00=1
Instrukcja USE
Dzięki instrukcji USE możemy ustalić bieżącą (aktywną) bazę. Jej składnia jest
bardzo prosta:
USE dbname
Aktywną bazę można też wybrać z listy rozwijalnej DB w programie
narzędziowym ISQL. Po utworzeniu nowej bazy w ISQL klikamy listę rozwijalną
i odświeżamy ją poleceniem Refresh. Jeśli klikniemy listę drugi raz będzie już na
niej umieszczona nowo utworzona baza.
Tworzenie tabel
Po utworzeniu bazy czynimy ją aktywną i możemy już rozpocząć definiowanie
obiektów. Prawie każde pojęcie odnoszące się do relacyjnych baz danych może
być zaprezentowane za pomocą co najwyżej trzech tabel. Utwórzmy więc na
początek trzy tabele, dzięki którym będziemy mogli zrozumieć istotę omawianych
zagadnień. Posłużymy się poleceniem: CREATE TABLE. Wprowadz
my
odpowiednią instrukcję SQL, aby utworzyć tabelę CUSTOMER:
CREATE TABLE CUSTOMER
(
CustomerNumber int NOT NULL,
LastName Char(30) NULL,
FirstName char(30) NULL,
StreetAddress char(30) NULL,
City char(20) NULL,
State char(2) NULL,
Zip char(10) NULL
)
Dalej utwórzmy tabelę SALE :
CREATE TABLE SALE
(
SaleNumber int NOT NULL,
SaleDate datetime NULL,
CustomerNumber int NOT NULL,
ItemNumber int NOT NULL,
Amount money
)
458 Część III
Po zbudowaniu tabeli SALE, pozostała do utworzenia tylko tabela ITEM:
CREATE TABLE ITEM
(
ItemNumber int NOT NULL,
Description char(30) NULL,
Price money NULL
)
Dodawanie kolumn
Instrukcja ALTER TABLE umożliwia dodanie kolumn do utworzonej tabeli.
Microsoft SQL Server, jako jeden z niewielu, nie daje możliwości modyfikacji ani
usuwania kolumn. Kolumnę możemy dodać w następujący sposób:
ALTER TABLE CONTACT
ADD PhoneNumber char(10) NULL
Oczywiście nie można rozszerzyć niepustej tabeli o nową kolumnę z zastrzeżeniem
NOT NULL. Wtedy bowiem nie można byłoby odpowiednio uzupełnić
umieszczonych w niej wierszy.
Więzy (constrains)
Więzy to mechanizmy, dzięki którym możemy ograniczyć rodzaj danych
umieszczanych w kolumnie lub powiązać je ze sobą. Pozwalają także określić
wartości domyślne dla kolumn. Definiuje się je dołączając odpowiednie instrukcje
do CREATE TABLE albo ALTER TABLE. Jednym z przykładów nakładania
więzów jest tworzenie klucza głównego:
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (CustomerNumber)
W tym przykładzie zdefiniowaliśmy klucz główny dla tabeli CUSTOMER jako
pole CustomerNumber. Dzięki temu - na bazie pola CustomerNumber
zostanie utworzony unikalny indeks. Oczywiście do zdefiniowania klucza
głównego tabeli nie można użyć kolumny, która zezwala na użycie wartości NULL.
Klucz obcy definiuje kolumnę w jednej tabeli, której wartości muszą znajdować
się w innej tabeli. Nie określa jednoznacznie wierszy (tak jak jest to w przypadku
klucza głównego). Musi być jednak kluczem głównym lub unikalnym w tabeli, do
której się odnosi. Dodanie klucza obcego powoduje, że SQL Server buduje wtórny
indeks bazujący na polu kluczowym. Oto odpowiedni przykład:
ALTER TABLE SALE
ADD CONSTRAINT INVALID_CUSTOMER_NUMBER FOREIGN KEY
(CustomerNumber)REFERENCES CUSTOMER
Rozdział 15 Delphi na serwerze SQL Microsoft 459
W przykładzie zdefiniowano pole CustomerNumber w tabeli SALE jako klucz
obcy, odnoszący się do tej samej kolumny w tabeli CUSTOMER. Nałożone więzy
powodują, że aby numer klienta ( ang customer number) mógł być wprowadzony
do tabeli SALE, musi najpierw istnieć w tabeli CUSTOMER.Także numery
używane w tabeli SALE nie mogą zostać usunięte z tabeli CUSTOMER.
Możliwość wymuszenia zależności pomiędzy dwiema tabelami przez
zadeklarowanie ich relacji w SQL nazywana jest deklaratywną spójnością
referencyjną (ang declarative referential integrity). Ten termin znaczy po prostu,
że spójność danych w tabelach związanych relacją jest zapewniana przez
zdefiniowanie (lub zadeklarowanie) tej relacji w bazie, a nie przez kod programu.
Trzeci typ więzów pozwala ustalić zakres dozwolonych wartości dla
wprowadzanych danych np.
ALTER TABLE CUSTOMER
ADD CONSTRAINT INVALID_STATE CHECK (State in('OK','AR','MO'))
Zwracamy uwagę na celowość użycia elementów negacji w konwencji
nazewniczej dotyczącej więzów. Oprogramowanie czołowe (ang.front-end), które
zapobiega naruszeniu więzów, ma dostarczać użytkownikowi łatwą do
poprawnego zinterpretowania informację . Gdyby informacja o błędzie była
sygnowana VALID_STATE użytkownik mógłby się nie zorientować, co jest
przyczyną problemu. Dobrze dobrana nazwa ograniczenia, która służy jako
informacja zwrotna, będzie dla użytkownika wystarczającą wskazówką, w jaki
sposób próbowano naruszyć więzy . Dzięki temu nie musimy zastępować (swoimi
własnymi) komunikatów z wyjątków generowanych przez Delphi.
Testowanie funkcjonowania więzów
Każde więzy nałożone na bazę powinny zostać sprawdzone. Najlepszym testem
będzie próba ich naruszenia. Np. aby sprawdzić zdefiniowane powyżej więzy
INVALID_STATE, wprowadzamy w programie ISQL następującą instrukcję:
INSERT INTO CUSTOMER (CustomerNumber,State)
VALUES(123,'CA')
Z uwagi na fakt, iż pozwalają one tylko na wprowadzenie wartości: OK, AR
i MO, próba umieszczenia tego wiersza w bazie powinna wywołać komunikat
błędu.
Jeśli wprowadzone więzy nie funkcjonują, należy przede wszystkim sprawdzić,
czy zostały one poprawnie zapisane w bazie oraz przeanalizować ich definicje.
460 Część III
Tworzenie indeksów
Do budowania indeksów w SQL służy instrukcja CREATE INDEX. Jej
podstawowa składnia wygląda następująco:
CREATE INDEX SALE02 ON SALE (SaleDate)
SALE02 jest nazwą nowego indeksu, SALE - tabelą, dla której budujemy indeks
a SaleDate - kluczem indeksu. Należy zaznaczyć, że nazwa indeksu w SQL
Server musi być unikatowa w bazie, w której jest on umieszczony.
Instrukcja CREATE UNIQE INDEX tworzy indeks zapewniający unikatowość
klucza, np:
CREATE UNIQE INDEX SALE01 ON SALE (SaleNumber)
Wprowadzanie danych
Instrukcja INSERT służy do wprowadzania danych do tabeli SQL Server'a. Każde
wystąpienie klauzuli VALUES w instrukcji INSERT umożliwia dodanie jednego
wiersza danych. Można też wprowadzić od razu kilka wierszy wybierając je
z innej tabeli. W poniższym przykładzie dodano dane do wszystkich naszych tabel.
Najpierw trzy wiersze do tabeli CUSTOMER (posłużymy się ISQL):
INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName,
Ą' StreetAddress, City, State, Zip)
VALUES(1,'Doe','John','123 Sunnylane','Anywhere',
Ą' 'MO','73115')
INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName,
Ą' StreetAddress, City, State, Zip)
VALUES(2,'Doe','Jane','123 Sunnylane','Anywhere',
Ą' 'MO','73115')
INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName,
Ą' StreetAddress, City, State, Zip)
VALUES(3,'Philgates','Buck','57 Riverside','Reo',
Ą' 'AR','65803')
Teraz dodamy trzy wiersze do tabeli ITEM:
INSERT INTO ITEM(ItemNumber, Description, Price)
VALUES(1001,'Zoso LP',13.45)
INSERT INTO ITEM(ItemNumber, Description, Price)
VALUES(1002,'White LP',67.90)
INSERT INTO ITEM(ItemNumber, Description, Price)
VALUES(1003,'Bad Co. LP',11.45)
W końcu dodamy cztery wiersze do tabeli SALE:
Rozdział 15 Delphi na serwerze SQL Microsoft 461
INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,
Ą' ItemNumber, Amount)
VALUES(101,'10/18/90',1,1001,13.45)
INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,
Ą' ItemNumber, Amount)
VALUES(102,'02/27/92',2,100,67.90)
INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,
ItemNumber, Amount)
VALUES(103,'05/20/95',3,1003,11.45)
INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,
Ą' ItemNumber, Amount)
VALUES(104,'11/27/97',4,1004,67.90)
Warto zauważyć, że nie trzeba uwzględniać wszystkich kolumn ani ich porządku
ustalonego przy tworzeniu tabeli ale lista podanych wartości musi być zgodna pod
względem liczby elementów i ich uporządkowania z listą kolumn w instrukcji
INSERT, np.:
INSERT INTO ITEM (Price, ItemNumber)
VALUES(13.45, 1001)
Instrukcja UPDATE
Instrukcja SQL: UPDATE służy do modyfikacji danych w tabeli. Jej klauzula
WHERE umożliwia wybór modyfikowanych wierszy. Oto odpowiedni przykład:
UPDATE CUSTOMER
SET Zip='65803'
WHERE City='SpringField'
Klauzula WHERE w instrukcji UPDATE pozwala ograniczyć liczbę
modyfikowanych wierszy nawet do jednego (zależnie od danych i warunku),
natomiast pomijając ją domyślnie zmodyfikujemy wszystkie wiersze:
UPDATE CUSTOMER
SET State='MO'
Poprawiając dane w kolumnie można korzystać z wartości innych kolumn danej
tabeli (także z tej kolumny). Załóżmy, że chcemy zwiększyć cenę każdego
produktu z tabeli ITEM o siedem procent. Modyfikację tę można wykonać
następująco:
UPDATE ITEM
SET Price=Price+(Price*.07)
462 Część III
Instrukcja DELETE
Dzięki instrukcji SQL: DELETE mamy możliwość usuwania wierszy z tabeli. Np.
aby opróżnić tabelę CUSTOMER wystarczy napisać:
DELETE FROM CUSTOMER
Instrukcja DELETE może też zawierać klauzulę WHERE, ograniczającą zakres
usuwanych wierszy. Oto odpowiedni przykład:
DELETE FROM CUSTOMER
WHERE LastName<>'Doe'
Kontrola Transakcji
Grupa powiązanych zmian w bazie nazywa się formalnie transakcją. Własne
transakcje inicjujemy instrukcją BEGIN TRANSACTION. Instrukcja COMMIT
służy do zachowania zmian dokonywanych w czasie transakcji, a ROLLBACK
pozwala na ich wycofanie. Obie odnoszą się tylko do zmian dokonanych od chwili
wywołania ostatniej instrukcji COMMIT. ROLLBACK nie spowoduje wycofania
zmian przyjętych poprzedzającymi ją instrukcjami COMMIT.
UWAGA:
Oprócz zwykłych instrukcji języka DML (Data Manipulation Language) (np.
INSERT, UPDATE, DELETE) kontrola transakcji uwzględnia także instrukcje
DDL (Data Definition Language - Język definiowania danych). Jeśli wycofa się
transakcję, w której został utworzony obiekt, to zostanie on usunięty z bazy.
Transakcji można więc użyć do warunkowego utworzenia tabeli roboczej.
Pozostanie ona w bazie pod warunkiem, że wszystkie dane zostaną do niej
poprawnie wprowadzone. Jeśli bowiem instrukcje CREATE TABLE i INSERT
są umieszczone w tej samej transakcji i INSERT nie wykona się poprawnie, to
stworzona tabela zostanie automatycznie usunięta.
Instrukcja SELECT
Instrukcja SQL: SELECT, pobiera dane z określonych kolumn tabeli. Pozwoli
nam więc sprawdzić zawartość trzech naszych tabel. W tym celu wykonajmy
trzykrotnie instrukcję o składni SELECT * FROM tablename, zamieniając za
każdym razem tablename na nazwę odpowiedniej tabeli (CUSTOMER, SALE
oraz ITEM). Jeśli wcześniej dodaliśmy proponowane dane, to każda z powstałych
w ten sposób tabel roboczych powinna mieć co najmniej trzy wiersze.
Rozdział 15 Delphi na serwerze SQL Microsoft 463
SELECT * zwraca całą tabelę. Jeśli gwiazdkę zastąpimy listą nazw pól
oddzielonych przecinkami to otrzymamy dane tylko z wybranych pól np.:
SELECT CustomerNumber, LastName, State FROM CUSTOMER
Wyrażenia kolumnowe
Instrukcja SELECT w SQL Server umożliwia podanie na liście kolumn nie tylko
samych nazw, ale także wyrażeń arytmetycznych zbudowanych z wartości kolumn,
stałych i funkcji. Podajemy taki przykład zastosowania instrukcji SELECT,
zwracającej rekordy z tabeli SALE z wartością sprzedaży powiększoną o $15
(opłatę za dostarczenie towaru):
SELECT SaleNumber, SaleDate, Amount+15 AmountPlusShipping
FROM SALE
Funkcje sumaryczne
Funkcje sumaryczne - takie jak np. COUNT, SUM, AVG, MIN oraz MAX -
wykonują pewne obliczenia na zbiorach danych. Podamy teraz kilka przykładów
ich użycia:
SELECT COUNT(*) FROM CUSTOMER
To zapytanie daje w wyniku liczbę klientów w pliku.
SELECT MAX(Amount) FROM SALE
To z kolei podaje największą wartość sprzedaży (w dolarach).
SELECT SUM(Amount) FROM SALE
Natomiast wynikiem tego zapytania jest całkowita wartość sprzedaży (w dolarach).
Klauzula WHERE
Klauzula SQL WHERE umożliwia wybranie wierszy zwracanych przez instrukcję
SELECT. Oto przykład:
SELECT * FROM CUSTOMER
WHERE State='MO'
W wyniku otrzymujemy tylko tych klientów, którzy mieszkają w Missouri (symbol
'MO').
SELECT * FROM CUSTOMER
WHERE StreetAddress LIKE '%Sunny%'
Rezultatem tego zapytania będą dane klientów, u których w polu
StreetAddress występuje słowo Sunny. Należy pamiętać, że porównanie
znaków uwzględnia różnice pomiędzy wielkimi i małymi literami. Zawsze jednak
464 Część III
można zastosować funkcję UPPER do zamiany wszystkich liter (na duże)
w kolumnie i poszukiwanym wzorcu.
Oto dalsze przykłady:
SELECT * FROM SALE
WHERE Amount>500
W wyniku otrzymujemy listę wszystkich transakcji , których kwota przekroczyła
500$.
SELECT * FROM SALE
WHERE SaleDate BETWEEN '10/18/90' AND '05/20/95'
To zapytanie zwraca dane o wszystkich transakcjach dokonanych pomiędzy 18
paz
dziernika 1990 r. a 20. maja 1995 r. włącznie.
Złączenia (Joins)
Klauzula WHERE jest także wykorzystywana do łączenia tabel. Przy złączeniu
składnia klauzuli WHERE jest inna niż w podstawowej instrukcji SELECT. Określa
się dodatkowe tabele w klauzuli SELECT FROM i łączy się pola będące ze sobą
w relacji, formułując odpowiednie warunki w klauzuli WHERE. Dobrze ilustruje
to następujący przykład:
SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerNumber=SALE.CustomerNumber
Zauważmy, że do klauzuli FROM włączono tabelę SALE oraz użyto znaku
równości do złączenia tabel CUSTOMER i SALE poprzez pole
CustomerNumber. Tabela wymieniona po lewej stronie znaku równości
nazywana jest tabelą zewnętrzną, a po prawej - tabelą wewnętrzną. Ze względu na
ich pozycję w stosunku do znaku równości często używa się określenia prawa
i lewa. O złączeniu mówi się lewa do prawej lub lewostronne. Jest to najczęściej
używane złączenie w zapytaniach SQL.
Złączenia wewnętrzne (inner) oraz zewnętrzne (outer)
Złączenie lewostronne (wspomniane powyżej) formalnie nazywa się złączeniem
wewnętrznym. Złączenie wewnętrzne daje w wyniku tylko wiersze, dla których jest
spełniony warunek złączenia. Natomiast złączenia zewnętrzne uwzględnia też
wiersze, dla których warunek nie został spełniony. W złączeniu zewnętrznym, jeśli
nie znajdziemy pasujących wierszy w tabeli wewnętrznej, to kolumny z tabeli
wewnętrznej są dołączane z wartością NULL. W zależności od rodzaju złączenia
zewnętrznego - tzn. left (lewostronne) lub right (prawostronne) - wartościami
NULL są odpowiednio uzupełniane wszystkie wiersze lewej albo prawej tabeli, dla
Rozdział 15 Delphi na serwerze SQL Microsoft 465
których nie był spełniony warunek złączenia. Oto przykład złączenia
zewnętrznego, opisany w języku Transact-SQL:
SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerNumber*=SALE.CustomerNumber
Składnia złączenia w ANSI
Oprócz własnej składni SQL Server akceptuje, przy konstruowaniu złączeń, także
składnię ANSI. ANSI definiuje specjalne operatory złączeń, umieszczane
w klauzuli FROM. Np. lewostronne wewnętrzne złączenie może wyglądać
następująco:
SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER LEFT JOIN SALE
ON CUSTOMER.CustomerNumber=SALE.CustomerNumber
Składnię dla lewostronnego złączenia zewnętrznego przedstawiamy w poniższym
przykładzie:
SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER LEFT OUTER JOIN SALE
ON CUSTOMER.CustomerNumber=SALE.CustomerNumber
Dla określenia prawostronnego złączenia zewnętrznego wystarczy po prostu
zamienić LEFT na RIGHT. Zauważmy, że prawostronne i lewostronne złączenia
wewnętrzne dają ten sam wynik i dlatego nie różnią się składniowo.
Złączenia wielopoziomowe (Multi-Tier Joins)
Złączenia wielopoziomowe korzystają z więcej niż dwóch tabel. Tabela A jest
złączana z tabelą B a ta z kolei z tabelą C. Rozważmy następujące zapytanie:
SELECT C.LastName, C.FirstName, I.Description, S.Amount
FROM CUSTOMER C,
SALE S,
ITEM I
WHERE C.CustomerNumber=S.CustomerNumber
and S.ItemNumber=I.ItemNumber
W tym zapytaniu CUSTOMER i SALE są złączone przez wspólny klucz- pole
CustomerNumber a SALE i ITEM przez ItemNumber. W efekcie wszystkie
trzy tabele utworzą jeden zbiór wynikowy.
Samozłączenia (Self-Joins)
Oprócz złączenia z innymi tabelami, tabela może być złączana sama ze sobą. Ten
rodzaj złączenia nazywa się samozłączeniem. Rozważmy następujące zapytanie:
466 Część III
SELECT S.CustomerNumber, S.Amount,
(S.Amount/SUM(S2.Amount))*100 Precentage
FROM SALE S,
SALE S2
WHERE S.CustomerNumber=S2.CustomerNumber
GROUP BY S.CustomerNumber, S.Amount
To zapytanie podaje kwoty wszystkich zakupów dokonanych przez klientów, wraz
z rozbiciem procentowym w stosunku do całej kwoty zapłaconej przez danego
klienta. Budując takie zapytanie w ramach jednej instrukcji SELECT musimy
posłużyć się samozłączeniem. Najpierw tworzona jest i grupowana indywidualna
statystyka, a potem tabela SALE jest złączana ze sobą w celu uzyskania łącznej
kwoty zakupów każdego klienta. To pozwala już obliczyć odpowiednią wartość
procentową, która wystąpi w wyniku zapytania.
Złączenia z użyciem innych operatorów (Theta Joins)
W warunku złączenia oprócz najczęściej stosowanego operatora porównania " jest
równe" (=) mogą wystąpić inne - zwykle jest to operator " jest różne" (<>). Podany
przykład prezentuje takie złączenie, pokazując jednocześnie wykorzystanie
samozłączenia:
SELECT C.CustomerNumber, S.Amount, (S2.Amount) OTHERS
FROM CUSTOMER C,
SALE S,
SALE S2
WHERE C.CustomerNumber=S.CustomerNumber
AND C.CustomerNumber<>S2.CustomerNumber
GROUP BY C.CustomerNumber, S.Amount
Faktycznie to zapytanie zawiera dwa złączenia. Pierwsze - pomiędzy CUSTOMER
i SALE - aby uzyskać kwotę zakupu dla każdego klienta . Następne zaś (theta join)
- do obliczenia sumy kwot wszystkich zakupów, które nie zostały zrobione przez
klienta. Ponieważ stosuje się dwa różne typy złączenia do tej samej tabeli, więc
zapytanie korzysta z dwóch różnych aliasów dla SALE.
Pełne złączenia (Full Joins)
Pełne złączenie pozwala łączyć wiersze z dwóch tabel bez klauzuli WHERE. Jest
ono przydatne w przypadku łączenia tabel, powiązanych relacjami. Jeśli żadna
z tabel nie zawiera wszystkich interesujących nas wierszy, to łącząc je poprzez
zewnętrzne pełne złączenie otrzymamy wszystkie wiersze. Jest to po prostu
kombinacja złączenia prawo i lewostronnego. Oto przykład:
SELECT ITEM.Description, ITEM.Price SALE.Amount
FROM SALE FULL OUTER JOIN ITEM
ON ITEM.ItemNumber=SALE.ItemNumber
Rozdział 15 Delphi na serwerze SQL Microsoft 467
Iloczyn kartezjański
Iloczyn kartezjański jest wynikiem połączenia wszystkich wierszy z jednej tabeli
ze wszystkimi wierszami innej. Zwykle taki zbiór otrzymujemy przypadkowo, gdy
opuścimy lub niewłaściwie sformułujemy warunek złączenia. Oto odpowiedni
przykład:
SELECT SALE.SaleNumber, ITEM.ItemNumber
FROM SALE, ITEM
ORDER BY SaleNumber, ItemNumber
Dla tabel z dużą ilością danych obliczanie iloczynu kartezjańskiego może
zablokować serwer na tak długo, że konieczne będzie zamknięcie połączenia albo
zatrzymanie wykonania zapytania. W niektórych systemach zatrzymanie procesu
jest możliwe tylko przez zrestartowanie komputera serwera. Należy więc unikać
tworzenia iloczynu kartezjańskiego szczególnie w przypadku, gdy pracujemy
z dużymi tabelami.
Podzapytania (Subqueries)
Podzapytanie to instrukcja SELECT, umieszczona w klauzuli WHERE innej
instrukcji SELECT. Ogólnie podzapytania to zapytania zwracające dane, które nie
są końcowym wynikiem, lecz będą wykorzystywane w dalszej części instrukcji
Oto przykład:
SELECT * FROM CUSTOMER
WHERE CustomerNumber IN (SELECT CustomerNumber FROM SALE)
Pewne operatory funkcjonują tylko w podzapytaniach. Są to ANY, ALL, SOME,
EXISTS i SINGULAR. Chociaż słowo kluczowe ALL jest także używane
w instrukcji SELECT, to jako operator występuje tylko w podzapytaniach.
GROUP BY
Ponieważ SQL jest językiem zapytań zorientowanym na przetwarzanie zbiorów
(set - oriented), instrukcje grupujące dane są jego integralną częścią. Często osoby
tworzące bazy danych i pracujące z innymi systemami DBMS uważają takie
podejście za nietypowe, bowiem są przyzwyczajone do procedur przetwarzających
dane wiersz po wierszu. W wielu programach działających na komputerach PC,
aby zebrać dane sumaryczne iteruje się pętlę przez całą tabelę. Strategia SQL jest
zupełnie inna. Niekiedy pojedyncza instrukcja SQL zastępuje 10 lub nawet 50 linii
kodu programu napisanego w dBase. Jest to możliwe dzięki instrukcji SELECT,
klauzuli GROUP BY oraz funkcjom sumarycznym SQL. Klauzula GROUP BY
pozwala definiować grupy wyjściowych wierszy, do których odnoszą się funkcje
sumaryczne użyte w klauzuli SELECT. Następny przykład pokazuje zastosowanie
klauzuli GROUP BY:
468 Część III
SELECT CUSTOMER.CustomerNumber, SUM(SALE.Amount) TotalSale
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerNumber=SALE.CustomerNumber
GROUP BY CUSTOMER.CustomerNumber
Rezultatem tego zapytania jest lista wszystkich klientów, wraz z sumaryczną
wartością transakcji każdego klienta.
Oczywiście nasuwa się pytanie, które pola połączyć klauzulą GROUP BY . SQL
Server wymaga, aby klauzula GROUP BY zawierała wszystkie kolumny
wymienione na liście kolumn instrukcji SELECT, które nie są funkcjami
sumarycznymi. W przypadku korzystania z klauzuli GROUP BY na liście kolumn
instrukcji SELECT powinna znalez
ć się choć jedna funkcja sumaryczna. Jeśli ten
warunek nie jest spełniony, SQL Server uniemożliwia zastosowanie klauzuli
GROUP BY.
HAVING
Klauzula HAVING w instrukcji SELECT służy do selekcji wierszy zwracanych
przez klauzulę GROUP BY. Zależność między klauzulami GROUP BY oraz
HAVING przypomina relację pomiędzy instrukcją SELECT a klauzulą WHERE.
Klauzula HAVING działa podobnie jak klauzula WHERE, ale na zbiorze
wynikowym, a nie na wierszach w tabelach zapytania.
Ogólnie HAVING jest mniej efektywne niż WHERE, ponieważ selekcjonuje zbiór
wynikowy, po tym jak zostanie on zgrupowany , zaś WHERE czyni to najpierw. Są
jednak sytuacje, w których korzystamy z klauzuli HAVING. Przeanalizujmy
poniższy przykład:
SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName
HAVING COUNT(*)>1
HAVING wykorzystujemy przy selekcji wierszy otrzymywanych przez zapytanie
bazujące na funkcji sumarycznej. Zastosowanie WHERE jest niemożliwe, bowiem
potrzebna informacja nie istnieje aż do momentu wykonania zapytania i obliczenia
odpowiednich wartości.
ORDER BY
Klauzula ta służy do odpowiedniego posortowania zbioru wynikowego. Oto
przykład:
SELECT * FROM CUSTOMER
ORDER BY State
Rozdział 15 Delphi na serwerze SQL Microsoft 469
Bez ORDER BY nie ma żadnej gwarancji, że wiersze zostaną odpowiednio
posortowane. Wtedy nawet ta sama instrukcja SELECT, wywołana dwukrotnie,
może stworzyć zbiory wynikowe, za każdym razem uporządkowane inaczej.
Aliasy kolumn
W kilku prezentowanych wcześniej przykładach używaliśmy logicznych nazw
kolumn, w których umieszczaliśmy wyniki obliczeń funkcji sumarycznych - jak
COUNT() czy SUM(). Etykiety tego typu nazywamy aliasami kolumn. Dzięki nim
zapytanie i jego wynik są czytelniejsze. W SQL Server alias kolumny umieszcza
się bezpośrednio po prawej stronie odpowiadającej mu kolumny na liście pól
instrukcji SELECT. Na przykład w podanym poniżej zapytaniu aliasem wartości
funkcji sumarycznej COUNT() jest etykieta NumberWithName:
SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName
HAVING COUNT(*)>1
Aliasy można stosować nie tylko dla funkcji sumarycznych, lecz dla każdej
pozycji w zbiorze wynikowym, np.:
SELECT CUSTOMER.LastName LName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName
W tym zapytaniu zastąpiliśmy w zbiorze wynikowym nazwę kolumny LastName
przez jej alias LName. Aliasów nie można jednak stosować w innych częściach
zapytania, takich jak klauzula WHERE czy GROUP BY. Wtedy musimy posłużyć
się nazwą kolumny lub wartością.
Aliasy tabel
Składnia instrukcji SELECT umożliwia korzystanie ze skrótu, zamiast podawania
pełnej nazwy tabeli. Nazywamy go aliasem tabeli. Jest on definiowany w klauzuli
FROM instrukcji SELECT i umieszczany bezpośrednio po prawej stronie
właściwej nazwy, tak jak w poniższym przykładzie:
SELECT C.LastName, COUNT(*) NumberWithName
FROM CUSTOMER C
GROUP BY C.LastName
Może dziwić fakt, że alias występuje na liście pól instrukcji SELECT, czyli zanim
go zdefiniowano. Powinniśmy jednak pamiętać, że odwołanie się do obiektów
bazy danych musi nastąpić przed wykonaniem zapytania.
470 Część III
Perspektywy (Views)
Perspektywa SQL składa się z instrukcji SELECT, którą można traktować jak
tabelę i, w dalszej kolejności, zapytania z innymi instrukcjami SELECT.
W pewnych sytuacjach może być ona parametrem instrukcji INSERT, DELETE,
i UPDATE. Perspektywa nie zapamiętuje jednak żadnych danych. Jest ona tylko
pewną konstrukcją logiczną. Można o niej myśleć jako o małym programie
w języku SQL, uruchamianym przy każdym zapytaniu stworzonym na jej bazie.
Jest podobna w Oracle do procedury wyboru (omówionej w następnej sekcji "
Procedury pamiętane"). Gdy wykonywane jest zapytanie posługujące się
perspektywą, optymalizator zapytań tworzy najpierw perspektywę, łączy ją
z właściwym zapytaniem i optymalizuje obie czynności jako jedno zapytanie.
Perspektywy w SQL tworzy się instrukcją CREATE VIEW, np.:
CREATE VIEW MOCUSTOMERS AS
SELECT *
FROM CUSTOMER
WHERE State='MO'
Perspektywa może zostać wykorzystana w zapytaniu jak zwykła tabela, np.:
SELECT * FROM MOCUSTOMERS
Mimo braku klauzuli WHERE w zapytaniu, zbiór wynikowy uwzględnia
oczywiście klauzulę WHERE, podaną w definicji perspektywy.
Instrukcja SELECT tworząca perspektywę, ma prawie takie same możliwości jak
podstawowa instrukcja SELECT. Nie może tylko korzystać z klauzuli ORDER BY.
To ograniczenie odnosi się nie tylko do systemu SQL Server ale także do Sybase,
InterBase oraz Oracle.
Tworząc modyfikowalną perspektywę można ograniczyć poprawianie lub
dodawanie wierszy do wartości spełniających zadane przez nią warunki . Serwer
blokuje wtedy wszelkie zmiany wykraczające poza perspektywę. W tym celu
wystarczy - w instrukcji CREATE VIEW - dodać klauzulę WITH CHECK
OPTION, np.:
CREATE VIEW MOCUSTOMERS AS
SELECT *
FROM CUSTOMER
WHERE State='MO'
WITH CHECK OPTION
W ramach zdefiniowanej perspektywy będzie można dodawać tylko takie rekordy,
dla których State = 'MO'. Nie ma także możliwości zmiany wartości pola
State.
Rozdział 15 Delphi na serwerze SQL Microsoft 471
Procedury pamiętane (Stored Procedures)
Procedury pamiętane są kompilowanymi programami SQL, przechowywanymi
wraz z innymi obiektami baz danych. Do tworzenia procedur pamiętanych służy
instrukcja CREATE PROCEDURE. Poniżej przedstawiamy przykład takiej
procedury dla SQL Server:
CREATE PROCEDURE listcustomers
AS
BEGIN
SELECT LastName FROM CUSTOMER
END
Dla procedury z parametrami składnia instrukcji podlega drobnej modyfikacji:
CREATE PROCEDURE listcustomersbystate (@State varchar(2),
Ą' @LastNameMask varchar(30))
AS
BEGIN
SELECT LastName FROM CUSTOMER
WHERE State= @State AND LastName LIKE @LastNameMask
END
Skrypty
Instrukcje Data Definition Language (DDL), zawierające procedury pamiętane,
warto umieszczać w specjalnych plikach zwanych skryptami. Są to zwykłe pliki
tekstowe, zawierające ciągi poleceń SQL, więc można je tworzyć korzystając z
dowolnego edytora tekstów. Warto przypomnieć, że skrypty powinny zawierać
niezbędne instrukcje USE oraz polecenie GO, które kończy wykonanie pliku
wsadowego. W SQL Server można wykonać skrypt SQL. W tym celu
w programie ISQL należy kliknąć przycisk Load SQL Script. Przykład
skryptu podajemy poniżej (listing 15.1). Procedura zdefiniowana w skrypcie
działa podobnie jak polecenie DIR.
listing 15.1. Skrypt SQL zawierający procedurę pamiętaną
sp_dir
use master
go
/*
* DROP PROC dbo.sp_dir
*/
IF OBJECT_ID('dbo.sp_dir') IS NOT NULL
BEGIN
DROP PROC dbo.sp_dir
PRINT '<<< DROPPED PROC dbo.sp_dir >>>'
END
go
472 Część III
create procedure sp_dir @mask varchar(30) = '%', @obtype
Ą' varchar(2) = 'U', @orderby varchar(3)='/N' as
/*
Stored procedure to list object catalog information similar
Ą' to the DOS DIR command.
Takes three parameters:
@mask = pattern of object names to list (supports SQL
wildcards); defaults to all objects
@obtype = type of objects to list (supports SQL wildcards);
Ą' default to user tables
@orderby = column on which to sort listing. The following
Ą' parameters are supported:
/N = sort by Name
/R = sort by number of Rows
/S = sort by total object Size
/D = sort by Date created
/DS = sort by total Size of Data pages
/IS = sort by total Size of Index pages
/US = sort by total Size of Unused pages
/RL = sort by maximum Row Length
/O = sort by Owner
The default order is by Name.
Parameters can be specified positionally, like so:
sp_dir 'TRA%','U','/S'
or by name, like so:
sp_dir @mask='TRA%',@obtype='U',@orderby='/S'
All parameters are optional. If no parameters are specified,
Ą' the following
command is executed:
sp_dir '%','U','/N'
*/
CREATE TABLE #sp_dir(
id int NOT NULL,
name varchar(30) NOT NULL,
type char(2) NOT NULL,
date_created datetime NOT NULL,
row_count int NOT NULL,
row_len_in_bytes int NOT NULL,
total_size_in_KB int NOT NULL,
data_space_in_KB int NOT NULL,
index_space_in_KB int NOT NULL,
unused_space_in_KB int NOT NULL,
owner varchar(30) NOT NULL)
insert into #sp_dir
select o.id,
o.name,
Rozdział 15 Delphi na serwerze SQL Microsoft 473
o.type,
date_created= o.crdate,
row_count = isnull(rows,0),
row_len_in_bytes= isnull((select sum(length) from
Ą'syscolumns where id=o.id and o.type in ('U','S')),0),
total_size_in_KB = isnull((select sum(reserved) from
Ą'sysindexes where indid in (0, 1, 255) and id =
Ą' o.id),0)*2,
data_space_in_KB = isnull(((select sum(dpages) from
Ą' sysindexes where indid < 2 and id = o.id)+
(select isnull(sum(used), 0) from sysindexes where
Ą' indid = 255 and id = o.id)),0)*2,
index_space_in_KB = isnull(((select sum(used) from
Ą'sysindexes where indid in (0, 1, 255) and id = o.id)-
((select sum(dpages) from sysindexes where indid < 2
Ą' and id = o.id)+
(select isnull(sum(used), 0) from sysindexes where
Ą' indid = 255 and id = o.id))),0)*2,
unused_space_in_KB = isnull(((select sum(reserved) from
Ą'sysindexes where indid in (0, 1, 255) and id = o.id)-
(select sum(used) from sysindexes where indid in (0, 1,
Ą' 255) and id = o.id)),0)*2,
owner= user_name(o.uid)
from sysobjects o,
sysindexes i
where o.name like @mask and o.type like @obtype
and o.id*=i.id
and i.indid<=1
select @orderby=upper(@orderby)
if @orderby = '/N'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (name,id)
else
if @orderby = '/R'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (row_count,name)
else
if @orderby = '/S'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (total_size_in_KB,name)
else
if @orderby = '/D'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (date_created,name)
else
if @orderby = '/DS'
474 Część III
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (data_space_in_KB,name)
else
if @orderby = '/IS'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (index_space_in_KB,name)
else
if @orderby = '/US'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (unused_space_in_KB,name)
if @orderby = '/RL'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (row_len_in_bytes,name)
else
if @orderby = '/O'
alter table #sp_dir add constraint PK_dir primary key
Ą' clustered (owner,name)
alter table #sp_dir add sequencer int identity
alter table #sp_dir drop constraint PK_dir
alter table #sp_dir add primary key clustered (sequencer)
insert into #sp_dir
(id,name,type,date_created,row_count,row_len_in_bytes,total_
Ą'size_in_KB,data_space_in_KB,index_space_in_KB,unused_space_
Ą'in_KB,owner)
select power(2.0,31)-1,'TOTAL:','NA',getdate(),row_count=
Ą' sum(row_count),row_len_in_bytes=max(row_len_in_bytes),
Ą' total_size_in_KB=sum(total_size_in_KB),date_space=
Ą' sum(data_space_in_KB),
index_space_in_KB=sum(index_space_in_KB),unused_space_in_KB=
Ą' sum(unused_space_in_KB),'NA'
from #sp_dir
select name,type,date_created, row_count, row_len_in_bytes,
Ą' total_size_in_KB, data_space_in_KB, index_space_in_KB,
unused_space_in_KB, owner
from #sp_dir
drop table #sp_dir
go
IF OBJECT_ID('dbo.sp_dir') IS NOT NULL
PRINT '<<< CREATED PROC dbo.sp_dir >>>'
ELSE
PRINT '<<< FAILED CREATING PROC dbo.sp_dir >>>'
go
Instrukcja USE na początku pliku określa bazę danych , w której procedura
zostanie utworzona. Natomiast polecenie GO, kończące wykonanie programu
wsadowego, separuje oddzielne części skryptu.
Rozdział 15 Delphi na serwerze SQL Microsoft 475
UWAGA:
Pełny tekst skryptu zapisany jest na dysku CD dołączonym do książki.
Uruchamianie procedur pamiętanych
Procedury pamiętane systemu SQL Server uruchamiamy instrukcją EXECUTE.
Składnia instrukcji jest następująca:
EXECUTE procedurename parameters
Można skrócić EXECUTE do EXEC, a nawet opuścić, jeśli EXEC jest pierwszą
instrukcją w linii. Na przykład, uruchomienie procedury listcustomers
w ISQL może wyglądać następująco:
listcustomers
Dopuszczalne jest przekazanie parametrów według kolejności, w jakiej występują
w definicji procedury:
exec listcustomersbystate 'MO', '%'
lub przez nazwę parametru:
exec listcustomersbystate @LastName Mask='%', @State='MO'
Procedury zdarzeń (Triggers)
Procedury zdarzeń są to (podobnie jak procedury pamiętane) podprogramy SQL,
uruchamiane gdy dane z danej tabeli są wprowadzane, modyfikowane lub
usuwane. Można skojarzyć procedurę zdarzenia ze specyficzną operacją
dokonywaną na tabeli: wstawianiem wiersza, modyfikacją lub usuwaniem. Oto
odpowiedni przykład w Transact-SQL:
CREATE TRIGGER SALEDelete ON CUSTOMER FOR DELETE
AS
BEGIN
DELETE FROM SALE
WHERE CustomerNumber=(SELECT CustomerNumber FROM
deleted);
END
Ta procedura zdarzenia likwiduje transakcje danego klienta w tabeli SALE, gdy
jego rekord jest usuwany z tabeli CUSTOMER (usuwanie kaskadowe).Operacja
usuwania z tabeli uruchamia "kaskadowy" proces eliminacji odpowiednich danych
w innych tabelach połączonych z nią za pomocą wspólnego klucza.
Warto zwrócić uwagę na użycie logicznej tabeli deleted. Zawsze gdy
uruchamiana jest procedura zdarzenia skojarzona zDELETE, to z wierszy
476 Część III
przeznaczonych do usunięcia tworzona jest tabela logiczna o nazwie deleted.
W tej procedurze wykonywane jest najpierw podzapytanie do deleted, które
pozwala określić najpierw wartość CustomerNumber dla usuwanego rekordu.
Następnie usuwane są te wiersze z tabeli SALE, mające taką wartość
CustomerNumber .
Gdy zostaną uruchomione procedury związane z INSERT albo UPDATE, to
analogicznie serwer tworzy tabelę logiczną o nazwie inserted. Istnieje ona
tylko w pamięci serwera, ale jest dostępna dla procedury zdarzenia, jak każda
normalna tabela
Kursory (Cursors)
Koncepcja kursorów jest wynikiem zastosowania nastawionej na zbiory strategii
SQL w odniesieniu do przetwarzania wierszowego. Pozwalają one pracować
z jednym wierszem w danej chwili. Ponieważ ich tworzenie i obsługa jest w BDE
zautomatyzowana, więc w zasadzie nie ma potrzeby tworzenia własnych kursorów.
Jednak niekiedy mogą one okazać się użyteczne w procedurach pamiętanych.
Są cztery podstawowe operacje wykonywane na kursorze: deklarowanie (ang. declare) ,
otwarcie (ang. open) , pobranie (ang. fetch), zamknięcie (ang. close). Można go też
wykorzystać do zmodyfikowania lub usunięcia pojedynczego wiersza tabeli.
Deklaracja kursora składa się z instrukcji SELECT i (dla kursorów
modyfikowalnych) z listy modyfikowalnych kolumn. Oto przykład:
DECLARE CUSTOMER_SELECT CURSOR
FOR SELECT * FROM CUSTOMER
Zanim uzyskamy dostęp do wierszy przy użyciu kursora, musi on być najpierw
otwarty. Aby przygotować zapytanie zapisane w definicji kursora należy użyć
instrukcji OPEN:
OPEN CUSTOMER_SELECT
Samo OPEN nie wprowadza jednak wierszy do aplikacji klienta. Niezbędna jest
instrukcja FETCH:
FETCH CUSTOMER_SELECT
W ten sposób otrzymamy jeden wiersz ze zbioru wynikowego kursora. Każde
następne FETCH pozwala uzyskać następny wiersz w zbiorze. SQL Server
posiada tylko kursory jednokierunkowe. Aby przejść do wierszy poprzednich
należy kursor zamknąć (CLOSE) i ponownie otworzyć (OPEN).
Rozdział 15 Delphi na serwerze SQL Microsoft 477
UWAGA:
Mimo że SQL Server nie obsługuje kursorów dwukierunkowych, można ich użyć
w aplikacji stworzonej w Delphi. BDE emuluje bowiem dwukierunkowy kursor na
poziomie programowym - bez względu na to, czy serwer baz danych go obsługuje.
Dlatego można przewijać bazę danych w obie strony przy użyciu obiektów
TDataSets, takich jak TQuery i TTable.
Wiersze zwracane przez modyfikowalny kursor mogą być korygowane za pomocą
specjalnych wersji instrukcji UPDATE i DELETE, jak np.:
DECLARE CUSTOMER_UPDATE CURSOR
FOR SELECT *FROM CUSTOMER
FOR UPDATE OF LastName
UWAGA:
Należy się upewnić, czy na liście kolumn klauzuli FOR UPDATE OF
umieszczono tylko te kolumny, które mają być rzeczywiście aktualizowane.
Umieszczenie na tej liście innych pól niepotrzebnie wiąże zasoby serwera.
Aby zmodyfikować lub usunąć bieżący wiersz modyfikowalnego kursora, należy
posłużyć się wyrażeniem WHERE CURRENT OF cursorname , jak to pokazano
w poniższym przykładzie:
UPDATE CUSTOMER
SET LastName="Cane"
WHERE CURRENT OF CUSTOMER_UPDATE
lub
DELETE FROM CURSOR
WHERE CURRENT OF CUSTOMER_UPDATE
Gdy kończymy pracę z kursorem należy go zamknąć (instrukcją CLOSE).
Zamknięcie kursora zwalnia wszystkie zasoby systemowe, których używał. Oto
przykład:
CLOSE CUSTOMER_UPDATE
Zamknięcie kursora nie zwalnia zasobów serwera używanych przez ten kursor.
Zasoby te należy zwalniać przy pomocy instrukcji DEALLOCATE. Oto przykład:
DEALLOCATE CUSTOMER_UPDATE