Bankovní institut vysoká škola Praha
Katedra informačních technologií a elektronického obchodování
Jazyk PL/SQL Bakalářská práce
Autor: David Petrášek
Informační technologie - Správce informačních systémů
Vedoucí práce: Ing. Vladimír Beneš
Praha Duben 2005
Prohlášení
Prohlašuji, že jsem bakalářskou práci zpracoval samostatně a s použitím uvedené
literatury.
V Praze dne 20. 4. 2005
………………
David Petrášek
Poděkování
Rád bych poděkoval za projevenou pomoc především vedoucímu práce. Dále bych
rád poděkoval kolegům ze zaměstnání za jejich odborné konzultace a neskrývanou
kritiku, která nemalou měrou přispěla ke zvýšení přehlednosti a ucelenosti této práce.
Anotace práce
Práce se zabývá popisem vlastností procedurálního jazyka PL/SQL, který je
nástavbou neprocedurálního jazyka SQL. Tento databázový programovací jazyk
umožňuje naprogramovat i ty nejsložitější algoritmy pro práci s daty v databázi Oracle.
V práci se podrobně popisuje deklarace PL/SQL proměnných, větvení toku programu
pomocí podmínek a cyklů, ošetření chybových stavů programu a vytváření databázových
objektů, jako jsou procedury, funkce, balíčky a spouště. Práce také obsahuje základní
porovnání jazyka PL/SQL a jazyka T-SQL.
Vedoucí práce: Ing. Vladimír Beneš
5
Obsah 1. Úvod do jazyka PL/SQL...................................................................................... 7
1.1 Jazyk SQL............................................................................................................ 7 1.2 Jazyk PL/SQL - rozšíření jazyka SQL................................................................. 8 1.3 Vývojové prostředí pro jazyk PL/SQL ................................................................ 9 1.4 Společnost Oracle ................................................................................................ 9
1.4.1 Historie vývoje společnosti Oracle a databázových produktů Oracle ....... 10 1.4.2 Databázová platforma Oracle 9i ................................................................ 11
2. Základní programový blok jazyka PL/SQL....................................................... 12
2.1 Deklarační sekce ................................................................................................ 12 2.2 Výkonná sekce ................................................................................................... 12 2.3 Sekce pro zpracování výjimek ........................................................................... 13 2.4 Kód programového bloku .................................................................................. 13 2.5 Proměnné v jazyce PL/SQL............................................................................... 14
2.5.1 Typy proměnných ...................................................................................... 14 2.5.2 Ukázka deklarací skalárních datových typů .............................................. 15 2.5.3 Typ proměnné podle jiné proměnné nebo sloupce tabulky ....................... 16
2.6 Výpis textu do konzole ...................................................................................... 17 2.7 Pravidla pro psaní PL/SQL bloků ...................................................................... 17
2.7.1 Komentáře v kódu PL/SQL ....................................................................... 18 2.7.2 SQL funkce v PL/SQL............................................................................... 19 2.7.3 Vnořené bloky PL/SQL ............................................................................. 19 2.7.4 Operátory v PL/SQL.................................................................................. 20 2.7.5 Doporučená pravidla pro přehlednější zápis programového kódu ............ 20
2.8 Použití SQL příkazů v jazyce PL/SQL .............................................................. 21 2.8.1 Porovnání SQL a PL/SQL příkazů ............................................................ 21 2.8.2 Příkaz SELECT v jazyce PL/SQL ............................................................. 22 2.8.3 Příkazy pro manipulaci s daty v jazyce PL/SQL ....................................... 23 2.8.4 SQL kurzory a jejich použití v PL/SQL .................................................... 23
2.9 Řízení toku programu ........................................................................................ 24 2.9.1 Podmínky ................................................................................................... 25 2.9.2 Příkaz „CASE“ pro vícenásobné větvení programu .................................. 27 2.9.3 Cykly.......................................................................................................... 28
2.10 Práce s kompozitními datovými typy ................................................................ 31 2.10.1 PL/SQL RECORD..................................................................................... 32 2.10.2 PL/SQL TABLE ........................................................................................ 34
2.11 Práce s kurzory................................................................................................... 36 2.11.1 PL/SQL explicitní kurzory......................................................................... 36
2.12 Výjimky a jejich ošetření v jazyce PL/SQL ...................................................... 39 2.12.1 Funkce pro zachycení výjimek .................................................................. 42 2.12.2 Umělé vyvolání výjimky............................................................................ 42
3. Podprogramy – procedury, funkce, balíčky a triggery v jazyce PL/SQL.......... 44
3.1 Vývojová prostředí pro podprogramy................................................................ 44 3.2 PL/SQL procedury ............................................................................................. 45
3.2.1 Volání vytvořené procedury ...................................................................... 46 3.3 PL/SQL Funkce ................................................................................................. 47
3.3.1 Volání vytvořené funkce............................................................................ 48
6
3.4 PL/SQL balíčky (packages) ............................................................................... 49 3.4.1 Volání vytvořeného balíčku....................................................................... 51
3.5 PL/SQL triggery (spouště)................................................................................. 52 4. Porovnání jazyka PL/SQL a jazyka T-SQL....................................................... 54
4.1 Proměnné v PL/SQL a T-SQL........................................................................... 54 4.2 Řízení toku programu ........................................................................................ 55
4.2.1 Podmínky ................................................................................................... 55 4.2.2 Cykly.......................................................................................................... 56
Úvod
Pro efektivní práci s daty v databázi Oracle je nutná alespoň částečná znalost jazyka
SQL. V případě, že potřebujeme provádět složitější operace s daty, je nutné použít
nástavbu jazyka SQL a to jazyk PL/SQL.
Cílem této práce je analýza a popis programových struktur jazyka PL/SQL. Práce by
měla obsahovat především seznámení se syntaxí jazyka PL/SQL a měla by upozorňovat
na problematické oblasti při vývoji PL/SQL programových bloků. V ukázkových PL/SQL
kódech by měly být vytvořeny a použity některé algoritmy, které mohou sloužit jako
předloha pro vývoj reálných PL/SQL kódů.
V práci by také mělo být obsaženo praktické porovnání procedurálních jazyků dvou
v současné době nejvýznamnějších databázových platforem, Oracle 9i (platforma
dodávána firmou Oracle) a MS SQL Serveru 2000 (platforma dodávána firmou
Microsoft).
Tato práce je určena především vývojářům a programátorům, kteří se zabývají
tvorbou databázových aplikací na platformě Oracle 9i.
7
1. Úvod do jazyka PL/SQL V současném světě je velký důraz kladen na rozvoj informatiky. V posledních deseti
letech došlo k rozvoji především mobilních telekomunikací, ale nejen jich. I operátoři
pevných sítí neustále rozšiřují a vylepšují své služby zákazníkům. Také podniky a firmy
investují nemalé finanční částky do vývoje a implementování nových informačních
systémů.
Všechny tyto aktivity potřebují ke své činnosti nějakou datovou základnu, nějaké
úložiště dat. S neustálým rozvojem informatiky souvisí potřeba ukládat stále větší objemy
dat v digitální podobě. Nejspolehlivější a nejvíce efektivní je uložení dat do
nějaké databáze, v současné době jsou nejvíce používané relační databáze.
Pojem „Databáze“ zahrnuje data a nástroje zajišťující jejich ukládání a manipulaci
s nimi. V češtině zavedená zkratka SŘBD (systém řízení báze dat) to vystihuje asi
nejpřesněji.
Pojem „Databázový server“ představuje soubor programových prostředků určených
pro práci s daty, včetně organizace a realizace přístupu klientů k těmto datům.
Pojem „Databázová platforma“ zahrnuje databázový server včetně sady nástrojů pro
správu a zabezpečení dat v databázi.
Pro přístup k datům v databázi se využívá převážně jazyk SQL (Structured Query
Language).
1.1 Jazyk SQL Databázový jazyk SQL (Structured Query Language) vznikl na základě projektu
společnosti IBM. Cílem projektu bylo vytvořit jazyk blízký angličtině pro práci s daty
v databázích. Později na vývoji databázového jazyka spolupracovaly také další firmy,
přičemž komerčně jej uvedla do praxe společnost Oracle. Postupem času se ujaly
vylepšené a upravené standardy tohoto jazyka s označením SQL 86 a SQL 92.
SQL je dotazovací jazyk, přičemž základní princip komunikace s relačním
databázovým serverem lze vyjádřit jako „klient zformuluje a položí svůj dotaz a
databázový server na něj odpoví, obvykle tím, že vygeneruje nějakou množinu výstupních
dat“. Tento princip komunikace s databázovým serverem je velmi jednoduchý a efektivní.
Samozřejmě ale jen z pohledu uživatele. Jazyk SQL totiž připomíná klasický přirozený
8
jazyk (anglický), má ale přesně definovaná syntaktická a lexikální pravidla. Z pohledu
serveru se příkaz SQL přenese, dekóduje, optimalizuje a provede.
1.2 Jazyk PL/SQL - rozšíření jazyka SQL Hlavním omezením jazyka SQL je jedna z jeho vlastností, jazyk SQL je
neprocedurální jazyk. V praxi to znamená, že příkazy jazyka SQL se provádějí sekvenčně.
Nelze použít klasické programátorské konstrukce, jako jsou například cykly, podmínky,
procedury nebo funkce. Pomocí jazyka SQL vlastně říkáme, co chceme získat a nikoli již
to, jak to chceme provést. Vzhledem k tomu, že jednotlivá omezení jsou někdy
nevýhodná, nabízí většina moderních databázových platforem procedurální rozšíření
jazyka SQL. Rozšířený jazyk SQL se stává mocným nástrojem, který umožňuje
naprogramovat i ty nejsložitější algoritmy pro práci s daty. Rozšíření jazyka SQL z dílny
společnosti ORACLE má název „PL/SQL“. Společnost Microsoft použila ve své
platformě MS SQL Server rozšíření jazyka SQL s názvem „T-SQL“ (Transact-SQL).
Obecně se zkratka „PL“ vysvětluje jako „Transact Processing Language“. Jazyk
PL/SQL plně podporuje procedury a větvení toku programu pomocí podmínek a cyklů,
podobně jako klasické programovací jazyky, kam patří například Pascal či C++. Jazyk
PL/SQL umožňuje deklarovat konstanty, proměnné a kurzory, nabízí dokonce podporu
dynamických deklarací. Samozřejmostí je také podpora transakčního zpracování.
Chybové stavy procesu je možné ošetřit pomocí výjimek.
Tento jazyk podporuje také modularitu, je tedy možné vkládat vnořené bloky do
jednotlivých programových bloků. Obecně platí, že modularita velmi přispívá
k přehlednosti programů, v konečném důsledku také k urychlení vývoje a spolehlivějšímu
běhu výsledné aplikace. Jazyk PL/SQL také usnadňuje přenos aplikace do jiného
prostředí, například do jazyka C++. V takovém případě musíme pochopitelně kód přepsat
do příslušného programovacího jazyka, ale zjednodušení spočívá v tom, že zůstávají
platné algoritmy, které tvoří aplikační logiku.
Od platformy Oracle9i je implementace jazyka PL/SQL značně vylepšena. Vedle
podpory nativního překladu byl vylepšen také celkový překlad PL/SQL. Vývoj značně
zefektivňuje také vylepšená podpora dědičnosti či dynamické přidělování systémových
zdrojů. Vhodné použití objektů výrazně snižuje náklady nejen na etapu vývoje, ale také na
následnou údržbu a případné rozšiřování aplikace. Byla také přidána možnost ladění
skriptů na straně serveru ve více jazycích. Díky tomu může vývojář prostřednictvím
9
jednoho vývojového prostředí IDE (Integrated Development Enviroment) ladit programy
PL/SQL například v jazyku Java. Další vylepšení jazyka PL/SQL spočívá v rozšíření
paralelních dotazů SQL o ukládací procedury napsané v jazycích PL/SQL, Java a externí
rutiny, plná podpora příkazů „CASE“ a výrazů ve stylu „ANSI“, dále lepší podpora pro
aplikace, které jsou náročné na výpočetní výkon procesoru prostřednictvím podpory
nativního překladu a lepší integrace běhových enginů pro SQL a PL/SQL.
1.3 Vývojové prostředí pro jazyk PL/SQL Databázová platforma Oracle9i nenabízí v základní instalaci žádné komplexní
vývojové prostředí. Pro vytvoření jednodušších programů lze použít konzolové aplikace
SQL*Plus či SQL*Plus Worksheet. V produktu Jdeveloper od společnosti Oracle je sice
implementována podpora práce s kódem PL/SQL, ale tento produkt není součástí
instalace databázové platformy Oracle9i. Společnost Oracle tedy vytvořila široký prostor
pro jiné firmy, které se zabývají nástroji na vývoj aplikací v jazyce PL/SQL.
Mezi kvalitní nástroje pro práci s jazykem PL/SQL patří:
• DBPartner Debugger
• PL/SQL Developer
• Rapid SQL
• SQL Programmer
• SQL Navigator
• Toad
1.4 Společnost Oracle Oracle je druhá největší softwarová firma na světě a vedoucí dodavatel
nejmodernějších globálních e-business řešení pro komerční internet a webové aplikace.
Oracle je první softwarová společnost, která se zaměřuje stoprocentně na internetový
software díky úplné řadě produktů: databáze, server, obchodní aplikace, a aplikační
vývojové a rozhodující podpůrné nástroje. Sídlo společnosti je v Redwood Shores v
Kalifornii.
Software Oracle běží na PC, pracovních stanicích, minipočítačích, mainframech a
masivně paralelních počítačích, tak jako na osobních digitálních asistentech a set-top
nástrojích.
Česká pobočka společnosti Oracle byla založena v roce 1994. Zaměstnanci pobočky
pomáhají řešit firmám v České republice komplexní problémy spojené s řízením
10
rozhodujících informací v podnicích všech typů a velikostí. Sídlo české pobočky
společnosti Oracle je v Praze na Vinohradech ve Škrétově ulici.
1.4.1 Historie vývoje společnosti Oracle a databázových produktů Oracle
V roce 1977 založili Lawrence J. Ellison, Robert N. Miner a Edward Oates firmu
SDL (Software Development Laboratory). Firma pracovala na vývoji relačních databází
podle teorie doktora E. F. Codda.
Společnost SDL vytvářela projekt na zakázku pro CIA, tento projekt měl kódový
název Oracle.
V roce 1978 se společnost SDL přejmenovala na Relational Software Inc. Vznikla
první verze databázové platformy Oracle verze 1.0. Tato verze se ale příliš neprosadila.
V roce 1980 se společnost opět přejmenovala, nový název byl Oracle Corporation.
Vznikla nová verze databázové platformy Oracle 2.0. Tato verze měla jako největší
nedostatek portabilitu na jiné hardwarové platformy. V roce 1983 vznikla databázová
platforma Oracle 3.0. Byla napsána v jazyce C a dala se již portovat na úrovni zdrojového
kódu. V roce 1984 vznikla verze Oracle 4.0. Tato verze podporovala spolupráci mezi více
servery při zachování konzistentního čtení.V roce 1985 vzniká verze Oracle 5.0, která
může být portována na všechny významné hardwarové platformy své doby. Rok 1988 byl
rokem vzniku verze Oracle 6.0, která umožňovala budování velkých transakčních
systémů. V roce 1993 vznikla verze Oracle 7.0 ve které bylo možno budovat velmi
rozsáhlé databáze a datové sklady s kapacitou více než 5 terabajtů. V roce 1997 vznikla
verze Oracle 8.0. V této verzi byl integrovaný jazyk Java pro aplikace klient-server. Tato
verze byla portována také na nově se vyvíjející operační systém Linux. Po verzi 8.0
následovala verze 8i , která se kompletně orientovala na prostředí Internetu.
Platforma Oracle9i byla poprvé představena na konferenci Oracle OpenWorld v říjnu
roku 2000. Komerčně byla tato platforma uvedena v červnu roku 2001. Tato verze
obsahuje nové technologie jako Real Applications Clusters a Cache Fusion, umožňující
jednoduché rozšiřování systému a sdílení vyrovnávacích pamětí. V roce 2003 přichází
nová a zatím poslední verze Oracle 10g. Databáze Oracle 10g lépe zajišťuje výkon a
stabilitu systému díky unikátním vlastnostem self-diagnosing a self-tuning.
Databáze Oracle 10g je první databází, navrženou pro Grid computing. Grid
computing snižuje náklady na IT spojením (clustering) více serverů, které se potom jeví
11
jako jeden velký počítač, který dynamicky rozděluje výpočetní výkon podle aktuální
potřeby jednotlivých aplikací.
Poslední verze Oracle 10g je zatím v oblasti databází spíše novinkou. Komerčně je
v současnosti nejvíce využívána verze Oracle9i. Tato bakalářská práce je tedy platná pro
verzi Oracle 9i. Na této verzi jsou také testovány uvedené příklady jazyka PL/SQL.
1.4.2 Databázová platforma Oracle 9i Prvořadé požadavky na databázové platformy jsou spolehlivost a bezpečnost. Další
neméně důležité požadavky jsou na výkon, rychlost a jednoduchou administraci.
Platforma Oracle 9i obsahuje novou architekturu, která umožňuje administrátorům
reorganizaci databáze včetně změny databázových struktur za provozu. Důsledně je tak
podporován požadavek na stoprocentní dostupnost.
Nejvýznamnější technologické vymoženosti platformy Oracle 9i jsou:
• architektura Real Application Clusters
- tato architektura umožňuje zákazníkům využít vedle vysokého výkonu
především možnosti jednoduchého rozšiřování jejich systému (jde o tzv.
škálovatelnost), lze tedy škálovat databázovou vrstvu v závislosti na
zvýšení zátěže, která může být způsobena zvýšením počtu přístupu
k datům
• zvýšení výkonnosti jazyka PL/SQL
- byla implementována podpora pro nativní překlad a zdokonalená
optimalizace kódu v jazyce PL/SQL
• podpora technologie XML
- poskytuje vhodný formát pro výměnu dat mezi aplikacemi, ve verzi
Oracle 9i Release 2 je podpora XML integrována přímo do jazyka SQL
• koncept Oracle Managed Files
- tento koncept zjednodušuje správu databáze eliminováním přímé správy
souborů tvořících vlastní databázi
12
2. Základní programový blok jazyka PL/SQL Programovací jazyk PL/SQL je modulární a tudíž i blokově strukturovaný, což
znamená, že programy mohou být rozděleny do logických bloků. Základní entitou jazyka
je tedy programový blok. Rozlišujeme anonymní programový blok, dále programový blok
procedury, funkce, balíčku (package) nebo spouště (triggeru).
Anonymní programový blok se skládá ze tří základních částí (sekcí):
• deklarační sekce
• výkonná sekce
• sekce pro zpracování výjimek
obrázek 1 Programový blok
2.1 Deklarační sekce Deklarační sekce obsahuje deklarace proměnných, konstant a kurzorů. Tato sekce
může také obsahovat deklaraci uživatelsky definovaných výjimek, které se vyskytují ve
výkonné sekci. Tato sekce není povinná.
2.2 Výkonná sekce Výkonná sekce obsahuje funkční logiku a je to tedy aplikační jádro programového
bloku. Pomocí příkazů v této sekci lze manipulovat s daty v databázi, tuto manipulaci
obstarávají SQL příkazy. PL/SQL příkazy naopak manipulují s daty v programovém
bloku. Tato sekce je povinná.
DECLARE
BEGIN
EXCEPTION
END;
-- deklarační sekce …
-- sekce zpracování výjimek …
-- výkonná sekce …
13
2.3 Sekce pro zpracování výjimek Tato sekce obsahuje ošetření chybových stavů, které mohou nastat ve výkonné sekci.
Tato sekce není povinná. Protože deklarační sekce a sekce pro zpracování výjimek nejsou
povinné lze minimální funkčně správné schéma programového bloku zobrazit takto:
obrázek 2 Minimální programový blok
2.4 Kód programového bloku Při psaní kódu programových bloků PL/SQL musíme dodržovat některé zákonitosti:
• konec každého příkazu či deklarace zakončit znakem „;“ (středník)
• použít znak lomítko „/“ ke spuštění anonymního PL/SQL bloku, jestliže blok
spouštíme v aplikaci SQL*Plus
• použít znak tečka „.“ k uzavření bufferu aplikace SQL*Plus (k oddělení
programového bloku)
• když spuštění bloku proběhne úspěšně, to znamená bez neošetřených chyb
(výjimek) nebo chyb kompilace, je vrácena výstupní zpráva (odezva konzole)
„PL/SQL procedure succesfully completed“, v případě anglické verze
databázového stroje, v případě české verze „Procedura PL/SQL úspěšně
dokončena “
• v PL/SQL se chyba (error) nazývá „exception“
• za klíčová slova sekcí („DECLARE“,“BEGIN“,“EXEPTION“) nepíšeme znak
středník „;“, ačkoli klíčové slovo „END“ a všechny další PL/SQL příkazy
vyžadují ukončení znakem středník „;“
BEGIN
-- výkonná sekce …
END;
14
Ukázka kódu programového bloku PL/SQL:
DECLARE v_variable VARCHAR(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN ... END;
2.5 Proměnné v jazyce PL/SQL Proměnné v jazyce PL/SQL slouží především k dočasnému uchovávání hodnot. Díky
proměnným pak není nutné hodnoty pokaždé získávat z databáze. Pomocí proměnných
lze také snadněji manipulovat s uloženými hodnotami.
Proměnné v jazyce PL/SQL musíme před jejich prvním použitím deklarovat. Během
provádění deklarační sekce se pro deklarovanou proměnnou vyhradí odpovídající
paměťový prostor. Během deklarace je možné proměnnou také inicializovat. Inicializace
proměnné (tedy počáteční přiřazení hodnoty) dosáhneme pomocí operátoru přiřazení
(znaků „:=“) nebo pomocí klíčového slova DEFAULT. Pomocí operátoru NOT NULL
můžeme určit omezení, které říká, že proměnná nesmí nabývat hodnoty NULL.
Proměnné mohou být různých datových typů, které jsou shodné či odvozené od
nativních datových typů databázové platformy Oracle9i.
2.5.1 Typy proměnných Skalární datové typy
- slouží pro ukládání jedné hodnoty
Kompozitní datové typy
- slouží pro ukládání složitějších datových struktur jako jsou pole atd.
Referenční datové typy
- slouží pro uchování objektu sloužícího jako ukazatel (pointer)
LOB typy
- slouží pro uchování lokátorů (specifikují umístění LOB objektu)
V PL/SQL programech se používají také „vazební proměnné“, někdy se nazývají také
„globální“. Tyto proměnné slouží k provázání jednotlivých prostředí, kde běží daná
15
aplikace. Nejčastější použití těchto proměnných je v aplikaci SQL*Plus. V PL/SQL bloku
se odlišují tím, že jako první znak v názvu proměnné mají dvojtečku „:“.
Datový typ Popis VARCHAR2(maximální_délka) Základní typ pro uchování řetězcových proměnných
proměnné délky. Maximální počet bajtů je 32 767. NUMBER[(p,s)] Základní typ pro uchování celých i desetinných čísel (s
pevnou i pohyblivou řádovou čárkou). DATE Základní typ pro uchování data a času. CHAR[(maximální_délka)] Základní typ pro uchování řetězcových proměnných
pevné délky. LONG Typ pro uchování rozsáhlých řetězcových proměnných
proměnné délky. Maximální počet bajtů je 2 147 483 647.
BOOLEAN Základní typ pro uchování tří možných hodnot: TRUE, FALSE nebo NULL
BINARY_INTEGER Typ určený pro uchování čísel v intervalu (-2 147 483 647; 2 147 483 647)
PLS_INTEGER Typ určený pro uchování označených čísel v intervalu (-2 147 483 647; 2 147 483 647)
tabulka 1 Tabulka skalárních datových typů PL/SQL
Datový typ Popis TABLE Definuje uspořádaný seznam elementů stejného typu.
Pro identifikaci elementů se používá sloupec typu BINARY_INTEGER.
RECORD Slouží pro uchování datových typů celého řádku tabulky.
tabulka 2 Tabulka kompozitních datových typů PL/SQL
2.5.2 Ukázka deklarací skalárních datových typů v_job VARCHAR2(9); v_country VARCHAR2(30) := 'Czech Republic'; v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_order_date DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE;
V ukázce je vidět jak lze deklarovat a popřípadě i inicializovat proměnné různých
datových typů.
16
Stručný popis deklarací z ukázky:
v_job … tato proměnná je typu řetězec s proměnnou délkou, ale maximální
počet znaků řetězce je 9
v_country … tato proměnná je také typu řetězec s proměnnou délkou, ale
maximální počet znaků řetězce je 30 a je také inicializována
výrazem „Czech Republic“
v_count … tato proměnná je deklarována jako celé číslo a je také inicializována
hodnotou 0
v_total_sal … tato proměnná je deklarována jako desetinné číslo, které má
maximum číslic před desetinnou čárkou 9 a maximum čísel za
desetinnou čárkou 2
v_order_date … tato proměnná je deklarována jako datumová hodnota a je
inicializována datumem za sedm dní od systémového data
c_tax_rate … tato konstanta je deklarována jako desetinné číslo, které má
maximum číslic před desetinnou čárkou 3 a maximum čísel za
desetinnou čárkou 2 a je jí přiřazena hodnota 8.25
v_valid … tato proměnná je deklarována jako boolenovská, nesmí nabývat
hodnoty NULL a je inicializována hodnotou TRUE
2.5.3 Typ proměnné podle jiné proměnné nebo sloupce tabulky
Platforma Oracle9i nabízí atribut „%TYPE“, který umožňuje deklarovat datový typ
včetně rozsahu přesně podle datového typu sloupce tabulky nebo jiné proměnné. Určení
datového typu se tedy provede pomocí odkazu na datový typ již existujícího objektu.
Ukázka deklarace přes atribut „%TYPE“:
v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10;
V této ukázce je deklarována proměnná „v_ename“, které je přiřazen shodný datový
typ jako má sloupec „ename“ tabulky „emp“. Další proměnná „v_balance“ má datový typ
číslo, a shodný datový typ bude mít i proměnná „v_min_balance“.
Upozornění :
- když je na sloupci tabulky omezení NOT NULL a tento datový typ sloupce je
přiřazen nějaké proměnné, tak tato proměnná může nabývat hodnoty NULL
17
2.6 Výpis textu do konzole Pro výpis textu do konzole slouží standardní programový balík „DBMS_OUTPUT“,
konkrétně jeho procedura „PUT_LINE“. Tato procedura je velmi užitečná při ladění
PL/SQL kódu.
Jestliže chceme použít tuto proceduru z prostředí SQL*Plus je nutné nejprve
aktivovat textový výpis do konzole, případně nastavit velikost vyrovnávací paměti
výstupu. Standardně je velikost vyrovnávací paměti výstupu nastavena na 2000.
Aktivaci textového výpisu provedeme příkazem „SET SERVEROUT ON“, jestliže
chceme nastavit i velikost vyrovnávací paměti výstupu použijeme příkaz „SET SERVER
OUT ON SIZE 10000“.
Ukázka použití procedury „PUT_LINE“:
BEGIN DBMS_OUTPUT.PUT_LINE('Hello world'); END;
Po spuštění tohoto programového bloku se na konzoli objeví text „Hello world“.
2.7 Pravidla pro psaní PL/SQL bloků Příkazy
- mohou být napsány na několika řádcích, ale musejí být zakončeny
znakem středník „;“
Programové bloky
- mohou být složeny z mezer, identifikátorů, oddělovačů, konstant
(klíčových slov), komentářů
Identifikátory
- mohou být složeny maximálně z 30 znaků
- nesmějí být shodné s rezervovanými slovy ORACLE, pakliže nejsou
uzavřeny v uvozovkách (příklad: „SELECT“)
- musejí začínat abecedním znakem
Řetězcové hodnoty
- musejí být uzavřeny v apostrofech (příklad: ‘jméno a příjmení’)
Datumové hodnoty
18
- pro zadání datumových hodnot je vhodné použít funkci „TO_DATE“,
příklad: TO_DATE(’January 13, 2004’,’Month DD, YYYY’)
Číselné hodnoty
- zadávají se přímo (bez uvozovek nebo apostrofů)
- lze je zadat i ve vědeckém formátu, příklad: 2E5, což odpovídá 2 * 105
PL/SQL blok je zakončen znakem lomítko „/“, které slouží pro
odeslání bufferu ke zpracování. Tento znak musí být na samostatném
řádku.
Symbol Význam Symbol Význam + Operátor sčítání <> Operátor porovnání - Odečítání nebo negační operátor != Operátor porovnání * Operátor násobení || Operátor zřetězení / Operátor dělení -- Indikátor jednořádkového
komentáře = Operátor porovnání /* Indikátor začátku víceřádkového
komentáře @ Indikátor vzdáleného přístupu */ Indikátor konce víceřádkového
komentáře ; Zakončení příkazu := Operátor přiřazení
tabulka 3 Tabulka oddělovacích znaků
2.7.1 Komentáře v kódu PL/SQL
V jazyce PL/SQL je možné využívat dva typy komentářů:
• jednořádkové komentáře
- začínají dvěma pomlčkami „--“
• víceřádkové komentáře
- uzavírají se do párových značek „/* a */“
Komentáře se nepřekládají ani neprovádějí, slouží jen pro větší přehlednost
programového kódu, která přispívá k rychlosti vývoje aplikací a efektivnějšímu ladění.
Ukázka použití komentářů v kódu PL/SQL:
/* Tento PL/SQL blok vypíše na konzoli jméno uložené do proměnné v_variable */ DECLARE -- deklarace a inicializace proměnné v_variable VARCHAR(5) := 'David';
19
BEGIN -- použití standardního balíčku ORACLE DBMS_OUTPUT.PUT_LINE(v_variable); END;
2.7.2 SQL funkce v PL/SQL
V programovém kódu PL/SQL lze používat všechny funkce, které jsou dostupné
v jazyku SQL mimo funkce DECODE a všech agregačních funkcí.
Ukázka použití vestavěných SQL funkcí v kódu PL/SQL: -- funkce LOWER změní všechna písmena na malá v_variable VARCHAR(5) := LOWER('David'); -- funkce TO_DATE slouží ke konverzi řetězcové hodnoty na typ DATE v_order_date DATE := TO_DATE('25.12.2004','DD.MM.YYYY');
2.7.3 Vnořené bloky PL/SQL
Při programování v jazyce PL/SQL je možné používat vnořené bloky, což jsou
vlastně programové bloky vložené do výkonné sekce jiného bloku. Programový kód ve
vnitřním vnořeném bloku může využívat proměnné deklarované ve vnějším bloku.
Naopak ve vnějším bloku nelze používat proměnné deklarované ve vnitřním bloku.
obrázek 3 Vnořený programový blok
DECLARE
BEGIN
EXCEPTION
END;
-- deklarační sekce …
-- sekce zpracování výjimek …
-- výkonná sekce …
-- deklarační sekce …
-- výkonná sekce …
-- sekce zpracování výjimek …
DECLARE
BEGIN
EXCEPTION
END;
Vnořený programový blok
20
2.7.4 Operátory v PL/SQL
Podobně jako v případě příkazů jazyka SQL také v jazyce PL/SQL používáme
operátory pro zápis matematických a logických operací. Platí také priorita operátorů,
kterou lze ovlivnit pomocí závorek. Jediný operátor, který není v jazyce SQL a je pouze
v jazyce PL/SQL je operátor umocnění.
Následující tabulka obsahuje přehled operátorů seřazených podle jejich priorit.
Operátor Operace **, NOT Umocňování, logická negace +,- Změna znaménka *, / Násobení, dělení +,-,|| Sčítání, odečítání, slučování =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN
Porovnávání
AND Logický součin OR Logický součet
tabulka 4 Operátory jazyka PL/SQL
X y x AND y x OR y NOT x TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE TRUE FALSE TRUE NULL NULL TRUE FALSE FALSE TRUE FALSE TRUE TRUE FALSE FALSE FALSE FALSE TRUE FALSE NULL FALSE NULL TRUE NULL TRUE NULL TRUE NULL NULL FALSE FALSE NULL NULL NULL NULL NULL NULL NULL
tabulka 5 Pravdivostní tabulka logických operátorů
Upozornění:
- při logickém součinu proměnné typu boolen TRUE a proměnné typu
boolen NULL je výsledkem hodnota NULL.
2.7.5 Doporučená pravidla pro přehlednější zápis programového kódu
• SQL příkazy, klíčová slova jazyka PL/SQL a datové typy je vhodné psát velkými
písmeny
• Názvy proměnných, tabulek, sloupců a parametrů je vhodné psát malými písmeny
21
• Jednotlivé úrovně a vnořené kódy by měly být odsazené (pomocí mezer nebo
tabelátorů)
• Je vhodné používat komentáře v kódu
Doporučené značení Popis v_xxx Označuje skalární proměnnou (variable) c_xxx Označuje konstantu (constant) xxx_cur Označuje kurzor e_xxx Označuje výjimku xxx_tab_typ Označuje PL/SQL typ tabulky xxx_tab Označuje PL/SQL tabulku xxx_rec_type Označuje PL/SQL typ záznamu (record) xxx_rec Označuje PL/SQL záznam (record) p_xxx Označuje SQL*Plus substituční proměnnou g_xxx Označuje SQL*Plus vazební proměnnou (global)
tabulka 6 Doporučené konvence pro proměnné PL/SQL
2.8 Použití SQL příkazů v jazyce PL/SQL Jazyka PL/SQL je primárně určen pro získávání a zpracování dat z databázových
tabulek, zejména s ohledem na zajištění aplikační logiky. Nejčastěji pomocí jazyka
PL/SQL generujeme dotazy SQL a získaná data dále zpracujeme, případně naopak, data
získaná z nějakého procesu či zařízení nejprve zpracujeme a teprve poté je ukládáme do
databázových tabulek.
Když potřebujeme získat data z databáze, nebo provést v databázi nějakou změnu,
musíme použít jazyk SQL. Jazyk PL/SQL plně podporuje příkazy DML (Data
Manipulation Language) a DCC (Transaction Control Commands). Pro získání dat
z databáze je nutné v jazyku PL/SQL použít příkaz „SELECT“, který musí být doplněn
klausulí „INTO“ a omezen pouze na vrácení dat jednoho řádku.
2.8.1 Porovnání SQL a PL/SQL příkazů • PL/SQL blok není transakční jednotkou. Příkazy „COMMIT, SAVEPOINT,
ROLLBACK “ jsou na blocích nezávislé.
• Jazyk PL/SQL nepodporuje příkazy DDL (Data Definition Language), jako jsou
příkazy „CREATE TABLE, ALTER TABLE, DROP TABLE“
• Jazyk PL/SQL nepodporuje příkazy DCL (Data Control Language), jako jsou
příkazy „GRAND, REVOKE“
22
2.8.2 Příkaz SELECT v jazyce PL/SQL
Tento příkaz slouží k získání dat z databáze a jejich uložení do proměnných.
Syntaxe příkazu je:
SELECT select_list INTO {variable_name[, variable_name] ... | record_name} FROM table WHERE condition ;
Vysvětlení syntaxe: select_list … je to výpis jednotlivých sloupců tabulky, může obsahovat SQL
výrazy, SQL funkce nebo agregační funkce variable_name … je to skalární výstupní proměnná pro uložení navrácené hodnoty record_name … je to proměnná typu PL/SQL RECORD pro uložení navrácených
hodnot table … je to název databázové tabulky condition … je to podmínkový výraz složený z názvů sloupců, výrazů, konstant a
operátorů porovnání, může obsahovat i PL/SQL proměnné a konstanty
Ukázka použití příkazu SELECT v jazyce PL/SQL:
DECLARE -- deklarace proměnných v_deptno NUMBER(2); v_loc dept.loc%TYPE; BEGIN -- použití dat z tabulky dept SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = 'SALES'; END;
Pravidla pro použití příkazu „SELECT“ v jazyce PL/SQL:
• příkaz musí být zakončen znakem středník „;“
• příkaz musí obsahovat klausuli „INTO“
• příkaz musí mít shodný počet výstupních proměnných a sloupců tabulky,
výstupní proměnné musejí mít stejné pořadí jako sloupce tabulky
• příkaz může obsahovat klausuli „WHERE“, která je použita pro specifikaci
vstupních proměnných, konstant nebo PL/SQL výrazů
• musí být shodný datový typ výstupní proměnné a datový typ sloupce a je vhodné
používat atribut „%TYPE“
23
• agregační funkce lze použít pouze jako součást příkazu SQL
• příkaz musí vrátit pouze záznamy jednoho řádku tabulky jinak nastane výjimka
„TOO_MANY_ROWS“, když příkaz nevrátí žádný záznam nastane výjimka
„NO_DATA_FOUND“
2.8.3 Příkazy pro manipulaci s daty v jazyce PL/SQL
Když potřebujeme provést nějakou změnu dat v databázi je nutné použít příkazy
DML. Jedná se o tři příkazy:
• příkaz „INSERT“
- slouží k přidání nového záznamu do tabulky
• příkaz „UPDATE“
- slouží k modifikaci jednoho nebo více existujících záznamů v tabulce
• příkaz „DELETE“
- slouží ke smazání jednoho nebo více záznamů z tabulky
Ukázka použití příkazu „UPDATE“ v jazyce PL/SQL:
DECLARE -- deklarace promennych v_deptno dept.deptno%TYPE := 40; v_loc dept.loc%TYPE := 'COLORADO'; BEGIN -- modifikace dat v tabulce dept UPDATE dept SET loc = v_loc WHERE deptno = v_deptno; -- potvrdi zmeny v databazi COMMIT; END;
Pro potvrzení nebo navrácení změn se používají transakční příkazy „COMMIT“,
„ROLLBACK “. Lze také použít transakční příkaz „SAVEPOINT“.
2.8.4 SQL kurzory a jejich použití v PL/SQL
Pro libovolný SQL příkaz vytvoří databázový server oblast v pracovní paměti, ve
které zpracovává analýzu a další pomocné informace pro provedení daného příkazu.
V této oblasti paměti se vytváří také ukazatel (kurzor), který ukazuje na aktuálně zasílaný
záznam na klienta. Tento ukazatel se při jednoduchém výpisu pomocí příkazu „SELECT“
posouvá od prvního k poslednímu záznamu.
24
Kurzory mohou být implicitní, vytvářené automaticky databázovým serverem a
explicitní, deklarované programátorem. Pro implicitní kurzory existují čtyři atributy
dostupné i z prostředí PL/SQL.
Atribut Popis SQL%ROWCOUNT Vrací počet řádek ovlivněných naposledy použitým příkazem
SQL, atribut vrací číselnou hodnotu SQL%FOUND Vrací boolen hodnotu TRUE, pakliže naposledy použitý příkaz
SQL ovlivnil jeden nebo více řádků SQL%NOTFOUND Vrací boolen hodnotu TRUE, pakliže naposledy použitý příkaz
SQL neovlivnil jeden nebo více řádků SQL%ISOPEN Vrací vždy boolen hodnotu FALSE, protože PL/SQL uzavírá
implicitní kurzor okamžitě po vykonání příkazu
tabulka 7 Tabulka implicitních kurzorů
Ukázka použití atributu „SQL%ROWCOUNT“ implicitního kurzoru:
DECLARE -- deklarace promennych v_deptno emp.deptno%TYPE := 20; -- deklarace konstanty c_sal_rate CONSTANT NUMBER(3,2) := 1.25; BEGIN -- modifikace dat v tabulce emp UPDATE emp SET sal = sal * c_sal_rate WHERE deptno = v_deptno; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows were updated'); -- potvrdi zmeny v databazi COMMIT; END;
V této ukázce je vypsán počet modifikovaných záznamů. Po provedení příkazu
„UPDATE“ obsahuje atribut „SQL%ROWCOUNT“ počet ovlivněných záznamů.
2.9 Řízení toku programu Programy, které neobsahují podmínky, cykly nebo explicitní kurzory jsou prováděny
sekvenčně. Znamená to, že program je vykonáván postupně od prvního až po poslední
příkaz.
25
2.9.1 Podmínky
V reálné aplikaci je nutné reagovat na různé stavy a události. Podmínkovou
konstrukcí s příkazem „IF“ je možné dosáhnout větvení programového kódu. Pomocí
příkazu „IF“, podobně jako v jiných vyšších programovacích jazycích, můžeme sestavit
tři druhy podmínek.
• IF – THEN – END IF
• IF – THEN – ELSE – END IF
• IF – THEN – ELSIF – END IF
Syntaxe podmínky je:
IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF;
Vysvětlení syntaxe:
condition … je to boolen proměnná nebo výraz („TRUE“, „FALSE“, „NULL“) statements … je to jeden nebo více PL/SQL nebo SQL příkazů
Ukázka použití podmínky typu IF – THEN – ELSE – END IF :
DECLARE -- deklarace promennych v_deptno emp.deptno%TYPE := 20; BEGIN -- pouziti podminky IF v_deptno = 20 THEN DBMS_OUTPUT.PUT_LINE('DEPTNO is 20'); ELSE DBMS_OUTPUT.PUT_LINE('DEPTNO is not 20'); END IF; END;
V této ukázce se testuje proměnná „v_deptno“. Když je tato proměnná rovna číslu 20,
podmínka „v_deptno = 20“ je splněna a je vyhodnocena jako boolen proměnná „TRUE“.
Následuje tedy vykonání kódu za klíčovým slovem „THEN“, kód za klíčovým slovem
„ELSE“ se neprovede.
26
Ukázka použití podmínky typu „IF – THEN – ELSIF – END IF“:
DECLARE -- deklarace promennych v_deptno emp.deptno%TYPE := 10; -- dalsi moznosti pro test podminky --v_deptno emp.deptno%TYPE := 30; --v_deptno emp.deptno%TYPE := 20; --v_deptno emp.deptno%TYPE := NULL; BEGIN -- pouziti podminky IF v_deptno = 20 THEN DBMS_OUTPUT.PUT_LINE('DEPTNO is 20'); ELSIF v_deptno > 20 THEN DBMS_OUTPUT.PUT_LINE('DEPTNO is more than 20'); ELSIF v_deptno < 20 THEN DBMS_OUTPUT.PUT_LINE('DEPTNO is less than 20'); ELSE DBMS_OUTPUT.PUT_LINE('DEPTNO is NULL'); END IF; END;
V této ukázce se testuje proměnná „v_deptno“. Když je tato proměnná rovna číslu 10,
je podmínka „v_deptno < 20“ splněna a vyhodnocena jako boolen proměnná „TRUE“.
Následuje vykonání kódu za touto podmínkou, programový kód za ostatními podmínkami
se nevykoná.
Poznámka:
- pozor, když nastane situace, kdy je splněno více podmínek najednou,
vykoná se pouze programový kód za první splněnou podmínkou.
Vnořené podmínky
Podmínky lze také vnořovat do sebe. Vnořená podmínka se vytvoří tak, že v
podmínce do bloku „THEN“ nebo „ELSE“ vepíšeme další podmínku. Tato vnořená
podmínka se vyhodnocuje pouze v případě, kdy je spuštěn blok nadřazené podmínky.
Ukázka použití vnořené podmínky :
DECLARE -- deklarace promennych v_deptno emp.deptno%TYPE := 30; BEGIN -- pouziti podminky IF v_deptno = 20 THEN DBMS_OUTPUT.PUT_LINE('DEPTNO is 20'); ELSE DBMS_OUTPUT.PUT_LINE('DEPTNO is not 20');
27
-- pouziti vnorene podminky IF v_deptno > 20 THEN DBMS_OUTPUT.PUT_LINE('DEPTNO is more than 20'); END IF; END IF; END;
V této ukázce se testuje proměnná „v_deptno“. Když je tato proměnná rovna číslu 30,
není podmínka „v_deptno = 20“ splněna a je vyhodnocena jako boolen proměnná
„FALSE“. Následuje tudíž vykonání kódu za klíčovým slovem „ELSE“.
V bloku „ELSE“ je vnořená podmínka „v_deptno > 20“, která je vyhodnocena jako
boolen proměnná „TRUE“ a u této podmínky je vykonán kód za klíčovým slovem
„THEN“.
2.9.2 Příkaz „CASE“ pro vícenásobné větvení programu
Pro větvení programu lze použít nejen příkaz „IF“, ale také příkaz „CASE“.
V některých případech je použití tohoto příkazu vhodnější, zejména z důvodu
zpřehlednění programového kódu a zvýšení efektivnosti.
Poznámka: Příkaz „CASE“ lze využívat pouze na ORACLE platformě 9i a vyšší.
Syntaxe příkazu „CASE“ pro větvení programu pomocí konkrétní hodnoty proměnné: CASE variable WHEN expression1 THEN Statements1; [WHEN expression2 THEN statements2; WHEN expressionN THEN statementsN;] END CASE;
Existuje i jiný způsob větvení programu pomocí příkazu „CASE“. Ne vždy totiž
potřebujeme větvit program pomocí konkrétních hodnot proměnné. Za klíčovým slovem
„WHEN“ může následovat libovolná logická podmínka.
Syntaxe příkazu „CASE“ pro větvení programu pomocí logické podmínky je : CASE WHEN condition1 THEN Statements1; [WHEN condition2 THEN statements2; WHEN conditionN THEN statementsN;] END CASE;
28
Ukázka použití příkazu „CASE“ pro větvení programu pomocí konkrétní hodnoty
proměnné : DECLARE -- deklarace promennych v_mark NUMBER(1) := 2; BEGIN -- pouziti podminky CASE CASE v_mark WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Commendable'); WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Well'); WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Sufficient'); WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('Insufficient'); ELSE DBMS_OUTPUT.PUT_LINE('Mark is not valid'); END CASE; END;
V této ukázce se testuje proměnná „v_mark“. Proměnná se porovnává se všemi
hodnotami zadanými za klíčovým slovem „WHEN“. Když je tato proměnná rovna číslu 2,
vykoná se pouze kód za částí příkazu „WHEN 2 THEN“.
2.9.3 Cykly
V programovém kódu se velmi často využívá cyklické opakování určitých bloků
kódu, přičemž se mění některé parametry a proměnné. Program je tedy vykonáván
v cyklu či smyčce.
Jazyk PL/SQL umožňuje vytvářet tři druhy cyklů:
• Jednoduchý cyklus „LOOP“ bez podmínky, který je možné ukončit příkazem
„EXIT“
• Cyklus „FOR“ na principu čítače
• Cyklus „WHILE“ s podmínkou na začátku cyklu
Jednoduchý cyklus
Tomuto cyklu se přezdívá také nekonečný cyklus, protože sekvence příkazů v něm
zapsaná se bude opakovat do té doby, dokud z cyklu nevyskočíme příkazem „EXIT“.
Syntaxe jednoduchého cyklu je: LOOP statement1; EXIT [WHEN condition]; END LOOP;
29
Vysvětlení syntaxe:
statements … je to jeden nebo více PL/SQL nebo SQL příkazů condition … je to boolen proměnná nebo výraz („TRUE, FALSE, NULL“)
Ukázka použití jednoduchého cyklu : DECLARE -- deklarace a inicializace promenne pro cyklus v_counter NUMBER(2) := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('Pocet behu cyklu: '||v_counter); -- navysovani promenné pro cyklus v_counter := v_counter + 1; -- podminka pro ukonceni cyklu EXIT WHEN v_counter > 10; END LOOP; END;
V této ukázce je použitý jednoduchý cyklus. V těle cyklu se navyšuje proměnná
cyklu, která se dále testuje podmínkou „EXIT WHEN“. Když je hodnota této proměnné
větší než číslo 10, pak se cyklus ukončí a vykonání programového kódu se přesune za
klíčová slova „END LOOP;“. Cyklus tedy v tomto případě proběhne desetkrát, desetkrát
se tedy vypíše i informační zpráva o počtu běhů cyklu.
Poznámka:
- pozor, tento druh cyklu musí obsahovat podmínku s příkazem „EXIT“,
který slouží pro vyskočení z těla cyklu, tato podmínka se musí při každém
běhu programového kódu alespoň jednou vykonat, jinak vznikne
nekonečný cyklus.
Cyklus „FOR“ Cyklus typu „FOR“ funguje na principu čítače.Tento typ cyklu se často využívá ve
vyšších programovacích či skriptovacích jazycích. Proměnnou, která tvoří počítadlo
cyklu, nemusíme deklarovat ani inicializovat. Po každém průchodu cyklem se čítač
automaticky navýší. Klausule „REVERSE“ umožňuje realizovat cyklus, v němž dochází
ke snižování čítače. Proměnná čítače je dostupná pouze v těle cyklu a není povoleno ji
programově měnit.
Syntaxe cyklu „FOR“ je: FOR counter IN [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . . END LOOP;
30
Vysvětlení syntaxe:
counter … specifikuje čítač, implicitně deklarované číslo, které automaticky narůstá nebo se snižuje o hodnotu 1 při každém běhu cyklu
statement1,2 … je to jeden nebo více PL/SQL nebo SQL příkazů lower_bound … specifikuje dolní mez čítače, při které se cyklus provede upper_bound … specifikuje horní mez čítače, při které se cyklus provede REVERSE … definuje, zda čítač bude narůstat nebo se snižovat, když je tato
klausule zadána, čítač se bude snižovat Ukázka použití cyklu „FOR“ : BEGIN -- cyklus FOR v_counter IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE('Counter = '||v_counter); END LOOP; END;
V této ukázce je použitý cyklus „FOR“, který se provede třikrát. V těle cyklu se
automaticky navyšuje čítač cyklu, jehož hodnota je také zobrazována na konzoli.
V případě, že potřebujeme určit rozsah hodnot pro čítač dynamicky, například podle
počtu záznamů v tabulce, lze použít místo hodnoty rozsahu proměnnou.
Ukázka použití cyklu „FOR“ s proměnnou v rozsahu čítače : DECLARE -- deklarace promenne pro horni rozsah cyklu v_count NUMBER(5); BEGIN -- inicializace promenne pro horni rozsah cyklu SELECT count(*) INTO v_count FROM emp; -- cyklus FOR v_counter IN 1..v_count LOOP DBMS_OUTPUT.PUT_LINE('Counter = '||v_counter); END LOOP; END;
V této ukázce je použitý cyklus „FOR“, počet běhů cyklu je závislý na počtu záznamů
v tabulce „emp“. V rozsahu hodnot pro čítač cyklu je použita proměnná, která byla
inicializována číslem určujícím počet záznamů v tabulce „emp“.
Cyklus „WHILE“ Cyklus typu „WHILE“ je cyklus, který testuje podmínku na začátku cyklu. Když je
podmínka splněna, cyklus se vykoná, když podmínka splněna není cyklus se nevykoná.
Podmínka se testuje při každém běhu cyklu.
31
Syntaxe cyklu „WHILE“ je: WHILE condition LOOP statement1; statement2; . . . END LOOP;
Vysvětlení syntaxe:
condition … je to boolen proměnná nebo výraz („TRUE, FALSE, NULL“) statement1,2 … je to jeden nebo více PL/SQL nebo SQL příkazů
Ukázka použití cyklu „WHILE“ : DECLARE -- deklarace a inicializace promenne urcujici pocet cyklu v_count NUMBER(5) := 5; -- deklarace a inicializace pomocne promene pro cyklus v_counter NUMBER(5) := 0; BEGIN -- cyklus WHILE v_counter < v_count LOOP v_counter := v_counter + 1; DBMS_OUTPUT.PUT_LINE('Counter = '||v_counter); END LOOP; END;
V této ukázce se použitý cyklus „WHILE“ provede pětkrát. V těle cyklu se
programově navyšuje čítač cyklu, jehož hodnota je také zobrazována na konzoli.
V podmínce za klíčovým slovem „WHILE“ se testuje, zda je hodnota čítače cyklu nižší
než nadefinovaná hodnota. Cyklus se provádí až do stavu, kdy je podmínka „v_counter <
v_count“ vyhodnocena jako boolen proměnná „FALSE“.
2.10 Práce s kompozitními datovými typy Tak jako skalární proměnné tak i kompozitní proměnné mají své datové typy.
Kompozitní datové typy, známé také jako kolekce, jsou „RECORD“, „TABLE“, „Nested
TABLE“ a „VARRAY“. Datové typy „Nested TABLE“ a „VARRAY“ nejsou tak
používané jako „RECORD“ a „TABLE“, proto se bližší popis těchto datových typů v této
práci neuvádí. Bližší informace o těchto datových typech lze získat na internetovém
portálu společnosti Oracle.
Kompozitní datové typy obecně slouží k uchování nějaké množiny dat, v této
množině se pak lze pomocí různých metod pohybovat a zjišťovat hodnoty záznamů
uložených na požadované pozici.
32
2.10.1 PL/SQL RECORD
Datový typ „RECORD“ je vlastně datovým typem proměnné typu záznam. Proměnná
typu záznam obsahuje logicky svázanou skupinu datových polí. Každé datové pole v
proměnné typu záznam má svůj vlastní název a datový typ.
V proměnné typu záznam se mohou vyskytnout pole s těmito datovými typy:
- skalární datové typy
- datové typy „RECORD“
- datové typy „TABLE“
Pro proměnné typu záznam platí tyto zásady:
- počet polí záznamu není omezen
- polím záznamu mohou být přiřazeny inicializační hodnoty
- pole záznamu mohou být definovány s omezením „NOT NULL“
- pole bez inicializační hodnoty je inicializováno jako „NULL“
- proměnné typu záznam je třeba deklarovat v deklarační sekci
- v proměnné typu záznam lze jako pole deklarovat jinou proměnnou typu
záznam
Na jednotlivá pole v proměnné typu záznam se odkazujeme tečkovou notací.
Například na pole „job“ v proměnné typu záznam „emp_record“ se odkážeme
„emp_record.job“. Pro inicializaci pole v proměnné typu záznam použijeme tento zápis
„emp_record.job : = ‘CLERK’“. V tomto příkladu se pole „job“ inicializuje hodnotou
„CLERK“.
Syntaxe pro definici proměnné typu záznam je: TYPE type_name IS RECORD (field_declaration[,field_declaration] … ); identifier type_name;
Vysvětlení syntaxe:
type_name … je to název nově zavedeného datového typu „RECORD“ field_declaration … je to deklarace jednoho pole proměnné typu záznam identifier … je to název proměnné typu záznam
33
Syntaxe pro deklaraci pole v proměnné typu záznam je:
field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE } [[NOT NULL] { := | DEFAULT} expr ]
Vysvětlení syntaxe:
field_name … je to název pole v proměnné typu záznam field_type … je to datový typ jednoho pole v proměnné typu záznam, lze použít
atributy „%TYPE“ a „%ROWTYPE“ expr … je to datový typ pole v proměnné typu záznam nebo inicializační
hodnota pole
Ukázka použití proměnné typu záznam : DECLARE -- deklarace RECORDu TYPE emp_record_type IS RECORD (empno NUMBER(4) NOT NULL := 100, ename emp.ename%TYPE, job emp.job%TYPE); -- deklarace promenne typu RECORD emp_record emp_record_type; BEGIN -- ulozeni dat do promenne typu RECORD SELECT empno,ename,job INTO emp_record FROM emp WHERE empno=7499; -- zobrazeni informaci z promenne typu RECORD DBMS_OUTPUT.PUT_LINE('emp_record.empno is '||emp_record.empno); DBMS_OUTPUT.PUT_LINE('emp_record.ename is '||emp_record.ename); DBMS_OUTPUT.PUT_LINE('emp_record.job is '||emp_record.job); END;
V této ukázce se deklaruje proměnná typu záznam (RECORD). Poté se do této
proměnné uloží tři atributy z tabulky „emp“. Následuje vypsání polí proměnné typu
záznam na konzoli.
V případě, že chceme deklarovat proměnnou typu záznam podle struktury libovolné
tabulky, je vhodné použít atribut „%ROWTYPE“. Tento atribut zajistí, že struktura
proměnné typu záznam bude stejná jako struktura zadané tabulky.
Ukázka použití atributu „%ROWTYPE“ u proměnné typu záznam : DECLARE -- deklarace RECORDu s pouzitim atributu %ROWTYPE emp_record emp%ROWTYPE; BEGIN -- ulozeni dat do promenne typu RECORD SELECT * INTO emp_record FROM emp WHERE empno=7499; -- zobrazeni nekterych informaci z promenne typu RECORD DBMS_OUTPUT.PUT_LINE('emp_record.empno is '||emp_record.empno); DBMS_OUTPUT.PUT_LINE('emp_record.ename is '||emp_record.ename); DBMS_OUTPUT.PUT_LINE('emp_record.job is '||emp_record.job); END;
34
2.10.2 PL/SQL TABLE
PL/SQL tabulka se liší od SQL Tabulky. PL/SQL tabulka obsahuje uspořádaný
seznam elementů stejného typu, je tedy podobná poli (array), které je známé z jiných
programovacích jazyků.. Každý element je identifikován primárním klíčem, který určuje
jeho jednoznačnou pozici.
PL/SQL tabulka musí obsahovat dva atributy (sloupce):
- primární klíč, který je datového typu „BINARY_INTEGER“ a indexuje
PL/SQL tabulku
- sloupec se skalárním datovým typem nebo s datovým typem záznam
(record)
PL/SQL tabulka není omezena počtem elementů, může tedy dynamicky růst. Rozsah
datového typu „BINARY_INTEGER“ je (-2147483647; 2147483647) , hodnota
primárního klíče může tady nabývat i záporných hodnot.
Vytvoření PL/SQL tabulky sestává ze dvou kroků:
1. deklarace datového typu „TABLE“
2. deklarace proměnné pro deklarovaný datový typ „TABLE“
Syntaxe definice pro vytvoření PL/SQL tabulky je:
TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.column%TYPE | table%ROWTYPE } [NOT NULL] INDEX BY BINARY_INTEGER ; identifier type_name;
Vysvětlení syntaxe:
type_name … je to název nově zavedeného datového typu „TABLE“ column_type … je to skalární datový typ (lze použít atribut “%TYPE” k poskytnutí
datového typu požadovaného sloupce) nebo kompozitní datový typ „RECORD“
identifier … je to název proměnné typu „TABLE“
Ukázka použití proměnné typu „TABLE“: DECLARE -- deklarace datového typu TABLE s pouzitim atributu %ROWTYPE TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; emp_table emp_table_type; BEGIN -- ulozeni dat do promenne typu TABLE SELECT * INTO emp_table(1) FROM emp WHERE empno = 7369; SELECT * INTO emp_table(2) FROM emp WHERE empno = 7499;
35
SELECT * INTO emp_table(3) FROM emp WHERE empno = 7521; -- zobrazeni nekterych informaci z promenne typu TABLE DBMS_OUTPUT.PUT_LINE ('emp_table(1).ename is '||emp_table(1).ename); DBMS_OUTPUT.PUT_LINE ('emp_table(2).ename is '||emp_table(2).ename); DBMS_OUTPUT.PUT_LINE ('emp_table(3).ename is '||emp_table(3).ename); END;
V této ukázce se deklaruje proměnná typu „TABLE“. Sloupec primárního klíče má
datový typ „BINARY_INTEGER“, sloupec PL/SQL tabulky má datový typ „RECORD“.
Do PL/SQL tabulky se uloží tři řádky z tabulky „emp“. Následuje vypsání tří řádků
PL/SQL tabulky na konzoli, z každého řádku je vypsán pouze atribut (sloupec) „ename“.
Pro snadnější práci s datovým typem „TABLE“ jsou definovány PL/SQL tabulkové
metody.
Metoda Popis EXISTS(n) Vrací „TRUE“ jestliže n-tý element existuje v PL/SQL tabulce.COUNT Vrací aktuální počet elementů v PL/SQL tabulce. FIRST LAST
Vrací první a poslední (nejnižší a nejvyšší) index v PL/SQL tabulce. Vrací NULL když je PL/SQL tabulka prázdná.
PRIOR(n) Vrací číslo předcházejícího indexu před indexem n. NEXT(n) Vrací číslo následujícího indexu po indexu n. EXTEND(n,i) Zvětší velikost PL/SQL tabulky.
EXTEND přidá jeden null element do PL/SQL tabulky EXTEND(n) přidá n null elementů EXTEND(n,i) přidá n kopií i-tého elementu
TRIM TRIM odstraní jeden element na konci PL/SQL tabulky TRIM(n) odstraní n elementů na konci PL/SQL tabulky
DELETE DELETE odstraní všechny elementy z PL/SQL tabulky DELETE(n) odstraní n-tý element DELETE(m,n) odstraní elementy v rozsahu m … n
tabulka 8 Metody PL/SQL tabulky
Ukázka použití metod pro proměnnou typu „TABLE“:
DECLARE -- deklarace datoveho typu TABLE s pouzitim atributu %ROWTYPE TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; emp_table emp_table_type; BEGIN -- ulozeni dat do promenne typu TABLE SELECT * INTO emp_table(1) FROM emp WHERE empno = 7369; SELECT * INTO emp_table(2) FROM emp WHERE empno = 7499; SELECT * INTO emp_table(3) FROM emp WHERE empno = 7521; -- zobrazeni nekterych informaci z promenne typu TABLE DBMS_OUTPUT.PUT_LINE('emp_table(1).ename is '||emp_table(2).ename);
36
-- zobrazeni nekterych informaci o promenne typu TABLE IF emp_table.EXISTS(2) THEN DBMS_OUTPUT.PUT_LINE('elemet 2 exists'); END IF; DBMS_OUTPUT.PUT_LINE('count of elements : '||emp_table.COUNT); END;
V této ukázce se opět deklaruje proměnná typu „TABLE“. Do PL/SQL tabulky se
uloží tři řádky z tabulky „emp“. Následuje použití metody „EXISTS“, která testuje
existenci elementu 2. Element 2 v tabulce existuje, proto se na konzoli vypíše informační
text. Další metoda vrací celkový počet elementů v tabulce, tato informace je také vypsána
na konzoli.
2.11 Práce s kurzory Jak již bylo popsáno výše v tématu „SQL kurzory a jejich použití v PL/SQL“, kurzory
mohou být implicitní, vytvářené automaticky databázovým serverem, a explicitní,
deklarované programátorem.
Implicitní kurzory jsou deklarovány implicitně jazykem PL/SQL pro každý DML
příkaz nebo příkaz SELECT.
Explicitní kurzory jsou deklarovány a pojmenovány programátorem. Pro manipulaci
s nimi jsou definovány speciální příkazy.
2.11.1 PL/SQL explicitní kurzory
Explicitní kurzor si lze zjednodušeně představit jako data načtená do paměti. Těmito
daty lze pomocí speciálních příkazů sekvenčně procházet. Kurzor prochází sadu záznamů,
která je vrácena příkazem „SELECT“. Tato sada se nazývá aktivní sada. Aktivní sada je
složena z řádků, po řádcích se kurzor posouvá sekvenčně od prvního řádku až do
posledního řádku.
Přístup k datům prostřednictvím explicitního kurzoru se skládá ze čtyř základních
kroků.
1. Deklarace kurzoru
2. Otevření kurzoru
3. Výběr dat prostřednictvím kurzoru
4. Uzavření kurzoru
37
obrázek 4 Práce s explicitním kurzorem
Deklarace kurzoru
Pro deklaraci explicitního kurzoru slouží příkaz „CURSOR“. Deklarace se skládá ze
dvou částí. V první části určíme název kurzoru v druhé části samotný dotaz SQL.
Syntaxe pro deklaraci explicitního kurzoru je: CURSOR cursor_name IS select_statement;
Vysvětlení syntaxe:
cursor_name … je to námy vytvořený název kurzoru select_statement… je to příkaz SELECT bez použití klausule INTO
Ukázka použití deklarace explicitního kurzoru: DECLARE -- deklarace explicitniho kurzoru CURSOR emp_cur IS SELECT empno,ename FROM emp; BEGIN ... Otevření kurzoru
Při otevření explicitního kurzoru se vykoná SQL příkaz použitý v deklarační části.
Vytvoří se aktivní sada, která obsahuje všechny záznamy, které vrátil SQL příkaz
v deklarační části. Po otevření kurzoru ukazuje ukazatel na první záznam v aktivní sadě.
V případě, že SQL příkaz nevrátí žádné záznamy není vyvolán chybový stav.
Syntaxe pro otevření explicitního kurzoru je: OPEN cursor_name;
Deklarace kurzoru
Otevření kurzoru
Výběr dat (FETCH)
Je kurzor na konci?
Ne
Ano
Uzavření kurzoru
38
Vysvětlení syntaxe:
cursor_name … je to název již deklarovaného kurzoru Výběr dat
Po otevření kurzoru lze procházet aktivní sadu a zjišťovat hodnoty jednotlivých polí
aktivní sady. Pro sekvenční procházení kurzoru slouží příkaz „FETCH“. Zjištěné hodnoty
lze uložit do více skalárních proměnných nebo jedné proměnné typu záznam (record).
Počet skalárních proměnných musí odpovídat počtu sloupců v aktivní sadě. Také je nutné,
aby pořadí proměnných odpovídalo pořadí sloupců v aktivní sadě. Příkaz „FETCH“ lze
také použít pro zjištění, zda kurzor obsahuje nějaké záznamy.
Syntaxe pro výběr dat prostřednictvím explicitního kurzoru je: FETCH cursor_name; INTO [variable1, variable2, … | record_name];
Vysvětlení syntaxe:
cursor_name … je to název již deklarovaného kurzoru variable1 … je to název již deklarované proměnné record_name … je to název již deklarované proměnné typu záznam (record)
Ukázka použití výběru dat prostřednictvím explicitního kurzoru: DECLARE -- deklarace skalarnich promennych v_empno emp.EMPNO%TYPE; v_ename emp.ENAME%TYPE; -- deklarace explicitniho kurzoru CURSOR emp_cur IS SELECT empno,ename FROM emp; BEGIN --otevreni explicitniho kurzoru OPEN emp_cur; --cyklus prochazejici vsechny zaznamy v kurzoru LOOP --sekvencni prochazeni kurzoru FETCH emp_cur INTO v_empno,v_ename; --test zda je ukazatel kurzoru na konci EXIT WHEN emp_cur%NOTFOUND; --vypsani hodnot kurzoru DBMS_OUTPUT.PUT_LINE('v_empno = '||v_empno); DBMS_OUTPUT.PUT_LINE('v_empno = '||v_ename); END LOOP; END;
V této ukázce se nejprve deklarují skalární proměnné „v_empno“ a „v_ename“. Dále
se v deklarační sekci deklaruje explicitní kurzor „emp_cur“. Aktivní sada kurzoru je
nadefinována příkazem SELECT, který obsahuje projekci sloupců „empno“ a „ename“
tabulky „emp“. Ve výkonné sekci dojde nejprve k otevření kurzoru a poté je v cyklu
39
spouštěno sekvenční procházení aktivní sady. Na konzoli jsou při každém běhu cyklu
vypisovány hodnoty deklarovaných proměnných. Když je kurzor na konci aktivní sady,
atribut kurzoru „NOTFOUND“ se vyhodnotí jako „TRUE“ a cyklus je ukončen.
Atribut kurzoru Popis %ISOPEN Vrací vždy boolen hodnotu TRUE, když je explicitní kurzor
otevřen %NOTFOUND Vrací boolen hodnotu TRUE, když při procházení explicitním
kurzorem není vrácen žádný záznam (když je ukazatel kurzoru na konci aktivní sady)
%FOUND Vrací boolen hodnotu TRUE, když při procházení explicitním kurzorem je vrácen nějaký záznam
%ROWCOUNT Vrací počet záznamů, které byly dosud navráceny z aktivní sady (vrací pozici ukazatele explicitního kurzoru)
tabulka 9 Tabulka atributů explicitního kurzoru
2.12 Výjimky a jejich ošetření v jazyce PL/SQL Chyby běhu PL/SQL programu mohou být způsobeny špatným návrhem, chybou v
kódu, hardwarovou chybou a z mnoha jiných příčin. V programu nelze předem
odhadnout, které všechny chyby se v průběhu běhu vyskytnou. Lze ale ošetřit ty chyby,
které z povahy programu mohou vzniknout. Syntaktické chyby se projeví již v procesu
kompilace, program nebo programový blok lze přeložit a spustit až po odstranění těchto
chyb.
V mnoha programovacích jazycích platí, že když nastane běhová chyba například
přetečení zásobníku či dělení nulou, program se zastaví a vrací kontrolu operačnímu
systému. V jazyce PL/SQL má programátor navíc k dispozici také mechanismus
ošetřování výjimek. Ten dovoluje programátorovi vytvořit stabilní program, který dokáže
pokračovat v běhu i při výskytu běhové chyby.
V PL/SQL se varování a chyby nazývají výjimkami (exeptions) a dělí se na vnitřní
(definované systémem) a uživatelské. Vnitřní výjimkou je například známé dělení nulou
nebo nedostatek paměti. Některé z běžných výjimek jsou pojmenovány konstantami, např.
„ZERO_DIVIDE“ a „STORAGE_ERROR“. Vnitřní výjimky bez předdefinovaných
konstant, lze dodatečně pojmenovat.
40
Své vlastní výjimky (uživatelské) lze definovat v deklarační části PL/SQL bloku,
podprogramu či balíčku. Lze například nadefinovat výjimku s názvem
„NEDOSTATEK_PROSTREDKU“, která bude znamenat přečerpání určitého
bankovního účtu. Narozdíl od vnitřních výjimek musí být uživatelské výjimky
pojmenovány.
V případě, že v průběhu běhu programu nastane chyba, je vyvolána příslušná
výjimka. Znamená to, že běh programu je zastaven a kontrola je předána bloku či
podprogramu, který ošetřuje výjimku. Vnitřní výjimky jsou vyvolávány automaticky
runtime systémem. Uživatelské výjimky je nutné vyvolat explicitně pomocí příkazu
„RAISE“, který umožňuje zároveň vyvolávat i předdefinované výjimky.
K ošetření vyvolané výjimky se používá tzv. „exception handler“, což je zvláštní část
programu, která při svém spuštění zastaví právě vykonávaný blok programu a po svém
skončení běh programu pokračuje dál (od místa, kde byla výjimka vyvolána), případně
vrací kontrolu prostředí (byl-li to konec programu).
Syntaxe pro zápis zachycení výjimek je: EXEPTION
WHEN exeption1 [OR exeption2 …] THEN statement1; statement2; [WHEN exeption3 [OR exeption4 …] THEN statement1; statement2; …] [WHEN OTHERS THEN statement1; statement2; …]
Vysvětlení syntaxe:
exeption … je to název předdefinované výjimky nebo název uživatelsky definované výjimky, která je deklarována v deklarační sekci
statement … je to nějaký SQL nebo PL/SQL příkaz record_name … je to název již deklarované proměnné typu záznam (record) OTHERS … označuje sekci výjimek, která se vykoná v případě
nespecifikovaných výjimek
Ukázka zachycení předdefinované výjimky: DECLARE -- deklarace skalarnich promennych v_num1 NUMBER := 1; v_num2 NUMBER := 0; v_result NUMBER;
41
BEGIN v_result := v_num1/v_num2; --vypsani hodnot promenne DBMS_OUTPUT.PUT_LINE('v_result = '||v_result); EXCEPTION --zachyceni vyjimky deleni nulou WHEN ZERO_DIVIDE THEN --vypsani informace, ze doslo k vyjimce deleni nulou DBMS_OUTPUT.PUT_LINE('Exception ZERO_DIVIDE!!!'); END;
V této ukázce se nejprve deklarují a inicializují skalární proměnné „v_num1“ a
„v_num2“. Proměnná „v_result“ se pouze deklaruje. Ve výkonné sekci se u přiřazení
proměnné „v_result“ vyvolá výjimka dělení nulou. Protože v sekci pro zpracování
výjimek je výjimka pro dělení nulou definována, vykonají se příkazy z bloku kódu
„WHEN ZERO_DIVIDE“. Tím dojde k ukončení programu.
Ukázka deklarace a zachycení uživatelsky definované výjimky: DECLARE -- deklarace skalarnich promennych v_num1 NUMBER := 3; v_num2 NUMBER := 4; v_result NUMBER; -- deklarace uzivatelsky definovane vyjimky e_minus_number EXCEPTION; BEGIN v_result := v_num1-v_num2; IF v_result < 0 THEN RAISE e_minus_number; END IF; --vypsani hodnot promenne DBMS_OUTPUT.PUT_LINE('v_result = '||v_result); EXCEPTION --zachyceni uzivatelsky definovane vyjimky WHEN e_minus_number THEN --vypsani informace, ze doslo k vyjimce DBMS_OUTPUT.PUT_LINE('Result is minus number!!!'); END;
V této ukázce se opět nejprve deklarují a inicializují skalární proměnné „v_num1“ a
„v_num2“. Proměnná „v_result“ se pouze deklaruje. Ve výkonné sekci se proměnné
„v_result“ přiřadí hodnota rozdílu proměnných „v_num1“ a „v_num2“. Poté se testuje
zda hodnota proměnné „v_result“ není záporná. Když ano, vyvolá se uživatelsky
definovaná výjimka „e_minus_number“, která způsobí vypsání informace na konzoli.
42
2.12.1 Funkce pro zachycení výjimek Když se v programu vyskytne nějaká výjimka, lze ji identifikovat přiřazeným číslem
výjimky nebo textem výjimky. K tomu slouží dvě funkce SQLCODE a SQLERRM.
V případě, že chceme tyto funkce využít, musíme je použít bezprostředně po příkazu,
který nahlásil výjimku. Funkce SQLCODE vrací číselnou hodnotu vyvolané výjimky,
funkce SQLERRM vrací řetězcovou hodnotu vyvolané výjimky.
2.12.2 Umělé vyvolání výjimky Balíček DBMS_STANDARD dodávaný spolu s Oracle, poskytuje jazykové
prostředky, které mohou PL/SQL aplikacím napomoci při spolupráci s Oracle. Například
procedura „RAISE_APPLICATION_ERROR“ umožňuje zveřejnit uživatelsky
definované chybové hlášky z uložených podprogramů (stored subprograms). Touto cestou
lze aplikaci oznamovat chyby a vyhnout se vracení neošetřených chyb. Aplikace může
volat proceduru „RAISE_APPLICATION_ERROR“ pouze ze spustitelného uloženého
podprogramu. Je-li zavolána, procedura „RAISE_APPLICATION_ERROR“ ukončí
podprogram a vrátí uživatelsky definovanou chybu (číslo chyby) a zprávu aplikaci. Číslo
chyby a zpráva pak může být odchycena stejně jako každá jiná Oracle chyba.
Syntaxe pro vyvolání výjimky je: RAISE_APPLICATION_ERROR(error_number,message[, {TRUE | FALSE}])
Vysvětlení syntaxe:
error_number … je to číslo chyby, hodnotou je záporné celé číslo (integer) v rozsahu (-20000 , –20999)
message … je to text chyby, hodnotou je řetězec maximální délky 2048 bytů TRUE | FALSE … je to boolen proměnná, jestliže je TRUE, chyba je uložena na
zásobník, pokud je FALSE (default), chyba nahradí všechny dosud uložené chyby nespecifikovaných výjimek
Ukázka zachycení předdefinované výjimky:
DECLARE -- deklarace skalarnich promennych v_empno emp.EMPNO%TYPE; BEGIN --inicializace promenne SELECT empno INTO v_empno FROM emp WHERE empno = 100; --vypsani hodnoty promenne DBMS_OUTPUT.PUT_LINE('v_empno = '||v_empno);
43
EXCEPTION --zachyceni vyjimky WHEN NO_DATA_FOUND THEN --vyvolani vlastniho nadefinovaneho chyboveho stavu RAISE_APPLICATION_ERROR (-20201,'empno was not found'); END;
V této ukázce se nejprve deklaruje skalární proměnná „v_empno“. Ve výkonné části
se tato proměnná inicializuje, ale podmínka „WHERE empno = 100“ je nesplnitelná.
Příkaz „SELECT“ tedy nevrátí žádný záznam a je vyvolána výjimka
„NO_DATA_FOUND“. V sekci výjimek je definované odchycení této výjimky a proto se
vykoná procedura „RAISE_APPLICATION_ERROR“, která vyvolá výjimku (chybový
stav) s číslem 20201 a textem „empno was not found“.
44
3. Podprogramy – procedury, funkce, balíčky a triggery v jazyce PL/SQL
Základní anonymní programový blok PL/SQL je kód, který je zasílán databázovému
serveru. Ten jej pouze vykoná. Procedury, funkce, balíčky a triggery jsou podprogramy
uložené přímo v databázi spolu s dalšími databázovými strukturami, jako jsou například
tabulky nebo pohledy. Do databáze se tedy umísťují nejen data, ale také aplikační logika
pro zpracování těchto dat. Toto řešení je výhodné nejen pro jednodušší distribuci, ale
přispívá také ke zvýšení spolehlivosti. Aplikační logika je totiž zabezpečená a
zálohovatelná podobnými postupy jako samotná data. Další velká výhoda uložených
procedur je ta, že jsou v databázi uloženy v předkompilované podobě. Jestliže jsou často
prováděny stejné nebo pouze parametricky odlišené operace s daty, databázový server
neztrácí čas s analýzou a interpretací procedur, ale provádí již předkompilovaný kód. U
architektury klient/server je velmi často vhodné umístit aplikační logiku na aplikační
server (například Oracle9iAS), ale i přesto může být výhodné použití uložených procedur.
Uložené procedury totiž redukují objem přenášených dat mezi databází a aplikačním
serverem.
3.1 Vývojová prostředí pro podprogramy Podprogramy (subprograms) lze vyvíjet v různých vývojových prostředích. Základní
univerzální vývojový nástroj nejen pro PL/SQL programy je produkt „SQL*Plus“, který
je přímo dodáván firmou Oracle. Dále firma Oracle vyvinula produkt , který zahrnuje
vývojové prostředí přímo pro vývoj PL/SQL programů. Tento produkt se nazývá
„Procedure Builder“. Výhodou tohoto softwarového produktu je fakt, že má přímo
vestavěný „PL/SQL engine“, který zpracovává PL/SQL kód. U produktu „SQL*Plus“
není „PL/SQL engine“ vestavěný, pro zpracování PL/SQL kódu se využívá „PL/SQL
engine“, který je součástí Oracle serveru.
V prostředí „SQL*Plus“ se vytvořené procedury či funkce volají klíčovým slovem
„EXECUTE“, v prostředí „Procedure Builder“ se toto klíčové slovo nezadává, volání se
provádí pouze uvedením názvu daného podprogramu.
Vývojové prostředí pro PL/SQL programy je také dostupné v softwarových
produktech, které nejsou dodávány firmou Oracle. Takovým velmi zdařilým produktem je
například „TOAD“ dodávaný firmou QUEST SOFTWARE.
45
3.2 PL/SQL procedury PL/SQL procedura je pojmenovaný PL/SQL blok, který vykonává nějakou akci.
Procedura je uložena na databázovém serveru jako databázový objekt, při volání
procedury voláme vlastně databázový objekt na serveru Oracle.
Procedura může obsahovat parametry, pak ji musíme volat s patřičným počtem
parametrů. Parametry slouží k přesunu hodnot směrem do procedury a z procedury.
Parametr může nabývat jeden ze tří typů :
• IN parametr (default) - je to vstupní parametr přes který se do procedury předávají hodnoty
• OUT parametr - je výstupní parametr přes který se z procedury předávají hodnoty do
prostředí, odkud je procedura volána • IN OUT parametr
- je to vstupně-výstupní parametr, slouží zároveň pro předání hodnoty proceduře a předání hodnoty z procedury
Syntaxe pro vytvoření procedury je: CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, …) ]
IS | AS PL/SQL Block;
Vysvětlení syntaxe:
procedure_name … je to název procedury parameter … označuje název parametru, je to proměnná, která se využívá
v PL/SQL bloku mode … definuje typ parametru (IN,OUT, IN OUT) datatype … je to datový typ parametru (například NUMBER nebo
VARCHAR2) PL/SQL Block … je to tělo procedury ve kterém je obsažen hlavní kód procedury,
blok začíná klíčovým slovem „BEGIN“ nebo deklarací lokálních proměnných, v těle může být použita sekce ošetření výjimek
[OR REPLACE]… když je zadáno toto klíčové slovo, pak při vytváření nové verze již existující procedury není nutné původní proceduru nejprve odstranit
Ukázka vytvoření uložené procedury:
CREATE OR REPLACE PROCEDURE d_query_emp (v_id IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_salary OUT emp.sal%TYPE,
46
v_comm OUT emp.comm%TYPE, v_profit IN OUT NUMBER ) IS -- deklarace lokalni skalarni promenne v_local NUMBER; BEGIN --inicializace promennych SELECT ename, sal, comm INTO v_name, v_salary, v_comm FROM emp WHERE empno = v_id; --inicializace lokalni promenne v_local := v_profit; --nastavení hodnoty pro vystupni parametr v_profit := v_salary/100 * v_local; EXCEPTION --zachyceni vyjimky WHEN NO_DATA_FOUND THEN --vyvolani vlastniho nadefinovaneho chyboveho stavu RAISE_APPLICATION_ERROR (-20201,'empno = '||v_id||' was not found'); END;
V této ukázce se vytvoří procedura s jedním vstupním parametrem (parametr
představuje číslo zaměstnance), třemi výstupními parametry (parametry představují
příjmení zaměstnance, plat a provizi) a jedním vstupně-výstupním parametrem (ten jako
vstupní parametr představuje procento platu a jako výstupní parametr představuje hodnotu
tohoto procenta platu - profit). V těle procedury se nejprve pomocí příkazu „SELECT“
zjistí hodnoty výstupních parametrů pro zadaný vstupní parametr (vlastně pro zadaného
zaměstnance) a poté se do vstupně-výstupního parametru vypočítá hodnota profitu. Profit
představuje procentuální část platu, je závislý na platu zaměstnance a počtu procent (tento
počet se zadává do vstupně-výstupního parametru). Procedura obsahuje také ošetření
výjimky „NO_DATA_FOUND“, která může nastat v případě zadání neznámého čísla
zaměstnance (vstupního parametru). V sekci výjimek je definované odchycení této
výjimky a vykonání příkazu „RAISE_APPLICATION_ERROR“, který zobrazí číslo a
text chybového stavu.
3.2.1 Volání vytvořené procedury Syntaxe volání vytvořené procedury se liší podle prostředí, ze kterého proceduru
voláme. Při volání procedury s výstupními parametry je nutné připravit v prostředí ze
kterého proceduru voláme proměnné, do kterých se výstupní parametry procedury uloží.
To je možné realizovat pomocí vazebních (globálních) proměnných, nebo pomocí
programového bloku PL/SQL.
47
Ukázka volání uložené procedury pomocí programového bloku PL/SQL: DECLARE v_name emp.ename%TYPE; v_salary emp.sal%TYPE; v_comm emp.comm%TYPE; v_profit NUMBER:= 5; BEGIN --volani ulozene procedury d_query_emp(7499,v_name,v_salary,v_comm,v_profit); --zobrazeni vystupnich parametru procedury na konzoli DBMS_OUTPUT.PUT_LINE('v_name = '||v_name); DBMS_OUTPUT.PUT_LINE('v_salary = '||v_salary); DBMS_OUTPUT.PUT_LINE('v_comm = '||v_comm); DBMS_OUTPUT.PUT_LINE('v_profit = '||v_profit); END;
V této ukázce se nejprve deklarují čtyři skalární proměnné, do kterých se později
uloží výstupní parametry procedury. Protože parametr „v_profit“ je vstupně-výstupní je
nutné tuto proměnnou také inicializovat požadovanou hodnotou počtu procent. Ve
výkonné části PL/SQL bloku je volána uložená procedura, jako vstupní parametr je jí
předáno přímo číslo zaměstnance, počet procent profitu je zadán při inicializaci proměnné
„v_profit“. Před spuštěním procedury má tedy proměnná „v_profit“ hodnotu počtu
procent (v tomto případě 5), po spuštění procedury má proměnná „v_profit“ hodnotu
počtu procent platu (v tomto případě 80, plat zaměstnance „ALLEN“ je totiž 1600). Na
závěr se všechny výstupní parametry vypíší na konzoli.
3.3 PL/SQL Funkce PL/SQL funkce jsou pojmenované PL/SQL bloky. Funkce a procedury jsou podobně
strukturované, i funkce mají hlavičku, deklarativní sekci a sekci pro zpracování výjimek.
Funkce ale vrací vždy nějakou hodnotu do prostředí, ze kterého je volána. Procedura
hodnotu vracet nemusí. Funkci lze předat také parametry, ale pouze vstupní. Liší se i
volání funkcí a procedur, funkce mohou být volány jako část SQL příkazu.
Syntaxe pro vytvoření funkce je: CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, …) ]
RETURN datatype IS | AS PL/SQL Block;
Vysvětlení syntaxe:
function_name … je to název funkce
48
parameter … označuje název parametru, je to proměnná, která se využívá v PL/SQL bloku
mode … definuje typ parametru (IN) datatype … je to datový typ parametru nebo návratové hodnoty (například
NUMBER nebo VARCHAR2) PL/SQL Block … je to tělo funkce ve kterém je obsažen hlavní kód procedury,
blok začíná klíčovým slovem „BEGIN“ nebo deklarací lokálních proměnných, v těle může být použita sekce ošetření výjimek
[OR REPLACE]… když je zadáno toto klíčové slovo, pak při vytváření nové verze již existující funkce není nutné původní funkci nejprve odstranit
Ukázka vytvoření funkce:
CREATE OR REPLACE FUNCTION d_qet_sal (v_id IN emp.empno%TYPE) RETURN NUMBER IS -- deklarace lokalni skalarni promenne v_salary NUMBER; BEGIN --inicializace promennych SELECT sal INTO v_salary FROM emp WHERE empno = v_id; --nastavení hodnoty pro návratovou hodnotu RETURN v_salary ; EXCEPTION --zachyceni vyjimky WHEN NO_DATA_FOUND THEN --vyvolani vlastniho nadefinovaneho chyboveho stavu RAISE_APPLICATION_ERROR (-20201,'empno = '||v_id||' was not found'); END;
V této ukázce se vytvoří funkce s jedním vstupním parametrem (parametr
představuje číslo zaměstnance). Návratové hodnotě funkce je přiřazen plat daného
zaměstnance. V těle funkce se nejprve pomocí příkazu „SELECT“ zjistí plat zaměstnance.
Zaměstnanec je identifikován vstupním parametrem (číslem zaměstnance). Poté se
zjištěná hodnota platu zaměstnance předá jako návratová hodnota funkce. Funkce
obsahuje také ošetření výjimky „NO_DATA_FOUND“, která může nastat v případě
zadání neznámého čísla zaměstnance (vstupního parametru).
3.3.1 Volání vytvořené funkce Syntaxe volání vytvořené funkce se liší podle prostředí, ze kterého funkci voláme.
Pro odchycení návratové hodnoty funkce platí podobná pravidla, jako pro odchycení
výstupních parametrů uložených procedur. Funkce ale nelze volat samostatně, je nutné
volání funkce vložit do nějakého příkazu (například PL/SQL příkazu přiřazení, SQL
příkazu).
49
Ukázka volání funkce z příkazu SQL: SELECT d_qet_sal(7499) FROM dual;
V této krátké ukázce se volá funkce „d_get_sal“ za klíčovým slovem „SELECT“.
Funkci je předán přímo jeden vstupní parametr. Návratová hodnota funkce se v tomto
případě zobrazí ve výsledné projekci příkazu „SELECT“. Pro testování funkcí je často
vhodné použít systémovou tabulku „dual“, které je k tomuto účelu v databázi Oracle
vytvořena.
Ukázka volání funkce z prostředí PL/SQL: DECLARE -- deklarace lokalni skalarni promenne v_salary NUMBER; BEGIN --inicializace promenne volanim funkce v_salary := d_qet_sal(7499); --zobrazeni navratove hodnoty funkce na konzoli DBMS_OUTPUT.PUT_LINE('v_salary = '||v_salary); END;
V této ukázce se v deklarační sekci deklaruje proměnná „v_salary“, která bude sloužit
pro uložení návratové hodnoty funkce. Poté se provede příkaz přiřazení návratové
hodnoty funkce „d_get_sal“ do proměnné „v_salary“. Nakonec se hodnota proměnné
„v_salary“ zobrazí na konzoli.
3.4 PL/SQL balíčky (packages) PL/SQL balíčky jsou pojmenované PL/SQL bloky. Tyto bloky nejčastěji obsahují
procedury a funkce. Balíčky zapouzdřují do jednoho celku PL/SQL typy, proměnné a
podprogramy, které spolu nějak souvisí. Balíčky mají dvě části, část specifikace a část
těla balíčku. Specifikace balíčku obsahuje deklaraci objektů (nejčastěji podprogramů),
které je možné volat z různých prostředí. Tělo balíčku obsahuje vlastní kód podprogramů.
Jestliže je nějaký podprogram obsažen v těle balíčku, ale jeho deklarace není obsažena ve
specifikaci balíčku, pak lze tento podprogram volat pouze v rámci těla balíčku. Balíček
nemůže být volán samostatně, volání se vždy odkazuje na nějaký objekt v balíčku.
Syntaxe pro vytvoření specifikace balíčku je: CREATE [OR REPLACE] PACKAGE package_name IS | AS
public type and item declarations subprograms specifications
END package_name;
50
Vysvětlení syntaxe:
package_name … je to název balíčku public type and item declarations …
je to deklarace veřejných proměnných, konstant, kurzorů, výjimek a typů
subprogram specifications … je to deklarace PL/SQL podprogramů
Ukázka vytvoření specifikace balíčku: CREATE OR REPLACE PACKAGE d_sum_pack IS --deklarace funkce FUNCTION d_sum_func (v_number1 IN NUMBER, v_number2 IN NUMBER) RETURN NUMBER; --deklarace procedury PROCEDURE d_sum_proc (v_number1 IN NUMBER, v_number2 IN NUMBER, v_out OUT NUMBER ); END d_sum_pack;
V této ukázce se vytvoří specifikace balíčku obsahující jednu funkci s názvem
„d_sum_func“ a jednu proceduru s názvem „d_sum_proc“.
Syntaxe pro vytvoření těla balíčku je: CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS
private type and item declarations subprogram bodies
END package_name;
Vysvětlení syntaxe:
package_name … je to název balíčku private type and item declarations … je to deklarace privátních proměnných,
konstant, kurzorů, výjimek a typů subprogram bodies … je to kód PL/SQL podprogramů (veřejných i privátních)
Ukázka vytvoření těla balíčku: CREATE OR REPLACE PACKAGE BODY d_sum_pack IS FUNCTION d_sum_func (v_number1 IN NUMBER, v_number2 IN NUMBER) RETURN NUMBER IS BEGIN --vraceni návratove hodnoty RETURN v_number1 + v_number2; END d_sum_func;
51
PROCEDURE d_sum_proc (v_number1 IN NUMBER, v_number2 IN NUMBER, v_out OUT NUMBER ) IS BEGIN --nastavení hodnoty pro vystupni parametr v_out := v_number1 + v_number2; END d_sum_proc; END d_sum_pack;
V této ukázce se vytvoří tělo balíčku obsahující PL/SQL kód funkce s názvem
„d_sum_func“ a procedury s názvem „d_sum_proc“. Oba podprogramy slouží k součtu
dvou numerických hodnot. Vstupními parametry „v_number1“ a „v_number2“ jsou dvě
číselné hodnoty. Výstupní parametr u procedury „v_out“ obsahuje součet těchto hodnot.
Funkce vrací součet hodnot jako návratovou hodnotu. Jelikož jsou oba podprogramy
deklarované ve specifikaci balíčku, lze je veřejně volat.
3.4.1 Volání vytvořeného balíčku Syntaxe volání objektů z vytvořeného balíčku se liší podle prostředí, ze kterého
objekty z balíčku voláme. Zápis volání podprogramů z balíčku je shodný jako zápis
volání jednotlivých podprogramů (procedur a funkcí).
Ukázka volání funkce z balíčku v příkazu SQL: SELECT d_sum_pack.d_sum_func(12,5) FROM dual;
V této krátké ukázce se volá funkce „d_get_sal“, která je součástí balíčku
„d_sum_pack“. Funkci jsou předány přímo dva vstupní parametry. Návratová hodnota
funkce je zobrazena v projekci příkazu „SELECT“.
Ukázka volání procedury z balíčku v prostředí PL/SQL: DECLARE -- deklarace lokalni skalarni promenne v_out NUMBER; BEGIN --volani procedury z balicku d_sum_pack.d_sum_proc(12,5,v_out); --zobrazeni vystupniho parametru procedury na konzoli DBMS_OUTPUT.PUT_LINE('v_out = '||v_out); END;
V této ukázce se volá procedura „d_sum_proc“, která je součástí balíčku
„d_sum_pack“. Proceduře jsou předány přímo dva vstupní parametry. Výstupní parametr
procedury je zobrazen na konzoli.
52
3.5 PL/SQL triggery (spouště) Triggery jsou pojmenované PL/SQL bloky, které se automaticky provedou v případě
předem definované operace s daty. Tato operace může být například vložení nového
záznamu do tabulky, vymazání záznamu z tabulky nebo editace záznamu v tabulce.
Triggery se nejčastěji využívají k zajištění referenční integrity.
Triggery jsou velmi podobné uloženým procedurám. Jako uložené procedury i triggery
jsou uložené v databázi, ale na rozdíl od procedur se triggery nespouštějí přímo, ale jsou
spouštěny nějakou událostí.
Při vytváření triggeru je třeba určit, kdy se trigger aktivuje, možnosti jsou před
vykonáním příslušné operace (trigger BEFORE), po jejím vykonání (trigger AFTER)
nebo se trigger vykoná místo příslušné operace (trigger INSTEAD OF). Také je třeba
určit událost, na kterou má příslušný trigger reagovat. Tato událost je například INSERT,
UPDATE nebo DELETE.
Zjednodušená syntaxe pro vytvoření triggeru je: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} [INSTEAD OF] event1 [OR event2 OR event3] ON table_name [FOR EACH ROW] PL/SQL Block;
Vysvětlení syntaxe:
trigger_name … je to název triggeru {BEFORE|AFTER} … toto nastavení určuje kdy se trigger aktivuje, zda před vykonáním
příslušné operace nebo po ní [INSTEAD OF] … toto nastavení určuje, zda se trigger aktivuje místo příslušné
operace event … je to název příkazu nebo událost, která trigger aktivuje (nejčastěji
jsou to klíčová slova „INSERT“, „UPDATE“, „DELETE“) table_name … je to název tabulky nebo pohledu, na které má být trigger
implementován FOR EACH ROW … tato klauzule určuje, zda se trigger spustí pro každý zpracovávaný
řádek nebo pouze jednou, při zpracování každého řádku se lze v těle triggeru odkazovat na změněné záznamy, na původní záznamy se odkazujeme „:OLD“, na nové záznamy se odkazujeme „:NEW“
53
Ukázka vytvoření jednoduchého triggeru : CREATE OR REPLACE TRIGGER d_ins_tri AFTER INSERT ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(
'Record was succesfully inserted. Empno = '||:NEW.empno); END;
V této ukázce se vytvoří trigger „d_ins_tri“, který zajistí vypsání informace na
konzoli při každém vložení nového záznamu do tabulky „emp“. Na konzoli se vypíše také
nově vkládané číslo zaměstnance.
54
4. Porovnání jazyka PL/SQL a jazyka T-SQL V této kapitole bych rád porovnal základní aspekty jazyka PL/SQL vyvinutého
firmou ORACLE a jazyka T-SQL (Transact-SQL) vyvinutého firmou Microsoft. Budu
porovnávat jazyk PL/SQL, který je součástí platformy Oracle9i a jazyk T-SQL platný pro
platformu MS SQL Server 2000. Tyto databázové platformy jsou velmi kvalitní a při
práci s nimi lze dosáhnout rámcově stejných výsledků. Obě databázové platformy
obsahují procedurální jazyky, které jsou nástavbou jazyka SQL, jazyk PL/SQL vyvinula
firma ORACLE a jazyk T-SQL (Transact-SQL) vyvinula firma Microsoft. Výrobci
zmíněných databázových platforem, firmy Oracle a Microsoft, se různými rozšířeními
standardu SQL snaží získat pro svou databázovou platformu konkurenční výhodu.
Systémy pak obsahují mnohá rozšíření, která napomáhají vyvíjet kvalitnější a výkonnější
aplikace na druhé straně se však od sebe co se týče kompatibility stále více vzdalují.
Jazyky PL/SQL a T-SQL jsou téměř totožné funkcionalitou, ale rozdílné syntaxí.
Aplikační logika u obou jazyků je podobná, proto portace aplikací pracujících nad jedním
nebo druhým systémem není vyloučena.
4.1 Proměnné v PL/SQL a T-SQL Proměnné jsou v obou systémech definovány klíčovým slovem „DECLARE“.
Proměnné T-SQL jsou označovány znakem „@“. Proměnné PL/SQL i T-SQL jsou při své
deklaraci nastaveny na hodnotu NULL. V jazyku T-SQL se pro přiřazení hodnoty
proměnné používá klíčové slovo „SET“.
Následující tabulka obsahuje porovnání deklarací proměnných.
PL/SQL T-SQL DECLARE name char(20); job varchar(50); date1 date; price number(10,2); BEGIN name := 'DAVID'; job := 'CLERK'; date1 := SYSDATE; price := 12.20; DBMS_OUTPUT.PUT_LINE(name); DBMS_OUTPUT.PUT_LINE(job); DBMS_OUTPUT.PUT_LINE(date1); DBMS_OUTPUT.PUT_LINE(price); END;
DECLARE @name char(20), @job varchar(50), @date1 datetime, @price numeric(10,2) SET @name = 'DAVID' SET @job = 'CLERK' SET @date1 = GETDATE() SET @price = 12.20 PRINT @name PRINT @job PRINT @date1 PRINT @price
tabulka 10 Tabulka porovnání deklarací a inicializací proměnných
55
Pro zobrazení informací na konzoli se v ORACLE využívá package
DBMS_OUTPUT. V MS SQL SERVERU se k tomuto účelu používá příkaz „PRINT“.
Pro zjištění aktuálního data a času se v ORACLE využívá funkce „SYSDATE“ v MS
SQL SERVERU se využívá funkce „GETDATE“.
4.2 Řízení toku programu Základními programovými prostředky pro řízení toku programu jsou podmínky a
cykly. Procedurální jazyky obou platforem obsahují podmínky i cykly. V jazyce PL/SQL
je nutné všechny příkazy pro řízení toku programu uzavřít do bloku začínající příkazem
„BEGIN“ a končící příkazem „END“. V jazyce T-SQL je možné použít tyto příkazy i
mimo blok „BEGIN“ a „END“.
4.2.1 Podmínky
Podmínková konstrukce s příkazem „IF“ slouží k větvení programového kódu.
Následující tabulka slouží k porovnání podmínkové konstrukce „IF“ v jazyku PL/SQL a
T-SQL.
PL/SQL DECLARE name VARCHAR2(30) := 'DAVID'; BEGIN IF name = 'DAVID' THEN DBMS_OUTPUT.PUT_LINE('NAME is '||name); ELSE DBMS_OUTPUT.PUT_LINE('NAME is not DAVID'); END IF; END; T-SQL DECLARE @name char(20) SET @name = 'DAVID' IF @name = 'DAVID' PRINT 'NAME is ' + @name ELSE PRINT 'NAME is not DAVID'
tabulka 11 Tabulka porovnání podmínkových konstrukcí
V jazyku T-SQL se v podmínkové konstrukci nepoužívá klíčové slovo „THEN“.
V obou jazycích lze použít také podmínkovou konstrukci „CASE“ pro vícenásobné
větvení programu.
56
4.2.2 Cykly Cyklus slouží k opakovanému vykonávání části programového kódu.
V jazyku PL/SQL jsou k dispozici tři druhy cyklů. Jednoduchý cyklus „LOOP“,
cyklus „FOR“ na principu čítače a cyklus „WHILE“ s podmínkou na začátku cyklu.
V jazyku T-SQL je možné použít pouze cyklus „WHILE“. Pro předčasné ukončení
cyklu je v jazyku PL/SQL možné použít příkaz „EXIT“. V jazyku T-SQL se pro
předčasné ukončení cyklu používá příkaz „BREAK“ a lze použít příkaz „CONTINUE“,
který způsobí přerušení vykonávání momentálního běhu cyklu a spuštění dalšího běhu
cyklu.
Následující tabulka slouží k porovnání cyklu „WHILE“ v jazyku PL/SQL a T-SQL.
PL/SQL DECLARE number1 NUMBER(10) := 5; BEGIN WHILE number1 < 10 LOOP number1 := number1 + 1; DBMS_OUTPUT.PUT_LINE('NUMBER is ' || TO_CHAR(number1)); END LOOP; END; T-SQL DECLARE @number1 numeric(10) SET @number1 = 5 WHILE @number1 < 10 BEGIN SET @number1 = @number1 + 1 PRINT 'NUMBER is ' + CONVERT(VARCHAR(10),@number1) END
tabulka 12 Tabulka porovnání cyklu „WHILE“
57
Výsledky
Cílem této práce bylo především celkové seznámení s jazykem PL/SQL. Práce navíc
upozorňuje na některé specifické vlastnosti tohoto jazyka a na ukázkových kódech
ukazuje aplikaci základních programových algoritmů. V práci jsou podrobně vysvětleny
příkazy pro řízení toku programu a v závěrečná část obsahuje srovnání jazyka PL/SQL a
jazyka T-SQL.
Tuto práci mohou využít zejména začínající PL/SQL vývojáři, ale je vhodná i pro
ostatní specialisty zabývající se zpracováním dat v databázi Oracle. Práce obsahuje
mnoho ukázkových kódů PL/SQL, které jsou podrobně popsány a vysvětleny. Tyto kódy
mohou sloužit jako předloha pro vývoj reálných PL/SQL kódů.
Tato práce slouží především k získání základních znalostí a dovedností při vytváření
programových bloků v jazyku PL/SQL, napomáhá také lepší orientaci v již existujících
PL/SQL programových blocích.
Závěry a doporučení
Práce shrnuje poznatky o jazyku PL/SQL a vytváří základní náhled na databázový
systém Oracle. V Česku bohužel chybí v nabídce odborné literatury publikace, která se
podrobně zabývá popisem jazyka PL/SQL. Tato práce částečně nahrazuje absenci této
publikace.
Tato práce je vhodná zejména pro začínající PL/SQL vývojáře. Práce nemá za cíl
popis jazyka SQL, předpokládá se již předchozí znalost tohoto jazyka. Pro rychlejší
pochopení ukázkových programových kódů je vhodná alespoň také částečná znalost
nějakého programovacího jazyka (například VisualBasic, C++, Java, Delphi). Jako
vhodný doplněk této práce bych doporučil seznámení se základy administrace databázové
platformy Oracle 9i.
Vzhledem k pevně stanovenému rozsahu bakalářské práce je v této práci pouze
základní popis databázových objektů jako jsou procedury, funkce, balíčky a spouště.
Detailní popis těchto objektů by bylo vhodným tématem pro diplomovou práci.
58
Seznam použité literatury:
1. Ellen Gravina, Priya Nathan.
PL/SQL Fundamentals.
Redwood Shores U.S.A., Oracle University, July 1999.
41023GC13
2. Donna Keesling.
Develop PL/SQL Program Units.
Redwood Shores U.S.A., Oracle University, June 2000.
41024GC14
3. Bruce Ernst, Rasmussen HanneRue, Schwinn Ulrike, Venkatachalam Vijay.
Enterprise DBA Part 1A: Architecture and Administration Volume1,2.
Redwood Shores U.S.A., Oracle University, July 2001.
30049GC11
4. Lacko Luboslav. Oracle. Správa, programování a použití databázového systému.
Brno, Computer Press, 2003.
ISBN 80-7226-699-3
5. Prof.RNDr. Jaroslav Pokorný,CSc , Ing. Ivan Halaška.
Databázové systémy. Praha, Vydavatelství ČVUT, 2003.
ISBN 80-01-02789-9
Internet:
1. http://www.oracle.com/technology/products/database/oracle10g/index.html
2. http://nb.vse.cz/~zelenyj/it380/eseje/xlizt01/sql.htm
3. http://www.zizelevak.wz.cz/oracle.htm
4. http://www.cs.vsb.cz/ticha/oracle/orap1.htm
5. http://www.microsoft.com/sql/techinfo/default.asp
6. http://www.fee.vutbr.cz/UIVT/courses/DSI/public/ORACLE/Dokumentace
/PLSQL.DOC
59
Seznam obrázků obrázek 1 Programový blok........................................................................................ 12 obrázek 2 Minimální programový blok ...................................................................... 13 obrázek 3 Vnořený programový blok ........................................................................ 19 obrázek 4 Práce s explicitním kurzorem..................................................................... 37
Seznam tabulek tabulka 1 Tabulka skalárních datových typů PL/SQL................................................... 15 tabulka 2 Tabulka kompozitních datových typů PL/SQL ............................................. 15 tabulka 3 Tabulka oddělovacích znaků.......................................................................... 18 tabulka 4 Operátory jazyka PL/SQL.............................................................................. 20 tabulka 5 Pravdivostní tabulka logických operátorů ..................................................... 20 tabulka 6 Doporučené konvence pro proměnné PL/SQL.............................................. 21 tabulka 7 Tabulka implicitních kurzorů......................................................................... 24 tabulka 8 Metody PL/SQL tabulky................................................................................ 35 tabulka 9 Tabulka atributů explicitního kurzoru ........................................................... 39 tabulka 10 Tabulka porovnání deklarací a inicializací proměnných ........................... 54 tabulka 11 Tabulka porovnání podmínkových konstrukcí .......................................... 55 tabulka 12 Tabulka porovnání cyklu „WHILE“.......................................................... 56
Seznam příloh příloha 1 Ukázka balíčku PL/SQL ............................................................................... 60 příloha 2 Data v tabulkách DEPT a EMP ..................................................................... 61
60
příloha 1 Ukázka balíčku PL/SQL
CREATE OR REPLACE PACKAGE d_info_pack IS --deklarace funkce FUNCTION d_pocprac_func (v_loc IN VARCHAR2) RETURN NUMBER; --deklarace procedury PROCEDURE d_mistopoc_proc (v_empno IN NUMBER, v_loc OUT VARCHAR2, v_emp_num OUT NUMBER ); END d_info_pack; CREATE OR REPLACE PACKAGE BODY d_info_pack IS FUNCTION d_pocprac_func (v_loc IN VARCHAR2) RETURN NUMBER IS /* Tato funkce slouží k navrácení počtu zaměstnanců v daném městě. Vstupní parametr : název města Výstupní hodnota : počet zaměstnanců */ --*** Deklarace proměnné v_return NUMBER(10); BEGIN SELECT count(e.EMPNO) INTO v_return FROM DEPT d, EMP e WHERE d.DEPTNO = e.DEPTNO AND LOC = v_loc; --vracení návratové hodnoty RETURN v_return; END d_pocprac_func; PROCEDURE d_mistopoc_proc (v_empno IN NUMBER, v_loc OUT VARCHAR2, v_emp_num OUT NUMBER ) IS /* Tato procedura slouží k navrácení názvu města a počtu zaměstnanců v daném městě. Vstupní parametr : číslo zaměstnance Výstupní parametr1 : název města Výstupní parametr2 : počet zaměstnanců */ BEGIN SELECT LOC INTO v_loc FROM DEPT WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO = v_empno);
61
v_emp_num := d_pocprac_func(v_loc); EXCEPTION --zachycení vyjímky WHEN NO_DATA_FOUND THEN --vyvolání vlastního nadefinovaného chybového stavu RAISE_APPLICATION_ERROR (-20201,'Nenalezeno číslo zaměstnance'); END d_mistopoc_proc; END d_info_pack;
příloha 2 Data v tabulkách DEPT a EMP
Tabulka EMP
empno ename job mgr hiredate sal comm deptno 7369 SMITH CLERK 7902 17.12.1980 800 207499 ALLEN SALESMAN 7698 20.2.1981 1600 300 307521 WARD SALESMAN 7698 22.2.1981 1250 500 307566 JONES MANAGER 7839 2.4.1981 2975 207654 MARTIN SALESMAN 7698 28.9.1981 1250 1400 307698 BLAKE MANAGER 7839 1.5.1981 2850 307782 CLARK MANAGER 7839 9.6.1981 2450 107788 SCOTT ANALYST 7566 19.4.1987 3000 207839 KING PRESIDENT 17.11.1981 5000 107844 TURNER SALESMAN 7698 8.9.1981 1500 0 307876 ADAMS CLERK 7788 23.5.1987 1100 207900 JAMES CLERK 7698 3.12.1981 950 307902 FORD ANALYST 7566 3.12.1981 3000 207934 MILLER CLERK 7782 23.1.1982 1300 10
Tabulka DEPT
deptno dname loc 10 ACCOUNTING NEW YORK20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON