informatyka programowanie serwera oracle 11g sql i pl sql adam pelikant ebook

Programowanie serwera
Oracle 11g SQL i PL/SQL

Autor: Adam Pelikant
ISBN: 978-83-246-2429-4
Format: 158u235, stron: 336

Twórz przejrzyste bazy danych i w³aœciwie przetwarzaj informacje

• Podstawy – organizacja serwera, instalacja bazy danych i koñcówki klienta
• Jêzyk SQL – tworzenie ró¿nych rodzajów zapytañ, funkcjonalnoœci dodatkowe
• Jêzyk PL/SQL – procedury, funkcje, dynamiczny SQL

Bazy danych Oracle od lat stanowi¹ najlepsz¹ alternatywê dla wszystkich tych, którzy
potrzebuj¹ funkcjonalnych i pojemnych struktur przechowywania danych,
wyposa¿onych dodatkowo w mo¿liwoœæ wszechstronnego przeszukiwania i zestawiania
potrzebnych informacji. Jednak podstawowa wiedza na temat œrodowiska Oracle nie
wystarczy, aby zaprojektowaæ naprawdê przejrzyst¹, prost¹ w obs³udze bazê. Do tego
potrzebna jest solidna wiedza, któr¹ znajdziesz w³aœnie w tym podrêczniku.

„Programowanie serwera Oracle 11g SQL i PL/SQL” to kontynuacja ksi¹¿ki Adama
Pelikanta „Bazy danych. Pierwsze starcie”, a poruszane w niej zagadnienia s¹ bardziej
zaawansowane, choæ przy odrobinie samozaparcia tak¿e nowicjusz w tej dziedzinie
bêdzie w stanie przyswoiæ sobie zawart¹ tu praktyczn¹ wiedzê. Oprócz organizacji
serwera, instalacji bazy danych i sk³adni jêzyka SQL szczegó³owo omówione s¹ tutaj
ró¿ne rodzaje zapytañ w tym jêzyku (prostych i z³o¿onych), a tak¿e funkcje rozszerzenia
proceduralnego PL/SQL. W ksi¹¿ce opisano tak¿e zastosowanie Javy do tworzenia
oprogramowania po stronie serwera oraz funkcje analityczne, stanowi¹ce wstêp do
przetwarzania OLAP. Ca³oœæ uzupe³niono praktycznymi przyk³adami, obrazuj¹cymi
dzia³anie poszczególnych konstrukcji i procedur.

• Organizacja serwera
• Instalacja bazy i koñcówki klienta
• Zapytania wybieraj¹ce, modyfikuj¹ce dane i tworz¹ce tabele
• Dodatkowe funkcjonalnoœci SQL
• Procedury sk³adowane i wyzwalane
• Funkcje w PL/SQL
• Pakiety, kursory, transakcje
• Dynamiczny SQL
• Zastosowanie Javy do tworzenia oprogramowania po stronie serwera
• Elementy administracji – zarz¹dzanie uprawnieniami z poziomu SQL
• Obiektowoœæ w Oracle

Wydajna, bezpieczna i prosta w obs³udze – zaprojektuj doskona³¹ bazê danych!

Spis treĈci

Od autora ......................................................................................... 5

CzöĈè I

Oracle SQL ..................................................................... 7

Rozdziaä 1. Wstöp .............................................................................................. 9

Organizacja serwera ....................................................................................................... 10
Instalacja bazy i koĔcówki klienta .................................................................................. 12

Rozdziaä 2. Zapytania wybierajñce ................................................................... 27

Podstawowe elementy skáadni ........................................................................................ 27
Grupowanie i funkcje agregujące ................................................................................... 36
Zapytania do wielu tabel — záączenia ............................................................................ 40
Grupowanie i funkcje analityczne .................................................................................. 49
Funkcje analityczne i rankingowe .................................................................................. 63
Pozostaáe elementy skáadniowe stosowane w SQL ........................................................ 87
Obsáuga grafów w SQL .................................................................................................. 94

Rozdziaä 3. Zapytania modyfikujñce dane ......................................................... 99

Rozdziaä 4. Zapytania tworzñce tabele ........................................................... 103

Zapytania modyfikujące tabelĊ ..................................................................................... 110
Dodatkowe informacje ................................................................................................. 114
Sekwencja ..................................................................................................................... 119
Perspektywy ................................................................................................................. 121
Indeksy ......................................................................................................................... 130

Rozdziaä 5. Dodatkowe funkcjonalnoĈci SQL ................................................... 137

Zapytania dla struktur XML ......................................................................................... 137

CzöĈè II ORACLE PL/SQL ........................................................ 153

Rozdziaä 6. PL/SQL ........................................................................................ 155

Podstawy skáadni .......................................................................................................... 155

Rozdziaä 7. Procedury skäadowane .................................................................. 163

Rozdziaä 8. Funkcje w PL/SQL ....................................................................... 179

Rozdziaä 9. Pakiety ........................................................................................ 187

Spis treĈci

Rozdziaä 10. Procedury wyzwalane ................................................................... 197

Rozdziaä 11. Kursory ........................................................................................ 229

Rozdziaä 12. Transakcje ................................................................................... 247

Rozdziaä 13. Dynamiczny SQL ........................................................................... 253

Rozdziaä 14. Zastosowanie Javy do tworzenia oprogramowania

po stronie serwera ....................................................................... 269

Rozdziaä 15. Elementy administracji

— zarzñdzanie uprawnieniami z poziomu SQL ................................. 289

Rozdziaä 16. ObiektowoĈè w Oracle .................................................................. 301

Zakoþczenie ................................................................................ 315

Skorowidz .................................................................................... 317

Rozdziaä 7.

Procedury skäadowane

Zamiast tworzy

bloki anonimowe, wygodniej jest organizowa

kod w nazwane pro-

cedury. W

rodowisku baz danych nosz

one miano procedur sk

adowanych. W przy-

padku j

zyków wy

szego rz

du taka organizacja kodu podyktowana jest ch

utrzy-

mania jego przejrzysto

