Darmowy fragment publikacji:
IDZ DO
IDZ DO
PRZYK£ADOWY ROZDZIA£
PRZYK£ADOWY ROZDZIA£
SPIS TREĎCI
SPIS TREĎCI
KATALOG KSI¥¯EK
KATALOG KSI¥¯EK
KATALOG ONLINE
KATALOG ONLINE
ZAMÓW DRUKOWANY KATALOG
ZAMÓW DRUKOWANY KATALOG
TWÓJ KOSZYK
TWÓJ KOSZYK
DODAJ DO KOSZYKA
DODAJ DO KOSZYKA
CENNIK I INFORMACJE
CENNIK I INFORMACJE
ZAMÓW INFORMACJE
ZAMÓW INFORMACJE
O NOWOĎCIACH
O NOWOĎCIACH
ZAMÓW CENNIK
ZAMÓW CENNIK
CZYTELNIA
CZYTELNIA
FRAGMENTY KSI¥¯EK ONLINE
FRAGMENTY KSI¥¯EK ONLINE
Wydawnictwo Helion
ul. Chopina 6
44-100 Gliwice
tel. (32)230-98-63
e-mail: helion@helion.pl
Oracle PL/SQL.
Wprowadzenie
Oracle PL/SQL. Wprowadzenie
Autorzy: Bill Pribyl, Steven Feuerstein
T³umaczenie: Bart³omiej Garbacz
ISBN: 83-7197-727-1
Tytu³ orygina³u: Learning Oracle PL/SQL
Format: B5, stron: 412
Przyk³ady na ftp: 118 kB
PL-SQL — jêzyk programowania systemu Oracle, przeznaczony do tworzenia procedur
magazynowanych — zapewnia ogromne mo¿liwoġci pisz¹cym oprogramowanie baz
danych. PL/SQL rozszerza standard jêzyka relacyjnych baz danych SQL poprzez
umo¿liwienie korzystania z takich konstrukcji, jak: pêtle, instrukcje IF-THEN, z³o¿one
struktury danych czy szerokie mo¿liwoġci kontroli operacji transakcyjnych. Wszystkie
z nich s¹ ġciġle zintegrowane z serwerem bazy danych Oracle.
„Oracle PL/SQL. Wprowadzenie” daje Czytelnikowi mo¿liwoġæ pe³nego zrozumienia
jêzyka PL/SQL bez wzglêdu na to, czy jest pocz¹tkuj¹cym, czy doġwiadczonym
programist¹. W niniejszej ksi¹¿ce przedstawiono nastêpuj¹ce zagadnienia:
• cechy jêzyka PL/SQL i korzyġci wynikaj¹cych z jego u¿ywania;
• sk³adnia i przyk³ady zastosowania wszystkich g³ównych konstrukcji jêzyka;
• tworzenie i wykorzystywanie procedur, funkcji oraz pakietów magazynowanych;
• tworzenie aplikacji opartych na sieci Internet;
• zabezpieczanie programów w jêzyku PL/SQL przed atakami z zewn¹trz;
• korzyġci wynikaj¹ce z wykorzystania narzêdzi wspomagaj¹cych programowanie,
• wykorzystanie jêzyka PL/SQL do programowania zadañ zwi¹zanych z u¿yciem
poczty elektronicznej, jêzyka Java oraz sieci Internet.
pochodz¹cych od innych dostawców;
„Oracle PL/SQL. Wprowadzenie” zawiera szczegó³owy opis konstrukcji jêzyka we
wszystkich wersjach od Oracle7 do Oracle9i, podparty przyk³adami programów
dostêpnych tak¿e pod adresem http://oracle.oreilly.com. Autorami jej s¹ eksperci
jêzyka PL/SQL Bill Pribyl oraz Steven Feuerstein. Ksi¹¿ka daje solidne podstawy
ka¿demu programiġcie baz danych i administratorowi, który zmuszony jest do poznania
jêzyka PL/SQL.
5RKUVTGħEK
9UVúR
Rozdział 1. 2.53.ōRKGTYU\GKPHQTOCELG
Podstawy języka PL/SQL ...................................................j...................................................j.. 19
Zalety języka PL/SQL ...................................................j...................................................j....... 27
Wymagania dotyczące stosowania języka PL/SQL ...................................................j............. 33
Rozdział 2. 2QFUVCY[
Podstawy składni ...................................................j...................................................j............... 38
Pierwszy program w PL/SQL...................................................j............................................... 40
Wprowadzenie do budowy programu...................................................j................................... 44
Zmienne ...................................................j...................................................j............................. 48
Podstawowe operatory...................................................j...................................................j....... 56
Wyrażenia warunkowe ...................................................j...................................................j......63
Instrukcje wykonywania w pętlach ...................................................j...................................... 67
Formatowanie kodu: wymagania i wskazówki ...................................................j.................... 73
Podstawy bardziej złożonych zagadnień ...................................................j.............................. 75
Rozdział 3. 2TQITCOQYCPKG
Informacje o programie przykładowym ...................................................j............................... 81
Pierwsze zadanie programistyczne...................................................j....................................... 83
Pobieranie informacji o liczbie książek za pomocą funkcji ...................................................j. 97
Tworzenie elastycznego kodu...................................................j............................................. 102
Wykorzystanie pakietów PL/SQL w celu organizacji kodu.................................................. 108
Przejście na wyższy poziom ...................................................j............................................... 117
Dalsza droga ...................................................j...................................................j.................... 122
5RKUVTGħEK
Rozdział 4. +PVGTPGV
Wprowadzenie do HTML...................................................j...................................................j 124
Tworzenie stron internetowych za pomocą języka PL/SQL ................................................. 134
Inne zagadnienia ...................................................j...................................................j.............. 167
Rozdział 5. 2QDKGTCPKGFCP[EJ
Wprowadzenie ...................................................j...................................................j................. 170
Prosta metoda: pobierania danych z jednego wiersza ...................................................j........ 170
Pobieranie wielu wierszy za pomocą kursora...................................................j..................... 172
Prezentowanie wyników zapytania na stronie WWW ...................................................j....... 184
Tworzenie strony WWW służącej do wyszukiwania
za pomocą mechanizmu dynamicznego SQL ...................................................j.................. 188
Zaawansowane zagadnienia związane z pobieraniem danych .............................................. 205
Rozdział 6. 7VT\[O[YCPKGRQT\æFMW
Organizowanie kodu...................................................j...................................................j........ 215
Narzędzia pomagające w efektywnym programowaniu...................................................j..... 228
Rozdział 7. $G\RKGE\GēUVYQ
Podstawy bezpieczeństwa w systemie Oracle ...................................................j.................... 246
Organizowanie kont w celu zwiększenia poziomu zabezpieczeń ......................................... 255
Analiza wymagań systemu bibliotecznego...................................................j......................... 267
Śledzenie zmian w bazie danych ...................................................j........................................ 273
Szczególne kwestie bezpieczeństwa związane z programowaniem w PL/SQL ................... 281
Rozdział 8. -QOWPKMCELC\GħYKCVGO\GYPúVT\P[O
Wysyłanie wiadomości pocztą elektroniczną za pomocą PL/SQL ....................................... 288
Wykorzystanie narzędzia służącego do wysyłania wiadomoścji elektronicznych
w systemie bibliotecznym ...................................................j................................................ 293
Odbieranie wiadomości z poziomu bazy danych ...................................................j............... 296
Pobieranie danych ze zdalnych stron internetowych...................................................j.......... 305
Integracja z innymi językami programowania ...................................................j................... 317
Rozdział 9. 6TWFPKGLU\G\CICFPKGPKCKKPPGMYGUVKG
Cykle istnienia oprogramowania ...................................................j........................................ 324
Listy obiektów (kolekcje) w języku PL/SQL ...................................................j..................... 326
Pakiety obsługi wyjątków...................................................j...................................................j 339
5RKUVTGħEK
Kontrola transakcji...................................................j...................................................j........... 343
Kompilator PL/SQL...................................................j...................................................j......... 349
Zarządzanie uprawnieniami czytelników i bibliotekarzy...................................................j... 352
Inne cechy PL/SQL...................................................j...................................................j.......... 372
2QUđQYKG
Programowanie a bazy danych ...................................................j........................................... 381
Fakty ...................................................j...................................................j................................ 381
5đQYPKE\GM
5MQTQYKF\
2TQITCOQYCPKG
W niniejszym rozdziale omówiono następujące zagadnimenia:
•
Informacje o programie przykładowym
•
Pierwsze zadanie programistyczne
•
Pobieranie informacji o liczbie książek za pomocą feunkcji
•
Tworzenie elastycznego kodu
•
Wykorzystanie pakietów PL/SQL w celu organizacji kodu
•
Przejście na wyższy poziom
•
Dalsza droga
Po zapoznaniu się z podstawami języka PL/SQL Czytelnik jest przygotowany do tworzenia pro-
gramów bardziej rozbudowanych niż zwykłe wyświetlanie komunikatu. W niniejszym rozdziale
przedstawiono sposób rozpoczęcia tworzenia aplikacji obsługi katalogu biblioteki. W kolejnych czę-
ściach książki opisano sposób jej dalszej rozbudowy. Nowymi elementami języka PL/SQL, które
przedstawiono w niniejszym rozdziale, są procedury (procedures), funkcje (functions) oraz pakiety
(packages). Czytelnik dowie się, do czego te elementy służą, jak je konstruować oraz w jaki spo-
sób używać w celu osiągnięcia wymaganych celów.
+PHQTOCELGQRTQITCOKGRT\[MđCFQY[O
Przykładowe zadanie programistyczne, opisane w niniejszej książce, polega na próbie utworzenia
systemu, który służyłby do katalogowania oraz wyszukiwania książek w bibliotece — jest to rodzaj
elektronicznego katalogu. W przypadku tej hipotetycznej biblioteki zakłada się, że wszelkie dane
operacyjne znajdują się w bazie danych Oracle. Istnieje więcej niż jeden sposób gromadzenia danych
— dotyczących tytułów, autorów itp. — w bazie danych. Jednym z nich jest ręczne wpisywanie
danych przez bibliotekarza. W kolejnych rozdziałach zostaną opisane metody automatycznego ła-
dowania danych z odległych źródeł oraz metody wyszukiwania przez użytkownika danych znajdują-
cych się w katalogu.
4Q\F\KCđ2TQITCOQYCPKG
Istnieje konieczność spełnienia dwóch wymagań o podstawowym znaczeniu dla działania opisywa-
nej aplikacji:
•
Należy umożliwić tworzenie wpisów do katalogu dla kaeżdej nowej książki.
•
Należy udostępnić możliwość określania liczby egzemplarezy danej książki, znajdujących się
w zasobach biblioteki.
W celu spełnienia pierwszego wymagania należy utworzyć procedurę PL/SQL, służącą do wprowa-
dzania danych do bazy danych. Program umożliwiający spełnienie drugiego warunku wymaga za-
stosowania funkcji PL/SQL. Przed opisaniem sposobu tworzenia tych elementów należy najpierw
przedstawić metody projektowania struktury samej baezy danych.
/QFGNFCP[EJ
Podobnie jak w przypadku większości projektów tworzonych przez programistów PL/SQL, struktura
bazy danych dotyczących książek w bibliotece została wcześniej zaprojektowana i utworzona na
podstawie wymagań postawionych przez przyszłego użytkownika. Podzbiór logicznego projektu
bazy danych, związany ze wspomnianymi wcześniej wymaganiami, można ograniczyć do infor-
macji o każdym egzemplarzu książki w bibliotece. Rysunek 3.1 przedstawia tak zwany diagram
związków encji (entity-relationship diagram, ERD).
Rysunek 3.1. Zależność między wpisem (informacją) d.otyczącym książki a jej fizycznie istniejącymi egzemp.larzami
Diagramy takie w zwięzłej i uproszczonej formie prezentują informacje o realnym świecie. Opisane
w odpowiedni sposób prostokąty są encjami (entities) bazy danych, a linie pomiędzy nimi ozna-
czają związki (relationships) pomiędzy encjami. Relacyjne bazy danych przedstawiają rzeczywi-
stość w formie zbioru struktur danych (które przechowują informacje o pewnych obiektach) oraz zbio-
ru metod (które definiują powiązania między obiektami). Encja book reprezentuje podstawowe
informacje o książce (tytuł, autor itd.), które zawierea każda biblioteka. Encja book_copy zawiera
informacje o fizycznie istniejących egzemplarzach daenej książki.
Pewne zdziwienie może wywołać fakt, że całość rozdzielono na dwie encje zamiast użycia jednej. Wy-
korzystanie tylko jednej encji spowodowałaby jednak problemy w późniejszym czasie, ponieważ wy-
stąpiłaby konieczność kopiowania informacji dotyczących książki razem z informacjami dotyczącymi
jej pojedynczych egzemplarzy, co z pewnością byłoby niewłaściwym sposobem wykorzystania zaso-
bów komputera i wysiłku ludzkiego. Pełna dyskusja na temat normalizacji (normalization) baz danych
(procesu organizowania danych w postaci tabel zgodnie z ich wewnętrzną strukturą) wykracza poza
zakres treści niniejszej książki. Warto jednak podkreślić, że główna idea polega na przechowywaniu
ważnych informacji w jednym i tylko w jednym miejscu. Każdy kolejny egzemplarz danej książki wy-
maga utworzenia jedynie dodatkowego rekordu zawierającego identyfikator, który w powyższym przy-
padku jest numerem identyfikacyjnym pochodzącym z seamoprzylepnej naklejki z kodem kreskowym.
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
W tym miejscu należy wyjaśnić znaczenie linii przedstawiających relację, pokazaną na rysunku 3.1.
Z treści tego rysunku wynikają następujące fakty dotyeczące realnej sytuacji:
•
Każdy egzemplarz danej książki jest egzemplarzem tyeko jednej książki.
•
Każda książka może posiadać jeden lub większą liczbę sewoich egzemplarzy.
Relacja taka jest znana jako relacja jeden-do-wielu (one-to-many relationship): jedna pozycja książ-
kowa i wiele egzemplarzy danej książki.
Fakty powyższe wydają się być truizmem. Jednak przed przystąpieniem do właściwego projektowa-
nia bazy danych należy dokonać analizy rozwiązywanegeo problemu na takim właśnie, podstawowym
poziomie. Rozłożenie informacji dotyczących projektowanej aplikacji na szereg pozornie banalnych
faktów i ustalenie wszystkich podstawowych związków pomiędzy jej elementami z pewnością ułatwi
pracę podczas tworzenia kodu programu.
2TQLGMVQYCPKGHK\[E\PGLUVTWMVWT[DC\[FCP[EJ
Faktyczna struktura bazy danych odzwierciedla model związków encji — każda encja odpowiada ta-
beli w bazie danych. Poniżej przedstawiono kod w języku SQL, służący do utworzenia takiej tabeli:
CREATE TABLE books (
isbn VARCHAR2(13) NOT NULL PRIMARY KEY,
title VARCHAR2(200),
summary VARCHAR2(2000),
author VARCHAR2(200),
date_published DATE,
page_count NUMBER
);
CREATE TABLE book_copies(
barcode_id VARCHAR2(100) NOT NULL PRIMARY KEY,
isbn VARCHAR2(13) NOT NULL,
CONSTRAINT book_copies_isbn_fk FOREIGN KEY (isbn) REFEREoNCES books (isbn)
);
Z powodów zrozumiałych dla specjalistów od modelowania danych, encjom nadaje się nazwy w for-
mie rzeczowników w liczbie pojedynczej (book, book_copy), natomiast tabelom — w liczbie
mnogiej (books, book_copies). Ponadto warto wykonać graficzną reprezentację tabeli — na
rysunku 3.2 przedstawiono możliwą postać zdefiniowanyceh tabel zapełnionych pewnymi danymi.
Zapoznawszy się z danymi zawartymi w tabelach, Czytelnik z pewnością zwrócił uwagę na pewne
problemy. Przykładowo, dane w kolumnie author są nieprawidłowe i nie jest możliwe poprawne
przechowanie informacji o kilku autorach jednej książki. W dalszej części niniejszej książki zosta-
nie opisany sposób poprawienia tych wad.
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
Pierwszym, przykładowym zadaniem będzie utworzenie programu w języku PL/SQL, który posłu-
ży do dodawania nowych książek do bazy danych. Oczywiście, zamiast takiego programu można
po prostu wykonać instrukcję INSERT (lub dwie takie instrukcje) języka SQL:
4Q\F\KCđ2TQITCOQYCPKG
Rysunek 3.2. Przykład danych w postaci relacyjnej, rozbi.tych na wiersze i kolumny
INSERT INTO books (isbn, title, author)
VALUES ( 0-596-00180-0 , Oracle PL/SQL. Wprowadzenie ,
Bill Pribyl, Steven Feuerstein );
W związku z powyższym Czytelnik z pewnością zastanawia się nad sensem tworzenia programu
w języku PL/SQL.
7\CUCFPKGPKGRTQLGMVW
Należy założyć, że istnieje konieczność zapewnienia stosowania dwóch sposobów dodawania ksią-
żek do katalogu: po pierwsze w sposób interaktywny, poprzez ręczne wpisanie danych, po drugie
w sposób automatyczny, pobierając informacje o książce z innej bazy danych. Nasuwa się zatem py-
tanie, czy w takiej sytuacji należy skopiować instrukcje INSERT do dwóch programów. Jednakże
później może pojawić się potrzeba napisania trzeciego programu, który dodawałby informacje o książ-
kach, przykładowo, odczytując je z płyty CD-ROM. A zatem należałoby uwzględnić kolejne ko-
piowanie instrukcji INSERT. Zakładając jednak, że zaistnieje potrzeba zmiany struktury tabel,
trzeba by wówczas wszystkie dotąd utworzone programey oddzielnie uaktualnić.
Istnieje kilka powodów, dla których instrukcje INSERT warto umieścić w programie PL/SQL. Poni-
żej wymieniono najważniejsze z nich:
•
Umożliwia to ograniczenie, a nawet wyeliminowanie męcezącej pracy związanej z aktualizacją
oprogramowania po zmianie struktury bazy danych, podeczas wykonywania której istnieje
możliwość popełniania błędów.
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
•
Pozwala to na zwiększenie wydajności działania serweera bazy danych.
•
Pozwala to umieścić rozwiązanie problemu programistyecznego w tylko jednym miejscu.
Tworzenie poprawnych instrukcji w języku SQL może wymagać interpretowania oraz zapisywa-
nia w formie kodu wielu skomplikowanych reguł zarządzania. Ewentualna konieczność dokonania
zmian we wszystkich napisanych programach oddzielnie oznacza bezzasadne marnotrawstwo cza-
su i wysiłku. Ogólna zasada mówi, że:
Instrukcje SQL należy umieszczać w jednym, możliwym do fwielokrotnego wykorzystania,
programie napisanym w języku PL/SQL, a nie kopiować je w fwielu aplikacjach.
Nawet jeśli Czytelnik jest jedynym programistą w swoim miejscu pracy, powinien trzymać się tej
zasady. Nie ogranicza się ona zresztą tylko do wykorzystania języka SQL — wszelkie zadania
programistyczne powinno się zapisywać tylko raz, a potem w razie konieczności jedynie wykonywać
odpowiedni program. Przez definiowanie czynności wykonywanych przez każdą jednostkę progra-
mu, którą można wielokrotnie wywoływać, można utworzyć własny interfejs tworzenia oprogramo-
wania (application programming interface, API).
Tak prosty, jak to możliwe, ale nie prostszy
Prawdziwy bibliotekarz uznałby niniejszy model za banalnie prosty, nawet nie biorąc pod
uwagę nie uwzględnionych w nim kwestii związanych zb czytelnikami, operacjami wypożyczenia/
oddawania i kupowania książek. W rzeczywistości funkcjonowanie biblioteki jest znacznie bar-
dziej skomplikowane:
•
Poza przechowywaniem książek biblioteki magazynują także gazety i czasopisma, nagrania
muzyczne i kasety wideo.
•
Wiele dzieł, na przykład starsze książki, nie posiada numeru ISBN (International Standard
Book Number), co ogranicza jego zastosowanie jako jednoznacznego identyfikatora.
•
Dzieła mogą mieć różne tytuły i wielu autorów.
•
Należy przechowywać o wiele więcej informacji: o działach, wydawcach, ilustratorach,
wydaniach, dziełach wielotomowych i pochodnych.
•
Biblioteki zazwyczaj udostępniają sobie nawzajem swoje katalogi drogą elektronicznej
wymiany.
Podobnych przykładów jest więcej, dlatego należy wyjaśnić powody uwzględnienia w niniejszej
książce tak prostego przykładu.
Wykonanie bazy danych, której schemat pokazano na rysunku 3.1, w zasadzie jest zbyt skom-
plikowanym zadaniem dla zupełnie początkujących programistów. Relacja jeden do wielu stanowi
podstawę większości aspektów projektowania baz danych (tyle że w realnych sytuacjach chodzi
o wiele takich relacji) oraz programowania w języku PL/SQL. W dalszej części niniejszej książki
zostanie opisany sposób rozszerzania funkcjonalności projektowanej bazy danych, a także spo-
soby wykorzystania PL/SQL w bardziej „realnych” zadaniach.
Innym powodem wprowadzenia pewnych uproszczeń jest fakt, że nauka nowego materiału
przychodzi najłatwiej, jeśli można studiować jedno zagadnienie w jednym czasie. W przeciwnym
razie nauka przychodzi dużo trudniej.
4Q\F\KCđ2TQITCOQYCPKG
W niniejszym rozdziale przedstawiono informacje o sposobach lokalizowania kodu SQL (stosowanie
kodu w jednym miejscu) za pomocą obwolut tabel (table wrappers), które są programami odpo-
wiedzialnymi za wszelkie operacje zmiany zawartoście każdej z tabel bazy danych.
+FGPV[HKMCELCFCP[EJYGLħEKQY[EJRTQEGUWRT\GVYCT\ECPKC
QTC\FCP[EJY[LħEKQY[EJ
Zapewne większość programistów, niezależnie od stopnia zaawansowania, odczuwa niepewność
w sytuacji, gdy ma napisać program od podstaw. Z reguły w takiej sytuacji można rozpocząć pracę
od przerabiania już istniejących lub przykładowych programów. W tym przypadku zostanie przed-
stawiony sposób tworzenia programu od samego początku, ale prawdopodobnie Czytelnik rzadko
będzie do tego zmuszony.
Godnym polecenia zwyczajem jest rozpoczęcie tworzenia programu od narysowania schematu. Ry-
sunek 3.3 przedstawia możliwą reprezentację graficzną omawianego programu oraz jego danych wej-
ściowych i wyjściowych.
Rysunek 3.3. Schemat programu służącego do dodawania ks.iążek do bazy danych
W przeciwieństwie do wcześniej prezentowanego diagramu związków i encji, powyższy diagram
nie został skonstruowany zgodnie z jakimiś zasadami projektowania. Najważniejszą rzeczą jest
przedstawienie najbardziej znaczących elementów. Z powyższego rysunku wynika, że trzeba skon-
struować program zdolny do pobierania pewnych informacji, a następnie do umieszczania ich w ta-
belach bazy danych. Choć zapewne nie jest to oczywiste, uwzględniono możliwość poszerzenia funk-
cjonalności aplikacji w przyszłości.
Ogólnie rzecz biorąc, jednostka programu powinna służfyć do wykonania jednego, konkretnego
zadania i należy zapewnić prawidłowe działanie tej jedfnostki w swoim zakresie. Wskazane jest,
aby miała on niewielki rozmiar — ułatwi to jej zapis i późniejsze modyfikacje.
Trzeba jeszcze znaleźć odpowiedź na pytanie, jakiej konstrukcji języka PL/SQL należałoby użyć
w celu zdefiniowania operacji dodawania książki add_book. Z uwagi na fakt, że program nie bę-
dzie zwracał żadnej wartości, najbardziej rozsądnym rozwiązaniem jest zastosowanie procedury.
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
CUVQUQYCPKGRTQEGFWT[OCIC\[PQYCPGLYEGNWFQFCPKCEMUKæľMK
W tym podrozdziale zostanie przedstawiony sposób napisania procedury, która ma wstawiać infor-
macje na temat danej książki do bazy danych. Procedura taka może być nazwana obwolutą. Można
też powiedzieć, używając terminologii pochodzącej z języka greckiego, że będą wykorzystywane
abstrakcje (abstraction), procesy enkapsulacji (encapsulation) oraz ukrywania informacji (informa-
tion hiding) — definicje wymienionych pojęć znajdują się w Słowniczku. Na rysunku 3.4 przedsta-
wiono więcej szczegółów dotyczących takiego rozwiązania.
Rysunek 3.4. Jedynie z poziomu programu PL/SQL będzie możl.iwe dodawanie informacji o książce do bazy danych
Użytkownicy gotowego programu nie muszą wiedzieć na jego temat zbyt wiele, jednak twórca danej
aplikacji musi z pewnością posiadać gruntowną wiedzę dotyczącą struktury bazy danych. Dlatego
też pierwszą rzeczą, jaką należy wykonać, jest określenie tabel i ich kolumn związanych z procesem
wykonania reguły zarządzania (w tym przypadku – dodania książki). Trzeba odpowiedzieć sobie na
pytanie, jakie informacje są potrzebne do manipuloweania zawartością tych tabel.
#PCNK\CRTQLGMVW
W celu określenia istotnych dla wykonania planowanej operacji tabel i kolumn trzeba przyjrzeć się
projektowi bazy danych (przedstawionemu we wcześniejszej części niniejszego rozdziału). Listę tych
kolumn można z łatwością utworzyć za pomocą instrukcji DESCRIBE, dostępnej w SQL*Plus (w for-
mie skróconej DESC):
SQL DESC ksiazki
Name Null? Toype
-------------------------------------------- -------- -----------------------
ISBN NOT NoULL VARCHAR2(13)
TITLE o VARCHAR2(200)
SUMMARY o VARCHAR2(2000)
AUTHOR o VARCHAR2(200)
DATE_PUBLISHED o DATE
PAGE_COUNT o NUMBER
SQL DESC egzemplarze_ksiazki
Name Null? Toype
-------------------------------------------- -------- -----------------------
BARCODE_ID NOT NULoL VARCHAR2(100)
ISBN o VARCHAR2(13)
Po przeanalizowaniu listy kolumn można przyjąć, że większość zawartych w nich informacji powin-
na być znana osobie wykonującej katalogowanie i dlatego nie trzeba niczego syntetyzować, spraw-
dzać lub obliczać. Procedura będzie więc bardzo prosta.
4Q\F\KCđ2TQITCOQYCPKG
Podstawowym problemem do rozwiązania jest podjęcie decyzji, czy użytkownik będzie wstawiał
dane do obydwóch tabel jednocześnie, czy też oddzielnie. Na tym etapie pracy odpowiedź na to
pytanie jest jeszcze nieznana. Najprawdopodobniej w bibliotekach odbywa się to w ten sposób, że
informacje o książkach są dodawane do bazy danych w momencie pojawienia się pierwszego eg-
zemplarza. Dlatego też podczas dodawania danej książki po raz pierwszy wszystkie informacje
wymagane dla wypełnienia obydwóch tabel są od razu znane: od numeru ISBN po identyfikator
kodu kreskowego. Jednak istnieje także konieczność katalogowania nowych egzemplarzy książki
już znajdującej się w zbiorach biblioteki — tę potrzebę również należy uwzględnić podczas pla-
nowania programu.
Rozpoczynając opracowywanie nowego projektu wielu programistów zapisuje tzw. pseudokod
w postaci zwykłych zdań, które opisują z grubsza działanie programu. W tym przypadku można
by napisać:
Sprawdź, czy dane wejściowe są prawidłowe.
Wstaw nowy rekord do tabeli books .
Wstaw nowy rekord do tabeli book_copies .
Kolejnym etapem będzie przedstawienie składni konstrukcji językowych potrzebnych do utworze-
nia procedury. Następnie zapisany pseudokod zostanie ezamieniony na konkretne instrukcje.
5MđCFPKCVYQT\GPKCRTQEGFWT[
Poniżej znajduje się opis różnych części składowych procedury. Zazwyczaj procedury tworzy się za
pomocą instrukcji o następującej składni:
CREATE [ OR REPLACE ] PROCEDURE nazwa_procedury
(parametr1 TRYB TYP_DANYCH [ DEFAULT wyrażenie ],
parametr2 TRYB TYP_DANYCH [ DEFAULT wyrażenie ],
...)
AS
[ zmienna1 TYP_DANYCH;
zmienna2 TYP_DANYCH;
... ]
BEGIN
instrukcje_wykonawcze
[ EXCEPTION
WHEN nazwa_wyjątku
THEN
instrukcje_wykonawcze ]
END;
/
Powyższy wzorzec zawiera kombinację słów kluczowych języka PL/SQL (pisane dużymi literami
i nie kursywą) oraz wyrażeń do zastąpienia (pisane kuersywą) w kodzie tworzonego programu.
CREATE [ OR REPLACE ]
Jest to szczególna instrukcja SQL, służąca do utworzeneia procedury. Fraza OR REPLACE (lub
zastąp) jest opcjonalna i pozwala na uniknięcie koniecznoścei usuwania już istniejącej procedury
w przypadku tworzenia jej nowej wersji. Zastosowaniee wyrażenia OR REPLACE zachowuje
także wszelkie synonimy (synonyms) oraz granty (grants), jakie zostały uprzednio utworzone,
a są zależne od działania procedury. Jest to duża zaeleta.
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
PROCEDURE nazwa_procedury
W sekcji nagłówka podaje rodzaj tworzonej jednostkie programowej (w tym wypadku — procedura)
oraz nadaje się jej nazwę.
parametr1 TRYB TYP_DANYCH [ DEFAULT wyrażenie ]
Aby umożliwić użytkownikowi podawanie parametrów wywołania procedury, należy utworzyć
listę definicji parametrów oddzielonych przecinkami, ea całą listę objąć w nawiasy. TRYB może
mieć wartość: IN, OUT lub IN OUT. Poniżej znajduje się opis wszystkich opcji.
IN
Słowo kluczowe oznaczające tryb tylko do odczytu. Wyewołujący procedurę podaje wartość
parametru, a PL/SQL nie pozwala na jej zmianę wewnątrz perogramu.
OUT
Słowo kluczowe oznaczające tryb tylko do zapisu. Oznaecza to, że procedura nadaje
parametrowi pewną wartość, która jest odczytywana przeez program wywołujący. Podanie
jakiejkolwiek wartości takiego parametru podczas wyewołania procedury jest ignorowane.
IN OUT
Słowo kluczowe oznaczające tryb do odczytu lub zapiesu. Słowo to jest używane w sytuacji,
gdy wartość zmiennej, przekazywanej do procedury jakoe parametr, ma być zarówno
odczytywana, jak i zmieniana, a następnie zwracana doe programu wywołującego.
TYP_DANYCH
Znaczenie tego parametru przedstawiono w rozdziale e2. — dopuszczalnymi wartościami są
na przykład: NUMBER, INTEGER, VARCHAR2, DATE. Jedyna różnica polega na tym,
że w tym miejscu należy podać jedynie rodzaj typu, bez jego dokładnej specyfikacji. Innymi
słowy, należy użyć VARCHAR2 zamiast VARCHAR2(30) i NUMBER zamiast NUMBER(10,2).
DEFAULT wyrażenie
Pozwala na przypisanie parametrowi wartości domyślneej w przypadku, gdy wywołanie procedury
jej nie określa. Można także użyć symbolu „:=” (dwukropek i znak równości) zamiast słowa
kluczowego DEFAULT.
AS
Słowo kluczowe AS oddziela nagłówek od reszty jednostki programowej.e Opcjonalnie można
użyć słowa kluczowego IS, które jest równoważne AS.
BEGIN..END
Słowa BEGIN i END oddzielają zwykłe, czyli wykonawcze instrukcje od ereszty programu.
EXCEPTION
Oznacza początek kodu obsługi wyjątku — tej części proegramu, która jest wykonywana tylko
w przypadku przechwycenia wyjątku w sekcji wykonawcezej. Wszystkie elementy umieszczone
po słowie kluczowym EXCEPTION i przed instrukcją END są częścią obsługi wyjątku.
4Q\F\KCđ2TQITCOQYCPKG
WHEN nazwa_wyjątku THEN instrukcje_wykonawcze
Możliwym do wystąpienia sytuacjom błędnego działaniae programu zazwyczaj są nadawane
nazwy — albo przez system Oracle, albo przez programiestę. Dzięki temu można w kodzie
programu zapisać instrukcje „wychwytujące” te sytuacjee i umożliwić odpowiednią na nie
reakcję, którą jest wykonanie pewnego fragmentu kodue. Jeśli taka nazwa jest nieznana lub
w celu wychwytywania błędów, które nie zostały nazwanee, można posłużyć się słowem
OTHERS, które dotyczy wszystkich sytuacji wyjątkowych. W zaepisie wyglądałoby
to następująco: WHEN OTHERS THEN....
Pewne elementy kodu procedury są opcjonalne. Najprostsza, możliwa do utworzenia procedura po-
siada następującą formę:
CREATE PROCEDURE nie_rob_nic AS
BEGIN
NULL;
END;
Z powyższego wynika, że parametry, zmienne i kod obsłeugi wyjątków są opcjonalne. Słowo kluczowe
NULL zostało tutaj zastosowane jako instrukcja wykonawceza. Oznacza ona tyle, co „nie rób nic”.
2TQEGFWTCCFFADQQM
Korzystając z potrzebnych elementów podanego wcześniej wzorca można zapisać pseudokod w for-
mie prawdziwego kodu:
CREATE OR REPLACE PROCEDURE add_book (isbn_in IN VARCHAoR2,
barcode_id_in IN VARCHAR2, title_in IN VARCHAR2, author_in oIN VARCHAR2,
page_count_in IN NUMBER, summary_in IN VARCHAR2 DEFAULT NULLo,
date_published_in IN DATE DEFAULT NULL)
AS
BEGIN
/* sprawdzenie poprawności danych wejściowych */
IF isbn_in IS NULL
THEN
RAISE VALUE_ERROR;
END IF;
/* wstawienie rekordu do tabeli books */
INSERT INTO books (isbn, title, summary, author, date_published,o
page_count)
VALUES (isbn_in, title_in, summary_in, author_in, date_published_in,o
page_count_in);
/* jeśli to konieczne, wstawienie rekordu do tabeli book_copies */
IF barcode_id_in IS NOT NULL
THEN
INSERT INTO book_copies (isbn, barcode_id)
VALUES (isbn_in, barcode_id_in);
END IF;
END add_book;
/
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
Poniżej znajduje się opis działania utworzonej proceedury.
Nazwa procedury i kolejność parametrów. Nazwą procedury jest wyrażenie czasownikowe, które
opisuje działanie tej procedury. Podano tu także parametry wejściowe — na każdą z kolumn tabel,
do których mają być wprowadzane dane, przypada jeden parametr. W celu wyeksponowania naj-
ważniejszych parametrów (isbn_in czy barcode_id_in) oraz w celu umieszczenia parame-
trów o wartościach domyślnych na końcu, zmieniono nieco ich kolejność w odniesieniu do kolej-
ności kolumn w tabelach.
Nazwy parametrów. Warto przyjąć pewną konwencję nazewnictwa w programowaniu i do nazw
parametrów dodać się nazwę ich trybu (IN, OUT lub IN OUT). Ponieważ wszystkie parametry
procedury add_book są parametrami w trybie IN, ich nazwy zawierają końcówkę _in. Taka
konwencja nadawania nazw nie jest obowiązkowa, ale jest pomocna w unikaniu konfliktów z na-
zwami kolumn podczas używania instrukcji SQL. Gdyby pozostały one identyczne, otrzymana in-
strukcja miałaby następującą postać:
INSERT INTO book_copies (barcode_id, isbn)
VALUES (barcode_id, isbn);
W takim przypadku odróżnianie nazwy kolumn od nazw zmiennych podczas odczytywania kodu
mogłoby okazać się problematyczne nawet dla doświadczonego programisty. Jednakże sama in-
strukcja zostałaby wykonana prawidłowo, gdyż PL/SQL zineterpretowałby wszystko następująco:
INSERT INTO book_copies (barcode_id, isbn) /* nazwy kolumn */
VALUES (barcode_id, isbn); /* zmienne PL/SQLo */
Jednakże instrukcja:
UPDATE ksiazki
SET summary = summary /* Błąd! */
WHERE isbn = isbn; /* Nie wolno tak robić! */
nie zostanie wykonana prawidłowo. PL/SQL zinterpretuje każde wystąpienie summary oraz isbn
jako nazwy kolumn.
Weryfikacja danych wejściowych. Zapis pierwszej linii zaprezentowanego wcześniej pseudoko-
du mówił: „Sprawdź, czy dane wejściowe są prawidłowe”. Konieczne jest jednak podjęcie jeszcze
kilku ustaleń. Przykładowo, można założyć, że użytkowneicy systemu będą domagać się minimalnych
wymagań ze strony procedury w momencie jej wywoływania. Może to oznaczać, że jedynym ab-
solutnie wymaganym parametrem tworzonej procedury jest ISBN. Stąd też sekcja weryfikacji da-
nych wejściowych została zapisana jako:
IF isbn_in IS NULL
THEN
RAISE VALUE_ERROR;
END IF;
Bez dokładniejszej znajomości powyższych identyfikatorów nie jest możliwe przeprowadzenie bar-
dziej wymyślnej weryfikacji poprawności danych wejściowych. Przedstawiony wyżej fragment kodu
oznacza, że brak ISBN powoduje zatrzymanie wykonywaniae programu.
4Q\F\KCđ2TQITCOQYCPKG
W dalszej części kodu jest także wykonywane sprawdzenie kodu kreskowego. Program został skon-
struowany w ten sposób, aby uniknąć wykonania niepoprawnej instrukcji INSERT, co spowodo-
wałoby błąd.
1DUđWICU[VWCELKY[LæVMQY[EJ
W rozdziale 2. wyjaśniono, że w razie wystąpienia błędu PL/SQL zatrzymuje wykonywanie progra-
mu za pomocą mechanizmu zwanego przechwytywaniem wyjątku (raising an exception). Jak wy-
nika z rysunku 3.5, instrukcja:
RAISE VALUE_ERROR
zatrzymuje wykonywanie instrukcji sekcji wykonawczej i przekazuje sterowanie do kodu obsługi
wyjątku. Jeśli taki kod nie istnieje, wyjątek zwraca pewien błąd do programu wywołującego, a ten
reaguje w określony przez programistę sposób.
Rysunek 3.5. Prosty przykład obsługi wyjątku
Wyjątek VALUE_EXCEPTION należy do grupy wyjątków wewnętrznych, które w pewnych sytu-
acjach są przechwytywane przez system Oracle. Program wywołujący procedurę add_book po-
winien umożliwiać obsługę wszelkich możliwych do przewidzenia wyjątków i na podstawie infor-
macji dotyczących napotkanego błędu zdecydować o dalszym funkcjonowaniu programu. W tym
przypadku pożądanym działaniem byłoby przerwanie procesu dodawania do bazy danych niekom-
pletnych informacji. Najlepiej byłoby, gdyby program wywołujący zwracał użytkownikowi infor-
mację o błędzie.
Być może Czytelnik zastanawia się na celowością podejmowania takich działań zamiast, przykła-
dowo, umożliwienia samej procedurze add_book wyświetlania komunikatu o napotkanym błę-
dzie. Celowość użycia mechanizmu wyjątków staje się jasna po uświadomieniu sobie, że przeka-
zanie obsługi sytuacji wyjątkowych do procedury ograniczyłoby w znacznym stopniu możliwości jej
ponownego wykorzystania. Jeśli procedura add_book obsługuje wyjątek i wyświetla komunikat
o błędzie, niemożliwe jest wykorzystanie jej przez program, dla którego działania takie komuni-
katy są niewskazane. Przykładowo, można założyć istnienie programu, który odczytuje informacje
o książkach z pliku i jednorazowo wprowadza do bazy danych informacje dotyczące tysięcy książek.
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
Program ten ma wywoływać procedurę add_book dla każdej kolejnej pozycji i w razie wystą-
pienia błędu zapamiętać informację o jego przyczynie, nie przerywając jednak swojego działania,
aż do zakończenia wprowadzania danych dotyczących ostatniej książki. Dopiero wówczas można
by przedstawić użytkownikowi podsumowanie informacji o napotkanych problemach. Wykorzysta-
nie procedury add_book w taki sposób jest możliwe jedynie poprzez propagację wyjątków na ze-
wnątrz — do programu wywołującego.
Uogólniając, jeśli istnieje prawdopodobieństwo napoątkania przez program błędu, którego
nie da się naprawić w prosty sposób, należy zapewniąć możliwość przechwycenia wyjątku.
W dalszej części niniejszej książki opisano wyjątki,ą które należy brać pod uwagę.
Jeżeli weryfikacja poprawności danych wejściowych przebiega pomyślnie, program kontynuuje swoje
działanie wykonując instrukcje INSERT języka SQL. Nie ma potrzeby zwracania jakichkolwiek
danych do programu wywołującego. Jednak gdyby zaszła taka konieczność, można uwzględnić
jedną z dwóch możliwości: zastosowanie funkcji zamiast procedury lub wykorzystanie parametrów
w trybie OUT. Więcej informacji Czytelnik znajdzie w dalszej częścei niniejszego rozdziału.
7ľ[EKGRTQEGFWT[YEGNWFQFCPKCMUKæľMKFQMCVCNQIW
Najłatwiejszym sposobem wywołania procedury z poziomu kodu PL/SQL jest napisanie jej nazwy
wraz z potrzebnymi argumentami, umieszczonymi w naweiasach i oddzielonymi przecinkami:
BEGIN
nazwa_procedury (argument1, argument2, ...);
END;
Przykładowo, aby dodać do katalogu nową książkę, należey napisać:
BEGIN
add_book( 1-56592-335-9 ,
100000001 ,
Programowanie w Oracle PL/SQL ,
Feuerstein, Steven, Bill Pribyl ,
987,
Kompendium informacji o języku PL/SQL,
|| wraz z przykładami i poradami na temat programowania. ,
TO_DATE( 01-WRZ-1997 , DD-MON-YYYY ));
END;
/
Takie wywołanie procedury spowoduje wstawienie po jednym rekordzie do tabel books oraz book_
copies. W powyższym przykładzie argumentami są tak zwane wyrażenia literalne, a PL/SQL
przekazuje te wartości do programu jako parametry wejściowe1, zgodnie z ich kolejnością. Ozna-
cza to, że zazwyczaj wartości trzeba podawać w takiej samej kolejności, w jakiej występują parame-
try w programie wywoływanym. Informacje te przedstawiono w poniższej tabeli:
1 Wyrażenie przekazywane przez program wywołujący nosią nazwę argumentu lub parametru aktualnego,
podczas gdy zmienne zdefiniowane w nagłówku programąu wywołującego są nazywane parametrami
formalnymi lub po prostu parametrami. W tym kontekście „formalny” oznacza „formujący” postać zmiennej.
4Q\F\KCđ2TQITCOQYCPKG
Pozycja Nazwa parametru
Typ danych
parametru
Wartość użyta w przykładowym wywołaniu
1
2
3
4
5
6
7
isbn_in
VARCHAR2
1-56592-335-9
barcode_id_in
VARCHAR2
100000001
title_in
author_in
VARCHAR2
Programowanie w Oracle PL/SQL
VARCHAR2
Feuerstein, Steven, Bill Pribyl
page_count_in
NUMBER
987
summary_in
VARCHAR2
Kompendium informacji o języku
PL/SQL wraz z przykładami i poradami
na temat programowania.
date_published_in DATE
TO_DATE( 01-WRZ-1997 , DD-MON-YYYY )
Warto zwrócić uwagę, że podane wartości są zgodne z typami danych, jakich wymaga procedura.
Oznacza to, że każdemu parametrowi typu VARCHAR2 odpowiada ciąg znakowy, parametrowi typu
NUMBER odpowiada seria cyfr. Czytelnik zapewne zauważył, że parametrowi typu DATE odpo-
wiada wartość o dość dziwnym wyglądzie.
2TCEC\V[RGO #6
Parametr date_published_in wymaga podania wartości typu DATE systemu Oracle. W rze-
czywistości jest to seria bitów o skomplikowanym formacie wewnętrznym — z pewnością nie jest
to zwykle używana kombinacja roku, miesiąca i dnia. Często stosowaną metodą otrzymania takiej
wartości jest wykorzystanie wewnętrznej funkcji systemu Oracle o nazwie TO_DATE. Podczas
wywoływania tej funkcji należy podać datę, zapisaną w formie ciągu znakowego (w tym przypadku
— 01-WRZ-1997 ), oraz dodatkowy element, zwany maską formatu (format mask) — DD-
-MON-YYYY . Funkcja TO_DATE próbuje dopasować do siebie poszczególne elementy wyspecy-
fikowanego ciągu znaków z odpowiednimi elementami maski formatującej. W przypadku pomyśl-
nego wykonania funkcja TO_DATE zwraca wartość binarną, którą system Oracle rozpoznaje jako
wartość typu DATE. W przeciwnym przypadku następuje przechwycenie wyjąetku.
Wróćmy do omawianego przykładu: funkcja TO_DATE przekazuje wspomnianą wartość binarną
jako argument do procedury add_book. Dla tej procedury sposób utworzenia wartości typu
DATE jest bez znaczenia — bezpośrednie podawanie wartości literałów jest równie dobrym roz-
wiązaniem, co uwzględnienie wyniku działania funkcjie, takiej jak na przykład TO_DATE.
Zastosowanie funkcji TO_DATE nie zawsze jest konieczne, ponieważ system Oracle
automatycznie podejmuje próbę konwersji ciągu znakoąwego na datę. W sytuacji takiej trzeba
opierać się na domyślnej masce formatu systemu Oracląe, która może zostać zmieniona przez
administratora bazy danych. Zwiększa to prawdopodobieąństwo wystąpienia pewnych
problemów, jak na przykład interpretacja wartości rąoku (jaki rok jest oznaczony wartością 00 ?).
Ogólnie można powiedzieć, że w celu konwersji na formaąt daty systemu Oracle należy
skorzystać z funkcji TO_DATE.
2KGTYU\G\CFCPKGRTQITCOKUV[E\PG
Powyższy fragment kodu zawiera argumenty podane w formie literałów (literals), to znaczy z za-
stosowaniem konkretnych wartości. Jest to dobry sposób w przypadku przeprowadzania testów,
jednakże podczas programowania w języku PL/SQL zazwyczaj używa się zmiennych w wywoła-
niach. Wartości zmiennych są zazwyczaj ustalane nie poprzez bezpośredni zapis w kodzie programu,
ale na przykład przez użytkownika korzystającego z klawiatury i ekranu, wyświetlającego odpowied-
nie informacje.
#TIWOGPV[QRELQPCNPG
Warto się zastanowić nad sposobem wywoływania procedury w przypadku posiadania niepełnej
informacji dotyczącej dodawanej książki — na przykład w przypadku braku daty wydania lub opisu.
Przykładowo, w takim przypadku można podać wartości NULL dla tych parametrów i sprawdzić, czy
nie spowoduje to problemów z wykonywaniem programu:e2
add_book( 1-56592-335-9 , 100000001 , Programowanie w Oracle PL/SQL ,
Feuerstein, Steven, Bill Pribyl , 987, NULL, NULL);
Jest to jedna z możliwości. Można także w ogóle nie podawać wartości argumentów i oprzeć się na
wartościach domyślnych. W tym przypadku będzie to wywołanie poprawne, gdyż w sekcji nagłów-
kowej procedury dla dwóch ostatnich parametrów zdefiniowano wartości domyślne:
...summary_in IN VARCHAR2 DEFAULT NULL,
date_published_in IN DATE DEFAULT NULL);
Wartości NULL w tym kontekście są w pełni poprawnymi wartościami.
Korzystając z wartości domyślnych, można przedstawione powyżej wywołanie uprościć i pominąć
ostatnie dwa argumenty:
add_book( 1-56592-335-9 , 100000001 , Programowanie w Oracle PL/SQL ,
Feuerstein, Steven, Bill Pribyl , 987);
W tym przypadku mechanizm odpowiedzialny za wykonanie kodu PL/SQL w miejsce brakujących
argumentów podstawi wartości domyślne i wykonanie procedury odbędzie się identycznie, jak we
wcześniejszym przypadku.
Możliwość pomijania argumentów dla parametrów posiadąających wartość domyślną jest
niezwykle przydatną cechą języka PL/SQL, ponieważ umożliwąia prawidłowe działanie
jednostki programowej nawet bez podania tych argumeąntów.
Ponadto mechanizm ten może w ogromnym stopniu zredukoąwać negatywny wpływ
ewentualnych, przyszłych modyfikacji. Przykładowo, jeśąli zaistnieje potrzeba dodania
do programu pewnych parametrów, to dzięki zdefiniowaąniu ich wartości domyślnych nie
będzie trzeba przeglądać całego kodu, aby zmienić kąażde występujące w nim wywołanie.
2 Ze względu na oszczędność miejsca w niniejszej ksiąążce nie zawsze jest przedstawiany pełny kod programąu.
W tym przypadku należałoby dodać instrukcję EXECUTE lub oąbjąć wywołanie słowami kluczowymi
BEGIN i END oraz dodać końcowe “/” w celu uruchomienia tej procedury z poziomu SQL*Plus.
4Q\F\KCđ2TQITCOQYCPKG
Warto też wiedzieć, że wywołując procedurę można jednocześnie pomijać parametry posiadające
wartości domyślne oraz podawać wartości NULL:
add_book( 1-56592-335-9 , 100000001 , Programowanie w Oracle PL/SQL , NULL,
987);
Czytelnik być może ma pewne problemy z zapamiętaniem, która wartość odpowiada któremu ar-
gumentowi, ale jest to rzecz normalna w przypadku notacji pozycyjnej (positional notation), co ozna-
cza konieczność podawania argumentów w takiej samej kolejności, w jakiej zapisano dane parametry
w kodzie programu.
0QVCELCKOKGPPC
Rozwiązaniem tych problemów może być zastosowanie innego, bardzo użytecznego mechanizmu
— notacji imiennej (named notation). Sposób działania notacji imiennej najlepiej można wyjaśnić
na przykładzie. W przypadku poniższego wywołania preocedury add_book wykorzystano właśnie
ten mechanizm:
add_book(isbn_in = 1-56592-335-9 ,
title_in = Programowanie w Oracle PL/SQL ,
summary_in = Kompendium informacji o języku PL/SQL, ||
wraz z przykładami i poradami na temat programowania. ,
author_in = Feuerstein, Steven, Bill Pribyl ,
date_published_in = NULL,
page_count_in = 987,
barcode_id_in = 100000001 );
Z powyższego wynika, że dla każdego argumentu podaje się nazwę parametru zdefiniowanego w pro-
cedurze add_book, symbol = oraz wartość danego parametru. Zaletą tej metody jest to, że po-
szczególne argumenty można podawać w dowolnej kolejności — nie trzeba pamiętać kolejności,
w jakiej zdefiniowano parametry w procedurze.
Wybór metody całkowicie zależy od programisty — dla kompilatora jest to bez znaczenia. Argu-
mentem przemawiającym za stosowaniem notacji imiennej może być fakt, że w ten sposób kod staje
się dużo bardziej przejrzysty. Znaczenie wyrażenia page_count = 987 nie pozostawia wąt-
pliwości, a na pewno nie jest tak w przypadku podania samej wartości 987. Można także, oczywi-
ście, pomijać parametry opcjonalne (domyślne) — w tym przypadku może to być, przykładowo,
date_published_in:
add_book(isbn_in = 1-56592-335-9 ,
title_in = Programowanie w Oracle PL/SQL ,
summary_in = Kompendium informacji o języku PL/SQL, ||
wraz z przykładami i poradami na temat programowania. ,
author_in = Feuerstein, Steven, Bill Pribyl ,
page_count_in = 987,
barcode__is_in = 100000001 );
Poza koniecznością pisania większej ilości kodu istnieje także pewna niedogodność, wynikająca ze
stosowania tej notacji. W razie potrzeby zmiany nazwy parametru konieczne jest uaktualnienie nie
tylko samej procedury, ale także każdego jej wywołania w notacjiimiennej. Z drugiej jednak stro-
ny zmiana nazwy parametru jest raczej rzadko wykonyewaną operacją.
2QDKGTCPKGKPHQTOCELKQNKE\DKGMUKæľGM\CRQOQEæHWPMELK
Istnieje także możliwość wykorzystania obydwóch notacji w jednym wywołaniu. Należy pamiętać
jedynie o paru zasadach. Należy zacząć od notacji pozycyjnej, a po przejściu do notacji imiennej
trzeba przy niej pozostać do końca. Oto przykład:
add_book( 1-56592-335-9 , 100000001 ,
Programowanie w Oracle PL/SQL ,
summary_in = NULL, author_in = Feuerstein, Steven, Bill Pribyl ,
page_count_in = 987);
Używanie notacji imiennej
W razie konieczności dokonania wyboru należy stosować notację wymieniającą wszędzie tam,
gdzie znaczenie poszczególnych argumentów nie jest obczywiste. Przykładowo:
uaktualnij_moj_profil(ulub_ksiazka_isbn = 1-56592-335-9 );
Notacji pozycyjnej należy używać w przypadku często używanych programów użytkowych,
które posiadają jeden lub dwa parametry, a znaczenibe tych parametrów jest oczywiste:
DBMS_OUTPUT.PUT_LINE( Witaj Muddah. );
Zastosowanie obydwóch notacji może okazać się przydatne w sytuacji, gdy na przykład
pierwszy argument jest oczywisty, zaś kolejne nie:
moje_put_line( Witaj Fadduh. , linie_do_pominiecia = 2);
W tym podrozdziale przedstawiono przykład konstruowania i wykorzystywania procedury magazy-
nowanej języka PL/SQL, która wykonuje pojedyncze zadanie. Zadanie to polega na dodaniu do bazy
danych informacji o książce identyfikowanej za pomocą odpowiedniego kodu kreskowego. Kolej-
ne zadanie polega na określeniu liczby egzemplarzy danej książki. Potrzeba pobierania informacji
o takiej pojedynczej wartości stanowi idealną sposobność wykorzystania funkcji języka PL/SQL,
która ze swej definicji zwraca jakąś wartość (lub końcezy działanie nie obsłużonym wyjątkiem).
2QDKGTCPKGKPHQTOCELKQNKE\DKGMUKæľGM
\CRQOQEæHWPMELK
Przed podjęciem próby zapisania kodu funkcji trzebae najpierw zapoznać się z ogólną składnią two-
rzenia funkcji. Funkcje stanowią drugi typ programówe magazynowanych.
5MđCFPKCVYQT\GPKCHWPMELK
Poniżej przedstawiono wzorzec definiowania funkcji. Elementy, które oznaczono za pomocą czcion-
ki pogrubionej, mogą być jeszcze Czytelnikowi nieznane.
CREATE [ OR REPLACE ] FUNCTION nazwa_funkcji
(parametr1 TRYB TYP_DANYCH [ DEFAULT wyrażenie ],
parametr2 TRYB TYP_DANYCH [ DEFAULT wyrażenie ],
...)
RETURN TYP_DANYCH
AS
4Q\F\KCđ2TQITCOQYCPKG
[ zmienna1 TYP_DANYCH;
zmienna2 TYP_DANYCH;
... ]
BEGIN
instrukcje_wykonawcze;
RETURN wyrażenie;
[ EXCEPTION
WHEN nazwa_wyjątku
THEN
instrukcje_wykonawcze ]
END;
/
Różnice pomiędzy wzorcem dotyczącym funkcji i wzorcem procedury są minimalne. Poza zamianą
słowa kluczowego PROCEDURE na FUNCTION w instrukcji CREATE, kod różni się tylko w dwóch
miejscach. Pierwszym z nich jest nagłówek, gdzie podaje się zwracany typ danych, a drugim jest
część wykonawcza, gdzie następuje bezpośrednie przekazanie zwracanej wartości do programu wy-
wołującego.
RETURN TYP_DANYCH
W nagłówku wyrażenie RETURN stanowi część deklaracji funkcji. Jest to informacja eo typie
danych wartości, które mają być zwracane w wyniku wyweołania funkcji.
RETURN wyrażenie
Wewnątrz sekcji wykonawczej wyrażenie RETURN jest instrukcją i oznacza, że działania zostały
zakończone i że należy zwrócić (return) wartość, którą definiuje wyrażenie. Instrukcja RETURN
może znaleźć się także w sekcji EXCEPTION.
Obydwie wymienione instrukcje są wymagane. W następnym podrozdziale przedstawiono kod go-
towej funkcji.
-QFHWPMELKDQQMAEQR[ASV[
Funkcja book_copy_qty zwraca liczbę książek, których numer ISBN odpowiada poedanemu.
W poniższym, przykładowym kodzie funkcji do pobrania danych z bazy danych wykorzystano
kursor (cursor). Szczegółowe omówienie tej struktury znajduje się dopiero w rozdziale 5. Uogólnia-
jąc, kursor jest określonym miejscem w pamięci, do którego program może przenieść pewne dane po-
brane z bazy danych. W sekcji deklaracji następuje powiązanie pewnej instrukcji SELECT z kur-
sorem. Powiązanie to zachodzi za pomocą instrukcji CURSOR. Warto zwrócić uwagę, że parametr
wejściowy isbn_in występuje po prawej stronie wyrażenia WHERE. W celu pobrania danych nale-
ży kursor otworzyć (open), pobrać (fetch) z niego dane, a w końcu go zamknąć (close).
CREATE OR REPLACE FUNCTION book_copy_qty(isbn_in IN VARCHoAR2)
RETURN NUMBER
AS
number_o_copies NUMBER := 0;
CURSOR bc_cur IS
SELECT COUNT(*)
FROM book_copies
WHERE isbn = isbn_in;
BEGIN
2QDKGTCPKGKPHQTOCELKQNKE\DKGMUKæľGM\CRQOQEæHWPMELK
IF isbn_in IS NOT NULL
THEN
OPEN bc_cur;
FETCH bc_cur INTO number_o_copies;
CLOSE bc_cur;
END IF;
RETURN number_o_copies;
END;
/
Konstrukcja funkcji niewiele odbiega od procedury. Różnią się one jednak swoim zachowaniem
— szczegóły dotyczące działania funkcji przedstawioeno na rysunku 3.6.
Rysunek 3.6. Wykorzystanie funkcji języka PL/SQL w celu zwrócenia pewnej wartości do programu wywołującego; dla danej wartości
ISBN funkcja zwraca liczbę egzemplarzy znajdujących się. w bazie danych
Zasadnicza różnica pomiędzy wywołaniem funkcji i procedury wynika z tego, że funkcje zwracają
pewną wartość.
7ľ[YCPKGHWPMELK
Najprostszym sposobem wykorzystania wartości wynikowej, zwracanej przez funkcję, jest jej przy-
pisanie do pewnej zmiennej, której typ odpowiada typowi zwracanej wartości. Zwykle wygląda to
w następujący sposób:
DECLARE
zmienna_lokalna TYP_DANYCH;
BEGIN
zmienna_lokalna := nazwa_funkcji (argument1, argument2, ...);
END;
/
Jest to zatem typowa instrukcja przypisania, gdzie wywołanie funkcji znajduje się po prawej stronie
operatora przypisania, a zmienna lokalna po lewej. Moeżna więc napisać:
DECLARE
ile INTEGER;
BEGIN
ile := book_copy_qty( 1.56592-335-9 );
END;
/
4Q\F\KCđ2TQITCOQYCPKG
Z informacji przedstawionych w rozdziale 1. wynika, że w celu wyświetlenia wyników na ekranie
można przekazać je do funkcji DBMS_OUTPUT.PUT_LINE:
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE( Liczba kopii 1-56592-335-9:
|| book_copy_qty( 1-56592-335-9 ));
END;
/
Funkcja book_copy_qty zwraca wartość typu VARCHAR2, co umożliwia konkatenację z innym
ciągiem znaków i użycie funkcji PUT_LINE. Takie zagnieżdżanie funkcji wewnątrz innych in-
strukcji jest często stosowaną techniką.
0KGMVÎTG\CUCF[FQV[E\æEGY[MQT\[UVCPKCHWPMELK
Poniżej wymieniono kilka wartych zapamiętania zasad edotyczących wykorzystania funkcji:
1. Nie można tworzyć niezależnych (standalone) funkcji o takich samych nazwach, jakie noszą
niezależne procedury. Jednakże przyjęcie konwencji naezywania procedur wyrażeniem
czasownikowym, a funkcji — wyrażeniem rzeczownikowyme może uchronić przed potencjalnymi
problemami.
2. Nieuwzględnienie w nagłówku wyrażenia RETURN spowoduje, że funkcja nie zostanie
skompilowana. Jest to działanie pozytywne, gdyż błędy eczasu kompilacji uważa się za metodę
wczesnego ostrzegania przed ewentualnymi problemamie. Jeśli jednak słowo kluczowe RETURN
zostanie pominięte w sekcji wykonawczej, system Oraclee poinformuje o tym dopiero po
uruchomieniu funkcji. Zostanie wtedy wygenerowany komeunikat o błędzie ORA-06503:
PL/SQL: Function returned without value. Dlatego zawsze warto przeprowadzać dogłębne
testy napisanych programów.
Procedury a funkcje
Uogólniając, funkcja służy do przeprowadzenia pewnych operacji, których celem jest otrzyma-
nie pewnej wartości. Oczywiście, jest możliwe wykorzystanie procedury zamiast funkcji i za-
stosowanie pojedynczego parametru OUT, jednak takie postępowanie nie jest zalecane.
Warto też pamiętać, że nie należy pisać programów, które zwracają wartość informującą o stanie
zakończenia. Postępowanie takie jest uzasadnione w języku takim jak C, lecz w przypadku
PL/SQL informowanie o błędach powinno się odbywać poprzez przechwytywanie wyjątków.
Jeśli program magazynowany nie przechwyci wyjątku, zakłada się, że podczas wykonania tego
programu nie napotkano żadnych problemów.
Czasami okazuje się, że istnieje potrzeba zwrócenia więcej niż jednej wartości — w takim przy-
padku należy utworzyć procedurę. W celu zwrócenia wielu wartości trzeba wykorzystać kilka
parametrów w trybie OUT lub IN OUT, których wartość może być odczytana przez program
wywołujący po zakończeniu działania procedury. Zdefiniowanie kilku parametrów w trybie
OUT może wprowadzić nieco zamieszania, jednak istnieje metoda zapisywania programów, któ-
re muszą zwracać większą ilość danych w bardziej czytelny sposób. Można bowiem różne ele-
menty połączyć w jedną całość przez zastosowanie jednego ze złożonych typów danych, jak na
przykład rekord, kolekcja lub tak zwany typ obiektobwy. Zagadnienia te opisano w rozdziale 5.
2QDKGTCPKGKPHQTOCELKQNKE\DKGMUKæľGM\CRQOQEæHWPMELK
3. Program wywołujący po wywołaniu funkcji musi w jakieś sposób wykorzystać zwróconą wartość,
na przykład przypisać ją pewnej zmiennej. PL/SQL, w przeceiwieństwie do języka C, nie dopuszcza
wywołania funkcji i zignorowania zwracanej wartoście.
4. Po wykonaniu instrukcji RETURN w sekcji wykonawczej do programu wywołującego zosteaje
przekazana nie tylko wartość, ale także sterowanie wyekonaniem programu. Innymi słowy, kod
zapisany po instrukcji RETURN nie jest wykonywany.
Warto również zwracać uwagę na stosowaną terminologię. Czasami, gdy ktoś mówi o „procedurach
magazynowanych”, naprawdę ma na myśli programy magazynowane, czyli albo procedury, albo
funkcje. Z drugiej jednak strony, może chodzić właśnie o procedury, nie zaś funkcje. Jeśli ogólny
kontekst rozmowy nie pozwala na rozpoznanie, o którą możliwość chodzi, z pewnością prośba
o wyjaśnienie nie będzie objawem dyletanctwa.
9[LæVMKRT\GEJY[V[YCPGRT\G\HWPMELGYUGMELKFGMNCTECELK
K\YKæ\CPG\PKOKOQľNKYGRTQDNGO[
Powyżej przedstawiono następujący sposób wywołania efunkcji:
DECLARE
ile INTEGER;
BEGIN
ile := book_copy_qty( 1-56592-335-9 );
END;
/
Wydawałoby się, że powyższy kod można zapisać w bardziej zwięzły sposób poprzez wywołanie
funkcji podczas inicjalizacji zmiennej:
DECLARE
ile INTEGER := book_copy_qty( xyz );
BEGIN
Z pozoru wszystko jest w porządku. Jednak jeśli funkcja przechwyci wyjątek, to okazuje się, że
żaden kod obsługi wyjątku w tej sekcji nie zdoła przechwycić wyjątku, który ewentualnie może
pojawić się po takim wywołaniu funkcji book_copy_qty. A zatem:
DECLARE
ile INTEGER := book_copy_qty( xyz );
BEGIN
...cokolwiek...
EXCEPTION
WHEN OTHERS
THEN
/* NIESPODZIANKA! Wyjątki przechwycone w sekcji deklaracji
|| NIE MOGĄ być obsłużone w tym miejscu!
*/
...
END;
/
Wytłumaczenie powodu takiego zachowania wykracza poeza zakres niniejszej książki. Dlatego należy
po prostu zapamiętać, że nie należy dokonywać prób iniecjalizowania zmiennych za pomocą funkcji.
4Q\F\KCđ2TQITCOQYCPKG
6YQT\GPKGGNCUV[E\PGIQMQFW
Z pewnością Czytelnik chciałby kontynuować rozwijanie tworzonej, przykładowej aplikacji. Nie
można jednak zapominać o konsekwentnym upewnianiu się, że zapisany od nie zawiera błędów. Z tego
też względu poniżej zamieszczono pewnego rodzaju dyegresję.
Z pewnością każdy zetknął się już z wyrażeniem garbage in, garbage out (GIGO). Jest to wyraże-
nie dość często używane lub słyszane przez telefon podczas rozmowy z obsługą techniczną. Oznacza
ono mniej więcej tyle, co „wprowadzasz błędne dane — uezyskujesz błędne wyniki”.
Uniknięcie problemu GIGO jest sprawą dość problematyczną. Istnieje tendencja do nieuzasadnione-
go optymizmu w kwestii wykorzystywania utworzonych aplikacji. Chętniej przyjmuje się założenie
tidy in, tidy out (wprowadzasz poprawne dane — uzyskujesz poprawne wyniki)3. Nikt nie lubi z góry
planować działań zapobiegawczych względem zastosowaniea niepoprawnych danych wejściowych.
W celu uniknięcia nieprzewidzianych załamań programu trzeba przeprowadzić serie testów. Prze-
prowadzenie dobrych testów oznacza generowanie przeróżnych kombinacji danych testowych,
które potencjalnie powinny „zawiesić” program. Potem należy sprawdzić oczekiwany wynik, uru-
chomić program, porównać z danymi wyjściowymi, poprawić program i wreszcie… ponownie
przeprowadzić testy.
Z pewnością warto uprościć ten cały proces.
Monotonne wykonywanie fragmentu kodu z różnymi danymi wejściowymi jest dobrą okazją do na-
pisania pewnych programów użytkowych. Poniżej przedstawiono sposób zautomatyzowania tego
męczącego procesu za pomocą takich programów.
0CT\úF\KGURTCYF\CLæEGY[PKMK
Działanie pierwszego, sprawdzającego programu polega na porównywaniu dwóch wartości i wyświe-
tlaniu komunikatu o pomyślnym lub niepomyślnym przebiegu tego testu. U podstaw testowania
leży właśnie porównywanie wartości — wartości faktycznej ze spodziewaną wartością wyjściową.
Dlatego też wspomniany program jest bardzo użyteczny. Zawierał on także opis poszczególnych
iteracji, tak aby umożliwić otrzymanie informacji o tym, dla jakich wartości wykonanie programu
będzie niepomyślne. Poniżej znajduje się kod tej proceedury „test równości” (testrownosci):
CREATE OR REPLACE PROCEDURE reporteq (description IN VARCHAoR2,
expected_value IN VARCHAR2, actual_value IN VARCHAR2)
AS
BEGIN
DBMS_OUTPUT.PUT(description || : );
IF expected_value = actual_value
OR (expected_value IS NULL AND actual_value IS NULL)
THEN
DBMS_OUTPUT.PUT_LINE( PASSED );
3 Nieprzetłumaczalna gra słów: garbage to po angielsku śmieci, bzdury; tidy — czysty, schludny — przyp. tłum.
6YQT\GPKGGNCUV[E\PGIQMQFW
ELSE
DBMS_OUTPUT.PUT_LINE( FAILED. Expected || expected_value
|| ; got || actual_value);
END IF;
Pobierz darmowy fragment (pdf)
