plik

Rozdział 16

Delphi w środowisku Oracle

W tym rozdziale zapoznamy się z systemem Oracle DBMS oraz dowiemy się, jak
z niej korzystać w aplikacjach Delphi. Poznamy specyfikę języka Oracle SQL oraz
niektóre zagadnienia dotyczące współpracy Delphi i Oracle. Pokażemy także, jak
wykonywać podstawowe zadania administratora i przeprowadzać strojenie serwera
Oracle.

Zaczniemy oczywiście od uruchomienia serwera.

Uruchamianie serwera ORACLE

Sposób uruchomienia serwera Oracle’a w dużym stopniu zależy od systemu
operacyjnego. W naszym opisie ograniczymy się tylko do Windows 95/NT.

Po dokonaniu standardowej instalacji serwer Oracle’a będzie się uruchamiał
automatycznie przy każdym starcie systemu. Nie zawsze taka konfiguracja jest
wygodna. Można oczywiście uruchamiać serwer niezależnie - korzystając
z programu Oracle Instance Manager lub poprzez Control Panel w Windows NT.
W systemie NT serwer Oracle uruchamiany jest poleceniem

OracleStartORCL

(gdzie ORCL jest nazwą naszej bazy danych).

Łączenie się z serwerem

Najpierw należy zainstalować i skonfigurować oprogramowanie połączeniowe
Oracle’a o nazwie SQL Net. Do instalacji wykorzystamy program ORAINST.
Konfiguracja polega na zdefiniowaniu Database Aliases - tzn. określeniu zbioru
parametrów niezbędnych do uzyskania połączenia z serwerem bazy danych.
Przypominają one aliasy DBE używane w Delphi, ale dotyczą programów
sterujących samym serwerem Oracle’a, a nie ustawień dla poszczególnych baz.

Do skonfigurowania systemu Oracle w Windows95/NT używamy programu
konfiguracyjnego SQL Net. Uruchamiamy SQL Net Easy Configuration
i wybieramy

Add Database Alias

Ponieważ tworzymy nowy alias, to w pojawiającym się polu dialogowym
wpisujemy wybraną nazwę.

Następnie wybieramy protokół sieciowy do komunikacji z serwerem (np. TCP/IP).

480

Część III

W zależności od dokonanego wyboru musimy podać niezbędne dane o serwerze.
W przypadku TCP/IP jest to nazwa komputera serwera, użyta w pliku HOSTS lub
jego adres IP. Jeśli łączymy się z serwerem Personal Oracle, funkcjonującym
lokalnie, to jako jego adres IP wprowadzamy adres pętli zwrotnej (ang. loopback
address) 127.0.0.1. Protokół SPX wymaga natomiast podania nazwy usługi.

Końcowe okno dialogowe wyświetla i pozwala zweryfikować podane wartości
parametrów połączeniowych dla tworzonego aliasu. Jeśli wszystko jest
w porządku, klikamy

Następnie powracamy do głównego menu programu konfiguracyjnego SQL Net.

Możemy przetestować utworzony alias, posługując się narzędziem Oracle
SQL*Plus:

sqlplus

USERNAME/PASSWORD@ALIASNAME @

gdzie

USERNAME

PASSWORD

to odpowiednio - podane przez nas - nazwa

użytkownika i hasło, zaś

ALIASNAME

jest nazwą aliasu.

Sprawdzenie łączności z

serwerem umożliwia także program dostarczany

z serwerem Oracle o nazwie TNSPING. Parametry do niego podajemy zgodnie ze
składnią stosowaną w znanym poleceniu systemu UNIX - PING.

WSKAZÓWKA:

Jeśli posługujemy się serwerem Personal Oracle, funkcjonującym lokalnie, to
najprościej będzie skorzystać z protokołu TCP/IP, podając (jako adres serwera)
127.0.0.1. Oczywiście Windows 95 i Windows NT są standardowo wyposażone
w TCP/IP, należy tylko pamiętać, aby go zainstalować.

Konfigurowanie aliasów BDE

Przejdziemy teraz do tworzenia aliasów BDE, dzięki którym nasze aplikacje
w Delphi będą mogły korzystać z bazy Oracle. Ten temat był już omawiany
wcześniej, dlatego teraz przedstawimy tylko kolejne kroki, które pozwolą nam je
poprawnie skonfigurować.

Możemy wybrać jedno z narzędzi: BDE Administrator lub Delphi Database
Explorer. Opisany poniżej sposób postępowania odnosi się do programu BDE
Administrator.

WSKAZÓWKA:

BDE można uruchomić klikając odpowiednią ikonę w folderze Delphi albo
z Windows Control Panel.

