25 rozdzial 24 4LC3HTZN4LOZNAK7 Nieznany (2)

Rozdział 24

Zaawansowane programowanie
w SQL

Rozdziału niniejszego nie kontynuujemy od tego miejsca, w

którym

zakończyliśmy rozdział 5. Spora luka dzieli podstawową i zaawansowaną
znajomość języka SQL, stąd też brak ciągłości w tematyce obu rozdziałów.

W naszych rozważaniach przyjęliśmy, że połączenie z bazą danych, a także
formułowanie i wydawanie komend języka SQL nie sprawia nam większych
problemów. W

przykładach zamieszczonych w

tym rozdziale korzystamy

z platformy InterBase i dostępnego w jej ramach edytora WISQL (oczywiście
może to być dowolny, odpowiadający nam edytor SQL).

W rozdziale niniejszym stale korzystamy z bazy danych i tabel, utworzonych
w rozdziale 5.

Niektóre z występujących poniżej tabel utworzono w drugiej części książki.
Chociaż nie są one niezbędne do zrozumienia przedstawianych pojęć,
proponujemy zapoznać się z informacjami (o ich tworzeniu), zamieszczonymi
w rozdziale 8.

W rozdziale tym zestawiono dwie główne rodziny dialektów (i składni) SQL:
ANSI i Sybase. Ponieważ dialekt InterBase jest w dużym stopniu zgodny z normą
ANSI, większość przykładów - w których używa się składni InterBase - będzie
działało na innych, zgodnych z ANSI platformach (takich jak Oracle). Z drugiej
strony, produkt Sybase SQL Server odchodzi od normy ANSI pod wieloma
istotnymi względami, podobnie jak jego licencjonowany kuzyn, Microsoft SQL
Server.

UWAGA:

Chociaż w rozdziale niniejszym często zestawiamy ze sobą różne dialekty SQL,
więcej informacji o każdej z platform DBMS można znaleźć w rozdziałach od 15
do 18. Rozdziały te, z których każdy omawia jedną z platform: Oracle, InterBase,
Sybase lub Microsoft SQL Server, opisują szereg dostarczanych przez danego
producenta udogodnień oraz specyfikę składni dialektu SQL każdej z platform.

Celowo unikaliśmy powtarzania informacji, które można znaleźć w dokumentacji
posiadanej platformy SQL. Omówiliśmy szereg zagadnień, związanych z językiem

670

Część IV

SQL

−

niektóre z nich są ,,zaawansowane”, ponieważ nie uczy się ich na poziomie

podstawowym, a inne dlatego, że są naprawdę skomplikowane i trudne do
opanowania.

Wiele wysiłku poświęciliśmy „wysławianiu się” w języku SQL. Zamiast objaśniać
SQL w języku potocznym, używamy do jego opisu również języka SQL. Takie
podejście można porównać do nauczania języka francuskiego po francusku.

Języki DDL i DML

Poruszamy tu dwie szerokie kategorie zaawansowanych wyrażeń w

SQL:

komendy DDL (od Data Definition Language ) oraz komendy DML (od Data
Manipulation Language). Komend DDL używamy do tworzenia obiektów bazy
danych i zarządzania nimi. Na przykład komenda

CREATE TABLE

stanowi

wyrażenie języka DDL. Za pomocą komend DML kierujemy zapytania do tych
obiektów i modyfikujemy dane w nich zawarte. Przykładem komendy języka DML
jest wyrażenie SQL

UPDATE

. W celu podkreślenia różnicy między DDL a DML,

poniższy opis podzielono na część opisującą zaawansowaną składnię DDL i część
opisującą zaawansowaną składnię DML. Jakkolwiek SQL obejmuje, w ogólnym
sensie tego słowa, zarówno język DDL, jak i DML, często dobrze jest wiedzieć, do
której z tych dwóch klas języka SQL należy dana komenda. Komendy DDL są
przeważnie podobne do innych komend DDL, a komendy DML - do innych
komend DML.

Zaawansowane konstrukcje języka DDL

Przedstawione w tym podrozdziale fragmenty kodu nie dotyczą aktualizowania ani
modyfikowania danych, ale tworzenia i modyfikacji obiektów bazy danych - tzn.
sposobów, w jaki dane są pamiętane oraz w jaki użytkownicy i inne obiekty mogą
uzyskiwać do nich dostęp.

Bazy danych

Zgodnie z rozdziałem 5, komendy

CREATE DATABASE

używamy do tworzenia

nowych baz danych. Ponieważ obszar zajmowany przez dane zmienia się wraz
z upływem czasu, nasze bazy danych prawdopodobnie będą wymagały
rozszerzenia. Przez rozproszenie bazy danych na wielu dyskach możemy
zwiększyć jej wydajność. Do rozszerzania bazy używamy komendy

ALTER

DATABASE

- zgodnie z poniższym przykładem w dialekcie InterBase:

ALTER DATABASE
ADD ‘C:\DATA\IB\ORDENT2’

Rozdział 24 Zaawansowane programowanie w SQL

671

Dodaliśmy właśnie nowy plik do bazy danych.

Podobnej konstrukcji składniowej używamy w dialektach Sybase i Microsoft, jak
niżej:

ALTER DATABASE ORDENT
ON ORDENT2=100

W przypadku platform Sybase i Microsoft, komenda

ALTER DATABASE

rzeczywiście powoduje zwiększenie fizycznej wielkości oraz pojemności bazy
danych, która nie tylko zostaje rozproszona na dodatkowych urządzeniach
dyskowych, ale może też pomieścić więcej danych.

Segmenty i obszary tabel

Wydajność dużej bazy danych można zwiększyć przez rozproszenie jej na kilku
różnych napędach dyskowych i przypisanie wyznaczonym napędom określonych
obiektów bazy. W serwerach SQL firm Microsoft i Sybase używamy do tego celu
tzw. segmentów (segments) bazy danych. Umieszczenie tabel na jednym napędzie,
a jej indeksu na innym może przyczynić się do znacznego zwiększenia wydajności,
zwłaszcza jeśli napędy są sterowane przez oddzielne kontrolery dyskowe.

A oto, w jaki sposób możemy rozdzielić indeksy i tabele w serwerze Sybase
(począwszy od wersji System 10):

1. Utworzyć nowe dowiązanie do napędu fizycznego (w serwerze SQL określa się

je jako urządzenie

−

device) za pomocą komendy

DISK INIT

DISK

INIT

name=”INDDEV”
physname=”SYS:DATA\INDDEV.DAT”,
vdevno=25,
size=51200

2. Rozszerzyć bazę danych na powyższy napęd za pomocą komendy

ALTER

DATABASE

ALTER DATABASE ORDENT

INDDEV=100

3. Utworzyć nowy segment na nowo przydzielonym urządzeniu, za pomocą

komendy

sp_addsegment

‘indexeseg’,’ORDENT’,’INDDEV’

gdzie

indexseg

jest nazwą nowego segmentu,

ORDENT

jest nazwą bazy

danych, a

INDDEV

- nazwą nowo dodanego urządzenia.

672

Część IV

4. Utworzyć obiekt bazy danych w nowo utworzonym segmencie, np. wpisując

wyrażenie:

CREATE INDEX INVOICES02 ON INVOICES (CustomerNumber) ON

➥

indexeseg

Tabela INVOICES i

indeks

INVOICES02

znajdują się teraz w

różnych

segmentach i, mamy nadzieję, na różnych napędach, minimalizując tym samym
zakres ruchów głowicy dysku przy dostępie do tabeli za pośrednictwem indeksu

INVOICES02

W systemie Oracle to samo osiąga się przez założenie tzw. obszarów tabel
(tablespaces) na różnych napędach i utworzenie w ramach tych obszarów
obiektów bazy danych. Nowy obszar tabel tworzymy używając komendy

CREATE

TABLESPACE

, a nowe obiekty przypisujemy mu za pomocą komend

CREATE

TABLE

CREATE INDEX

, na przykład wyrażenia:

CREATE TABLESPACE indexspace
DATAFILE ‘c:\oracle\indexspace.dat’SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZX 10m.;

tworzą obszar tabel, a wyrażenie:

CREATE INDEX emp02 ON emp (ename, job) TABLESPACE indexspace;

tworzy znajdujący się w nim indeks. Ponieważ obszarom tabel przydziela się
konkretny plik, indeks zostaje w efekcie przypisany temu właśnie plikowi. Jeżeli
plik ten znajduje się na innym napędzie, niż sama tabela, oznacza to, że została ona
oddzielona od indeksu. Powinno to zlikwidować wąskie gardła spowodowane
dużym obciążeniem urządzeń wejścia/wyjścia.

Właściwa praktyka projektowania baz danych wymaga rozważenia celowości
segmentacji, zanim przystąpimy do konstruowania obiektów bazy, ponieważ
później nie będzie można ich łatwo przemieścić. A oto trzy reguły, które należy
wziąć pod uwagę przy obmyślaniu sposobu segmentacji bazy danych:

protokoły transakcji/wycofań (transaction/redo logs) przechowywać na innych

urządzeniach, niż dane. Przechowywanie protokołów na tym samym urządzeniu
fizycznym jest nie tylko niebezpieczne w przypadku jego awarii, ale znacznie
pogarsza wydajność - ze względu na ,,konkurencję” przy próbach dostępu do
protokołu z jednej, a do danych z drugiej strony.

Oddzielać tabele od ich zewnętrznych indeksów (non-clustered indexes)

wszędzie, gdzie to możliwe. Najprostszym sposobem jest umieszczenie na
jednym dysku wszystkich tabel bazy danych, a wszystkich indeksów

−

drugim. Zauważmy, że indeksu wewnętrznego (clustered index) tabel nie
możemy umieścić na innym urządzeniu, niż to, na którym znajduje się sama

Rozdział 24 Zaawansowane programowanie w SQL

673

tabela. Jeżeli spróbujemy to zrobić, to tabela ,,pójdzie śladami” swojego
indeksu wewnętrznego do segmentu, który dla niego podano (indeksy
wewnętrzne omawiamy dalej w

podrozdziale „Indeksy wewnętrzne

a zewnętrzne”).

Duże tabele umieszczać na różnych napędach, zwłaszcza jeżeli mają być one

jednocześnie używane. Jeśli baza danych ma trzy duże tabele i wiele małych,
prawidłowa konfiguracja urządzeń obejmuje przynajmniej osiem napędów: po
jednym dla każdej dużej tabeli, jeden dla małych tabel i po jednym napędzie
z indeksem dla każdego napędu z tabelą/tabelami.

Platforma InterBase nie obsługuje segmentacji jako takiej, ale i w niej bazę danych
możemy rozproszyć pomiędzy kilka napędów dyskowych. Niemożliwe jest
natomiast przypisanie konkretnym napędom konkretnych obiektów bazy danych.

Indeksy

Czytelnik na pewno już domyślił się, że indeksy tworzymy w języku SQL
używając komendy

CREATE INDEX

. Niżej podajemy podstawową składnię tej

komendy:

CREATE INDEX INVOICES02 ON INVOICES (CustomerNumber)

gdzie

INVOICES02

oznacza nazwę nowego indeksu,

INVOICES

jest nazwą

tabeli, dla której tworzymy indeks, a

CustomerNumber

oznacza klucz indeksu.

W systemie InterBase nazwy indeksów muszą być unikalne w całej bazie danych.
W systemach Sybase i Microsoft SQL Server muszą one być unikalne tylko
w ramach każdej tabeli z osobna.

Indeksy unikalne

Unikalne indeksy tworzymy używając komendy w wersji

CREATE UNIQUE

INDEX

, na przykład:

CREATE UNIQUE INDEX INVOICES01 ON INVOICES (InvoiceNo)

Indeksy w porządku malejącym

Klucze indeksu są domyślnie uporządkowane rosnąco. Indeksy uporządkowane
malejąco możemy utworzyć w

dialekcie InterBase z

użyciem komendy

DESCENDING

, na przykład:

CREATE DESCENDING INDEX INVOICES03 ON INVOICES (Amount)

Dzięki temu szybciej będą przetwarzane zapytania w rodzaju:

674

Część IV

SELECT * FROM INVOICES

ORDER BY Amount DESCENDING

Włączanie i wyłączanie indeksu

W InterBase można czasowo wyłączyć (deactivate) indeks, a potem znów go
włączyć (activate) i przebudować. W czasie, gdy indeks jest wyłączony, do tabeli
można dodać wiele wierszy bez uaktualniania go za każdym razem, gdy dodawany
jest nowy wiersz. A oto odpowiednie wyrażenie:

ALTER INDEX INVOICES03 INACTIVE

Indeks włącza się bardzo podobnie, z tym że słowo

INACTIVATE

trzeba zastąpić

przez

ACTIVATE

ALTER INDEX INVOICES03 ACTIVE

Wyłączenie i włączenie indeksu powoduje jego przebudowę. Jeśli chcemy
wyłączyć indeks, a jest on właśnie używany, to wyłączenie opóźni się do chwili
jego zwolnienia.

Generatory i sekwencje

Większość platform DBMS oferuje środki automatycznej inkrementacji kolumny.
Pracując z

relacyjnymi bazami danych często napotykamy konieczność

przetwarzania ciągów pól w kolumnie, stąd też większość producentów DBMS
realizuje automatyzację inkrementacji już na poziomie systemowym.

W platformach Sybase and Microsoft autoinkrementację kolumny uzyskuje się
poprzez atrybut

identity

. Kolumny typu identity (czyli liczniki) nie różnią się

od zwykłych, z tym wszakże wyjątkiem, że system wpisuje do nich wartości
w porządku rosnącym. Następujący przykład ilustruje definiowanie kolumn typu
identity:

CREATE TABLE CUSTOMER
(
CustomerNumber numeric(18) NOT NULL identity,
LastName char(30) NOT NULL,
FirstName char(30) NOT NULL,
StreetAddress char(30) NOT NULL,
City char(20) NOT NULL,
State char(2) NOT NULL,
Zip char(10) NOT NULL
)

