plik

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).

Łą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 ź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

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. Ź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

ź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

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

. Wprowadź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

CustomerNumbe

r. 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

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

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
paź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

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

ITEM

WHERE C.CustomerNumber=S.CustomerNumber
and S.ItemNumber=I.ItemNumber

W tym zapytaniu

CUSTOMER

SALE

są złączone przez wspólny klucz- pole

CustomerNumber

SALE

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

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

SALE

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

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 znaleźć 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

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

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 >>>'

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

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 z

DELETE

, 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

(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ąć (

) 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