Rozdział 16 Delphi w środowisku Oracle

481

1. W programie DBE Administrator, na fiszce

Configuration

, wybieramy

Configuration\Drivers\Native\Oracle

2. Na liście

VENDOR INIT

, w

części

Definition

, znajdujemy odpowiednią

bibliotekę klienta Oracle. Np. wersji serwera Oracle 7.2 odpowiada

ORA72.DLL

. Zauważmy, że można tu także określić pewne parametry

programu obsługi, które staną się ustawieniami domyślnymi dla wszystkich
nowych aliasów korzystających z tego programu.

3. Prawym przyciskiem myszki klikamy fiszkę

Databases

i wybieramy

New

Z listy rozwijalnej

Alias type

, w oknie dialogowym

New Database Alias

wybieramy

ORACLE

i klikamy

4. Wpisujemy nazwę tworzonego aliasu BDE.

5. Wprowadzamy wartości parametrów na stronie

Definition

6. W polu

SERVER NAME

podajemy nazwę aliasu naszej bazy (utworzonego

poprzednio za pomocą SQL Net)

7. Klikamy

NET PROTOKOL

i wybieramy ten sam protokół sieciowy, którego

użyliśmy poprzednio w programie konfiguracyjnym SQL Net.

8. Jeśli podamy nazwę użytkownika (parametr USER NAME), to będzie on

domyślnie przyłączany do serwera. Podana nazwa będzie też umieszczana we
wbudowanym w Delphi oknie dialogowym logowania.

Przycisk

Apply

(albo kombinacja klawiszy CTRL+ALT) umożliwia zapisanie

wprowadzonych zmian i zakończenie sesji Administratora.

WSKAZÓWKA:

Aliasy baz danych, zdefiniowane za pomocą programu BDE Administrator,
zapisywane są w rejestrze Windows. Klucz dostępu do tych danych jest
następujący:

HKEY_LOCAL_MACHINE\SOFTWARE\BORLAND\DATABASE\SETTINGS

Korzystając z tego klucza można definiować aliasy bez użycia programów BDE
Administrator czy Database Explorer z Delphi.

Podajemy jeszcze kilka użytecznych parametrów, które można ustawić za pomocą
programu BDE Administrator.

ENABLE INTEGERS

Wartość parametru

ENABLE INTEGERS

decyduje o tym, czy BDE interpretuje

pola

NUMERIC

bez cyfr dziesiętnych jako pola całkowitoliczbowe. Domyślnie są

one traktowane jako pola numeryczne (niezależnie od ich rozmiaru). Jest to

482

Część III

parametr tego samego rodzaju co

ENABLE BCD

. Jeśli wartości obu ustawione są

na TRUE, wtedy

ENABLE INTEGERS

jest nadrzędny.

Synonimy (synonims)

Oracle umożliwia tworzenie alternatywnych nazw obiektów, nazywanych
synonimami. O sposobie traktowania poszczególnych synonimów przez programy
sterujące bazy decyduje wartość parametru LIST SYNONIMS. Określamy ją
poprzez własność

Params

komponentu

Database

. Dopuszczalne wartości

opisano w tabeli 16.1

Tabela 16.1 Dopuszczalne wartości parametru LIST SYNONIMS

Wartość Znaczenie

NONE

Nie są dołączane żadne synonimy

PRIVATE

Dołączane są tylko synonimy prywatne

ALL

Są dołączane wszystkie synonimy (prywatne i ogólnodostępne)

Synonimy publiczne (Public synonims)

Jeśli wartość parametru

LIST SYNONIMS

jest ustawiona na

ALL

, to na liście

tabel pokazywane są także synonimy

PUBLIC

(publiczne). Jednak, aby otworzyć

obiekt określony synonimem, musimy mieć do niego odpowiednie prawa dostępu
(w sprawie uprawnień powinniśmy się zwrócić do administratora bazy danych).

Publiczne synonimy Oracle zawierają nazwy dynamicznie aktualizowanych tablic
kontrolnych (ang. performance tables). Domyślnie ma do nich dostęp tylko
użytkownik SYS. Synonimy te są postaci V$NAME - gdzie NAME jest pozostałą
częścią synonimu ( jak np.

LOCK, OPEN_CURSOR

itd.)

Rozwiązywanie problemów połączeniowych z serwerem Oracle

Poniżej zebraliśmy kilka praktycznych porad, które mogą okazać się przydatne
w przypadku napotkania problemów połączeniowych.

1. Jeśli nasza aplikacja (napisana w Delphi) nie łączy się z serwerem, powinniśmy