Rozdział 24 Zaawansowane programowanie w SQL

675

Chociaż nie gwarantuje się, że wartości te będą następowały ściśle jedna po
drugiej, to na pewno będą w porządku rosnącym.

OSTRZEŻENIE:

Tworzenie indeksów (zwłaszcza wewnętrznych) za pomocą pól autoinkremen-
towanych może spowodować problemy ze współbieżnością, zarówno w platformie
Sybase, jak i Microsoft SQL Server. Jest tak dlatego, że w obu platformach
wykorzystuje się model blokowania stronami (firma Microsoft wprowadziła
ostatnio blokowanie wierszami, ale sprawia ono jeszcze problemy). Implementacje
pesymistyczne, oparte na blokowaniu stronami, mają swoje zalety, ale jedną z ich
głównych wad jest możliwość wzajemnego blokowania niezwiązanych ze sobą
transakcji, w których modyfikuje się wiersze znajdujące się na tej samej stronie.
Oznacza to, że np. uaktualnienie pod adresem

CustomerNumber

1000 może

zablokować uaktualnienie pod adresem

CustomerNumber

1001 - tylko dlatego,

że oba wiersze znajdują się na tej samej stronie bazy danych. Jeżeli indeks
wewnętrzny tworzy się według bardziej przypadkowego kryterium, np. pola

StreetAddress

z naszego przykładu, to prawdopodobieństwo wystąpienia

zatoru (deadlock) jest mniejsze. Problem ten nie występuje w platformach DBMS
z blokowaniem niskopoziomowym (takich jak Oracle).

Autoinkrementację kolumny w InterBase umożliwiają generatory. Żeby utworzyć
pole autoinkrementowane, trzeba najpierw utworzyć generator, tak jak
w poniższym przykładzie:

CREATE GENERATOR CustomerNumberGen

Po utworzeniu generatora możemy go wykorzystać jako procedurę zdarzenia
(trigger), wstawiającą wartości do kolumny:

SET TERM!!;
CREATE TRIGGER CUSTOMERInsert FOR CUSTOMER
BEFORE INSERT
POSITION 0
AS BEGIN

NEW.CustomerNumber = GEN_ID(CustomerNumberGEN, 1);

END
SET TERM ; !!

W systemie Oracle przyjęto podobne podejście, z tym że używa się nim sekwencji.
Tworzy się je w sposób następujący:

CREATE SEQUENCE CustomerSEQ;

676

Część IV

Podobnie jak w InterBase, utworzony obiekt sekwencji można wykorzystać przy
wpisywaniu wartości w wyrażeniach dialektu PL/SQL, używanego w systemie
Oracle.

Perspektywy

Perspektywy (views) składają się ze skompilowanych wyrażeń

SELECT

, do

których można kierować zapytania, tak jakby były tabelami. Perspektywa nie
przechowuje w

rzeczywistości żadnych danych; składa się tylko ze

skompilowanych wyrażeń języka SQL. Perspektywy podobne są do procedur
pamiętanych (stored procedures), zwłaszcza procedur wyboru (select procedures).
Kiedy do perspektywy kieruje się zapytanie, serwer łączy wyrażenie

SELECT,

użyte do utworzenia perspektywy, z wyrażeniem opisującym zapytanie i realizuje
połączone zapytanie dla tabel, których dotyczy perspektywa.

Perspektywy budujemy za pomocą komendy

CREATE VIEW

, zgodnie

z przykładowym listingiem 24.1.

Listing 24.1 Przykład zastosowania komendy CREATE VIEW

CREATE VIEW NEWLEASES AS
SELECT *
FROM LEASE
WHERE MovedInDate >”12/01/96”

Po utworzeniu perspektywy można do niej kierować zapytania, tak jakby była
tabelą, co zilustrowano za pomocą poniższego wyrażenia:

SELECT * FROM NEWLEASES

Wyrażenia

SELECT

możemy używać w perspektywach z jednym ograniczeniem -

mianowicie takim, że nie może w

nim wystąpić klauzula

ORDER BY

Ograniczenie to dotyczy większości platform DBMS typu klient/serwer,
w szczególności produktów InterBase, Oracle, Sybase oraz Microsoft SQL Server.

Dodatkowe ograniczenia dotyczą tzw. perspektyw aktualizowanych (updatable
views). W

systemie InterBase perspektywa aktualizowana musi spełniać

następujące wymagania:

Wyrażenie

SELECT

musi odnosić się do jednej tabeli lub innej perspektywy

aktualizowanej.

Jeśli ma być obsługiwana komenda

INSERT

, kolumny, które nie mają być

uwzględniane, muszą dopuszczać wartości

NULL

Nie są obsługiwane zapytania podrzędne (subqueries), predykat

DISTINCT

klauzula

HAVING

, funkcje sumaryczne (aggregate functions), tabele dołączone

Rozdział 24 Zaawansowane programowanie w SQL

677

(joined tables), funkcje zdefiniowane przez użytkownika i wyrażenia

SELECT

z procedur pamiętanych.

Żeby wiersze wstawiane lub uaktualniane poprzez perspektywę aktualizowaną na
pewno spełniły narzucane w niej kryteria selekcji, należy - w komendzie

CREATE

VIEW

- użyć klauzuli

WITH CHECK OPTION

. Klauzula ta sprawia, że

perspektywa odmówi realizacji uaktualnień lub wstawień, w których wyniku
powstałby wiersz nie spełniający kryteriów selekcji. A oto przykładowy kod:

CREATE VIEW OKPROPERTY AS
SELECT *
FROM PROPERTY
WHERE State=”OK”
WITH CHECK OPTION

W systemach Microsoft i

Sybase perspektywy aktualizowane są nieco

elastyczniejsze, gdyż można się w nich odwoływać do wielu tabel naraz. Jednak
i tu mamy wiele ograniczeń. Niżej podajemy te ograniczenia, które muszą spełniać
perspektywy w systemie SQL Server:

W wyrażeniu

SELECT

perspektywy nie dopuszcza się klauzul

ORDER BY

COMPUTE

, słowa kluczowego

INTO

oraz operatora

UNION

Perspektyw nie można tworzyć dla tabel tymczasowych (temporary tables).

Procedur zdarzeń (trigger) i

indeksów nie można tworzyć w

oparciu

o perspektywy.

W perspektywach nie można używać procedur pisania (writetext) i czytania

(readtrext) w odniesieniu do kolumn z tekstami i obrazami.

Perspektywy aktualizowane muszą spełniać dodatkowe ograniczenia:

Nie obsługuje się komendy

DELETE

w perspektywie dotyczącej wielu tabel.

Żeby perspektywa obsługiwała komendę

INSERT

, nieuwzględniane kolumny

tabeli muszą dopuszczać wartość

NULL

Komenda

INSERT

nie jest obsługiwana w perspektywach zawierających

kolumny wyliczane (calculated columns).

Komendy

INSERT

nie obsługuje się w perspektywach dotyczących wielu

tabel, które utworzono z użyciem przełączników

DISTINCT

lub

WITH

CHECK OPTION

Komendy

UPDATE

nie obsługuje się w perspektywach utworzonych z użyciem

przełącznika

DISTINCT

Podczas aktualizowania perspektywy dotyczącej wielu tabel wszystkie

uaktualniane kolumny muszą należeć do tej samej tabeli.

678

Część IV

Nie można uaktualniać kolumn wyliczanych.

Perspektywy z funkcjami sumarycznymi nie mogą być aktualizowane.

Perspektywy dynamiczne

Po wywołaniu perspektywy serwer wykonuje tworzący ją program w języku SQL.
Chociaż dane zwracane w wyniku zapytania mogą się bardzo istotnie zmieniać,
kryteria w wyrażeniu

SELECT

przetwarzanym przez serwer nie ulegają zmianie.

Perspektywa dynamiczna (dynamic view) realizuje różniące się w kolejnych
wywołaniach wyrażenia

SELECT

, w

zależności od warunków w

chwili

wywołania. Powróćmy na chwilę do perspektywy z listingu 24.1. Ograniczono
w

niej zwracane posiadłości do znajdujących się w

Oklahomie. Kryteria

przekazywane do serwera przez tę perspektywę są zawsze takie same (chociaż
w wyniku kolejnych wywołań może ona zwracać różne wiersze)

−

jest to bowiem

perspektywa statyczna (static view). Kryteria przekazywane do serwera przez
perspektywę dynamiczną mogą się zmieniać w zależności od zewnętrznych
warunków istniejących w chwili jej wywołania. Ilustruje to poniższy przykład
napisany w dialekcie Transact-SQL platform Sybase/Microsoft:

CREATE VIEW CONTACTLISTV
SELECT * FROM CONTACTLIST
WHERE EnteredBy=suser_name()

Kolumna EnteredBy tabeli CONTACTLIST zawiera nazwy użytkowników
wprowadzających nowy rekord. Za każdym razem, gdy dodaje się nowy rekord,
pole to jest automatycznie wypełniane dzięki wykorzystaniu parametru

DEFAULT

Za każdym razem, gdy użytkownik aplikacji dodaje rekord, wartość

suser_name()

zostaje zapamiętana w odpowiednim polu rekordu. Dzięki temu

można ograniczyć widzianą przez użytkownika perspektywę całej tabeli
CONTACTLIST do rekordów, które wprowadził. Ponieważ kryteria
przeszukiwania dla serwera zmieniają się w zależności od tego, kto jest aktualnym
użytkownikiem aplikacji, jest to zachowanie dynamiczne.

Perspektywy a prawa dostępu

Użytkownik nie musi mieć praw do tabel uwzględnianych w perspektywie, żeby
z niej skorzystać. Perspektyw bowiem i ich tabel dotyczą oddzielne zezwolenia.
Wyjątkiem od tej reguły jest konieczność posiadania odpowiednich praw dostępu
do tabel uwzględnianych w perspektywach przez użytkowników, którzy tworzą
perspektywy.

Jeśli użytkownik ma prawo dostępu, umożliwiające mu wykonanie jakiejś operacji
na perspektywie, uzyskuje on poprzez nią dostęp do jej tabel - nawet jeśli nie ma
on identycznych praw do tych tabel !. Znamienne, że nawet sama firma Sybase

Rozdział 24 Zaawansowane programowanie w SQL

679

zaleca taki sposób używania perspektyw, jako zapewniający dobrą ochronę
danych.

Z kilku powodów nie jest to jednak dobrą praktyką. Umieszczając
w perspektywach część zezwoleń, uniemożliwiamy zarządzanie naszym systemem
ochrony z punktu centralnego

−

nie widzimy wówczas z jednego miejsca

wszystkich praw dostępu. Całościowy obraz praw dostępu w systemie uzyskamy
dopiero po sprawdzeniu uprawnień za pomocą komend

GRANT

REVOKE

oraz

przejrzeniu naszych tabel systemowych. Co więcej, narzędzia do administrowania
przeważnie nie pozwalają stwierdzić, że dany użytkownik nie ma uprawnień do
tabeli, do której uzyskał dostęp poprzez perspektywę. W związku z tym zaleca się
ograniczać uprawnienia użytkownika, nadając mu zezwolenia do tabel, a nie
perspektyw, wszędzie gdzie to możliwe.

WSKAZÓWKA:

Perspektywy umożliwiają wygodne przygotowanie danych do eksportu. Większość
platform DBMS typu klient/serwer obsługuje zbiorczy import/eksport danych.
W systemie Oracle służą do tego celu programy EXPORT i IMPORT, podczas gdy
w systemach Sybase i Microsoft SQL Server

−

program BCP (Bulk Copy

Program). Jeżeli z platformy SQL Server eksportujemy w formacie ASCII dane
z datami (datetime fields), możemy natknąć się na problem polegający na omijaniu
przez program BCP części pól daty w eksportowanych tabelach. Rozwiązanie
polega na utworzeniu perspektyw dla tych tabel i takim przeformatowaniu pól
daty, żeby zawierały one kompletną informacje o dacie/godzinie. Perspektywy te
można następnie wyeksportować, dzięki czemu żadne informacje nie zostaną
utracone przez program BCP w trakcie przekształcania na format ASCII danych
z serwera.

Procedury pamiętane

Procedury pamiętane (stored procedures) są to skompilowane programy w języku
SQL. Przeważnie składają się z wielu wyrażeń tego języka, a przechowuje się je
w bazie danych razem z innymi jej obiektami. Wyróżniamy dwa typy procedur
pamiętanych: procedury wyboru (select procedures) i procedury wykonywalne
(executable procedures). Procedura wyboru może wystąpić zamiast tabeli
w wyrażeniu

SELECT

. Procedury wykonywalne natomiast można uruchamiać;

mogą one (ale nie muszą) zwracać dane.

Procedury pamiętane tworzymy za pomocą komendy

CREATE PROCEDURE

Poniżej przykład w dialekcie InterBase:

CREATE PROCEDURE CLEARCALLS
AS

680

Część IV

BEGIN

DELETE FROM CALLS WHERE CallDateTime <’01/01/96’;

END

A oto ta sama procedura w dialekcie Transact-SQL (SQL Server):

CREATE PROCEDURE CLEARCALLS
AS
BEGIN

DELETE FROM CALLS WHERE CallDateTime <’01/01/96’

Jeżeli do procedury przekazuje się parametry, to jej konstrukcja jest trochę inna.
W InterBase definiujemy ją następująco:

CREATE PROCEDURE CLEARCALLS (BeginningDate DATE)
AS
BEGIN

DELETE FROM CALLS WHERE CallDateTime <:BeginningDate;

END

... natomiast w SQL Server:

CREATE PROCEDURE CLEARCALLS (@BeginningDate datetime)
AS

DELETE FROM CALLS WHERE CallDateTime < @BeginningDate

Procedury wyboru

