Umíme to s Delphi: 93. díl – seznamte se: uložené procedury

Dnes budeme pokračovat a vysvětlíme si další důležitou komponentu z uvedené záložky palety komponent. Předtím nás však čeká jeden důležitý databázový koncept – uložené procedury.

Uložené procedury

Ve druhé části dnešního článku se naučíme pracovat s komponentou IBStoredProc, která slouží k práci s tzv. uloženými procedurami. Protože jsme se uloženými procedurami však dosud nezabývali, vysvětlíme si nejprve, o co se vlastně jedná.

Žádného z pravidelných čtenářů jistě nepřekvapí, že databázové servery komunikují se svými uživateli (tedy s programátory aplikací, např. v Delphi) prostřednictvím tzv. databázových jazyků. Databázový jazyk je rozhraní mezi databázovým serverem a okolním světem. Databázové jazyky se obecně dělí do tří skupin:

• jazyky pro definici dat (Data Definition Language, DDL) – jazyky sloužící k definici dat a jejich struktury, k vytváření tabulek a k vkládání dat do databáze
• jazyky pro manipulaci s daty (Data Manipulation Language, DML) – jazyky sloužící pro nejrůznější operace s daty, pro změny databázových schémat a pro získávání dat pomocí databázových dotazů
• jazyky pro řízení přístupu k datům (Data Control Language, DCL) – jazyky sloužící k práci s přístupovými právy a pro povolování/zakazování přístupu jednotlivým uživatelům

Kromě tohoto dělení je možné zmínit i řadu dalších dělení (např. na interaktivní a hostitelské jazyky). Pro nás je však důležité především dělení na procedurální a neprocedurální databázové jazyky. Zatímco neprocedurální jazyk pouze říká, jaká data vybrat a s jakými daty pracovat, procedurální jazyk je velmi podobný standardním programovacím jazykům (např. Pascal, C, Java), neboť specifikuje i způsob, jak s daty pracovat.

Jedním z nejznámějších databázových jazyků je SQL. Tento rozsáhlý (avšak elegantní) jazyk v sobě skrývá všechny dosud zmíněné druhy databázových jazyků. SQL je:

• jazyk pro definici dat – obsahuje řadu klíčových slov pro vytváření databází (CREATE DATABASE), vytváření databázových tabulek (CREATE TABLE), vkládání dat do databáze (INSERT) apod.
• jazyk pro manipulaci s daty – obsahuje klíčová slova pro změnu datových struktur (ALTER TABLE), ale především pro získávání dat prostřednictvím dotazů (notoricky známý SELECT FROM).
• jazyk pro řízení přístupu k datům – obsahuje klíčové slovo GRANT pro nastavování přístupových práv k jednotlivým databázovým objektům

Až dosud jsme se setkali pouze s neprocedurální verzí jazyka SQL. Vzpomeňte si, že jsme vytvářeli mnoho a mnoho databázových dotazů, ale vždy jsme pouze specifikovali, jaká data nás zajímají a jaká data se mají vybrat (SELECT jméno, příjmení FROM zaměstnanci WHERE věk > 25). Neříkali jsme nic o tom, jak má databázový systém výběr provádět a jakým způsobem má s daty pracovat.

Některé databázové systémy pracující s jazykem SQL však disponují i jakousi procedurální nadstavbou jazyka SQL. Velmi známý a rozšířený databázový systém ORACLE například definoval svou vlastní procedurální verzi jazyka SQL, která je nazvána PL/SQL (Procedural Language for Structured Query Language).

Také databázový systém Interbase umožňuje pracovat s jazykem SQL procedurálně. Jak se to projevuje? Systém umožňuje v jazyce SQL (přesněji řečeno v jeho rozšíření) vytvářet tzv. bloky. Blokem rozumíme část programového kódu uzavřeného mezi klíčová slova BEGIN a END. Blok může obsahovat i příkazy, o kterých se nám dosud v SQL jen zdálo, například podmínky, cykly, skoky apod. Pokud blok pojmenujeme, můžeme jej uložit přímo na databázový server a blok se tak stává jedním z databázových objektů. Co to znamená? Databázovými objekty jsou například všechny datové tabulky, které v průběhu práce s databází vytváříme. Když jsme před čtrnácti dny vytvořili tabulky Zaměstnanec, Oddělení a Profese, vytvořili jsme ve skutečnosti tři databázové objekty a uložili jsme je na databázový server.