spróbować połączyć się poprzez Oracle SQL*Plus. Jeśli uda się nam nawiązać
komunikację, 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. Nieudana próba połączenia się poprzez Oracle SQL*Plus pozwala

przypuszczać, że przyczyną problemów może być protokół sieciowy. Jeśli
korzystamy z TCP/IP, to połączenie z serwerem można sprawdzić za pomocą

Rozdział 16 Delphi w środowisku Oracle

483

programu PING, będącego na wyposażeniu Windows 95/NT. Powinniśmy
spróbować łączenia poprzez nazwę komputera zapisaną w pliku HOSTS oraz
poprzez jego adres IP.

3. Jeśli łączymy się poprzez adres IP, nie nawiązując połączenia za

pośrednictwem nazwy, wtedy 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 korzystania z

SPX powinniśmy sprawdzić, czy wartości

parametrów protokołu sieciowego, podane w programie konfiguracyjnym SQL
Net, są właściwe. Jeśli nadal nie można uzyskać połączenia, radzimy zwrócić
się do administratora sieci.

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łów

TCP/IP funkcjonuje poprawnie. Jeśli test się nie powiedzie, wówczas
prawdopodobnie źle skonfigurowano sam protokół (w takiej sytuacji ponownie
radzimy zwrócić się do administratora sieci).

Jeśli PING znajduje serwer, natomiast nie możemy się połączyć poprzez
SQL*Plus, powinniśmy sprawdzić dostęp do katalogu Oracle w zmiennej

PATH

. Jeśli

PATH

jest w porządku, pozostaje skontrolować ustawienia zapisane

w pliku (KATALOG GŁÓWNY ORACLE)

\NETWORK\ADMIN

\TNSNAMES.

ORA

. Mimo iż jest to zwykły plik tekstowy, jego edycję powinno się

przeprowadzać programem SQL Net ( zdecydowanie odradzamy korzystanie
z edytora tekstów). Poniżej przedstawiamy przykładową zawartość takiego
pliku (w tym wypadku serwer jest uruchamiany lokalnie):

SCOTTSDATABASE.world =
(DESCRIPTION

(ADDRESS_LIST

(ADDRESS

(COMMUNITY

tcp.world)

(PROTOCOL

TCP)

(Host

127.0.0.1)

(Port

1521)

)

(ADDRESS

(COMMUNITY

tcp.world)

(PROTOCOL

TCP)

(Host

127.0.0.1)

484

Część III

(Port

1526)

)

CONNECT_DATA = (SID = ORCL)

)

7. Jeśli wszystko wydaje się być skonfigurowane poprawnie, możemy (dla próby)

wykorzystać inny protokół komunikacyjny (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 ponownego rozpoczęcia pracy przez drugi protokół. Np. przy
funkcjonującym lokalnie serwerze Oracle’a dysponujemy protokołem
Bequeath. Ten prosty protokół jest oparty na sygnale pętli zwrotnej i nie
wymaga funkcjonującej sieci.

Wstęp do SQL

W tym paragrafie zapoznamy się z dość rozbudowaną odmianą języka SQL,
stosowaną przez serwery Oracle. Szczególnie podkreślono cechy, które różnią tę
implementację SQL od produktów innych sprzedawców. Aby móc poznawać
w praktyce prezentowane elementy języka, wystarczy zapewnić sobie dostęp do
uruchomionego serwera i

umieć się z

nim komunikować poprzez Oracle

SQL*Plus.

Tworzenie bazy danych

Zaczniemy od utworzenia bazy danych i umieszczenia w niej tymczasowych tabel,
które zostaną wykorzystane w podanych dalej przykładach. W tym celu posłużymy
się instrukcją

CREATE DATABASE

. Szczegóły jej składni zależą od konkretnej

implementacji SQL. Na początek ograniczymy się do jej podstawowej,
rozpoznawanej przez Oracle postaci:

CREATE DATABASE databasename LOGFILE filespec DATAFILE

➥

filespec;

Oczywiście pełna składnia jest znacznie bardziej rozbudowana ale wydaje się, że
nazwa bazy oraz miejsce umieszczenia plików z danymi i plików protokołu (log),
powinny stanowić minimum niezbędne do jej poprawnego utworzenia. Okazuje się
jednak, że jest możliwe pominięcie nawet wszystkich parametrów:

CREATE DATABASE;

Rozdział 16 Delphi w środowisku Oracle

485

Powstała w ten sposób baza danych utworzona jest w oparciu o ustawienia
domyślne.

Pliki tworzące bazę danych mają zwykle rozszerzenia DBF lub DAT, co ułatwia
ich odszukanie w katalogach. Można dla nich wstępnie zarezerwować więcej
pamięci lub wybrać opcję automatycznego powiększania wraz ze wzrostem bazy.

Instrukcja CONNECT

Instrukcja

CONNECT

pozwala łączyć się z istniejącą bazą danych. Jej składnia jest

następująca:

CONNECT USER/PASSWORD@DBLink

DBLink

zastępujemy nazwą serwera, z

którym chcemy się łączyć. Jeśli

korzystamy z lokalnej bazy, możemy pominąć znak @ i nazwę serwera. Poniższy
przykład odnosi się właśnie do takiego przypadku:

CONNECT SYS/57RIVERSIDE;

Instrukcja

DISCONNECT

służy do zamknięcia połączenia:

DISCONNECT;

Tworzenie tabel

Gdy jesteśmy już podłączeni do bazy, możemy 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 SQL’a -

CREATE TABLE

. Wprowadźmy

odpowiednią instrukcję SQL, wykorzystując program SQL*PLUS, aby utworzyć
tabelę CUSTOMER:

CREATE TABLE CUSTOMER
(
CustomerNumber number

NOT

NULL,

LastName char(30),
FirstName char(30),
StreetAddress char(30),
City char(20),
State char(2),
Zip char(10)
)

Dalej utwórzmy tabelę SALE :

486

Część III

CREATE TABLE SALE
(
SaleNumber int

NOT

NULL,

SaleDate date,
CustomerNumber number

NOT

NULL,

ItemNumber number

NOT

NULL,

Amount number(5,2)
)

Po zbudowaniu

SALE

, pozostała jeszcze do utworzenia tylko tabela ITEM.

CREATE TABLE ITEM
(
ItemNumber number

NOT

NULL,

Description char(30),
Price number(5,2)
)

Tablespace ( przestrzeń)

Warto zwrócić uwagę na pewną interesującą odmianę instrukcji

CREATE TABLE

w języku SQL Oracle. Pozwala ona umieścić utworzoną tabelę w tzw. przestrzeni
(ang. tablespace). Przestrzeń w Oracle jest elementem pamiętanym, który może
zawierać elementy bazy danych (takie jak tabele i indeksy). Ogólnie baza Oracle
może być rozmieszczona na partycjach, w ramach których tworzymy przestrzenie.
W przestrzeniach natomiast umieszczamy strony danych i strony indeksów (tabele
i indeksy). Tablespace ma przypisane określone miejsce na dysku czy wolumenie,
co jest widoczne w poniższym przykładzie:

CREATE TABLESPACE salestables DATAFILE 'C:\ORANT\DATABASE\

➥

'salestables.dbf ' SIZE 250 K

W utworzonej w ten sposób przestrzeni można umieszczać elementy bazy danych,
używając klauzuli

TABLESPACE

w poleceniu

CREATE TABLE

CREATE TABLE ITEM
(
ItemNumber number

NOT

NULL,

Description char(30),
Price number(5,2)
)

TABLESPACE salestables

Umiejętnie rozmieszczając elementy bazy (tabele, indeksy) w przestrzeniach
umieszczonych na różnych dyskach ( najlepiej obsługiwanych przez niezależne
kontrolery dyskowe) można znacznie poprawić wydajność systemu

Rozdział 16 Delphi w środowisku Oracle

487

AS Subquery (jako podzapytanie)

Innym interesującym rozszerzeniem instrukcji

CREATE TABLE

jest możliwość

utworzenia tabeli poprzez zapytanie. Jest to odpowiednik instrukcji Transact-SQL

SELECT...INTO

. Pozwala ona utworzyć nową tabelę, opartą na wyniku

zapytania do innej. Będzie ona zawierała tylko te wiersze z tabeli wyjściowej,
które spełniają warunek ustalony w zapytaniu. Oto przykład:

CREATE TABLE LARGE_SALE
AS SELECT * FROM SALE WHERE Amount > 50;

Niezbędne dane odnoszące się do kolumn są wtedy pobierane z tabeli, do której
skierowano zapytanie. Instrukcja ta ma więc składnię zbliżoną do

CREATE VIEW

ale różnica tkwi w tym, że posługując się

AS Subquery

tworzymy nową

istniejącą fizycznie tabelę.

UNRECOVERABLE

Tworzenie dużych tabel można przyspieszyć wyłączając raportowanie podczas
dodawania wierszy. W instrukcji

CREATE TABLE

umieszcza się wtedy słowo

kluczowe

UNRECOVERABLE,

tak jak to pokazano w poniższym przykładzie:

CREATE TABLE LARGE_SALE
UNRECOVERABLE
AS SELECT * FROM SALE WHERE Amount > 50;

Klauzula PARALLEL (współbieżnie)

Kolejną możliwość przyspieszenia procesu tworzenia dużych tabel stwarza nam
klauzula

PARALLEL

. Dzięki niej zadanie to może być rozdzielone na zadaną

liczbę wątków, co ilustruje poniższy przykład (podział na 5 oddzielnych wątków):

CREATE TABLE LARGE_SALE
UNRECOVERABLE
PARALLEL (DEGREE 5)
AS SELECT * FROM SALE WHERE Amount > 50;

Proces tworzenia tabeli został rozdzielony na 5 oddzielnych wątków, co powinno
go znacznie przyspieszyć.

Dodawanie i modyfikowanie kolumn tabeli

Posługując się instrukcją

ALTER TABLE

możemy dodać lub zmodyfikować

kolumnę w istniejącej tabeli. W przeciwieństwie do niektórych serwerów baz
danych (np. Microsoft SQL Server) Oracle umożliwia także usuwanie kolumn. Dla
operacji dodawania składnia jest następująca:

488

Część III

ALTER TABLE CUSTOMER
ADD PhoneNumber char(10)

natomiast dla usuwania:

ALTER TABLE CUSTOMER
DROP PhoneNumber

Oczywiście nie można rozszerzyć wypełnionej 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ą one także określić
wartości domyślne dla kolumn. Definiuje się je dołączając odpowiednie instrukcje
do

CREATE TABLE

albo

ALTER TABLE

. Jednym z przykładów nakładania

więzów jest tworzenie klucza głównego:

ALTER TABLE CUSTOMER
ADD PRIMARY KEY (CustomerNumber)

W tym przykładzie zdefiniowaliśmy klucz główny dla tabeli CUSTOMER, jako
pole CustomerNumber. Dzięki temu, na bazie pola CustomerNumber zostanie
utworzony unikalny indeks. Oczywiście do zdefiniowania klucza głównego tabeli
nie można użyć kolumny umożliwiającej wprowadzenie wartości NULL.

Klucz obcy definiuje kolumnę w jednej tabeli, której wartości muszą znajdować
się w innej. Nie określa on 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. Klucz obcy tworzy się w następujący sposób:

ALTER TABLE SALE
ADD CONSTRAINT INVALID_CUSTOMER_NUMBER FOREIGN KEY

➥

(CustomerNumber)REFERENCES CUSTOMER

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 jest określana jako deklaratywna spójność
referencyjna (ang. declarative referential integrity). Termin ten oznacza, że

Rozdział 16 Delphi w środowisku Oracle

489

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 zdefiniować 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 pracujące po stronie
użytkownika (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 dobrane nazwy więzów, służące
jako informacja zwrotna, będą dla użytkownika wystarczającą wskazówką, w jaki
sposób próbowano je naruszyć. Dzięki temu nie musimy zastępować komunikatów
swoimi własnymi - tworzonymi przez obsługę wyjątków generowanych przez
Delphi.

Tworzenie indeksów

Do budowania indeksów w Oracle SQL służy instrukcja

CREATE INDEX.

Jej

podstawowa składnia wygląda następująco:

CREATE INDEX SALE02 ON SALE (SaleDate)

W podanym przykładzie utworzyliśmy indeks

SALE02

- dla tabeli SALE

bazie pola

SaleDate

. Należy zaznaczyć, ze nazwa indeksu w Oracle nie może

powtarzać się w ramach swojego schematu (ang. schema). Nie można także
utworzyć nowego indeksu, który korzystałby z identycznie uporządkowanych
kolumn.

Posługując się instrukcją

CREATE UNIQE INDEX

, zapewniamy sobie kontrolę

unikalności klucza indeksu, tak jak to pokazano w poniższym przykładzie:

CREATE UNIQE INDEX SALE01 ON SALE (SaleNumber)

Indeksy tabel Oracle są zawsze uporządkowane według rosnących wartości klucza,
mimo iż instrukcja

CREATE INDEX

pozwala na użycie słowa kluczowego

DESC

(ang. descending- malejąco). Słowa kluczowe

ASC

DESC

zostały wprowadzone

tylko dla zachowania zgodności z systemem

DB2

490

Część III

Klauzule UNRECOVERABLE i PARALLEL

Klauzule

UNRECOVERABLE

PARALLEL

, które opisaliśmy omawiając instrukcję

CREATE TABLE,

można także stosować z instrukcją

CREATE INDEX

. Powinno

to przyspieszyć tworzenie indeksów dla dużych tabel.

NOSORT

W ten sposób zaznaczamy, że wiersze danej tabeli są już posortowane według
pewnego klucza indeksowego. Dzięki temu unikniemy tworzenia indeksu dla
danych, które są już ustawione w odpowiednim porządku.

Tablespaces

Jak już mówiliśmy wcześniej, przestrzenie (ang. tablespaces) są przeznaczone nie
tylko dla tabel. Można je także wykorzystać do umieszczenia indeksów. Dzięki
umieszczeniu tabel i indeksów do nich na osobnych dyskach można zwiększyć
przepustowość.

Wprowadzanie danych

Instrukcja SQL -

INSERT

- służy do wprowadzania danych do tabeli Oracle.

Każde wystąpienie klauzuli

VALUES

umożliwia dodanie jednego wiersza danych.

Można też wprowadzić od razu kilka wierszy wybierając je z innej tabeli.
W poniższych przykładach będziemy uzupełniali wszystkie nasze tabele. Najpierw
dodamy trzy wiersze do tabeli CUSTOMER (posłużymy się SQL*PLUS):

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)

Rozdział 16 Delphi w środowisku Oracle

491

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)

a na koniec cztery wiersze do tabeli SALE:

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

pozwala zmodyfikować dane w tabeli. Jej klauzula

WHERE umożliwia natomiast wybranie modyfikowanych wierszy. Oto
odpowiedni przykład:

UPDATE CUSTOMER
SET Zip='65803'
WHERE City='SpringField'

Dzięki klauzuli

WHERE

możemy ograniczyć liczbę modyfikowanych wierszy

nawet do jednego (zależnie od danych i warunku wyboru), natomiast jej
pominięcie oznacza modyfikację wszystkich wierszy:

492

Część III

UPDATE CUSTOMER
SET State='MO'

Poprawiając dane można wykorzystać wartości z 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)