W procedurach wyboru dane, które mają zostać zwrócone do wywołującego,
definiuje się z użyciem słowa kluczowego

RETURNS

, np:

CREATE PROCEDURE LISTPROP (State char(2))
RETURNS (PROPERTYNO INTEGER,
ADDRESS VARCHAR(30))
AS
BEGIN

FOR SELECT PropertyNo, Address

FROM

PROPERTY

WHERE State = :State

INTO :PropertyNo, :Address

DO
SUSPEND;
END

Konstrukcja

FOR SELECT...DO

służy do zwracania wyników działania

procedury, a

SUSPEND

−

do wstrzymywania działania procedury, do chwili

zażądania następnego wiersza przez wywołującego. Parametry wyjściowe zwraca
się przed wstrzymaniem działania.

Rozdział 24 Zaawansowane programowanie w SQL

681

Dobrą praktyką jest przechowywanie kodu źródłowego procedur pamiętanych
w plikach ze skryptami SQL. Skrypt można utworzyć za pomocą praktycznie
dowolnego edytora tekstów. Nie zapomnijmy wpisać do niego wszystkich
koniecznych wyrażeń

CONNECT

SET TERM

−

potem będzie je można wykonać,

używając opcji

Run an SQL script

edytora WISQL. Przykład skryptu

przedstawia listing 24.2.

Listing 24.2 Skrypt SQL z

procedurą pamiętaną

CONNECT „C:\DATA\ RENTMAN\RENTMAN.GDB” ESER SYSDBA PASSWORD

➥

masterkey;

SET TERM^;
/* Stored procedures*/
CREATE PROCEDURE LISTPROP
RETURNS (PROPERTYNO INTEGER, ADDRESS VARCHAR (30))
AS
BEGIN

FOR SELECT PropertyNo, Address

FROM

PROPERTY

INTO :PropertyNo, :Address

SUSPEND;

END
^
SET TERM ;^

Wyrażenie

CONNECT

u góry skryptu nawiązuje połączenie z bazą danych.

Komenda

SET TERM

czasowo zmienia separator wyrażeń SQL - z domyślnego

znaku średnika (;) na znak ^. Dzięki temu komendy zawarte w definicji procedury
pamiętanej nie wykonują się w trakcie działania komendy

CREATE PROCEDURE

Po realizacji tej ostatniej komendy,

SET

TERM

zostanie znowu wywołana w celu

przywrócenia domyślnego separatora komend SQL.

Procedury wykonywalne

Procedury wykonywalne różnią się trochę od procedur wyboru, gdyż wyrażenie

RETURNS

nie jest w nich obligatoryjne. A oto przykład procedury wyboru

w dialekcie platformy InterBase:

CREATE PROCEDURE insertWORKTYPE (WorkTypeCode smallint,

➥

Description char (30), TaskDuration float)

AS
BEGIN

INSERT INTO WORKTYPE VALUES (:WorkTypeCode,

:Description, :TaskDuration);
END

Ta sama procedura w dialekcie Transact-SQL wygląda następująco:

682

Część IV

CREATE PROCEDURE insertWORKTYPE (@WorkTypeCode smallint,

➥

@Description char (30), @TaskDuration float)

INSERT INTO WORKTYPE VALUES (@WorkTypeCode,

➥

@Description, @TaskDuration)

Funkcje pamiętane

Zarówno SQL Server, jak i

Oracle udostępniają możliwość tworzenia

podprogramów, które zwracają wartości i

które są podobne do funkcji

w tradycyjnych językach programowania. W języku PL/SQL systemu Oracle
procedury takie nazywamy właśnie funkcjami. Niżej podajemy przykładową
definicję funkcji:

CREATE FUNCTION get_bal(CustomerNo IN NUMBER)
RETURN NUMBER
IS
custbal

NUMBER(11,2);

BEGIN
SELECT

balance

INTO custbal
FROM CUSTOMER
WHERE CustomerNo = get_bal.CustomerNo;
RETURN(custbal);
END

SQL Server jest trochę bardziej rygorystyczny pod względem formalnym, ale też
i składnia jego funkcji bardziej przypomina tradycyjne języki programowania.
Poniżej przedstawiono przykładowy kod w systemie Sybase:

CREATE PROCEDURE get_bal @CustomerNo int, @Balance money=NULL
OUTPUT
AS

SELECT @Balance = Balance

FROM

CUSTOMER

WHERE CustomerNo = @CustomerNo

RETURN 100 * @Balance – Convert to integer

Powyższa procedura funkcyjna zwraca bilans klienta zarówno za pomocą
wyrażenia

RETURN

, jak i poprzez zmienną wyjściową

@Balance

. Wartości

zwracane poprzez

RETURN

muszą być typu całkowitego (integer), stąd też

zmienną

Balance

mnoży się przez 100 - aby zapewnić, że część ułamkowa

bilansu nie zostanie obcięta.

Podprogram taki wywołuje się na kilka sposobów. Pierwszy z nich jest bardzo
podobny do tradycyjnego wywołania funkcji:

declare @mybal money
select @mybal = null

Rozdział 24 Zaawansowane programowanie w SQL

683

exec @mybal = get_bal @CustomerNo=3 - Supply a customer
number, „3” in this case
select @mybal / 100

Zauważmy, że zmienną, która przechowuje zwróconą wartość, dzieli się przez 100.

Drugi sposób wywoływania procedur funkcyjnych w systemie SQL Server
przypomina wywołanie zwykłej procedury pamiętanej, na przykład:

declare @mybal money
select @mybal = null
exec get_bal @CustomerNo=3, @mybal OUTPUT
select @mybal

Zwróćmy uwagę na słowo kluczowe

OUTPUT

, które wskazuje, że do jego

parametru kieruje się wartość zwracaną przez procedurę. Zauważmy również, że
dzielenie przez 100 nie jest tu konieczne, gdyż SQL Server sam zamienia wartość
całkowitą na daną, reprezentującą walutę.

Pakiety

Platforma Oracle umożliwia grupowanie w bazie danych procedur pamiętanych,
funkcji i innych, powiązanych obiektów pod postacią tzw. pakietów (packages).
Dzięki pakietom można dodawać, usuwać i modyfikować wiele procedur za
jednym razem, co bardzo upraszcza aktualizacje całych ich grup. Poniżej składnia
definicji pakietu w systemie Oracle:

CREATE OR REPLACE PACKAGE CustomerPKG AS

FUNCTION addcust(CustomerName VARCHAR2, Address

VARCHAR2,

City VARCHAR2, State CHAR(2), Zip CHAR(10))

RETURN

NUMBER;

PROCEDURE deletecust(CustomerNumber NUMBER);

Invalid_State EXEPCION;
Invalid_City EXEPCION;
END CustomerPKG

Podobnie jak w Delphi, gdzie sekcję Interface modułu oddziela się od sekcji
Implementation, nagłówek pakietu (czyli jego interfejs) definiuje się oddzielnie od
jego części implementacyjnej (body). Wyrażenie

CREATE PACKAGE

opisuje

publiczny interfejs pakietu. Interfejs publiczny jest widziany przez podprogramy
zewnętrzne i udostępnia im wewnętrzne obiekty pakietu. Z kolei wyrażenia

PACKAGE BODY

używamy przy specyfikacji części implementacyjnej pakietu,

tak jak w poniższym przykładzie:

CREATE OR REPLACE PACKAGE BODY CustomerPKG AS
tot_custs NUMBER;

684

Część IV

FUNCTION addcust(CustomerName VARCCHAR2, Address

VARCHAR2,

City VARCHAR2, State CHAR(2), Zip CHAR(10))

RETURN NUMBER is NewCustomerNo NUMBER(4);

BEGIN

IF State IS NULL THEN RAISE Invalid_State;

IF City is NULL THEN RAISE Invalid_City;

SELECT

CustomerSEQ.NEXTVAL

INTO

NewCustomerNo

FROM

DUAL;

ISERT INTO CUSTOMER (CustomerNo, CustomerName, Address,

➥

City, State, Zip)

VALUES (NewCustomerNo, CustomerName, Address, City,

➥

State, Zip);

tot_custs := tot_custs +1;

RETURN(NewCustomerNo);
END;
PROCEDURE deletecust(CustomerNumber NUMBER) IS
BEGIN

DELETE FROM CUSTOMER

WHERE CUSTOMER.CustomerNumber = deletecust.

➥

CustomerNumber;

tot_custs := tot_custs - 1;

END;
END CustomerPKG

Przewagą pakietów nad tradycyjnymi skryptami (obsługiwanymi przez pozostałe
platformy) jest możliwość ich ciągłego rozwijania, nawet jeśli korzystają z nich
inni programiści. Przy założeniu, że nie zmieniamy w sposób istotny interfejsu
naszego pakietu, inni mogą wywoływać w

swoich programach procedury

publiczne, podczas gdy my możemy modyfikować część implementacyjną. Tak
więc nawet przy częstych zmianach części implementacyjnej nagłówek
praktycznie pozostaje taki sam, co bardzo ułatwia projektowanie dużych aplikacji
w języku SQL.

Wyjątki

Wyjątki są obsługiwane w platformach InterBase, Oracle i Sybase. W InterBase
nowy wyjątek definiuje się za pomocą komendy

CREATE EXCEPTION

, a zgłasza

(throw, raise) go

−

za pomocą komendy

EXCEPTION

. W systemie Oracle wyjątek

użytkownika definiuje się z użyciem konstrukcji

DECLARE ... EXCEPTION

a do zgłoszenia go używa komendy

RAISE

. Sybase nie oferuje możliwości

definiowania wyjątków użytkownika, ale komenda

RAISERROR

umożliwia

skorzystanie z wyjątków systemowych. Można tu również tworzyć komunikaty do
późniejszego wykorzystania z komendą

RAISERROR

i skojarzyć je ze zdarzeniami

Rozdział 24 Zaawansowane programowanie w SQL

685

związanymi z błędami systemowymi. Poniżej przedstawiliśmy wyjątek w systemie
InterBase, wraz z procedurą, która go zgłasza.

/* The exception*/
CREATE EXCEPTION CREDIT_TOO_HIGH
„The fequested credit is too high. All Casino advances must
be less than $5000”;
/*The SQL script for a procedure that uses the exception*/
CONNECT „C:\DATA\IB\CASINO”;
SET TERM^;
CREATE PROCEDURE insertCREDIT (VoucherNumber smallint,

➥

VoucherDate date,

CustomerNumber int, Amount float)
AS
BEGIN

IF (:Amount>5000) THEN

EXCEPTION

CREDIT_TOO_HIGH;

ELSE
INSERT

INTO

CREDIT

VALUES (:VoucherNumber, :VoucherDate,

➥

:CustomerNumber, :Amount);

END^
SET TERM ;^
EXIT;

Przedstawiona wyżej procedura ogranicza realizacje kredytu w kasynie do 5000
dolarów. Za pomocą konstrukcji

IF...THEN

sprawdzamy, czy wartość pola

Amount

w wierszu, który właśnie ma zostać wstawiony, jest większa niż 5000.

Jeśli tak, to komenda

EXCEPTION

zgłasza wyjątek

CREDIT_TOO_HIGH

(kredyt za wysoki)

Teraz z kolei analogiczny przykład w dialekcie systemu SQL Server (zgodnie
z wcześniejszymi zaleceniami napisano go jako oddzielny skrypt):

sp_addmessage 20001,
„The requested credit is too high. All Casino advances must
be less than $5000.”
GO
use CASINO
GO
CREATE PROCEDURE insertCREDIT (@VoucherNumber smallint,

➥

@VoucherDate date, @CustomerNumber int, @Amount float)

IF @Amount > 5000

RAISERROR

20001

ELSE
INSERT

INTO

CREDIT

VALUES (@VoucherNumber, @VoucherDate, CustomerNumber,

➥

@Amount)

686

Część IV

SQL Server pozwala na umieszczanie parametrów w tekstach komunikatów (por.
kody konwersji i funkcje

printf

w języku C oraz

format

w Delphi). Tak więc

komunikat i procedurę można skonstruować w sposób następujący:

sp_addmessage 50010,
„The requested credit of %1 is too high. All Casino advances
must be less than $5000.”
GO
use ORDENT
GO
CREATE PROCEDURE insertCREDIT (@VoucherNumber smallint,

➥

@VoucherDate date, @CustomerNumber int, @Amount float)

IF @Amount > 5000

RAISERROR

50010,

@Amount

ELSE
INSERT

INTO

CREDIT

VALUES (@VoucherNumber, @VoucherDate, CustomerNumber,

➥

@Amount)

Zwróćmy uwagę na parametr formalny

. W Sybase dopuszcza się maksymalnie

20 ponumerowanych parametrów komunikatu. Przyjrzyjmy się również
zmodyfikowanemu wywołaniu komendy

RAISERROR

. Zmienna

@Amount

jest

tutaj jej drugim parametrem. Pomimo, iż łańcuch opcjonalny komunikatu
występuje zwykle jako jej drugi parametr, wykrywa ona, że przekazywany
komunikat zdefiniowany jest przez użytkownika, w związku z czym używa
pozostałych argumentów jako parametrów komunikatu.

Sybase daje również możliwość powiązania (bind) komunikatu zdefiniowanego
przez użytkownika ze zdarzeniem systemowym, takim jak naruszenie więzów,
czyli ograniczeń (constraint violation), zgodnie z poniższym przykładem:

ALTER TABLE INVOICES
ADD CONSTRAINT INVALID_CUSTOMER
FOREIGN KEY (CustomerNumber)
REFERENCES CUSTOMER(CustomerNumber)
GO
exec sp_addmessage 50010, „The customer number you’ve

➥

specified is not on file. Please specify a valid customer

➥

number.”

GO
exec sp_bindmsg INVALID_CUSTOMER,50010
GO

Zauważmy sposób użycia nazwy ograniczeń w wywołaniu