ci — je

li jego fragment ma by

wykonywany wielokrotnie,

warto go umie

w procedurze i odwo

ywa

do niego tylko przez jej wywo

ywanie.

Tak

e ró

ne funkcjonalno

ci, zadania kodu s

argumentem za jego podzia

em. Pod-

stawowa sk

adnia polecenia tworz

cego procedur

ma posta

CREATE PROCEDURE nazwa
IS
BEGIN
-- ciaáo procedury
END;

Zamiast s

owa kluczowego

na stosowa

same s

owo kluczowe

. Cia

procedury mo

e by

dowolny zestaw instrukcji PL/SQL oraz dowolne zapytania SQL,

za wyj

tkiem zapytania wybieraj

cego

SELECT

W przypadku

rodowisk baz danych wydaje si

jednak,

e argumenty zwi

zane z or-

ganizacj

kodu nie s

tu najwa

niejsze. Dla procedur sk

adowanych najpó

niej przy

pierwszym ich wykonaniu generowany jest plan wykonania zapyta

wchodz

cych w ich

ad, ich kod jest wst

pnie optymalizowany, a nast

pnie s

one prekompilowane. W ten

sposób na serwerze przechowywana jest procedura w dwóch postaciach: przepisu na

jej utworzenie

CREATE PROCEDURE

oraz skompilowanego kodu. Przy ka

dym nast

pnym

wykonaniu odwo

ujemy si

do postaci skompilowanej. Sprawia to,

e procedury s

wykonywane szybciej i wydajniej ni

ten sam kod w formie skryptu. Sprawa nie jest tak

oczywista w przypadku cz

sto wykonywanych zapyta

. Plany wykonania ka

dego za-

pytania przechowywane s

w pami

ci wspó

dzielonej (

SGA

—

System Global Area

) i je-

eli wykonamy je ponownie, wykorzystana zostanie jego przetworzona posta

. Nale

jednak pami

e zgodno

Ğü

dwóch zapyta

sprawdzana jest z dok

adno

do znaku,

nie jest natomiast analizowana semantyka. Poza tym informacje o planach zapyta

nadpisywane na stare definicje w momencie wyczerpania zasobów pami

ci wspó

dzie-

lonej. Bez wzgl

du na te uwagi mo

emy jednak przyj

ąü

e wydajno

Ğü

przetwarzania

procedury sk

adowanej jest wi

ksza ni

w przypadku równowa

nego jej skryptu (bloku

anonimowego). Je

eli chcemy usun

ąü

definicj

procedury, mo

emy u

nast

puj

cego

polecenia:

164

CzöĈè II i ORACLE PL/SQL

DROP PROCEDURE nazwa;

Wielokrotnie mo

emy chcie

modyfikowa

kod procedury. Ponowne wykonanie po-

lecenia

CREATE PROCEDURE

spowoduje wykrycie obiektu o tej samej nazwie i wy

wie-

tlenie komunikatu o b

áĊ

dzie. W zwi

zku z tym musimy najpierw usun

ąü

definicj

pro-

cedury, a nast

pnie utworzy

ponownie. Zamiast tego mo

emy pos

adni

CREATE OR REPLACE PROCEDURE

, która, w zale

ci od tego, czy procedura o danej na-

zwie ju

istnieje, czy te

nie, stworzy j

od podstaw lub nadpisze na istniej

cej now

definicj

. Z punktu widzenia programisty nie da si

rozró

, która z tych akcji zosta

wykonana (taki sam komunikat —

Procedura zostaáa pomyĞlnie utworzona

), dlatego,

korzystaj

c z tej sk

adni, nale

y si

upewni

, czy nadpisujemy kod w

ciwej proce-

dury! Przyk

adem mo

e by

utworzenie procedury, której zadaniem b

dzie zamiana

wszystkich nazwisk w tabeli

Osoby

na pisane du

ymi literami.

CREATE OR REPLACE PROCEDURE up
IS
BEGIN
UPDATE Osoby SET Nazwisko=UPPER(Nazwisko);
END;

Jak wida

, cia

o procedury zawiera zapytanie aktualizuj

UPDATE

zgodne ze sk

adni

SQL. Jej wywo

anie mo

e odby

na przyk

ad z wn

trza bloku anonimowego o postaci:

BEGIN
up;
END;

my,

e w ramach tego samego skryptu chcieliby

my sprawdzi

poprawno

Ğü

wy-

konania procedury zapytaniem wybieraj

cym

SELECT

. Zgodnie z wcze

niejsz

uwag

zamieszczenie go w ciele bloku anonimowego jest niedozwolone. Równie

umiesz-

czenie zapytania wybieraj

cego bezpo

rednio po s

owie kluczowym

END;

zako

czy

komunikatem o b

áĊ

dzie. St

d konieczno

Ğü

podzielenia skryptu na dwie cz

ĊĞ

ci, które

z punktu widzenia serwera stanowi

osobne fragmenty. Znakiem odpowiedzial-

nym za taki podzia

jest slash (

), przy czym cz

ĊĞ

ci b

traktowane jako fragmenty

kodu PL/SQL lub zapytania SQL, w zale

ci od zawarto

ci. Skrypt mo

e zosta

podzielony w ten sposób na dowoln

liczb

niezale

nych fragmentów.

BEGIN
up;
END;
/
SELECT Nazwisko FROM Osoby;

Innym przyk

adem realizacji procedury sk

adowanej jest zastosowanie jej do przepi-

sywania nazwisk i wzrostu osób do tabeli

wys_tab

, przy czym rekordy posortowane

malej

co wed

ug wzrostu. Nale

y zauwa

e tabela

wys_tab

o odpowiedniej

strukturze musi ju

istnie

w schemacie u

ytkownika.

CREATE OR REPLACE PROCEDURE wysocy
IS
BEGIN
INSERT INTO wys_tab(Nazwisko,Wzrost)
SELECT Nazwisko, Wzrost FROM Osoby
ORDER BY Wzrost DESC;
END wysocy;

Rozdziaä 7. i Procedury skäadowane