Instrukcja DELETE

Dzięki instrukcji SQL

DELETE

mamy możliwość usuwania wierszy z tabeli. 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ą. Oracle
domyślnie rozpoczyna nową transakcję po każdym zastosowaniu

CONNECT,

COMMIT

lub

ROLLBACK

. Instrukcja

COMMIT

służy do zachowania zmian

dokonanych w czasie transakcji, a

ROLLBACK

pozwala na ich anulowanie. Obie

odnoszą się tylko do zmian dokonanych od chwili wywołania ostatniej instrukcji

COMMIT

. Zastosowanie

ROLLBACK

nie spowoduje więc wycofania zmian

przyjętych przez wcześniejsze instrukcje

COMMIT

Oracle SQL*PLUS otwiera transakcję automatycznie przy pierwszym połączeniu.
Przy zamykaniu programu zapisuje dokonane zmiany. W każdym momencie pracy
z SQL*PLUS można zapisać lub wycofać wprowadzone zmiany posługując się
instrukcjami

COMMIT I ROLLBACK

UWAGA:

Oracle traktuje instrukcje DDL (Data Definition Language) inaczej niż inne
instrukcje wewnątrz transakcji. Commit jest zawsze inicjowane bezpośrednio
przed i po wykonaniu instrukcji DDL. Oznacza to, że w języku Oracle SQL nie
można wycofać instrukcji DDL.