sp_bindmsg

. Dzięki

temu ustanowiony zostaje związek między więzami, utworzonymi z użyciem

Rozdział 24 Zaawansowane programowanie w SQL

687

komendy

ALTER TABLE

komunikatem, utworzonym przez wywołanie

sp_addmessage

Procedury zdarzeń

Procedura zdarzenia (trigger) to program wywoływany wówczas, gdy zajdzie
określone zdarzenie, związane z tabelami. Jest ona bardzo podobna do procedury
pamiętanej

−

do tego stopnia, że specjalne rozszerzenia języka SQL w InterBase

mają zastosowanie zarówno do procedur pamiętanych, jak i procedur zdarzeń.
Procedury zdarzeń wiążą się z takimi zdarzeniami jak wstawianie, aktualizacja
i usuwanie. A oto przykład takiej procedury w systemie InterBase:

CREATE TRIGGER DELETE_DETAIL FOR WORDERS
ACTIVE BEFORE DELETE
AS BEGIN DELETE FROM WODETAIL WHERE
WODETAIL.Work_Order_Number=OLD.Work_Order_Number;
END

Usuwa on rekordy związane z zamówieniem (work order) za każdym razem, gdy
zostaje usunięty ich rekord główny (master record). Określa się to nazwą
usuwania kaskadowego (cascading delete)

−

usunięcie z jednej tabeli uruchamia

kaskadę usunięć z innych, z użyciem wspólnego klucza.

Zwróćmy uwagę na zmienną kontekstową

OLD

−

używamy jej do wyłuskania

wartości pola przed wykonaniem operacji

UPDATE

(uaktualnienie) lub

DELETE

(usunięcie). Z kolei zmienna kontekstowa

NEW

wskazuje na nową wartości

kolumny w operacji

INSERT

(wstawienie) lub

UPDATE

Ważne jest słowo kluczowe

BEFORE

(zanim, przed); procedury zdarzeń mogą być

wyzwalane przed (before) operacjami

INSERT

UPDATE

DELETE

lub po

(after) nich.

W InterBase z danym zdarzeniem można powiązać maksymalnie 32 768 procedur
zdarzeń. Słowo kluczowe

POSITION

określa kolejność procedury. Kolejność ta

jest nieokreślona dla procedur zdarzeń oznaczonych tą samą wartością

POSITION

. Przykład ze słowem kluczowym

POSITION

zamieszczono poniżej:

CREATE TRIGGER GENERATE_WORKTYPECODE FOR WORTYPE
ACTIVE BEFORE INSERT
POSITION 0
AS BEGIN
NEW.Work_Type_Code=GEN_ID(Work_Type_CodeGEN, 1);
END

Nieco inna jest składnia definicji procedur zdarzeń w

systemach Sybase

i Microsoft SQL Server:

CREATE TRIGGER INVOICESDelete ON CUSTOMER

688

Część IV

FOR DELETE AS
DELETE FROM INVOICES WHERE INVOICES.CustomerNumber=(SELECT

➥

CustomerNumber FROM deleted)

Tabela logiczna

deleted

służy do identyfikacji wiersza, który ma zostać

usunięty z tabeli procedur zdarzenia. W systemie SQL Server definiuje się również
tabele uaktualnień, które służą do analogicznego celu przy aktualizacji
i wstawianiu.

SQL Server udostępnia pożyteczny mechanizm ustalania, które pola uaktualnia się
w zapytaniach aktualizacji (update queries). Korzystamy przy tym z konstrukcji

IF UPDATE(columnname)

, wstawiając w miejsce

columnname

nazwę

kolumny w

tabeli. Można więc ograniczyć działania procedury zdarzenia

w zależności od tego, które kolumny zostały zmodyfikowane.

Są dwa podstawowe rodzaje procedur zdarzeń: jednokrotne (statement trigger)
i wielokrotne (row trigger). Procedury zdarzeń jednokrotne wykonują się tylko
jeden raz dla danego wyrażenia DML - niezależnie od liczby wierszy, których
wyrażenie to dotyczy. Ten rodzaj procedur obsługiwany jest przez SQL Server.
Z drugiej strony Oracle umożliwia określenie, czy procedura jest jednokrotna, czy
też wielokrotna; służy do tego opcja

FOR EACH ROW

wyrażenia

CREATE

TRIGGER

. Aby ustrzec się błędów podczas projektowania, musimy upewnić się,

że dobrze rozumiemy obsługiwany przez naszą platformę typ (lub typy) procedur
zdarzeń.

Tabele

Aby dodać lub usunąć kolumny do tabel, korzystamy z komendy SQL

ALTER

TABLE

. Niektóre platformy, takie jak Microsoft SQL Server, nie umożliwiają

usuwania kolumn po fakcie; jest ono jednak obsługiwane w InterBase oraz Sybase.

A oto jak dodaje się kolumnę do tabeli:

ALTER TABLE CUSTOMER
ADD PhoneNumber char(10)

a usuwa się ją następująco:

ALTER TABLE CUSTOMER
DROP PhoneNumber

Do tabeli, która zawiera już wiersze, nie możemy dodać kolumny o atrybucie NOT
NULL, ponieważ wpisuje się do niej wartości NULL zaraz po jej dodaniu do
tabeli.

Rozdział 24 Zaawansowane programowanie w SQL

689

UWAGA:

platformie Sybase SQL Server istnieje nieudokumentowana możliwość

usunięcia (drop) kolumny. Z możliwości tej należy korzystać bardzo ostrożnie,
pamiętając, że firma Sybase może ją w

każdej chwili wycofać, jako

nieudokumentowaną oficjalnie w języku Transact-SQL.

Zaawansowane konstrukcje języka DML

Niniejszy podrozdział przedstawia zaawansowane konstrukcje języka
manipulowania danymi w SQL. Opisujemy tu składnię wyrażeń udostępnianych
przez różnych producentów na potrzeby dopasowania zapytań SQL do wymagań
użytkownika, jak również wiele innych zagadnień związanych z językiem DML.

Inaczej niż w przypadku języka DDL, budując wyrażenia DML musimy brać pod
uwagę kwestie wydajności. Nieprawidłowo skonstruowane wyrażenie DML może
w efekcie wygenerować zapytanie o rząd wielkości, wolniejsze od prawidłowego.

Komenda SELECT

Podstawowym narzędziem pracy w języku SQL jest komenda

SELECT

(wybierz).

Za pomocą komend

SELECT

możemy dołączać (join) tabele, zwracać (return)

dane, a nawet określać kryteria selekcji dla innych komend

SELECT

. Poniżej

wymieniliśmy niektóre z licznych zastosowań tej komendy.

Złączenia wielopoziomowe (Multi-Tier Joins)

Są to złączenia działające na więcej niż dwóch tabelach. Np. tabelę Table1 dołącza
się do Table2, a tabelę Table2

−

do tabeli Table3. Złączenie wielopoziomowe

ilustruje następujący przykład:

SELECT w.Work_Order_Number, t.Name
FROM WORDER w, LEASE 1, TENANT t
WHERE w.Property_Number=1.Property_Number
and 1.Tenant_Number=t.Tenant_Number

W zapytaniu tym tabele WORDER i

LEASE łączy się według kolumn

Property_Number

, a

tabele LEASE i

TENANT - według kolumn

Tenant_Number

. W efekcie wszystkie tabele zostają połączone i tworzą jeden

zbiór wynikowy.

690

Część IV

Złączenia zwrotne (Self-Joins)

Tabelę można również dołączyć do samej siebie. Taki typ złączenia nazywamy
złączeniem zwrotnym (self-join lub reflexive join). Oto przykładowe zapytanie ze
złączeniem zwrotnym:

SELECT I.CustomerNumber, I.Amount, (I.Amount /
SUM(i2.Amount))*100 Percentage
FROM INVOICES I
INVOICES

WHERE I.CustomerNumber=I2.CustomerNumber
GROUP BY I.CustomerNumber, I.Amount

Zapytanie to wyświetla faktury każdego klienta oraz udział procentowy,
reprezentowany przez daną fakturę względem wszystkich jego faktur. Złączenie
zwrotne to jedyny sposób wygenerowania tego rodzaju informacji w jednym
przebiegu. Dane każdego klienta są zbierane i

grupowane, zgodnie

z oczekiwaniami, po czym tabela INVOICES zostaje zwrotnie dołączona do samej
siebie, aby umożliwić uzyskanie sumy dla faktur każdego klienta. Wartość dla
każdej faktury zostaje następnie podzielona przez tę sumę i przekształcona na
wartość procentową do umieszczenia w zbiorze wynikowym.

Złączenia z użyciem innych operatorów (theta joins)

Złączenie theta (theta joins) łączy dwie tabele przy użyciu operatorów nierówności
(takich jak operator

). Oto przykład:

SELECT C.CustomerNumber, O.Amount, Sum(02.Amount)OTHERS
FROM CUSTOMER C,
INVOICES 0,
INVOICES 02
WHERE C.CustomerNumber=0.CustomerNumber
AND C.CustomerNumber<>02.CustomerNumber
GROUP BY C.CustomerNumber, 0.Amount

Zapytanie powyższe składa się z

dwóch złączeń. Pierwsze łączy tabele

CUSTOMER i INVOICES i zwraca faktury każdego klienta. Drugie

−

to złączenie

theta, które zwraca sumę dla wszystkich faktur nie należących do danego klienta.
Ponieważ występuje tu łączenie z użyciem dwóch różnych typów złączeń,
musieliśmy skorzystać z dwóch różnych nazw zastępczych (aliasów, aliases) tabeli
INVOICES.

Złączenia kartezjańskie - iloczyn kartezjański

Złączenia kartezjańskiego (Cartesian join) używamy do budowania zbiorów
wynikowych, które zawierają wszystkie wiersze z jednej tabeli połączone ze
wszystkimi wierszami innej. Poniżej przykład takiego złączenia:

Rozdział 24 Zaawansowane programowanie w SQL

691

SELECT t.Name, w.Description
FROM TENANT t, WORKTYPE w

Iloczyn kartezjański jest przeważnie efektem błędu w klauzuli złączenia. Czasem
jednak może on okazać się pożyteczny przy uzyskiwaniu złożonego obrazu danych
dwóch różnych tabel. Np. poprzednie zapytanie mogło by służyć do
wygenerowania tabeli roboczej, zawierającej prace już wykonane dla każdego
najemcy (tenant).

Konstrukcja SELECT INTO

Sybase i Microsoft SQL Server udostępniają odmianę komendy

SELECT

, która

potrafi skierować swój zbiór wynikowy do innej tabeli. Dzięki temu w jednym
kroku możemy utworzyć nową tabelę i wypełnić ją danymi. A oto odpowiednia
konstrukcja składniowa:

SELECT w.Work_Order_Number, t.Name
INTO #MYWORKTABLE
FROM WORDER w, LEASE 1, TENANT t
WHERE w.Property_Number=1.Property_Number
and 1.Tenant_Number=t.Tenant_Number

Zauważmy, że klauzula

INTO

występuje zaraz za listą kryteriów wyboru. Właśnie

ona kieruje dane wyjściowe komendy

SELECT

do drugiej tabeli. W tym

przypadku będzie to tabela tymczasowa (na co wskazuje symbol

); zostanie ona

usunięta (drop) po zamknięciu połączenia z serwerem. Nie mylmy jednak klauzuli

INTO

w systemie SQL Server z tak samo nazywającą się klauzulą w systemach

Oracle i InterBase! Służy ona do nadawania zmiennym wartości i nie pozwala na
tworzenie tabel.

UWAGA:

Równoważna konstrukcja w systemie Oracle jest następująca:

CREATE TABLE MYWORKTABLE
(Work_Order_Number,
Name)
AS
SELECT w.Work_Oredr_Number, t.Name
FROM WORDER w, LEASE 1, TENANT t
WHERE w.Property_Number=1.Property_Number
and 1.Tenant_Number=t.Tenant_Number

Taka specjalna postać komendy

CREATE TABLE

umożliwia wypełnienie tabel

danymi po jej utworzeniu. Zauważmy, że nie można określać typów danych
i rozmiarów kolumn dla tak tworzonych tabel. Oracle otrzymuje te informacje
wprost z zapytania podrzędnego.

692

Część IV

Komenda INSERT

Poza wstawianiem wartości statycznych do tabeli, komenda

INSERT

może

również wstawić wiele wierszy poprzez zapytania podrzędne. Oto przykład:

INSERT INTO WORDERBACKUP
SELECT * FROM WORDER

Podobnie jak w przypadku zwykłej postaci tej komendy, tabela docelowa musi już
istnieć. Kolumny podane w zapytaniu podrzędnym muszą odpowiadać kolumnom
podanym w samej komendzie

INSERT

UWAGA:

W systemie Microsoft SQL Server istotnie rozszerzono możliwości komendy

INSERT

−€

można bowiem w jej ramach wykonać procedurę pamiętaną zamiast

zapytania podrzędnego. Jest więc ona znacznie mocniejsza od standardowej
i pozwala na wstawianie danych, które pochodzą z serwerów zdalnych. To samo
można uzyskać w systemie InterBase, przez wybranie wierszy dla komendy

INSERT,

będącej fragmentem pamiętanej procedury wyboru (innymi słowy,

procedury pamiętanej typu

SELECT)

Komenda DELETE

Niektóre platformy zawierają rozszerzenie składni komendy

DELETE

umożliwiające kwalifikowanie wyrażeń z tą komendą poprzez wykonywanie (w
ich ramach) złączeń i zapytań podrzędnych. Rozważmy przypadek, w którym
chcemy usunąć tylko tych klientów, dla których nie istnieją żadne zlecenia.
Możemy w tym celu użyć złączenia lub zapytania podrzędnego - w celu ustalenia
klientów, dla których zlecenia już istnieją i usunięcia tylko tych, dla których nie
określono żadnych zleceń.