Podobně tedy můžeme vytvořit pojmenovaný blok příkazů, který se nazývá uložená procedura. Už z označení této koncepce (uložená procedura) je patrné, že procedura je uložena přímo na databázovém serveru. Klient (klientská aplikace) může požádat databázový server o spuštění uložené procedury. Procedura se rozběhne na databázovém serveru (není tedy třeba přenášet žádná data na klienta a nezatěžuje se síťový provoz). Uložené procedury mohou být dvou typů:

• uložené procedury nevracející žádná data: pokusíme se vymyslet smysluplný příklad. Dejme tomu, že programujeme databázovou aplikaci, která pravidelně každou půlnoc rozešle registrovaným uživatelů email s nabídkou nových služeb (nebo třeba s novým vtipem). Napíšeme tedy uloženou proceduru (její pravidelné spouštění zajistíme třeba systémovými prostředky – to není v tuto chvíli podstatné), která projde tabulky UZIVATELE a EMAILOVE_ADRESY a na všechny adresy, jejichž majitelé s tím souhlasí, rozešle předpřipravený email. Kromě toho uloží všechny použité emailové adresy společně s aktuálním datem a časem do tabulky LOGY_ZASILANI, aby bylo možné dohledat případné reklamace. Uložená procedura tedy pouze projde nějaké části databáze, provede nějaké činnosti (odeslání emailu) a uloží data do jiné části databáze. Pak skončí a nic nevrací (může vrátit nanejvýš chybový kód v případě neúspěšného dokončení).
• uložené procedury vracející data: příkladem by mohla být například úplně stejná situace jako v předchozím případě, avšak procedura by vrátila všechny emailové adresy, na které se zaslání nepodařilo (jsou v nekorektním formátu). A nebo by vrátila naopak počet všech odeslaných emailů. A nebo oba údaje. Jiný příklad by mohl vypadat například tak, že v administrátor nějaké aplikace se rozhodne, že vymaže ze systému všechny uživatele, kteří se v posledním půl roce ani jednou nepřihlásili. Součástí systému je uložená procedura, která dokáže z údajů v databázi takové uživatele vyhledat a vymazat. Pro kontrolu je však vrátí a aplikace je administrátorovi vypíše na obrazovku. Podobných příkladů bychom mohli nalézt mnoho. Uložená procedura se tedy spustí, vykoná nějaké operace a následně vrátí nějaká data, stejně jako kdybychom použili běžný databázový dotaz (SELECT).

Jak poznáme níže, komponenta IBStoredProc slouží pouze k zapouzdření uložené procedury (a k jejímu spuštění), nedokáže však vrátit data. Má-li uložená procedura vracet nějaké údaje, je nutné zavolat ji úplně stejně jako běžný databázový dotaz a výsledky předat pomocí IBQuery.

V předchozím textu jsou uvedeny mnohé vlastnosti uložených procedur, přesto však neuškodí stručné shrnutí. V následujícím přehledu naleznete všechny možnosti interakce (komunikace) aplikací s uloženými procedurami:

• Aplikace mohou do uložených procedur předávat parametry. Uložené procedury tedy jsou schopny přijímat parametry a následně jejich hodnoty využívat při výpočtu.
• Uložené procedury mohou vracet hodnoty, a to jak skalární (jednoduché, jeden údaj), tak i celé databázové sloupce a jejich množiny.
• Aplikace mohou přímo vyvolat spuštění uložených procedur.
• Aplikace mohou nahradit jméno tabulky (nebo pohledu) v příkazu SELECT názvem uložené procedury. Máme-li např. uloženou proceduru POŠLI, která vrací username a email uživatele, jemuž odesíláme mail, je možné spustit proceduru (a získat výsledná data) příkazem SELECT username, email FROM POŠLI.

A jaké má použití uložených procedur výhody?