Rozdział 16 Delphi w środowisku Oracle

493

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.

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 Oracle 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 wykonują pewne obliczenia na zbiorach danych. Są to m.in.

COUNT, SUM, AVG, MIN

oraz

MAX

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

494

Część III

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 przy

porównywaniu uwzględniana jest różnica pomiędzy wielkimi i małymi literami.
Zawsze jednak można zastosować funkcję

UPPER

, która dokona zamiany (na

duże) wszystkich liter 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 złączenie. Jest to
najczęściej używane złączenie w zapytaniach SQL.

Rozdział 16 Delphi w środowisku Oracle

495

Złączenia wewnętrzne (ang. inner) oraz zewnętrzne (ang. outer)

Wspomniane powyżej złączenie lewostronne jest formalnie znane jako
wewnętrzne. Złączenie wewnętrzne daje w wyniku tylko wiersze spełniające
warunek złączenia. Natomiast złączenie zewnętrzne uwzględnia te wiersze, które
go nie spełniają. W złączeniu zewnętrznym, jeśli nie znajdziemy pasujących
wierszy w tabeli wewnętrznej, wtedy kolumny z tabeli wewnętrznej dołączane są
z wartością NULL. Oto przykład :

SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER SALE
WHERE CUSTOMER.CustomerNumber(+)=SALE.CustomerNumber