Użytkownicy platform Sybase lub Microsoft SQL Server mogą usuwać wszystkie
dane z tabeli, używając komendy

TRUNCATE TABLE

(zamiast

DELETE)

- do

usuwania wierszy bez ograniczeń. Takie rozwiązanie jest szybsze i w minimalnym
tylko stopniu obciąża protokół transakcji.

Komenda UPDATE

Niektóre platformy umożliwiają odczytywanie w tej komendzie wartości z innych
tabel, tak że może ona wykonać złączenie w trakcie swego działania. Oto służąca
do tego konstrukcja składniowa:

UPDATE CUSTOMER

Rozdział 24 Zaawansowane programowanie w SQL

693

FROM CUSTOMER C,
ZIP Z
SET C.City=Z.City, C.State=Z.State
WHERE C.ZipCode = Z.ZipCode

Zwalnia nas to z konieczności stosowania kursorów lub innych złożonych
mechanizmów do uaktualniania jednej tabeli wartościami pochodzącymi z innej.

Kursory (Cursors)

Kursor (cursor) udostępnia wiersze w tabeli po jednym na raz. Z kursorami wiążą
się cztery podstawowe komendy:

DECLARE

OPEN

FETCH

. Komendy

UPDATE

DELETE

można też wykorzystać do pracy z kursorami aktualizowanymi

(updatable cursors).

Kursor definiujemy za pomocą komendy

SELECT

. Jeżeli ma być to kursor

aktualizowany, to trzeba dołączyć listę kolumn do aktualizacji. Poniżej konstrukcja
składniowa:

DECLARE c_PROPERTY CURSOR
FOR SELECT * FROM PROPERTY

Zanim kursor będzie mógł zwrócić jakieś wiersze, musi zostać otwarty. Komenda

OPEN

inicjuje zapytanie, które deklaruje kursor. Przykład poniżej:

OPEN c_PROPERTY

Zauważmy, że komenda

OPEN

nie otrzymuje wierszy z kursora; do tego celu

używamy komendy

FETCH

FETCH c_PROPERTY

Komenda

FETCH

zwraca po jednym wierszu na raz, z tabeli związanej z kursorem.

Za każdym wywołaniem

FETCH

zwracany jest nowy wiersz. Platformy InterBase,

Oracle i SQL Server udostępniają jedynie kursory, dla których kolejność działania
komendy

FETCH

jest rosnąca (kursory takie określa się jako forward cursors).

Aby poruszać się wstecz w zbiorze wynikowym kursora, należy go zamknąć
i ponownie otworzyć.

UWAGA:

Chociaż większość dostawców platform DBMS nie dostarcza kursorów
dwukierunkowych, DELPHI umożliwia ich wykorzystanie w naszych aplikacjach.
Środowisko BDE udostępnia bowiem mechanizm kursorów dwukierunkowych,
niezależnie od tego, czy platforma, na której zainstalowano DELPHI, obsługuje
takie kursory.

694

Część IV

Wiersze zwracane przez kursory aktualizowane możemy uaktualniać za pomocą
specjalnych wersji komend

UPDATE

DELETE

. Kursor musi być usuwany, jeżeli

ma być kursorem aktualizowanym, z użyciem klauzuli

FOR UPDATE OF

Ilustruje to poniższy przykład:

DECLARE c_PROPERTY CURSOR
FOR SELECT * FROM PROPERTY
FOR UPDATE OF Address

W klauzuli

FOR

UPDATE

należy uwzględniać tylko te kolumny, które

rzeczywiście moglibyśmy uaktualniać. Deklarowanie niepotrzebnych pól do
aktualizacji powoduje zbyteczne obciążenie zasobów serwera.

Bieżący wiersz kursora aktualizowanego uaktualnia się lub usuwa za pomocą
komend

UPDATE

lub

DELETE

klauzulą

WHERE CURRENT OF

cursorname

, zgodnie z poniższym przykładem:

UPDATE PROPERTY
SET Address=”357 Riverside Avenue”
WHERE CURRENT OF c_PROPERTY

Aby usunąć wiersz, piszemy:

DELETE FROM PROPERTY
WHERE CURRENT OF c_PROPERTY

Kursory, z którymi zakończyliśmy pracę, zamykamy komendą

CLOSE:

CLOSE c_PROPERTY

W platformie InterBase, komenda

zwalnia również wszystkie zasoby

systemowe związane z kursorem. W Sybase używamy do tego celu komendy

DEALLOCATE CURSOR

, zgodnie z przykładem:

DEALLOCATE CURSOR c_PROPERTY

Optymalizacja w SQL

Reszta tego rozdziału poświęcona jest programowaniu w języku SQL na jeszcze
wyższym poziomie (niż dotąd omawiany), tzn. tworzeniu optymalnego kodu. Przez
optymalny należy rozumieć nie tylko szybki, ale także niezawodny

−

dzięki

zastosowaniu zdrowych technik projektowych, umożliwiających tworzenie kodu
przejrzystego oraz łatwego do konserwacji i rozszerzania. Niektóre z tych technik
w ogóle nie wiążą się z wydajnością, ale oszczędzają czas, umożliwiając nam
unikanie częstych pułapek i

pomagając w

pisaniu czystych i

czytelnych

programów w języku SQL.

Zamieszczone niżej wskazówki na pewno nie są wyczerpujące. Są one
podpowiedziami, skrótami i sensownymi konwencjami, które autor uznał za

Rozdział 24 Zaawansowane programowanie w SQL

695

pożyteczne w pracy programisty. Realizowanie wszystkich tych wskazówek nie
jest tak ważne, jak pisanie programów w SQL w jednolity sposób. Pamiętajmy
chodzi tu o oszczędność czasu na dłuższą metę. Błyskawiczne sklecenie byle jakiej
procedury i dołączenie jej do naszej aplikacji może nas w końcowym efekcie
bardzo wiele kosztować

−

później i tak będziemy musieli ją poprawić, często

pogarszając integralność kodu. Wyświadczmy sobie przysługę: trzymajmy się
pewnych konwencji nazewnictwa i zasad kodowania we wszystkich programach,
podprogramach itd., które piszemy, zwłaszcza w języku SQL.

Autor podzielił wskazówki na dwie części: optymalne konstrukcje składniowe
w SQL oraz optymalna wydajność.

Składnia języka SQL

W tej części omawiamy niuanse, które nie mają faktycznego wpływu na
wydajność. Naszym celem jest przybliżenie zasad pisania kodu, który będzie
czytelny i przyjemny przez lata.

Przewaga składni według ANSI

Wszędzie, gdzie to możliwe, używajmy składni według ANSI. Autor czyni jedyny
wyjątek od tej zasady

−

w przypadku złączeń (joins). Składnia oferowana przez

Sybase/Microsoft jest bardziej zwięzła i czytelniejsza niż wzorcowa norma ANSI.
Tak czy inaczej bądźmy jednak zawsze świadomi różnic między używanym
dialektem SQL a składnią ANSI.

Takie podejście ułatwi innym korzystanie z efektów naszej pracy, dzięki używaniu
składni, na którą zgodziła się duża liczba dostawców platform DBMS. Zwiększy to
także pożytek z książek i materiałów szkoleniowych; gdyż największa ich liczba
dotyczy języka według ANSI, a nie dialektu konkretnego dostawcy. Wreszcie
konsekwentne trzymanie się wzorcowej wersji języka poszerzy możliwości
przenoszenia projektów między platformami i dostawcami.

Kwalifikowanie kolumn

Zawsze, gdy nasz kod dotyczy więcej niż jednej tabeli, używajmy pełnej
kwalifikacji kolumn do wyświetlenia. Z faktu, że tylko jedna (na razie!) tabela
w zapytaniu zawiera kolumnę o danej nazwie, wcale nie wynika, że możemy
omijać identyfikator z nazwą tabeli. Zawsze oznaczajmy każdą kolumnę nazwą jej
tabeli. Jeżeli zdarzy się nam później dodać tabelę o takiej samej nazwie kolumny,
co nazwy kolumn tabel już uwzględnionych w zapytaniu, takie podejście
zaoszczędzi nam czas poświęcony jego przeglądaniu - w

celu usunięcia

wieloznacznych odniesień do kolumn.

696

Część IV

Nazwy zastępcze (aliasy) tablic

Korzystajmy z nazw zastępczych, aby zwiększyć czytelność naszego kodu. Nie
wpisujmy za każdym razem pełnej nazwy tabeli przed każdą kolumną w liście
wyboru; zastąpmy ją aliasem. Skraca to czas wpisywania oraz sam tekst zapytania
i zwiększa jego czytelność.

Nazwy kolumn w wyrażeniach INSERT

W rozdziale wprowadzającym do języka SQL zwróciliśmy uwagę, że nazwy
kolumn są opcjonalne w wyrażeniach

INSERT

, jeśli wstawiamy wartości do

wszystkich kolumn tabeli. Konsekwentne stosowanie nazw kolumn jest jednak
pożyteczne, gdyż pozwala nam upewnić się, że wstawiamy to, co chcemy wstawić.
Wymusza to na nas dopasowanie każdej z

wprowadzanych wartości do

odpowiadającej jej nazwy kolumny. Jak poprzednio, nie jest to obligatoryjne, ale
stanowi dobrą praktykę programistyczną.

Klauzula GROUP BY

W zapytaniach zawierających funkcje sumaryczne (aggregates, aggragate
functions), powinniśmy zawsze używać do grupowania według wszystkich kolumn,
które nie są sumaryczne (tzw. nonaggregate columns) - klauzuli

GROUP

. Nie

przestrzeganie tego zalecenia prowadzi zwykle do generowania przez zapytania
bezużytecznych wyników i może stać się przyczyną zapętlenia. Niektóre platformy
DBMS odmawiają realizacji zapytań z klauzulą

GROUP

bez grupowania

według wszystkich kolumn które nie są sumaryczne.

Klauzula WHERE

Poniżej, w podpunktach „Unikanie iloczynów kartezjańskich” oraz „Korzystanie
z nawiasów” omówiliśmy techniki optymalizacji klauzuli

WHERE

. Używamy jej do

kwalifikowania uniwersalnej komendy

SELECT

, stąd też zachowanie jasności

i zwięzłości konstrukcji z jej użyciem ma kluczowe znaczenie dla czytelnego
programowania zapytań.

Unikanie iloczynów kartezjańskich

W przypadku komendy

SELECT,

operującej na wielu tabelach, każda tabela

wymaga poprawnego złączenia z przynajmniej jedną inną tabelą, a każde złączenie
winno wiązać się, poprzez słowo kluczowe

AND,

innymi złączeniami

w wyrażeniu. Niespełnienie tego wymagania prowadzi do zwrócenia fragmentu
iloczynu kartezjańskiego tych tabel. Jeżeli są one bardzo duże, zapytanie może
działać nieskończenie długo. Zgodnie z wcześniejszymi rozważaniami, iloczyny
kartezjańskie mają bardzo ograniczoną przydatność i zwykle są rezultatem błędów.

Rozdział 24 Zaawansowane programowanie w SQL

697

Korzystanie z nawiasów

Zaleca się korzystanie z nawiasów do rozbijania na czytelne fragmenty złożonych
klauzul

WHERE

z dużą liczbą operatorów

AND

. Ustala to jednoznacznie

priorytety elementów klauzuli i zwiększa czytelność kodu.

Procedury pamiętane

Poniższe wskazówki odnoszą się do optymalnych technik programowania procedur
pamiętanych (stored procedures). Ponieważ spora część kodu dużych systemów
typu klient/serwer napisana z wykorzystaniem takich procedur, ważne jest
zachowanie spójności podczas ich konstruowania.

Komentarze

Powinniśmy unikać komentarzy zagnieżdżonych - tym bardziej, że nie wszystkie
platformy je obsługują. Unikajmy też przesady w komentowaniu. Jedna ze szkół
programowania twierdzi, że cały program, niezależnie od tego, czy napisano go
w tradycyjnym języku programowania, czy też w SQL, powinien być bardzo
obszernie skomentowany. Autor nie zgadza się z taką koncepcją. Przyczyną, dla
której obszerne komentarze uważa za niepotrzebne (i w

rzeczywistości

przeszkadzające w dobrym programowaniu w SQL), jest duże podobieństwo
języka SQL do naturalnego angielskiego. Język SQL pierwotnie miał nosić nazwę
Structured English Query Language (strukturalny język angielski do formułowania
zapytań) i stąd pochodzi akronim SQL. Prawidłowo napisany program w tym
języku powinien być „samokomentujący się”.

Zbyt obszerne przypisy rozpraszają i odciągają uwagę od głównego tekstu książki.
Dotyczy to również umieszczania zbyt wielu komentarzy w kodzie SQL. Mogą one
bardzo utrudnić poruszanie się po obszernym skrypcie.

Przesadne komentarze powodują też podwojenie pracy

−

gdy zmieniamy kod,

musimy też zmieniać komentarze. Jeżeli kod jest za trudny do zrozumienia jedynie
z kilkoma dobrze umieszczonymi komentarzami, to być może trzeba go przepisać.

W żadnym jednak wypadku nie ma generalnego zakazu używania komentarzy
w programach SQL. Oszczędne komentowanie kodu źródłowego jest bardzo
potrzebne, nawet w

przypadku języków programowania zbliżonych do

naturalnych. Musimy jednak zdać sobie sprawę, że SQL jest w dużym stopniu
językiem „samokomentującym” się. Jeżeli więc prawidłowo konstruujemy nasze
zapytania, obszerne komentarze powinny okazać się zbędne.

Bloki BEGIN...END

Wydzielajmy fragmenty kodu za pomocą bloków

BEGIN...END

- nawet

w sytuacjach, w których nie jest to konieczne. Przykładowo, dialekt Transact-SQL

698

Część IV

platform Sybase/Microsoft pozwala omijać nawiasy

BEGIN...END