165

W przedstawionym przyk

adzie definicja tworzonej procedury jest ko

czona polece-

niem

END nazwa;

, gdzie

nazwa

jest jej nazw

. Pomini

cie jej w tym poleceniu nie po-

ga za sob

ą Ī

adnych zmian. Ko

czenie definicji procedury w prezentowany sposób

jest wskazane ze wzgl

dów organizacyjnych, porz

dkowych, zw

aszcza wtedy, kiedy

skrypt zawiera wi

ksz

liczb

onych procedur, co utrudnia znalezienie ko

ców de-

finicji przy jego poprawianiu.
Dotychczas prezentowane przyk

ady by

y procedurami niepobieraj

cymi

adnych da-

nych z wywo

cego je skryptu — by

y bezparametrowe. Brak parametrów wywo

nia jest szczególnie widoczny w drugim przypadku, gdzie do tabeli

wys_tab

trafiaj

wszyscy pracownicy — wszyscy s

traktowani jako wysocy. Zmodyfikujmy t

proce-

dur

tak, aby do tabeli docelowej przepisywane by

y tylko osoby wy

sze od pewnego

progu danego w postaci parametru.

CREATE OR REPLACE PROCEDURE wysocy
(mm number)
IS
BEGIN
INSERT INTO wys_tab(Nazwisko,wzrost)
SELECT Nazwisko, wzrost FROM Osoby
WHERE wzrost > mm
ORDER BY wzrost DESC;
END wysocy;

Jak pokazano, parametry procedury definiowane s

w nawiasie po jej nazwie, a na mi-

nimaln

definicj

ada si

nazwa i typ parametru, przy czym typ podawany jest bez

definiowania rozmiaru, czyli

number

, a nie

number(10)

. Podanie rozmiaru w tym miej-

scu powoduje wy

wietlenie komunikatu o b

áĊ

dzie. Zdefiniowanie parametru procedury

sprawia,

e jest on traktowany tak jak zadeklarowana zmienna i nie mo

e zosta

zade-

klarowany ponownie w jej ciele. Mo

e on zosta

yty w dowolnym miejscu definicji

procedury; w przedstawionym przyk

adzie zosta

wykorzystany do sformu

owania wa-

runku w klauzuli

WHERE

zapytania wstawiaj

cego wiersze.

eli chcemy u

w definicji procedury wi

cej ni

jednego parametru, ich lista po-

winna by

rozdzielona przecinkami. W przyk

adzie przedstawiono procedur

, która

wstawia do tabeli

Dodatki

wiersze zawieraj

ce sum

warto

ci brutto i sta

ej danej dru-

gim parametrem procedury

Dodatek

dla osoby, której identyfikator zawiera pierwszy

z parametrów

Num

CREATE OR REPLACE PROCEDURE Dodaj
(Num number, Dodatek number)
IS
BEGIN
INSERT INTO Dodatki
SELECT Brutto+Dodatek FROM Zarobki
WHERE IdOsoby = Num;
END;

Jak przedstawiono to w dotychczasowych przyk

adach, zawarto

Ğü

procedury mog

sta-

nowi

wszystkie zapytania modyfikuj

ce dane, ale równie

zapytania tworz

ce lub mo-

dyfikuj

ce struktur

bazy. Czasami jednak, zamiast wykonywa

jakie

operacje na da-

nych, chcemy dokona

operacji zwracaj

cej wynik w postaci zmiennej, np. policzy

czy

166

CzöĈè II i ORACLE PL/SQL

podsumowa

jak

ąĞ

wielko

Ğü

zapisan

w bazie. W takim przypadku w ciele procedury

wykorzystujemy bardzo cz

sto sk

adni

SELECT ... INTO zmienna

. Powoduje ona,

wyznaczona zapytaniem warto

Ğü

skalarna jest przypisywana do zmiennej wyst

puj

cej po s

owie kluczowym

INTO

. Zapytanie takie nie skutkuje wyprowadzeniem danych

na standardowe wyj

cie. Nie jest ono elementem SQL, a co za tym idzie, mo

e by

ywane tylko we wn

trzu procedur i funkcji albo w blokach anonimowych PL/SQL.

eli dokonujemy takiego przypisania do zmiennej, która jest parametrem procedury,

to parametr taki musi mie

sufiks

OUT

wskazuj

cy,

e jest on przekazywany z proce-

dury do miejsca jej wywo

ania. Je

li nie zdefiniujemy parametru jako „wychodz

cego”,

to próba przypisania mu warto

ci spowoduje pojawienie si

komunikatu o b

áĊ

dzie.

Wszystkie parametry, które nie maj

jawnie okre

lonego kierunku przekazywania da-

nych, s

domy

lnie typu

, czyli przekazuj

dane tylko do procedury. Dopuszczalny

jest jeszcze trzeci przypadek, w którym zarówno zmienna przekazywana jest do pro-

cedury, jak i obliczona wewn

trz procedury warto

Ğü

przekazywana jest na zewn

trz.

W takiej sytuacji zmienna jest opisywana par

IN OUT

. Prezentowana procedura zlicza

osoby wy

sze, ni

to okre

la warto

Ğü

progowa dana pierwszym parametrem.

CREATE OR REPLACE PROCEDURE wysocy
(mm number, ile OUT number)
IS
BEGIN
SELECT COUNT(wzrost) INTO ile FROM Osoby
WHERE wzrost > mm;
END wysocy;

Wywo

anie tak zdefiniowanej procedury z bloku anonimowego wymaga zadeklaro-

wania zmiennej, do której zostanie przypisany parametr typu

OUT

. Musi ona mie

typ

zgodny z parametrem formalnym, ale wymagane jest te

podanie jego rozmiaru, o ile

typ nie oferuje rozmiaru domy

lnego. W prezentowanym przyk

adzie zadeklarowano

zmienn

ile

jako

number