Symbol (+) określa tabelę zewnętrzną w złączeniu, której wszystkie wiersze będą
włączone, niezależnie od warunku, do tabeli wynikowej.

W złączeniu prawostronnym zewnętrznym (+) wystąpi po prawej stronie. Należy
zauważyć, ż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

Tutaj CUSTOMER i

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

CustomerNumber

SALE

ITEM

- przez kolumnę

ItemNumber

. W efekcie

wszystkie trzy tabele utworzą jeden zbiór wynikowy.

Samozłączenia (Self-Joins)

Tabela, oprócz złączenia z innymi tabelami, może być złączana sama ze sobą. Ten
rodzaj złączenia nazywa się samozłączeniem. Rozważmy następujące zapytanie:

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

496

Część III

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 jest tworzona i grupowana indywidualna
statystyka, a potem tabela SALE złączana jest ze sobą - dla wyliczenia łą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

i SALE - aby uzyskać kwotę zakupu dla każdego klienta. Następne zaś (ang. theta
join) umożliwia obliczenie 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.

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

Przyrost liczby wierszy w

zbiorze wynikowym jest ogromny, nawet dla

stosunkowo małych tabel. Np. iloczyn kartezjański dwóch tabel mających po 100
wierszy ma już 10 000 wierszy. Należy więc unikać tej operacji - szczególnie
wówczas, gdy ma się do czynienia z dużymi tabelami.

Rozdział 16 Delphi w środowisku Oracle

497

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)

GROUP BY

Ponieważ SQL jest językiem zapytań zorientowanym na przetwarzanie zbiorów
(ang. set - oriented), więc instrukcje grupujące dane są jego integralną częścią.
Niekiedy pojedyncza instrukcja SQL zastępuje 10 lub nawet 50 linii kodu
tradycyjnego programu, przetwarzającego dane wiersz po wierszu. Można to
osiągnąć dzięki instrukcji

SELECT

, klauzuli

GROUP BY

oraz funkcjom

sumarycznym SQL. Następny przykład pokazuje zastosowanie klauzuli

GROUP

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.

Skąd jednak wiadomo, które pola połączyć klauzulą

GROUP BY

? Zarówno Oracle

SQL, jak i ANSI SQL wymagają, aby klauzula

GROUP BY

zawierała wszystkie

kolumny wymienione na liście kolumn instrukcji

SELECT

, które nie są funkcjami

sumarycznymi.

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

funkcjonuje podobnie jak

WHERE

, lecz selekcja dokonywana

jest na zbiorze wynikowym, a nie na wierszach w tabelach zapytania.

Ogólnie

HAVING

jest mniej efektywne niż

WHERE

, ponieważ selekcjonuje zbiór

wynikowy dopiero po zgrupowaniu, natomiast

WHERE

czyni to najpierw. Są

jednak sytuacje, w których korzystamy z klauzuli

HAVING

. Przeanalizujmy

poniższy przykład:

498

Część III

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

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 Oracle SQL 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

Rozdział 16 Delphi w środowisku Oracle

499

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

Perspektywy (Views)

Perspektywa SQL składa się z instrukcji

SELECT

, którą można traktować jak

tabelę i 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, który jest
uruchamiany przy każdym zapytaniu stworzonym na jej (perspektywy) bazie. Jest
podobna w

Oracle do procedury wyboru (omówionej w

następnej sekcji

„Procedury pamiętane”). Gdy jest wykonywane 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.

500

Część III

Instrukcja

SELECT,

tworząca perspektywę, ma prawie takie same możliwości

jak podstawowa instrukcja

SELECT

. Nie może tylko korzystać z klauzuli

ORDER

. To ograniczenie odnosi się do wszystkich omawianych w tym rozdziale

serwerów baz danych.

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

Procedury pamiętane (Stored Procedures)

Procedury pamiętane są kompilowanymi programami SQL (często zawierającymi
wiele instrukcji SQL), przechowywanymi z innymi obiektami bazy danych.
Tworzymy je posługując się instrukcją

CREATE PROCEDURE

. Poniżej

przedstawiamy przykład takiej procedury w Oracle SQL:

CREATE OR REPLACE PROCEDURE increaseprices
AS
BEGIN
UPDATE ITEM set Price=Price+(Price*.05)
END;

Dla procedury z parametrami składnia instrukcji podlega drobnej modyfikacji:

CREATE OR REPLACE PROCEDURE increaseprices (number)
AS
BEGIN
UPDATE ITEM set Price=Price+( increacepercent/100));
END;

Skrypty

Instrukcje Data Definition Language (DDL) oraz procedury pamiętane warto
umieszczać w skryptach SQL (są to zwykłe pliki tekstowe zawierające ciągi
poleceń SQL). Warto przypomnieć, że powinny one zawierać wszystkie niezbędne