w wyrażeniu

IF, jeśli steruje ono tylko jedną instrukcją. Najlepiej jest stosować bloki

BEGIN...END

we wszystkich pętlach oraz konstrukcjach

. Zwiększa to

czytelność programów i ułatwia rozszerzanie wyrażeń warunkowych na wiele
wierszy.

Wydajność

Poniższe uwagi na temat optymalizacji dotyczą przede wszystkim wydajności.
Zastosowanie opisanych w nich technik może przyczynić się do znacznego
przyspieszenia pracy naszej bazy danych.

Indeksy

Poniżej opisano zasady optymalizacji pracy z indeksami. Przekonamy się, że
chociaż z czysto technicznego punktu widzenia model relacyjny nie obejmuje
indeksów, to mają one wielki wpływ na techniki projektowania zapytań.

Indeksy wewnętrzne a zewnętrzne

Systemy Sybase i

Microsoft SQL Server udostępniają rozszerzoną wersję

komendy

CREATE INDEX

, która umożliwia tworzenie tzw. indeksów

wewnętrznych (clustered indexes). Indeks zewnętrzny (nonclustered index) to
indeks w tradycyjnym znaczeniu

−

istnieje na najniższym poziomie i przechowuje

wartości kluczy, za pomocą których baza danych odwołuje się do danych fizycznej
tabeli. Indeks wewnętrzny przechowuje rzeczywiste dane na najniższym poziomie
swojej struktury drzewiastej. Oznacza to, że w liściach drzewa przechowuje się
cały wiersz, a nie tylko wartości klucza. Z definicji, jedna tabela może mieć tylko
jeden indeks wewnętrzny.

Dane w indeksie wewnętrznym sortuje się według określonego klucza, co daje
znaczną poprawę wydajności w porównaniu z tradycyjnymi indeksami, zwłaszcza
w przypadku zapytań zwracających podciągi wierszy. Nie ma wówczas potrzeby,
w celu wyszukania klucza, który znajduje się w indeksie, odnoszenia się do
fizycznej tabeli. Indeks jest tabelą. Nie ma żadnej oddzielnej tabeli, w której
miałoby się czegoś szukać.

Odpowiednia konstrukcja w dialekcie systemu SQL Server jest następująca:

CREATED CLUSTERED INDEX INVOICES01 ON INVOICES (OrderNumber)

Niestety, InterBase nie udostępnia żadnych podobnych konstrukcji do tworzenia
indeksów wewnętrznych.

Rozdział 24 Zaawansowane programowanie w SQL

699

UWAGA:

Nie należy mylić indeksów wewnętrznych (clustered index) w systemach firm
Sybase/Microsoft, z klastrami (clusters) udostępnianymi przez Oracle. Chociaż
w języku angielskim są to podobne terminy, mają one inne znaczenia. W wersji
Oracle 8 pojawią się tzw. tablice indeksowe (Index Only Tables)

−

indeksy

przechowujące dane w liściach drzewa. Będą one przybliżonym ekwiwalentem
indeksów wewnętrznych.

Zalecamy stosowanie indeksów wewnętrznych wszędzie tam, gdzie jest to
możliwe.

Indeksy wyczerpujące (covered indexes)

Optymalizator zapytań w serwerze sprawdza, czy indeks zewnętrzny udostępnia
wszystkie kolumny spełniające warunki podane w wyrażeniu

SELECT

, bez

potrzeby odwoływania się do samej tabeli. Wyczerpujący indeks (covered index)
to indeks zewnętrzny, który zawiera jako część swojego klucza wszystkie dane,
które mają zostać zwrócone przez zapytanie. Oznacza to, że poza swoimi
wartościami, klucz indeksu wyczerpującego zawiera wszystkie pozostałe elementy
danych, wymagane do realizacji danego zapytania. Tak więc indeks wyczerpuje
zapytanie

−

stąd jego nazwa.

A oto przykład wykorzystania indeksu wyczerpującego:

CREATE INDEX ORDERS04 ON ORDERS (OrderDate, Amount)

Teraz zapytanie

SELECT OrderDate, SUM(Amount) TotalAmount
FROM ORDERS
GROUP BY OrderDate

może zostać zoptymalizowane w serwerze bazy danych tak, żeby odczyt danych
przebiegał tylko z indeksu, bez konieczności wyszukiwania wartości kolumny

Amount

w samej tabeli.

Wydajność komendy SELECT

Kilka następnych wskazówek dotyczy komendy

SELECT

. Opisano w nich kilka

prostych sposobów zwiększenia wydajności używanych wyrażeń

SELECT,

bez

konieczności znacznego modyfikowania kodu.

Funkcja EXISTS

W wyrażeniach

SELECT,

do sprawdzania istnienia określonych danych należy

korzystać z

funkcji

EXISTS

. W

szczególności nie używajmy konstrukcji

700

Część IV

w rodzaju poniższych, jeżeli chcemy dokonać kwalifikacji zapytania według tego,
czy w tablicy istnieją wiersze spełniające dany warunek:

IF (SELECT COUNT(*) FROM TABLENAME WHERE condition) > 0

Wyrażenie to powoduje zliczenie wszystkich rekordów w tabeli, które spełniają
warunek w klauzuli

WHERE

, przed wyliczeniem warunku dla komendy

W przypadku dużych tabel zliczanie takie może trwać w nieskończoność. Poniższa
konstrukcja jest znacznie wydajniejsza:

IF EXISTS (SELECT * FROM TABLENAME WHERE condition)

Większość serwerów optymalizuje zapytania w rodzaju powyższego tak, że
sterowanie wraca w momencie wyszukania jednego wiersza, spełniającego kryteria
podane w komendzie. Jeśli z indeksu wynika, że dla kryteriów takich istnieje
rekord dokładnie je spełniający, zapytanie może w ogóle nie być wykonywane.

Komenda SELECT ze słowem kluczowym DISTINCT

Konstrukcja

SELECT DISTINCT

rzadko kiedy bywa naprawdę konieczna (w

praktyce prawie nigdy). Aby zrozumieć zasadność powyższego stwierdzenia
musimy dokładnie wiedzieć, do czego służy słowo kluczowe

DISTINCT

Eliminuje ono mianowicie duplikaty ze zbioru wynikowego, zwróconego przez
komendę

SELECT

. Zapytajmy więc, czemu w ogóle zbiór wynikowy zawiera jakieś

duplikaty? Czy dzieje się tak w rezultacie „nieoptymalnie” sformułowanego
zapytania? Jeśli odpowiedź na drugie z tych pytań brzmi tak, to powinniśmy
poprawić samo zapytanie. W praktyce konstrukcja

SELECT DISTINCT

służy

przeważnie do maskowania iloczynu kartezjańskiego, który niepotrzebnie
generowany jest przez sam kod. Konstrukcji tej powinniśmy używać tylko wtedy,
gdy jest ona jedynym sposobem otrzymania żądanego wyniku, a nie wtedy, gdy jest
sposobem najprostszym.

Klauzula WHERE

Klauzula ta jest prawdopodobnie głównym i najczęstszym kandydatem do
modyfikacji, prowadzących do poprawy wydajności. Komenda

SELECT

jest sama

w sobie tak złożona i ma tyle aspektów, że można by całe książki napisać na temat
jej optymalizacji wraz z klauzulami. Poniższe wskazówki powinny pomóc
w poprawie wydajności konstrukcji z klauzulami

WHERE

Ograniczanie DataSet

Głównym zadaniem klauzuli

WHERE

jest ograniczanie zbioru wierszy, z którymi

musi pracować zapytanie. Klauzule

WHERE

powinniśmy tak organizować, żeby

liczba wierszy, których dotyczą manipulacje, była możliwie najmniejsza. Wszędzie

Rozdział 24 Zaawansowane programowanie w SQL

701

tam gdzie to możliwe, powinniśmy do tego celu wykorzystywać indeksy. Jeżeli
często się zdarza, że musimy ograniczać zbiór danych (DataSet) - za pomocą
klauzuli

WHERE

bez użycia indeksu - to być może nadszedł już czas na dodanie

nowego indeksu.

Zwróćmy uwagę, że dany układ klauzul

FROM

WHERE

nie gwarantuje żadnego

konkretnego porządku dostępu do tabeli. W platformach z optymalizacją zapytań
(a większość platform dysponuje lepszą lub gorszą optymalizacją), optymalizator
rezerwuje sobie prawo do przekonstruowania zapytań w

celu osiągnięcia

optymalnej wydajności. Są sposoby ominięcia optymalizacji (np. za pomocą
komendy

SET FORCEPLAN ON w

systemie Microsoft SQL Server), ale zwykle

lepiej zostawić inicjatywę optymalizatorowi.

Pola kluczy w funkcjach i wyrażeniach

Unikajmy używania pól kluczy indeksowych w wyrażeniu lub jako parametrów
funkcji, jeżeli to wyrażenie lub funkcja występuje w klauzuli

WHERE -

serwer mógł wybrać

prawidłowy indeks. Oto przykład zapytania w dialekcie

platformy InterBase, uniemożliwiającego optymalizatorowi zapytań użycie
indeksu:

SELECT * FROM ORDERS
WHERE CAST (OrderDate AS CHAR(8)) >= ‘07/01/95’

Przykład ten z dwóch przyczyn jest ilustracją nieprawidłowego wykorzystania
funkcji

CAST()

. Po pierwsze jest ono niekonieczne, ponieważ system InterBase

automatycznie przeprowadza porównania między wyrażeniami typów znakowego
i daty. Po drugie wprowadza w błąd optymalizator zapytań, gdyż nie użyje on
indeksu, który istnieje dla kolumny

OrderDate

Słowo kluczowe LIKE

Przy słowie kluczowym

powinniśmy zwrócić uwagę, czy możliwe jest

użycie indeksu. Przykładowo, indeksu nie można wykorzystać w poniższym
zapytaniu:

SELECT * FROM CUSTOMER
WHERE LastName LIKE „%Joh%on”

Występowanie znaku uogólniającego (%*) na pierwszej pozycji wyrażenia

uniemożliwia wyszukiwanie indeksowe. Natomiast następne zapytanie jest w pełni
dopuszczalne, gdyż optymalizator zapytań może wyszukać pierwszą pozycję, która
spełnia kryterium wyszukiwania, używając jako klucza sekwencji znaków

Joh

SELECT * FROM CUSTOMER
WHERE LastName LIKE „Joh%on”

702

Część IV

Teraz, za pomocą tradycyjnego wyszukiwania sekwencyjnego, wiersze
kwalifikowane przez

Joh

można z kolei ograniczyć do tych, które kończą się na

. Tak więc potencjalny zbiór rekordów, spełniających kryterium wyszukiwania,

może - dzięki wyrażeniom w rodzaju

- zostać wyznaczony z użyciem

indeksu. Upraszcza to zadanie ograniczania zbioru zwracanych z tabeli rekordów -
do rekordów zawierających podaną maskę dla nazwiska.

Zapytania podrzędne (subqueries)

Należy ich unikać wszędzie, gdzie to możliwe, wpisując zamiast nich
bezpośrednio listy wartości. Z zapytaniem podrzędnym z reguły wiąże się
konieczność zakładania przez serwer tymczasowej tabeli roboczej do
przechowywania wyników tego zapytania. Jeżeli zaś wyników nie zachowuje się
w tabeli roboczej, to zapytanie trzeba wykonywać dla każdej iteracji nadrzędnego
zapytania. Żadna z tych dwóch możliwości nie wydaje się szczególnie atrakcyjna.
W praktyce często okazuje się, że zapytania z zapytaniami podrzędnymi można tak
przepisać, że nie tylko będą szybciej realizowane, ale także staną się czytelniejsze.

Wyobraźmy sobie, na przykład, że piszemy zapytanie, w

którym trzeba

podsumować tabelę z milionami potwierdzeń wypłat z użyciem kart kredytowych.
Interesują nas przy tym tylko potwierdzenia dla klientów indywidualnych (a nie
firm). Dysponujemy tabelą z

dziesięcioma różnymi rodzajami klientów,

oznaczonymi albo jako klient indywidualny, albo firma. Wielu z

nas

zrealizowałoby to zapytanie „nieoptymalnie” w sposób następujący:

SELECT UseDate, SUM(Amount)AmountSpent
FROM RECEIPTS
WHERE CardType in (SELECT CardType FROM CARDTYPE WHERE
Type=’I’)
GROUP BY UseDate

Jednym ze sposobów przyspieszenia realizacji tego zapytania jest bezpośrednie
wpisanie listy, zamiast zwracającego ją zapytania podrzędnego. Jeżeli jednak po
takiej optymalizacji jakiś użytkownik bazy danych doda rekord do tablicy

CARDTYPE

, to nie zostanie on uwzględniony w wygenerowanym raporcie. Tak

więc musimy każdorazowo rozstrzygnąć, czy wyżej podany sposób można
zastosować w danym przypadku.

Inna metoda optymalizacji polega na dynamicznym utworzeniu zapytania
w Delphi. A oto poszczególne etapy postępowania przy dynamicznym tworzeniu
zapytań:

1. Otworzyć zapytanie

TQuery

, którego kod SQL odpytuje serwer o typy kart

związane z klientami indywidualnymi

Rozdział 24 Zaawansowane programowanie w SQL

703

2. Przejrzeć w pętli zbiór wynikowy, zwrócony przez

TQuery

i utworzyć łańcuch

zawierający listę typów kart, oddzielonych przecinkami. Łańcuch taki ma
postać następującą:

‘1,2,4,12,13,20’

3. Przekazać ten łańcuch dynamicznemu zapytaniu SQL, które ma wystąpić

w klauzuli

naszego zapytania. Oto przykład takiego zapytania:

SELECT UseDate, SUM(Amount)AmountSpent

FROM RECEIPTS

WHERE CardType in (:CardTypeList)

GROUP BY UseDate