• Různé aplikace mohou sdílet programový kód. SQL blok (procedura) je napsán jen jednou a je uložen na databázovém serveru. Od toho okamžiku je přístupný všem aplikacím, která mají k této databázi přístup (typicky třeba klientská aplikace napsaná v Delphi, webové rozhraní napsané v jazyce PHP a řádková utilita dodávaná s Interbase – isql).
• Podporují modulární návrh aplikací. Opakovaně prováděné úlohy není nutné programovat víckrát, eliminace duplicitního kódu je zajištěna pomocí uložené procedury.
• Snazší úpravy a aktualizace. Je-li nutné změnit způsob fungování uložené procedury, nemusíme vůbec modifikovat aplikaci, nemusíme ji dokonce ani znovu kompilovat a linkovat. Potřebné změny stačí provést jednou, na serveru v uložené proceduře.
• Lepší výkon a efektivita, zejména v systémech klient/server, v nichž klienti přistupují vzdáleně (po síti). Procedura je spuštěna a provedena na serveru (typicky rychlém) a klientovi jsou poslána jen data – stejně, jako by data získal přímo z databázové tabulky.

V aplikaci mohou být použity dva druhy uložených procedur (kryjí se s výše uvedeným dělením na procedury vracející/nevracející data):

• Tzv. SELECT procedury (výběrové procedury), které mohou být použity namísto tabulky/pohledu v příkazech SELECT. Takové procedury musejí vracet data (případně chybové hlášení).
• Spustitelné procedury, které se spouštějí (na databázovém serveru, nikoliv v Delphi – v Delphi se k jejich spuštění použije právě komponenta IBStoredProc) příkazem EXECUTE PROCEDURE. Ty mohou (avšak nemusejí) vracet data.

Vraťme se však do vod Delphi. Použijeme-li níže popsanou komponentu IBStoredProc, je možné pracovat pouze s procedurami, které nevracejí data (už jsme si řekli, že IBStoredProc nedokáže vracet data), tedy se spustitelnými procedurami. Potřebujeme-li pracovat se SELECT procedurami, nepotřebujeme vůbec komponentu IBStoredProc a použijeme tradiční IBQuery: v jejím dotazu (vlastnost Query) jednoduše namísto jména tabulky uvedeme jméno uložené procedury.

Nyní si popíšeme komponentu IBStoredProc, která umožňuje pracovat se spustitelnými procedurami v Delphi.

Komponenta IBStoredProc

Komponenta IBStoredProc spouští na databázovém serveru Interbase jednu z uložených procedur. Je však nutné dávat pozor na skutečnost, že IBStoredProc nereprezentuje výslednou datovou sadu (výsledek uložené procedury): pokud uložená procedura vrací nějaká data, je k jejich získání a reprezentaci nutné použít komponentu IBQuery, resp. IBDataSet (s malou výjimkou). Důvody podrobněji rozebereme níže.

IBStoredProc se použije v okamžiku, kdy klientská aplikace musí použít uloženou proceduru na databázovém serveru. Pokud vyžaduje uložená procedura ke své činnosti předání nějakých parametrů, použije se vlastnost Params komponenty IBStoredProc. Vlastnost Params je typu pole hodnot. Komponenta IBStoredProc je napsána tak, že vlastnost Params dokáže použít i pro navrácení výsledku uložené procedury, avšak nikoliv jakéhokoliv. Je-li výsledkem běhu uložené procedury jedna množina (pole) hodnot, je možné tyto hodnoty získat z vlastnosti Params. Bylo-li by však nutné získat více výsledků, není IBStoredProc použitelná.

Důležitá je také vlastnost StoredProcName, která identifikuje jméno procedury uložené na databázovém serveru. Pokud zadáme takové jméno, které neoznačuje žádnou existující uloženou proceduru z databázového serveru, je generována výjimka.

Asi nemá smysl příliš zdůrazňovat, že je nutné nastavit i zděděné vlastnosti Database (komponenta zapouzdřující používanou databázi) a Transaction (komponenta zapouzdřující používanou transakci).

Ke spuštění uložené procedury se používá metoda komponenty IBStoredProc – ExecProc. Před zavoláním této metody je nutné:

• připravit všechny vstupní parametry do vlastnosti Params.
• zavolat metodu Prepare, která připraví proceduru ke spuštění (především připraví parametry).

Metoda ExecProc vrátí řízení aplikaci až poté, co uložená procedura skončí svůj běh. Od toho okamžiku už jsou případné výsledky procedura přístupné v poli Params. Aplikace může tyto hodnoty získat buď indexováním této vlastnosti (Params[0]) nebo pomocí metody ParamByName, která umožňuje přístup k poli Params nikoliv pomocí indexu, ale podle jmen jednotlivých položek. Příklad:

  StoredProc1.ParamByName(`Contact`).AsString := `Jane Smith`;

Vraťme se ještě jednou k několikrát zopakovanému omezení IBStoredProc pouze na spustitelné procedury.

Na uloženou proceduru je možné pohlížet (a také se tak činí) jako na obyčejný databázový objekt obsahující nějaká data. Jinak řečeno – na uloženou proceduru vracející údaje X a Y se můžeme klidně dívat jako na tabulku obsahující sloupce X a Y. Budeme-li chtít znát hodnoty X a Y, je nám (jakožto klientům) úplně lhostejné, získá-li databázový server data jednoduše z tabulky, nebo spustí-li nějakou proceduru, která data „vypočítá“. Proto se na SELECT procedury pohlíží stejně jako na tabulky a úplně stejně se s nimi i pracuje. Mějme tabulku Muž obsahující sloupce Jméno a Příjmení, dále mějme uloženou proceduru Žena vracející Jméno a Příjmení všech žen, které někde složitými postupy vydoluje. Programátor v Delphi toužící dozvědět se jména a příjmení všech mužů i žen použije jednoduše dvě komponenty IBQuery a do vlastnosti Query každé z nich napíše zcela analogický dotaz:

  SELECT Jméno, Příjmení FROM Muž
  SELECT Jméno, Příjmení, From Žena

Jak si s takovým dotazem poradí databázový server, nikoho nezajímá.

Problémy by však nastaly v okamžiku, kdy uložená procedura provede nějakou činnost a nic nevrací. Jak bychom pak formulovali dotaz? Nešlo by to, proto máme k dispozici komponentu IBStoredProc. Ta nedokáže vrátit data (ani to není nutné), ale dokáže spustit na serveru ty procedury, které nic nevrací.

Doufám, že se mi podařilo vysvětlit důvody určitého omezení komponenty IBStoredProc alespoň trochu srozumitelně.

Na závěr

V dnešním článku jsem se pokusil popsat používání komponenty IBStoredProc a také vysvětlit začátečníkům, co se rozumí pod pojmem uložená procedura. Protože dnešní popis byl ryze teoretický a neobsahoval dostatečné množství programového kódu :-), za týden se k uloženým procedurám ještě jednou vrátíme – ale tentokráte prakticky. Vytvoříme společně několik uložených procedur a ukážeme si, jak s nimi pracovat z Delphi aplikací.

Prosba o kritiku a o zpětnou vazbu Závěrem bych vás, milí čtenáři, rád poprosil o určitou formu zpětné vazby. Je-li mezi vámi někdo, kdo se dosud s uloženými procedurami nesetkal a v dnešním článku se o nich dozvěděl poprvé, moc bych jej poprosil, aby na mou adresu elektronické pošty (použijte prosím raději vkadlec@post.cz) poslal stručné zhodnocení toho, do jaké míry byl pro něho článek pochopitelný, srozumitelný a přínosný. Pokud ani po přečtení článku nemáte představu, co uložené procedury jsou, napište mi prosím, co podle vás článku chybí k tomu, aby se stal pochopitelným „učebním“ textem.

Zhodnoťte článek z jakéhokoliv úhlu pohledu. Rád bych své články dále zkvalitňoval a přiblížil je představám těch, komu jsou určeny – tedy vám, zájemcům o programování v Delphi. Tento cíl je velmi vzdálený, ale jakkoliv kritická reakce mi jej pomůže přiblížit. Vašich názorů si vážím a předem za ně děkuji.