Rozdział 16 Delphi w środowisku Oracle

501

instrukcje

CONNECT i SET TERM

. Aby wykonać skrypt Oracle SQL należy

kliknąć opcję

Run

ISQL Script

z menu

File

programu SQL*PLUS. Listing 16.1

jest przykładem takiego skryptu

listing 16.1. Procedura pamiętana w

skrypcie SQL.

CONNECT SCOTT/TIGER

SPOOL PRICE
PROMPT BULDING PROCEDURE increaseprices
CREATE OR REPLACE PROCEDURE increaseprices (increacepercent

➥

number)

AS
BEGIN
UPDATE ITEM set Price=Price+(Price*(increasepercent /100));
END;
/

SPOOL OFF
EXIT;

Użyta w skrypcie instrukcja

SPOOL

kieruje dane wyjściowe do pliku. Warto też

zwrócić uwagę na instrukcję

PROMPT

, która powoduje wyświetlenie podanego

tekstu na ekranie. Dobrym nawykiem przy pisaniu skryptów jest bowiem
umieszczanie na ekranie napisów informujących użytkownika o

aktualnie

wykonywanych operacjach.

Uruchamianie procedur pamiętanych

Procedurę pamiętaną można uruchomić w Oracle następująco:

Increaseprices(5);

Procedury zdarzeñ (Triggers)

Procedury zdarzeń są to (podobnie jak procedury pamiętane) podprogramy SQL,
uruchamiane w

momencie, gdy dane z

danej tabeli są wprowadzane,

modyfikowane lub usuwane. Można skojarzyć procedurę ze specyficzną operacją
(zdarzeniem) dokonywaną na tabeli: wstawianiem wiersza, modyfikacją lub
usuwaniem. Oto odpowiedni przykład w Oracle SQL:

CREATE TRIGGER SALEDelete BEFORE DELETE
ON CUSTOMER
BEGIN

DELETE FROM SALE

WHERE

CustomerNumber=OLD.CustomerNUmber;

END

502

Część III

Ta procedura zdarzenia usuwa transakcje danego klienta z tabeli SALE, gdy jego
rekord jest usuwany z tabeli CUSTOMER (usuwanie kaskadowe). Operacja
usuwania z tabeli powoduje "kaskadowe" kasowanie danych w innych tabelach za
pomocą wspólnego klucza.

Warto zwrócić uwagę na zmienną kontekstową

OLD. OLD

odwołuje się do

bieżącej wartości kolumny w danym wierszu przed wykonaniem operacji

UPDATE

lub

DELETE

. Zmienna kontekstowa

NEW

odwołuje się natomiast do nowej

wartości, która zostanie wprowadzona po wykonaniu

INSERT

lub

UPDATE

Istotną funkcję pełni też słowo kluczowe

BEFORE

. Procedura zdarzenia może być

bowiem uruchomiona przed (before) lub po (after)

INSERT

UPDATE

DELETE

Kursory (Cursors)

Stosowanie kursorów jest związane z przetwarzaniem wierszowym. 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 definiowania
własnych kursorów. Jednak niekiedy mogą one okazać się użyteczne
w procedurach pamiętanych.

Są cztery podstawowe operacje wykonywane na kursorach: deklarowanie (ang.
declare), otwarcie (ang. open), pobranie (ang. fetch), zamknięcie (ang. close).
Można je 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 poprzez kursor, musi on być najpierw otwarty.
Aby przygotować zapytanie zapisane w definicji kursora powinniśmy skorzystać
z 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 wywołanie

FETCH

pozwala uzyskać następny wiersz w zbiorze. Oracle

posiada tylko kursory jednokierunkowe. Aby przejść do wierszy poprzednich
należy kursor zamknąć (

) i ponownie otworzyć (

OPEN

Rozdział 16 Delphi w środowisku Oracle

503

UWAGA:

Mimo że Oracle 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 - w obiektach

TDataSets

, takich jak

TQuery

TTable

.- możliwe jest

przewijanie w obu kierunkach.

Wiersze zwracane przez modyfikowalny kursor mogą być zmodyfikowane 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. Inne
pola niepotrzebnie wiążą zasoby serwera.

Aby zmodyfikować lub usunąć bieżący wiersz modyfikowalnego kursora,
powinniśmy 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, powinniśmy go zamknąć instrukcją

Zamknięcie kursora zwalnia wszystkie zasoby systemowe, z których on korzystał.
Oto przykład:

CLOSE CUSTOMER_UPDATE