4. Przygotować i otworzyć dynamiczne zapytanie SQL.

W ten sposób otrzymaliśmy zapytanie w możliwie najelastyczniejszej postaci,
która pozwala na uzupełnianie tabeli CARDTYPE; z drugiej strony uniknęliśmy
zapytań podrzędnych w procesie podsumowania.

Najlepszym zaś sposobem optymalizacji jest po prostu złączenie dwóch tabel,
zgodnie z poniższym przykładem:

SELECT r.UseDate, SUM(r.Amount)AmountSpent
FROM RECEIPTS r, CARDTYPE c
WHERE r.CardType = c.CardType
and c.Type=’I’
GROUP BY UseDate

Dzięki temu całą pracę wykona serwer, a zapytania podrzędne zostały całkowicie
wyeliminowane.

Klauzula ORDER BY

Powinniśmy unikać tej klauzuli. Jest ona mało wydajna i może doprowadzić do
niepotrzebnego sortowania przez serwer wielkiego zbioru wynikowego. Nie
używajmy jej w ogóle - z wyjątkiem sytuacji, gdy rzeczywiście potrzeba nam
uporządkowania, jakie ta klauzula generuje. Zauważmy, że w platformach Sybase
i Microsoft SQL Server indeksy wewnętrzne powodują uporządkowanie wierszy
wynikowych. Oprócz tego w niektórych systemach, do uporządkowania danych
można wykorzystać klauzulę

ORDER BY

. Chociaż sam język SQL nie gwarantuje

żadnej konkretnej kolejności wierszy, niektóre platformy oferują takie czy inne
pośrednie metody porządkowania. Szczegółowych informacji na ten temat trzeba
zawsze szukać w dokumentacji posiadanego serwera.

Klauzula HAVING

Prawie zawsze istnieje jakiś lepszy sposób kwalifikowania zapytania, niż klauzula

HAVING

. Ogólnie rzecz biorąc, klauzula

HAVING

jest zawsze mniej wydajna od

704

Część IV

klauzuli

WHERE

; w pierwszej zbiór wynikowy jest kwalifikowany już po jego

podziale na grupy, podczas gdy w drugiej

−

przed. Oto przykład zbędnej klauzuli

HAVING

SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName
HAVING CUSTOMER.LastName<>’Citizen’

A teraz to samo zapytanie po jego przepisaniu do prawidłowej postaci z klauzulą

WHERE

SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
WHERE CUSTOMER.LastName<>’Citizen’
GROUP BY CUSTOMER.LastName

Podejście to jest lepsze, ponieważ grupowanie nie dotyczy już danych, których
i tak nie potrzebujemy. W przypadku klauzuli

HAVING

, większość serwerów

najpierw przeprowadziłoby grupowanie danych, a dopiero potem przefiltrowało je
według kryteriów podanych w tej klauzuli. Jest więc ona mniej wydajna -
chociażby z uwagi na uwzględnianie niepotrzebnych danych.

Jedynym usprawiedliwionym przypadkiem użycia

HAVING

jest kwalifikacja

zbioru wynikowego według funkcji sumarycznych

−

kolumn, które są wynikiem

obliczeń przeprowadzonych na zbiorze wynikowym. Ponieważ dane wejściowe dla
funkcji sumarycznych nie są znane do chwili utworzenia zbioru wynikowego przez
serwer, nie można ich użyć w klauzuli

WHERE

. Jeżeli chcemy więc dokonać

kwalifikacji zbioru wynikowego za pomocą funkcji sumarycznych, musimy
skorzystać z klauzuli

HAVING

. Oto przykład:

SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
WHERE CUSTOMER.LastName<>’Citizen’
GROUP BY CUSTOMER.LastName
HAVING COUNT(*) > 2

Ponieważ wartość pola

NumberWithName

jest nieokreślona do chwili

zakończenia tworzenia zbioru wynikowego, musieliśmy tutaj wykorzystać właśnie
klauzulę

HAVING

Rozdział 24 Zaawansowane programowanie w SQL

705

UWAGA:

Chociaż serwery coraz sprawniej optymalizują mało wydajne zapytania, zawsze
powinniśmy zwracać uwagę na wydajność pisanych przez nas programów. Nawet
jeśli konkretny, posiadany przez nas system RDBMS, potrafi zamienić
niewłaściwie użytą klauzulę

HAVING

WHERE

, to inne platformy nie muszą tego

umieć. Zwyczaj pisania zawsze wydajnego kodu SQL zaoszczędzi nam wysiłku
przy zmianie platformy DBMS.

Klauzula COMPUTE

Dialekty Sybase/Microsoft Transact-SQL umożliwiają sumowanie kolumn
zwracanych przez wyrażenie

SELECT,

bez konieczności odwoływania się w nim

do funkcji sumarycznych (aggregates) - dzięki klauzuli

COMPUTE

. Występuje ona

po innych klauzulach, składających się na wyrażenie

SELECT

i po prostu

powoduje zsumowanie pól zwróconej kolumny za pomocą zwykłej funkcji
sumarycznej - takiej, jak

SUM()

. Ilustruje to przykład:

SELECT CustomerNumber, Amount
FROM ORDERS
COMPUTE SUM(Amount)

Istnieje jednak kilka powodów, żeby z udogodnienia tego nie korzystać. Po
pierwsze, większość narzędzi użytkownika (front-end tools) nie umie przetwarzać
go jako części zbioru wynikowego. Po drugie, klauzula

COMPUTE

ma tak

bezsensowne ograniczenia, że jest w rzeczywistości znacznie mniej przydatna, niż
moglibyśmy przypuszczać. Po trzecie, i najważniejsze, zapytanie powyższe można
skonstruować tak, żeby żądana suma została zwrócona bez konieczności uciekania
się do klauzuli

COMPUTE

SELECT O.CustomerNumber, O.Amount, SUM(O2.Amount) TotalOrders
FROM ORDERS 0

ORDERS O2

GROUP BY O.CustomerNumber, O.Amount

Uzyskany format danych jest akceptowany przez większość narzędzi. Jeszcze
lepszym i wydajniejszym rozwiązaniem jest wykonanie dwóch oddzielnych
przebiegów dla danych: w jednym podzbiór danych kieruje się do tabeli
tymczasowej, a w drugim jest on sumowany. W każdym przypadku możemy, na
wiele sposobów, uzyskać żądane wielkości bez odwoływania się do mało
eleganckiej klauzuli

COMPUTE

Klauzula GROUP BY

Kolumny grupujmy zgodnie z kluczami indeksu, jeśli to możliwe. Przykładowo,
dla dwóch poniższych zapytań:

706

Część IV

SELECT OrderDate, CustomerNumber, SUM(Amount)TotalOrders
FROM ORDERS
GROUP BY OrderDate, CustomerNumber

SELECT OrderDate, CustomerNumber, SUM(Amount)TotalOrders
FROM ORDERS
GROUP BY CustomerNumber, OrderDate

tylko w drugim rozwiązaniu został użyty indeks dla tabeli ORDERS, który
utworzono za pomocą następującej konstrukcji:

CREATE INDEX ORDERS03 ON ORDERS (CustomerNumber, OrderDate)

Jeżeli nie istnieje indeks utworzony z użyciem

OrderDate,

jako kluczem

wyższego poziomu, optymalizator przy realizacji zapytania nie wykorzysta
żadnego indeksu.

Procedury pamiętane

Poniższe techniki odnoszą się do optymalizacji wydajności procedur pamiętanych
(stored procedures). Czasami już samo wykorzystanie tych procedur (zamiast
języka SQL w trybie interpretacyjnym), może przyczynić się do znacznego
zwiększenia wydajności naszej aplikacji. Poza tym optymalizacja samych procedur
pamiętanych może wpływać pozytywnie na wydajność.

Niewielka liczba parametrów

Parametry przekazywane do procedury pamiętanej powinny mieć jak najmniejszy
rozmiar i powinno ich być jak najmniej. Tak jak w przypadku tradycyjnych
języków programowania, powinniśmy minimalizować liczbę parametrów
przekazywanych na stos. Zagadnienie to staje się bardzo istotne w przypadku
parametrów procedur pamiętanych, gdyż przekazywanie ich do serwera przez sieć
może przyczyniać się do powstawania wąskich gardeł. Przekazujmy więc tylko
krótkie wartości całkowite i unikajmy za wszelką cenę długich łańcuchów
znakowych, jako parametrów.

Niektóre platformy pozwalają na przekazywanie parametrów zarówno przez
nazwę, jak i kolejność. W sytuacjach, kiedy procedury nie wywołujemy raz za
razem, parametry lepiej jest przekazywać przez nazwę, gdyż polepszy to
czytelność programu. Jeżeli zaś jest ona często wywoływana, to lepiej
przekazywać je przez położenie, gdyż przyczynia się to do niewielkiego
zwiększenia wydajności w większości platform DBMS. Takie małe zyski zwykle
gromadzą się, prowadząc w efekcie do znacznej poprawy wydajności wówczas,
gdy dana procedura wywoływana jest setki czy tysiące razy.

Rozdział 24 Zaawansowane programowanie w SQL

707

Konstrukcja SET NOCOUNT ON w systemie SQL Server

Systemy Sybase i Microsoftr SQL Server domyślnie przekazują do klienta liczbę
wierszy przetworzonych przez każde wyrażenie, które zawiera procedurę
pamiętaną, wywołaną przez tego klienta. Klient przeważnie w żaden sposób nie
wykorzystuje tych liczb. Wydajność procedur pamiętanych w systemie SQL
Server możemy zwiększyć za pomocą konstrukcji

SET NOCOUTN ON

. Jej

jedynym skutkiem ubocznym jest to, że komenda

READTEXT

, użyta razem

z funkcją

dbreadtext()

biblioteki

DB-Library

, może działać niepoprawnie.

Ponieważ sytuacja taka jest niezwykle rzadka, konstrukcję

SET NOCOUNT ON

powinniśmy uważać za bezpieczną i z reguły pożądaną w definicjach procedur
pamiętanych.

Nadawanie wartości zmiennym lokalnym

Jeżeli w ramach procedury pamiętanej zmiennym lokalnym nadaje się wartości
z użyciem wyrażeń

SELECT

, to należy to czynić w jednym wyrażeniu

SELECT

W niektórych platformach pewne dodatkowe obciążenie zasobów wiąże się
z pierwszym przypisaniem wartości do zmiennej, w każdym wyrażeniu

SELECT

z osobna. Tak więc zgrupowanie przypisań w jednym, zbiorczym wyrażeniu

SELECT,

jest wówczas bardziej wydajne, zgodnie z przykładem w dialekcie

InterBase:

SELECT City, State, Zip
INTO :City, :State, :Zip
FROM CUSTOMER
oraz w dialekcie Transact-SQL:
SELECT @City=City, @State=State, @Zip=Zip
FROM CUSTOMER

Dodatkowo zmniejszamy liczbę wyrażeń

SELECT

w procedurze pamiętanej, co

ułatwia optymalizatorowi zapytań w

serwerze jej rozkład gramatyczny

i optymalizację.

Wyszukiwanie informacji dodatkowych

Informacje dodatkowe powinny być wyszukiwane przez procedurę pamiętaną
(stored procedure) w ostatniej kolejności. Najpierw przecież trzeba ograniczyć
zbiór danych, a dopiero potem wyszukiwać w nim informacje.

Jeżeli chcemy np. dokonać podsumowania tabeli kart kredytowych, która zawiera
kilka milionów wierszy, w

procesie podsumowywania nie powinniśmy

wyszukiwać nazwy klienta dla każdej karty; lepiej jest odłożyć to na później
i wykonać dopiero po możliwie dużej redukcji zbioru wynikowego (tzn. kiedy
gotowe będą wiersze wynikowe dla aplikacji klienckiej). Najefektywniejsze jest
więc zapytanie zrealizowane w wielu przebiegach, które za każdym razem

708

Część IV

przechowuje zredukowany zbiór wynikowy w tabelach tymczasowych. Dane po
pełnej kwalifikacji należy złączyć z tabelą CUSTOMER - w celu zwrócenia
dodatkowych informacji o klientach (osobach) do aplikacji

−

klienta (programu).

Chociaż sens wykonywania wielu przebiegów na dużym zbiorze danych może
wydawać się niezgodny z intuicją, zapamiętajmy, że serwer także wykonuje wiele
przebiegów tam, gdzie to konieczne. Jeżeli rozbijamy nasze zapytanie na wiele
przebiegów, to w pewien sposób sami dokonujemy optymalizacji. Najważniejszą
rzeczą przy optymalizowaniu zapytań w postaci procedur pamiętanych jest
możliwie najwcześniejsza redukcja zbioru danych, które są przetwarzane przez
zapytania. To zaś czasem wymaga wielu przebiegów oraz tabel tymczasowych.

Zapytania krzyżowe

W pewnych sytuacjach standardowa metoda grupowania danych poprzez
wyrażenie

SELECT

okazuje się nie wystarczająca. W szczególności trudne mogą

okazać się w realizacji z użyciem języka SQL tzw. zapytania krzyżowe (cross-tab
queries). Zapytanie takie organizuje dane w wiersze i kolumny, podobnie do
arkusza kalkulacyjnego. Sporą trudność może sprawić przedstawienie w postaci
tabeli danych, które (co zdarza się najczęściej) są zorganizowane w sposób
liniowy.

Wytłumaczmy to na przykładzie. Załóżmy, że piszemy zapytanie, które ma
zwrócić dane o

wynikach sprzedaży trzech największych amerykańskich

producentów samochodów, Forda, General Motors i Chryslera. Aplikacja

−

klient

wymaga, żeby z lewej strony raportu znajdował się typ samochodu (subcompact,
compact, full size, truck itd.), a kolumny z danymi o sprzedaży dla każdego
wytwórcy

−

z prawej. Zwykły kod w języku SQL generujący te informacje jest

następujący:

SELECT CarType, Marker, Sales TotalSales
FROM BIGTHREESALES

Ponieważ jednak nasz systemowy program tworzący raporty (report writer) nie
umie generować odwołań krzyżowych (co umie, na szczęście, Delphi), to danych
nie można sformatować w sposób akceptowany przez klienta, bez pewnej dawki
,,ekwilibrystyki” na poziomie kodu źródłowego. W tym przypadku musielibyśmy
utworzyć tabelę roboczą o wymaganym przez klienta formacie wyjściowym,
a następnie wypełnić ją odpowiednimi danymi. Oto przykład:

CREATE TABLE CARCROSS
(CarType

CHAR(10) NULL,

FordSales

FLOAT

NULL,

GMSales

FLOAT

NULL,

ChryslerSales FLOAT

NULL)

Rozdział 24 Zaawansowane programowanie w SQL

709

Typowym podejściem jest wykonanie wielu przebiegów dla tej tabeli
i wypełnienie jej właściwymi danymi, przed zwróceniem kompletnego zbioru
danych do programu tworzącego raporty (report writer). I znowu przykład:

INSERT INTO CARCROSS (CarType, FordSales)
Select CarType, Sales FordSales
FROM BIGTHREESALES
WHERE Maker=’Ford’

Teraz albo musimy użyć kursora i uaktualnić właściwe kolumny dotyczące
pozostałych dwóch producentów, albo zastosować w pętlach wyrażenie

UPDATE

żeby uaktualnić odpowiednie kolumny za jednym razem:

SELECT Sales
INTO :GMSales
FROM BIGTHREESALES
WHERE Maker=’GM’
AND CarType=’SC’
UPDATE CARCROSS
SET GMSales=:GMSales
WHERE CarType=’SC’

Moglibyśmy też użyć następującej konstrukcji składniowej w dialekcie systemu
SQL Server:

UPDATE CARCROSS
SET GMSales=Sales
FROM SALES S, CARCROSS C
WHERE Maker=’GM’
AND S.CarType=C.CarType

Istnieje jednak lepszy sposób osiągnięcia pożądanego formatu danych.
Wykorzystamy w nim jedną z najmocniejszych stron języka SQL

−

zdolność do

łatwego grupowania danych i dokonywania ich podsumowań

−

do umieszczenia

danych w

formacie tabeli krzyżowej (cross-tab format) przy minimalnym

nakładzie pracy. Użyjemy przy tym techniki zwanej składaniem lub spłaszczaniem
zapytań (query folding lub query flattening). A oto poprzednie zapytanie
zrealizowane w technice składania zapytań:

Najpierw zbieramy i umieszczamy w odpowiedniej kolumnie dane dotyczące
Forda:

INSERT INTO CARCROSS (CarType, FordSales)
Select CarType, Sales FordSales
FROM BIGTHREESALES
WHERE Maker=’Ford’

Następnie to samo wykonujemy dla firmy GM:

INSERT INTO CARCROSS (CarType, GMSales)

710

Część IV

Select CarType, Sales GMSales
FROM BIGTHREESALES
WHERE Maker=’GM’

... i wreszcie dla Chryslera:

INSERT INTO CARCROSS (CarType, ChryslerSales)
Select CarType, Sales ChryslerSales
FROM BIGTHREESALES
WHERE Maker=’Chrysler’

W tym momencie wiersze tabeli CARCROSS wyglądają tak: (rys.24.1):

Tablicę CARCROSS składamy, używając następującego zapytania:

Select CarType,SUM(FordSales) FordSales, SUM(GMSales)

➥

GMSales, SUM(Chrysler Sales) ChryslerSales

FROM CARCROSS
GRUOP BY CarType

Zbiór wynikowy, zwrócony przez powyższe zapytanie, przedstawia rys.24.2.

Rysunek 24.1.
Tabela
CARCROSS przed
złożeniem

Rozdział 24 Zaawansowane programowanie w SQL

711

Jak widać, dane w formacie wymaganym dla raportu otrzymaliśmy tym razem bez
opisanych poprzednio komplikacji językowych.

Magazynowanie danych a język SQL

Jednym z

klasycznych problemów, napotykanych przez projektantów przy

tworzeniu magazynów danych (data warehouses), jest wybór najlepszego sposobu
przechowywania danych sumarycznych i ich odpytywania. Magazyny danych
przeważnie przechowują duże ilości danych sumarycznych. Przeważnie oprócz
bieżącego (szczegółowego) odpytywania danych, użytkownicy chcą również mieć
do nich dostęp po ich podsumowaniu. Stwarza to jednak szereg problemów, które
trzeba rozwiązać przy projektowaniu architektury magazynu danych. Pierwszym
z nich jest wybór odpowiedniego miejsca na przechowanie podsumowanych
danych. Jeśli będą one przechowywane w tej samej bazie danych, co szczegółowe
dane, to czy należy dla nich przeznaczyć specjalne tablice sumaryczne? A może
lepiej je umieścić w specjalnej sumarycznej bazie danych

−

jak jednak zapewnić

wówczas do nich dostęp? Czy podwoi to pracę przy administrowaniu danymi?
A co z procedurami pamiętanymi i

raportami utworzonymi dla danych

szczegółowych

−

czy powinno się je przebudować tak, żeby współpracowały

również z danymi sumarycznymi?

Optymalne rozwiązanie wyżej wymienionych problemów pociąga za sobą
niewielkie zwiększenie wymagań pamięciowych. Jego sednem jest tzw.
podsumowanie zintegrowanie (in-line summarization) danych. Polega ono na
ułożeniu tabel z danymi szczegółowymi w taki sposób, żeby podsumowywane
wiersze znajdowały się w tej samej tabeli, co wiersze szczegółowe, i żeby można

Rysunek 24.2.
Zbiór wynikowy po
operacji składania

712

Część IV

je było odpytywać za pomocą tych samych zapytań, co używane w przypadku
wierszy szczegółowych. Podejście takie eliminuje potrzebę sumarycznych
tabel/baz danych oraz specjalnych, zorientowanych na dane sumaryczne, procedur
pamiętanych i raportów.

Zilustrujmy to rozwiązanie na przykładzie. Załóżmy, że mamy bazę danych
z milionami wypłat dokonywanych z użyciem kart kredytowych. Pod koniec
każdego miesiąca chcemy podsumować informacje o wypłatach według dni,
numerów karty oraz miejsca wypłaty. Musimy obliczyć zarówno całkowitą liczbę
wypłat, jak i całkowitą kwotę. Załóżmy iż chcemy wygenerować tylko jeden
rekord dla sytuacji, w której John Doe zrealizował pierwszego stycznia trzy
wypłaty w jednym ze sklepów sieci Foley. Rekord ten zawierałby numer karty
Johna Doe, datę, numer sklepu sieci Foley, liczbę wypłat i całkowitą kwotę
zakupów.

Zamiast przechowywać informacje sumaryczne w oddzielnej tabeli, można je
zapamiętać w tabeli już istniejącej, przeprowadzając tylko kilka zmian w jej
strukturze. Powiedzmy, że dotychczasowy układ tabeli był następujący:

CREATE TABLE CARDTRANS
(CardNumber char(20)

NOT

NULL,

TransactionDate date

NOT

NULL,

Location int

NOT

NULL,

Amount float NOT

NULL)

Załóżmy też, że do utworzenia albo raportu, albo podsumowania, korzystamy
z poniższej instrukcji

SELECT

SELECT CardNumber, TransactionDate, Location, COUNT(*)

➥

NumberTrans, SUM(Amount) Amount

FROM CARDTRANS
WHERE TransactionDate between ‘01/01/95’ AND ‘02/01/95’
GROUP BY CardNumber, TransactionDate, Location

Tabelę tę i zapytanie możemy przystosować do pracy z danymi sumarycznymi,
przeprowadzając tylko niewielkie zmiany. Poniżej przedstawiono zmodyfikowany
układ tabeli obsługującej podsumowania zintegrowane:

CREATE TABLE CARDTRANS
(CardNumber char(20)

NOT

NULL,

TransactionDate date

NOT

NULL,

Location int

NOT

NULL,

NumberTrans int

NOT

NULL,

Amount float NOT

NULL)

Zauważmy, że pojawiła się tu nowa kolumna o nazwie NumberTrans. Co jest
pamiętane w jej wierszu szczegółowym? To samo, co w wierszu sumarycznym

−

liczba wypłat dla danego wiersza tablicy. Dla wierszy szczegółowych pole

Rozdział 24 Zaawansowane programowanie w SQL

713

NumberTrans ma zawsze wartość 1 - dla zapewnienia możemy nawet zdefiniować
więzy

DEFAULT

. Dla wierszy sumarycznych, NumberTrans zawiera liczbę wypłat

reprezentowanych przez kluczowe pola wiersza. W naszym przykładzie z Johnem
Doe, liczba ta wynosi 3, ale może być dowolna w zakresie dopuszczalnym przez
typ danych

int

. Oto wspomniane wyżej zapytanie, przebudowane dla obsługi

zarówno wierszy szczegółowych, jak i sumarycznych:

SELECT CardNumber, TransactionDate, Location,
SUM(NumberTrans) nUMBERtRANS, SUM(Amount) Amount
FROM CARDTRANS
WHERE TransactionDate between ‘01/01/95’ AND ‘02/01/95’
GROUP BY CardNumber, TransactionDate, Location

Jedyną modyfikacją, jaką wprowadziliśmy, jest zamiana fragmentu

COUNT(*)

(służącego do określania liczby wypłat) na fragment

SUM(NumberTrans)

Teraz nasz kod będzie dobrze działać zarówno dla wierszy szczegółowych, jak
i sumarycznych - dzięki dołączeniu do wierszy szczegółowych kolumny
NumberTrans.

Typowy proces obejmuje podsumowanie danych z miesiąca i skierowanie tak
uzyskanych informacji sumarycznych do tabeli tymczasowej, z zastosowaniem
zapytania podobnego do poprzedniego. Następnie stare dane szczegółowe są
usuwane z tabeli CARDTRANS, po czym dane sumaryczne kieruje się z powrotem
do głównej tabeli. Wszystkie powyższe operacje serwer wykonuje jako pojedynczą
transakcję, co ma stanowić zabezpieczenie przed utratą danych w przypadku
wystąpienia nieprzewidzianych problemów.

UWAGA:

W niektórych platformach DBMS opisany powyżej proces może wiązać się
z usunięciem informacji o

godzinie wypłaty z

kolumny TransactionDate

(DataWypłaty) w

trakcie podsumowania. W

systemach takich, jak Sybase

i Microsoft SQL Server informacje o godzinie i dacie przechowuje się z użyciem
tego samego typu danych. Oznacza to, że kolumna TransactionDate może czasem
zawierać zarówno datę jak i godzinę w wierszach szczegółowych. Ponieważ
jednak żądane podsumowanie odbywa się względem daty, informacje dotyczące
godziny muszą zostać usunięte. W systemie SQL Server dane dotyczące godziny
usuwamy za pomocą następującej konstrukcji składniowej:

SELECT CardNumber, CONVERT(char(8),TransactionDate,

➥

112, Location, SUM(NumberTrans) NumberTrans,

➥

SUM(Amount) Amount

FROM CARDTRANS
WHERE TransactionDate between ‘19950101’ AND

➥

‘19950201’

714

Część IV

GROUP BY CardNumber, CONVERT (char(8),TransactionDate,

➥

112)Location

Zwróćmy uwagę na użycie funkcji

CONVERT

do transformacji oryginalnego typu

pól kolumny TransactionDate na typ

CHAR(8)

. System SQL Server może

automatycznie ponownie zamienić wartości tego typu, dzięki domyślnej (implicit)
konwersji z typu

CHAR

na typ

DATETIME

. Trzeci parametr funkcji

CONVERT

112, nadaje dacie format

CCCCMMDD

. Zapamiętanie jej w

postaci typu

CHAR(8)

spowoduje obcięcie informacji o godzinie.

Zwróćmy uwagę na to, że konieczne może okazać się zwiększenie rozmiaru takich
pól, jak

Amount

- aby mogły one pomieścić dane sumaryczne. Jeżeli np.

wcześniej zdefiniowaliśmy typ zmiennej

Amount,

jako dostępny w systemie

Sybase typ

smallmoney

, to teraz trzeba będzie go zmienić na bardziej pojemny

typ

money

Oto wygodny sposób uniknięcia kłopotów związanych z tworzeniem nowych tabel,
czy nawet baz danych na dane sumaryczne. Kosztem nieznacznego zwiększenia
wymagań pamięciowych, możemy teraz w jednolity sposób traktować dane
szczegółowe i sumaryczne.

Wyszukiwarka

Podobne podstrony:
25 rozdzial 24 yuqrc7z5sklazoiq Nieznany (2)
24 rozdzial 23 wjv3mksbkbdm37qy Nieznany
24 rozdzial 23 ahuayqd5v2qhx4vw Nieznany (2)
24 25 ROZ w sprawie samodziel Nieznany (2)
07 Rozdzial 24 25
24 rozdzial 23 wjv3mksbkbdm37qy Nieznany
Rozdział 24 Prześladowca, Rozdział 25 misja
rozdział 24,25,26
05 rozdzial 04 nzig3du5fdy5tkt5 Nieznany (2)
2015 04 09 08 25 05 01id 28644 Nieznany (2)
28 rozdzial 27 vmxgkzibmm3xcof4 Nieznany (2)
25 soczewki, przyrzady optyczn Nieznany (2)
22 Rozdzial 21 KP4Q5YBIEV5DBSVC Nieznany (2)
09 08 Rozdzielnice budowlane RB Nieznany (2)
17 rozdzial 16 fq3zy7m2bu2oan6t Nieznany (2)
Kanicki Systemy Rozdzial 10 id Nieznany
mnozenie do 25 11 id 304283 Nieznany

więcej podobnych podstron