BP David Petr..ek - cvut.cz

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


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;







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;


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;


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


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


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;


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;


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 ]


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;


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;


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


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];


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; …]


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}])


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;


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;


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


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;


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;


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

Date post:	24-Feb-2022
Category:	Documents
Upload:	others
View:	2 times
Download:	0 times

BP David Petr..ek - cvut.cz

Documents