(bo domy

lnie

number { number(10)

). W przypadku zmien-

nych znakowych rozmiar pola musi by

dany jawnie (

varchar(11)

). Wywo

ania pro-

cedury dokonujemy przez podanie jej nazwy oraz zdefiniowanie warto

ci parametrów

— czyli podanie listy parametrów aktualnych. Do parametrów typu

emy przy-

pisywa

zarówno zmienne, jak i sta

e, natomiast do parametrów typu

OUT

musimy

przypisa

zmienne. W prezentowanym przyk

adzie nazwa zmiennej oraz parametru

w definicji procedury jest taka sama, co jest powszechnie stosowan

notacj

, cho

wzgl

dów sk

adniowych zgodno

Ğü

nazw nie jest wymagana.

SET SERVEROUTPUT ON;
DECLARE
ile number;
BEGIN
wysocy(1.8, ile);
DBMS_OUTPUT.PUT_LINE(ile);
END;

Oczywi

cie procedura mo

e zawiera

cej ni

jeden parametr typu

OUT

. Za

my,

e oprócz liczby osób o wzro

cie wi

kszym od warto

ci progowej chcemy wyznaczy

ich

redni wzrost. Mo

emy zastosowa

dwa zapytania agreguj

ce, które okre

intere-

suj

ce nas warto

ci, jednak bardziej wydajne jest u

ycie podwójnego przypisania w zapyta-

niu

SELECT ... INTO

. Je

li zwraca ono jeden wiersz, to po s

owie kluczowym

INTO

mu-

simy umie

tyle zmiennych, ile wyra

jest wyznaczanych w jego pierwszej cz

ĊĞ

ci.

Rozdziaä 7. i Procedury skäadowane

167

CREATE OR REPLACE PROCEDURE wysocy
(mm number, ile OUT number, sr OUT real)
IS
BEGIN
SELECT COUNT(wzrost), AVG(wzrost) INTO ile, sr FROM Osoby
WHERE wzrost > mm;
END wysocy;

Dla ka

dego parametru mo

e zosta

zdefiniowana warto

Ğü

domy

lna. Wykonujemy to

przez podanie po typie zmiennej s

owa kluczowego

DEFAULT

, po którym ustanawiana

jest warto

Ğü

. W prezentowanym przyk

adzie przyj

to,

e domy

warto

progu,

od którego zliczamy osoby wysokie, jest

1.7

CREATE OR REPLACE PROCEDURE wysocy
(mm NUMBER DEFAULT 1.7, ile OUT NUMBER)
IS
BEGIN
SELECT COUNT(wzrost) INTO ile FROM Osoby
WHERE wzrost > mm;
END wysocy;

Dla procedury ze zdefiniowan

warto

domy

poprawne jest wywo

anie, w któ-

rym podajemy warto

Ğü

posiadaj

cego j

parametru. Wówczas warto

Ğü

podana przy

wywo

aniu „nadpisuje” si

na domy

SET SERVEROUTPUT ON;
DECLARE
ile number;
BEGIN
wysocy(1.8,ile);
DBMS_OUTPUT.PUT_LINE(ile);
END;

eli jednak chcemy przy tak zdefiniowanych parametrach odwo

do warto

ci do-

lnej, to musimy zastosowa

wywo

anie nazewnicze o postaci

parametr=>zmienna

gdzie

parametr

jest nazw

parametru formalnego procedury, a

zmienna

jest warto

aktualn

tego parametru w miejscu, z którego j

wywo

ujemy. Notacj

emy

odczytywa

jako „

parametr staje siĊ zmienną

”. W przypadku takiego wywo

ania

zmienna, która nie zosta

a w nim wymieniona, b

dzie mia

a przypisan

warto

Ğü

domy

Gdyby w definicji procedury pomini

ta w wywo

aniu zmienna nie mia

a zdefiniowanej

warto

ci domy

lnej, to takie wywo

anie spowodowa

oby wy

wietlenie komunikatu

o b

áĊ

dzie.

SET SERVEROUTPUT ON;
DECLARE
ile number;
BEGIN
wysocy(ile => ile);
DBMS_OUTPUT.PUT_LINE(ile);
END;

W definicji procedury zmieniamy kolejno

Ğü

parametrów tak,

e drugi z nich ma przy-

pisan

warto

Ğü

domy

. Reszta procedury pozostaje bez zmian.

168

CzöĈè II i ORACLE PL/SQL

CREATE OR REPLACE PROCEDURE wysocy
(ile OUT NUMBER, mm NUMBER DEFAULT 1.7)
IS
BEGIN
SELECT COUNT(wzrost) INTO ile FROM Osoby
WHERE wzrost > mm;
END wysocy;

W takim przypadku, poniewa

przy odwo

aniu do warto

ci domy

lnej pomijamy ostatni

parametr, dopuszczalne jest zastosowanie wywo

ania pozycyjnego. Jest ono dozwolone

wtedy, gdy pomini

ciu podlega

ostatnich parametrów posiadaj

cych warto

ci domy

lne.

SET SERVEROUTPUT ON;
DECLARE
ile number;
BEGIN
wysocy(ile);
DBMS_OUTPUT.PUT_LINE(ile);
END;

Oczywi

cie bardziej ogólne jest wywo

anie nazewnicze, które, bez wzgl

du na to, na

których pozycjach znajduj

warto

ci domy

lne, jest zawsze poprawne.

SET SERVEROUTPUT ON;
DECLARE
ile number;
BEGIN
wysocy(ile => ile);
DBMS_OUTPUT.PUT_LINE(ile);
END;

Wywo

anie nazewnicze jest dopuszczalne równie

wtedy, kiedy podajemy pe

ny ze-

staw parametrów. W takim przypadku kolejno

Ğü

ich wymieniania nie odgrywa

adnej roli.

SET SERVEROUTPUT ON;
DECLARE
ile number;
BEGIN
wysocy(mm=>1.8, ile=>ile);
DBMS_OUTPUT.PUT_LINE(ile);
END;

Do wersji 11. mo

liwe by

o stosowanie albo tylko wywo

ania pozycyjnego, albo na-

zewniczego — jednoczesne u

ycie obu tych typów nie by

o dozwolone. Od wersji 11.

istnieje ju

taka mo

liwo

Ğü

. Przeanalizujmy to na przyk

adzie procedury o trzech pa-

rametrach numerycznych. Pierwsze dwa s

typu

, a trzeci typu

OUT

. W ciele proce-

dury warto

Ğü

parametru wyj

ciowego c jest wyznaczana w postaci ilorazu dwóch pierw-

szych parametrów.

CREATE OR REPLACE PROCEDURE dziel
(a real, b real, c OUT real)
AS
BEGIN
c:=a/b;
END;

Rozdziaä 7. i Procedury skäadowane

169

W przyk

adowym skrypcie pokazane zosta

y poprawne wywo

ania tej procedury:

w pe

ni pozycyjne, z dwoma pierwszymi parametrami danymi pozycyjnie oraz z danym

pozycyjnie pierwszym parametrem.

DECLARE
res real;
BEGIN
dziel(10, 9, res);
DBMS_OUTPUT.PUT_LINE('Wynik ' || res);
dziel(10, 9, c => res);
DBMS_OUTPUT.PUT_LINE('Wynik ' || res);
dziel(10, b => 9, c => res);
DBMS_OUTPUT.PUT_LINE('Wynik ' || res);
dziel(10, c => res, b => 9);
DBMS_OUTPUT.PUT_LINE('Wynik ' || res);
END;

Nie ka

de wywo

anie mieszane jest jednak dopuszczalne. Dozwolone s

tylko takie

przypadki, w których pierwsze na li

cie parametry podstawiane s

pozycyjnie, a po-

zosta

e nazewniczo. Kolejny przyk

ad pokazuje niepoprawne u

ycie wywo

ania mie-

szanego, gdzie b

áą

d polega na tym,

rodkowy parametr jest dany nazewniczo, czyli,

inaczej mówi

e po parametrze danym nazewniczo istnieje cho

jeden dany pozy-

cyjnie.

DECLARE
res real;
BEGIN
dziel(10, b => 9, res);
DBMS_OUTPUT.PUT_LINE('Wynik ' || res);
END;

W przypadku próby wykonania takiego bloku anonimowego wygenerowany zostanie

komunikat o b

áĊ

dzie w nast

puj

cej postaci:

Error report:
ORA-06550: linia 12, kolumna 15:
PLS-00312: skojarzenie parametrów przez nazwú moľe nie implikowaè skojarzenia przez pozycjú
ORA-06550: linia 12, kolumna 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Ciekaw

liwo

zastosowania procedury jest wykonywanie zapyta

adanych

z fragmentów, np. danych statycznymi napisami czy te

zawartych w zmiennej. Zbu-

dujmy procedur

, która w zale

ci od warto

ci parametru zmieni sposób zapisu pola

Nazwisko

w tabeli

Osoby

. Wykonanie takiego zapytania, sk

adaj

cego si

z fragmentów

napisów, wymaga u

ycia polecenia

EXECUTE IMMEDIATE

CREATE OR REPLACE PROCEDURE exe_tekst
(typ varchar2)
IS
BEGIN
EXECUTE IMMEDIATE
'UPDATE osoby SET Nazwisko=' || typ || '(Nazwisko)';
END exe_tekst;

170

CzöĈè II i ORACLE PL/SQL

Bardziej eleganckim rozwi

zaniem jest zastosowanie zmiennej pomocniczej

zap

, co

oczywi

cie nie wp

ywa na sposób wykonania. Przyk

ad ten pokazuje natomiast,

e miej-

scem deklaracji zmiennych lokalnych procedury (takich, które s

widoczne tylko w jej

ciele) jest obszar mi

dzy s

owami kluczowymi

oraz

BEGIN

. Przy deklarowaniu zmien-

nych w tym miejscu nie wolno stosowa

owa kluczowego

DECLARE

CREATE OR REPLACE PROCEDURE exe_tekst
(typ varchar2)
IS
zap varchar2(111);
BEGIN
zap:= 'UPDATE osoby SET Nazwisko=' || typ || '(Nazwisko)';
EXECUTE IMMEDIATE zap
END exe_tekst;

Przy okazji prezentowania tego przyk

adu chc

przedstawi

nieco archaiczne wywo-

anie procedury z zastosowaniem polecenia

CALL

. Poprawnymi parametrami s

tu na-

zwy funkcji operuj

cych na zmiennych

cuchowych:

UPPER

— przepisanie

cucha

do postaci pisanej tylko du

ymi literami,

LOWER

— przepisanie

cucha do postaci pi-

sanej tylko ma

ymi literami,

INITCAP

— przepisanie

cucha do postaci pisanej od

ej litery. Poprawne jest równie

ycie pustego ci

gu znaków

lub ci

gu sk

daj

cego si

z samych spacji

' '

. Zalet

tego typu wywo

ania jest mo

liwo

Ğü

za-

stosowania bezpo

rednio po nim zapytania wybieraj

cego, które ma za zadanie spraw-

dzi

poprawno

Ğü

wykonania procedury.

CALL exe_tekst('UPPER');
SELECT * FROM osoby;

W wi

kszo

ci sytuacji b

dziemy jednak stosowa

klasyczne wywo

anie wewn

trz bloku

anonimowego. W takim przypadku zastosowanie w nim zapytania wybieraj

cego zgod-

nie z wymogami sk

adni nie jest dozwolone, je

li jednak chcemy w ramach tego sa-

mego skryptu wykona

blok anonimowy i zapytanie wybieraj

ce, musimy rozdzieli

znakiem

. Faktycznie powoduje to,

e pomimo tego, i

oba elementy zapisane s

w tym

samym miejscu, stanowi

one dwa przetwarzane po sobie skrypty — jeden PL/SQL,

drugi SQL. Ka

dy d

szy skrypt mo

e zosta

podzielony znakami

na mniejsze frag-

menty, które b

przetwarzane szeregowo, oczywi

cie pod warunkiem,

e ka

dy z nich

jest sk

adniowo poprawny.

BEGIN
exe_tekst('INITCAP');
END;
/
SELECT * FROM osoby;

Kolejny przyk

ad przedstawia zastosowanie w definicji procedury wywo

ania wbudo-

wanej procedury

RAISE_APPLICATION_ERROR

, która powoduje wygenerowanie (ustano-

wienie) b

áĊ

du u

ytkownika o numerze danym pierwszym parametrem oraz komuni-

kacie stanowi

cym drugi z parametrów. Nale

y zauwa

e numeracja b

áĊ

dów w Oracle

przebiega przez warto

ci ujemne, a dla b

áĊ

dów u

ytkownika zarezerwowano przedzia

<–29999, –20001>.

Wywo

anie powy

szej procedury powoduje przerwanie dzia

ania

programu i wy

wietlenie komunikatu o b

áĊ

dzie.

Rozdziaä 7. i Procedury skäadowane

171

CREATE OR REPLACE PROCEDURE Blad
IS
BEGIN
RAISE_APPLICATION_ERROR (-20205, 'Bđæd programu');
END blad;

Bardziej z

onym przyk

adem zastosowania

RAISE_APPLICATION_ERROR

jest wykorzy-

stanie jej podczas wykonywania procedury o ograniczonym zestawie danych wej-

ciowych, co ma miejsce np. w opracowanej poprzednio procedurze

exe_tekst

. Je

eli

parametr wej

ciowy nie jest jednym z dopuszczalnych elementów wymienionych na

cie, generowany jest b

áą

d z odpowiednim komentarzem. W przeciwnym przypadku

wykonywane jest za pomoc

polecenia

EXECUTE IMMEDIATE

zapytanie. Nale

y zwróci

uwag

na fakt,

e do porównania z elementami listy u

yto zmiennej przetworzonej do

postaci pisanej du

ymi literami

UPPER(typ)

, gdy

przy porównywaniu

cuchów

Oracle rozró

nia ich wielko

Ğü

. Pozwala to na podanie w wywo

aniu procedury nazwy

funkcji modyfikuj

cej

cuch pisanej w dowolny sposób (literami ma

ymi, du

ymi oraz

ró

nej wielko

ci).

CREATE OR REPLACE PROCEDURE exe_tekst
(typ varchar2)
IS
BEGIN
IF UPPER(typ) NOT IN('UPPER', 'LOWER', 'INITCAP') THEN
RAISE_APPLICATION_ERROR (-20205, 'Zđa funkcja');
ELSE
EXECUTE IMMEDIATE
'UPDATE osoby SET Nazwisko=' || typ || '(Nazwisko)';
END IF;
END exe_tekst;

emy przekszta

procedur

, tak aby w jej ciele u

wywo

ania poprzednio utwo-

rzonej procedury generuj

cej b

áą

d przetwarzania o nazwie

Blad

CREATE OR REPLACE PROCEDURE exe_tekst
(typ varchar2)
IS
zap varchar2(111);
BEGIN
IF UPPER(typ) NOT IN('UPPER', 'LOWER', 'INITCAP') THEN
Blad;
ELSE
zap:= 'UPDATE osoby SET Nazwisko=' || typ || '(Nazwisko)';
EXECUTE IMMEDIATE zap;
END IF;
END exe_tekst;

áĊ

dy mog

jednak pojawia

podczas przetwarzania nie tylko na skutek celowej

dzia

alno

ci programisty, ale mog

spowodowane nie zawsze daj

cymi si

przewidzie

zdarzeniami, b

áĊ

dnymi wywo

aniami, nieodpowiednimi parametrami etc.

emy mówi

wtedy o sytuacji wyj

tkowej — o powstaniu wyj

tku. Takie zdarzenia

mog

zosta

w PL/SQL obs

one, oprogramowane.

172

CzöĈè II i ORACLE PL/SQL

Rozwa

my przyk

ad procedury, której zadaniem jest okre

lenie, czy pracownik o danym

numerze identyfikacyjnym

IdOsoby

, wskazanym parametrem

num

, istnieje w tabeli

Osoby

. Je

li tak, drugi z parametrów (

status

) ma przyj

ąü

warto

Ğü

; w przypadku

przeciwnym

. W celu realizacji tego zadania zastosowano zapytanie wybieraj

ce zwra-

caj

ce do zmiennej pomocniczej

kto

identyfikator osoby. Je

eli pracownik o danym

identyfikatorze istnieje, warto

Ğü

zwrócona przez zapytanie i warto

Ğü

parametru b

takie same, je

li jednak takiego pracownika nie ma, zapytanie wybieraj

ce nie zwróci

adnego wiersza. Spowoduje to,

e próba podstawienia pod zmienn

kto

zako

czy si

áĊ

dem przetwarzania:

Nie znaleziono Īadnych wierszy

. Stan ten mo

emy wykorzy-

sta

, wprowadzaj

c sekcj

EXCEPTION

i oprogramowuj

c wyj

tek

NO_DATA_FOUND

, któ-

rego obs

uga wykonywana jest wed

ug schematu

WHEN nazwa_wyjætku THEN instrukcje

W naszym przypadku obs

uga wyj

tku zawiera podstawienie odpowiedniej warto

ci pod

zmienn

status

oraz wypisanie komunikatu.

CREATE OR REPLACE PROCEDURE czy_jest
(num IN NUMBER, status OUT NUMBER)
IS
kto NUMBER;
BEGIN
SELECT IdOsoby INTO kto
ROM Osoby WHERE IdOsoby = num;
IF (kto = num) THEN
status := 1;
DBMS_OUTPUT.PUT_LINE ('Pracownik istnieje');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
status := 0;
DBMS_OUTPUT.PUT_LINE('Pracownik nie istnieje');
WHEN OTHERS THEN
NULL;
END;

rodowisku Oracle zdefiniowano wiele wyj

tków, których nazwy symboliczne i przy-

czyny wyst

pienia zawiera tabela 7.1. Je

eli gdziekolwiek w ciele procedury wyst

puje

sytuacja wyj

tkowa, przetwarzanie przenoszone jest do sekcji obs

ugi wyj

tków. Naj-

pierw sprawdzane jest to, czy wyj

tek, który przerwa

przetwarzanie, jest zgodny z nazw

symboliczn

wyst

puj

w sekcji

EXCEPTION

. Nast

pnie wykonywane s

instrukcje znaj-

duj

ce si

po s

owie kluczowym

THEN

jego obs

ugi, po czym przerywane jest przetwa-

rzanie procedury i nast

puje powrót do miejsca, z którego zosta

a ona wywo

ana. Je

w trakcie przetwarzania procedury pojawi

jednocze

nie dok

adnie dwa wyj

tki,

co jest mo

liwe, chocia

o prawdopodobne, to obs

ony zostanie tylko ten, który

wyst

puje jako pierwszy na li

cie w sekcji obs

ugi wyj

tków. Na szczególn

uwag

zas

uguje wyj

tek o nazwie

OTHERS

, który obs

uguje wszystkie inne, dot

d nieobs

one. Gdyby znalaz

on na pierwszym miejscu listy obs

ugi wyj

tków, to bez wzgl

na to, jakie zdarzenie powodowa

oby wyst

pienie sytuacji wyj

tkowej, wykonywana

aby zawsze ta sama sekcja znajduj

ca si

OTHERS

. Od wersji 9. Oracle umiesz-

czanie obs

ugi wyj

tku

OTHERS

przed obs

jakiegokolwiek innego jest zabronione

adniowo. Innymi s

owy, jego obs

uga musi by

ostatnim elementem sekcji obs

ugi

wyj

tków. W prezentowanym przyk

adzie zastosowano minimaln

obs

wyj

tku

(sekcja nie mo

e by

pusta) —

NULL

, nie rób nic. Bez wzgl

du na to, czy wyj

tki s

w pro-

cedurze obs

ugiwane, czy te

nie, zasady jej wywo

ania pozostaj

bez zmian.

Rozdziaä 7. i Procedury skäadowane

173

Tabela 7.1. Wykaz najczĊĞciej wystĊpujących wyjątków serwera

Nazwa wyjñtku

Numer bäödu

Opis

NO_DATA_FOUND

ORA-01403

Jednowierszowe zapytanie wybieraj

SELECT

nie zwróci

danych.

TOO_MANY_ROWS

ORA-01422

Zapytanie wybieraj

SELECT

zwróci

o wi

cej ni

jeden wiersz.

INVALID_CURSOR

ORA-01001

Niedopuszczalna operacja na kursorze.

ZERO_DIVIDE

ORA-01476

Próba dzielenia przez zero.

DUP_VAL_ON_INDEX

ORA-00001

Próba wstawienia powtarzaj

cej si

warto

ci w pole,

dla którego ustanowiono indeks unikatowy.

INVALID_NUMBER

ORA-01722

Konwersja

cucha na liczb

zako

czy

a si

niepowodzeniem.

CURSOR_ALREADY_OPEN

ORA-06511

Próba otwarcia kursora, który ju

zosta

otwarty.

LOGIN_DENIED

ORA-01017

Próba zalogowania si

z nieodpowiednim has

em lub loginem.

NOT_LOGGED_ON

ORA-01012

Próba wykonania polecenia operuj

cego na bazie danych

bez uprzedniego zalogowania si

PROGRAM_ERROR

ORA-06501

PL/SQL napotka

wewn

trzny problem podczas przetwarzania.

STORAGE_ERROR

ORA-06500

PL/SQL dysponuje zbyt ma

ymi zasobami pami

lub pami

Ċü

zosta

a uszkodzona.

TIMEOUT_ON_RESOURCE

ORA-00051

Zosta

przekroczony czas oczekiwania na odpowied

bazy danych.

ACCESS_INTO_NULL

ORA-06530

Próba przypisania do zmiennej warto

ci niezainicjowanej

(

NULL

CASE_NOT_FOUND

ORA-06592

adna z warto

ci okre

lonych warunkami

WHEN

w poleceniu

CASE

nie jest prawdziwa, a nie wyst

puje sekcja

ELSE

Wywo

anie pozycyjne procedury mo

e mie

posta

SET SERVEROUTPUT ON;
DECLARE
kto NUMBER;
status NUMBER;
BEGIN
kto:=1;
czy_jest (kto, status);
DBMS_OUTPUT.PUT_LINE(status);
END;

Wywo

anie nazewnicze mo

na zrealizowa

wed

ug schematu:

SET SERVEROUTPUT ON;
DECLARE
kto NUMBER;
status NUMBER;
BEGIN
kto := 1;
czy_jest (status => status, num => kto);
DBMS_OUTPUT.PUT_LINE(status);
END;

174

CzöĈè II i ORACLE PL/SQL

Przedstawiony poprzednio przyk

ad jest niew

tpliwie akademicki, poniewa

wyko-

rzystuje sekcj

obs

ugi wyj

tków do wyznaczania parametru

OUT

. W praktyce, je

w tym miejscu jest wyznaczany jaki

parametr wyj

ciowy, to jest on odpowiedzialny

za kodowanie sposobu zako

czenia przetwarzania, np.

— przetwarzanie zako

czone

sukcesem,

<>0

— przetwarzanie zako

czone niepowodzeniem; konkretna warto

Ğü

ko-

duje przyczyn

. Nasz

procedur

mogliby

my wi

c doprowadzi

do postaci, w której

wykorzystywaliby

my funkcj

agreguj

COUNT

CREATE OR REPLACE PROCEDURE czy_jest
(num IN NUMBER, status OUT NUMBER, ok OUT NUMBER)
IS
BEGIN
ok:=0;
SELECT COUNT(IdOsoby) INTO status
FROM Osoby WHERE IdOsoby = num;
IF (status = 1) THEN
DBMS_OUTPUT.PUT_LINE ('Pracownik istnieje');
ELSE
DBMS_OUTPUT.PUT_LINE('Pracownik nie istnieje');
END IF;
EXCEPTION
WHEN OTHERS THEN
ok := 99;
DBMS_OUTPUT.PUT_LINE('Bđæd przetwarzania');
END;

Zamiana zapytania wybieraj

cego na takie, które zawiera funkcj

zliczaj

rekordy,

spowoduje,

e je

li pracownik o danym numerze istnieje, to policzony zostanie

re-

kord, a je

li nie istnieje —

rekordów. Jak wida

, zmiana taka sprawia,

e przypisanie

e od razu dotyczy

zmiennej wychodz

cej oraz

e wyj

tek

NO_DATA_FOUND

nie po-

jawia si

. Zawsze jednak mo

liwe jest wyst

pienie innych b

áĊ

dów przetwarzania, st

obs

uga wyj

tku

OTHERS

, w której ustawiono zmienn

na warto

Ğü

ró

od zera, w tym

przypadku

, co koduje stan pojawienia si

áĊ

du. Zwyczajowo pierwsz

linijk

cia

procedury jest ustawienie zmiennej koduj

cej sposób wykonania na

— przetwarza-

nie zako

czone poprawnie.

Istnieje formalna mo

liwo

Ğü

obs

enia dwóch lub wi

cej wyj

tków w tym samym

miejscu sekcji ich obs

ugi. W tym celu po s

owie kluczowym

WHEN

áą

czymy nazwy sym-

boliczne wyj

tków operatorem logicznym

CREATE OR REPLACE PROCEDURE czy_jest
(num IN NUMBER, status OUT NUMBER, ok OUT NUMBER)
IS
BEGIN
ok := 0;
SELECT COUNT(IdOsoby) INTO status
FROM Osoby WHERE IdOsoby = num;
IF (status = 1) THEN
DBMS_OUTPUT.PUT_LINE ('Pracownik istnieje');
ELSE
DBMS_OUTPUT.PUT_LINE('Pracownik nie istnieje');
END IF;
EXCEPTION
WHEN INVALID_NUMBER OR NO_DATA_FOUND THEN

Czytaj dalej...

Rozdziaä 7. i Procedury skäadowane

175

ok := 11;
DBMS_OUTPUT.PUT_LINE('Bđæd wartoħci');
WHEN OTHERS THEN
ok := 99;
DBMS_OUTPUT.PUT_LINE('Bđæd przetwarzania');
END;

Nie zawsze jest tak,

e wyj

tek musi wi

z formalnym b

áĊ

dem przetwarzania.

Czasami wygodnym jest, aby pewne sytuacje nieprowadz

ce do b

áĊ

dów formalnych

y traktowane jako wyj

tkowe. Mówimy wtedy o wyj

tkach u

ytkownika, które musz

zosta

zdefiniowane, wykryte i które powinny zosta

obs

one. W prezentowanym

przyk

adzie za sytuacj

wyj

tkow

dziemy chcieli uzna

fakt,

e nie ma osób o wzro-

cie wy

szym od progu danego parametrem. Jak wida

, taka sytuacja nie spowoduje

powstania b

áĊ

du przetwarzania — trzeba wi

c j

wykry

CREATE OR REPLACE PROCEDURE licz
(mini NUMBER, ile out INT)
IS
brakuje EXCEPTION;
BEGIN
SELECT COUNT(IdOsoby) INTO ile FROM Osoby
WHERE Wzrost > mini;
IF (ile = 0) THEN
RAISE brakuje;
END IF;
EXCEPTION
WHEN brakuje THEN
RAISE;
WHEN OTHERS THEN
NULL;
END;

Deklaracji wyj

tku u

ytkownika, tak samo jak ka

dej innej zmiennej, dokonujemy

w sekcji deklaracji i nadajemy mu typ

EXCEPTION

(jak wida

, to s

owo kluczowe pe

podwójn

rol

: jest okre

leniem typu oraz sygnalizuje pocz

tek sekcji obs

ugi wyj

tków).

W przyk

adzie zdefiniowano wyj

tek o nazwie brakuje. Po zliczeniu osób o wzro

cie

szym od warto

ci progowej instrukcj

warunkow

sprawdzono, czy ich liczba jest

równa

, po czym dla takiego przypadku poleceniem

RAISE nazwa_wyjætku

ustawiono

(wygenerowano) b

áą

d u

ytkownika. Obs

ugi wyj

tku u

ytkownika dokonujemy na ta-

kich samych zasadach jak wyj

tków wbudowanych (systemowych). W przyk

adzie za-

stosowano drug

, po minimalnej obs

udze

NULL

, najcz

ĊĞ

ciej spotykan

metod

— u

ycie

polecenia

RAISE

, które odpowiada za wygenerowanie wyj

tku. Spowoduje to propa-

gacj

wyj

tku do miejsca, z którego procedura zosta

a wywo

ana. Mo

na powiedzie

e nie da si

z sekcji obs

ugi wyj

tków przenie

Ğü

do tej samej sekcji, wi

c wyj

tek

oszony w sekcji obs

ugi wyj

tków musi zosta

obs

ony „pi

tro wy

ej” — w pro-

cedurze wywo

cej.

Bardzo cz

sto wykorzystujemy przy obs

udze wyj

tków dwie wbudowane funkcje

PL/SQL:

SQLCODE

— zwracaj

numer wyj

tku (b

áĊ

du), oraz

SQLERRM

— wy

wietla-

zwi

zany z tym b

áĊ

dem (wyj

tkiem) komunikat. Sposób ich zastosowania ilustruje

nast

pny przyk

ad.

Wyszukiwarka

Podobne podstrony:
informatyka hurtownie danych od przetwarzania analitycznego do raportowania adam pelikant ebook
Programowanie serwera Oracle 11 Nieznany
informatyka w drodze do ccna zadania przygotowujace do egzaminu adam jozefiok ebook
informatyka budowa sieci komputerowych na przelacznikach i routerach cisco adam jozefiok ebook
informatyka montaz komputera pc ilustrowany przewodnik wydanie ii adam chabinski ebook
Oracle Database 11g i SQL Programowanie or11pr
Oracle Database 11g i SQL Programowanie or11pr

więcej podobnych podstron