Umíme to s Delphi: 94. díl – vytváříme uložené procedury

Dnešní článek je zaměřen na podrobný popis uložených procedur. Nejprve si vysvětlíme vztah uložených procedur a platformy BDE, pak prozkoumáme oba druhy uložených procedur – výběrových i spustitelných a nakonec společně jednu proceduru vytvoříme a uložíme na databázový server.
Dnes společně vytvoříme uloženou proceduru a ukážeme si, jakým způsobem ji dostat (uložit) na databázový server Interbase, jak ji na serveru spustit a jak získat a interpretovat její výsledky. Vyjdeme přitom z ukázkové databáze Platy, kterou jsme vytvořili v 90. dílu seriálu.

Nejprve bych však rád uvedl na pravou míru jednu drobnou nesrovnalost související se starším databázovým přístupem pomocí BDE.

Uložené procedury a BDE

Před nedávnem se na mě obrátil jeden ze čtenářů s povzdechem, že BDE neobsahuje uložené procedury, což je velká škoda. V této souvislosti jsem si uvědomil, že jsem při popisu BDE skutečně uložené procedury nezmínil, nicméně aby nedocházelo ke zbytečným nedorozuměním, napravím to v následujících odstavcích.

BDE je sice mírně zastaralý mechanismus, nicméně není pravda, že nepodporuje uložené procedury. Je však nutné uvědomit si rozdíl mezi BDE a například platformou Interbase Interbase je fyzicky existující databáze (platforma, server), která je často používána a pro jejíž obsluhu existuje v Delphi mnoho komponent. Pomocí těchto komponent můžeme pracovat s nainstalovaným serverem Interbase a můžeme využívat všech jeho možností. Naproti tomu BDE není žádná databázová platforma – je to jen a pouze rozhraní pro přístup k nejrůznějším druhům databází (k nejrůznějším databázovým platformám).

Z tohoto rozdílu plyne několik důsledků. Pomocí komponent Interbase je možné pracovat pouze s platformou Interbase (příp. Firebird – důvody viz 89. díl seriálu). Komponenty Delphi ze záložky Interbase tedy pokrývají všechnu (skoro) funkčnost, kterou databázové servery Interbase dokáží nabídnout a práce s těmito komponentami (jejich přístup k databázi) je poměrně rychlý.

Naproti tomu pomocí komponent BDE je možné přistupovat k více databázovým platformám a je dokonce možné získávat data přímo z některých databázových souborů bez nutnosti vlastnit příslušnou platformu (např. Paradox, s nímž jsme se v seriálu setkávali nejčastěji). Databázové komponenty ze záložky BDE tedy musí nabízet takovou funkčnost, aby dokázaly pokrýt všechny podporované databáze. Na druhou stranu tyto komponenty musí být koncipovány mnohem obecněji a robustněji, proto může být práce přes BDE méně efektivní (a to je také jedna z největších výhrad k této technologii).

Z těchto důvodů nabízí záložka BDE i komponentu pro práci s uloženými procedurami. Komponenta se nazývá StoredProc. Její význam je stejný jako u komponenty IBStoredProc popsané v předchozí části seriálu, stejný je i způsob předávání parametrů (pomocí vlastnosti Params) a získávání výsledků (též pomocí vlastnosti Params).

Je však nutné zdůraznit, že ne všechny databázové servery (s nimiž můžeme pracovat pomocí BDE) podporují uložené procedury. Například zmíněné tabulky Paradoxu samozřejmě žádné uložené procedury nepodporují (ani podporovat nemohou), takže při práci s Paradoxovými soubory je nám komponenta StoredProc zhola k ničemu. Pokud chcete v BDE používat uložené procedury, je nutné vždy prozkopumat dokumentaci k používané databázové platformě – jedině tak si ověříte, že komponentu StoredProc můžete použít.

Tolik obdočka k BDE. Nyní se vrátíme zpět k Interbase. Ještě předtím, než vytvoříme uloženou proceduru ve své ukázkové databázi Platy, popíšeme podrobněji oba druhy procedur – spustitelné (EXECUTE) i výběrové (SELECT) procedury.

Výběrové (SELECT) procedury

Výběrové procedury se (jak už jsme si vysvětlili v minulém dílu) používají (volají) v příkazech SELECT namísto běžných tabulek a pohledů. Mohou vracet žádný, jeden nebo více záznamů (řádků). Výběrové procedury musí vracet jeden nebo více výstupních parametrů (nebo chybové hlášení). Pokud nespecifikujeme návratovou hodnotu (/návratové hodnoty), server standardně zajistí vrácení hodnoty NULL. Mezi výhody výběrových procedur patří zejména:

  • možnost předávat do nich vstupní parametry a ovlivňovat tak výsledky procedury
  • možnost obsahovat řídicí příkazy, lokální proměnné a příkazy pro manipulaci s daty

Vstupní parametry jsou do procedury předávány při v seznamu odděleném čárkami uzavřeného v závorkách za názvem procedury, jak uvidíme vzápětí.

Ukážeme si konečně také ukázku výběrové procedury. Následující SQL skript zajistí

vytvoření a uložení procedury GET_EMP_PROJ na databázový server. Procedura vrací údaj EMP_PROJ, což jsou čísla všech projektů přiřazených k zadanému zaměstnanci. Procedura tedy přijímá jeden vstupní parametr – číslo zaměstnance, jehož projekty nás zajímají.

CREATE PROCEDURE GET_EMP_PROJ (emp_no SMALLINT)
RETURNS (emp_proj SMALLINT) AS
BEGIN
  FOR SELECT PROJ_ID
      FROM EMPLOYEE_PROJECT
      WHERE EMP_NO = :emp_no
      INTO :emp_proj
  DO
      SUSPEND;
END;

Následující SQL příkaz zajistí zavolání procedury a předání odpovídajících parametrů. Připomínám, že nyní se nepohybujeme v Delphi – následující příkaz bychom použili, kdybychom proceduru volali z některého SQL klienta (v případě Interbase např. pomocí isql, ale také pomocí utilit jako IBExpert nebo IBConsole), případně pokud bychom používali SQL jako součást webových stránek. Příkaz také ukazuje, jak v SQL předávat parametry:

SELECT PROJ_ID FROM GET_EMP_PROJ (25);

Uvedený příklad není příliš flexibilní – vždycky bychom se dozvěděli projekty zaměstnance číslo 25. Dejme tomu, že je číslo zaměstnance uloženo v proměnné :cislo. Pak vypadá příkaz takto:

SELECT PROJ_ID FROM GET_EMP_PROJ (:cislo);

Tento příkaz je již velmi podobný tomu, který budeme později zapisovat přímo v Delphi.

Poznámka: je samozřejmě možné používat všechny další prvky tradičních SQL dotazů, např.

SELECT PROJ_ID FROM GET_EMP_PROJ (:cislo) WHERE PROJ_ID > 25 ORDER BY PROJ_ID DESC;

Spustitelné (EXECUTE) uložené procedury

Spustitelná procedura je typicky zavolána (spuštěna) přímo aplikací – v případě Delphi pomocí komponenty IBStoredProc. Spustitelné procedury mohou (podobně jako výběrové) přebírat vstupní parametry. Mohou také vracet určité výsledky, nejvýše však jednu jedinou řádku dat. Tento druh nemůže v žádném případě vracet více řádků výsledku.

Parametry se do procedury předávají úplně stejně jako v případě výběrových procedur a mohou být buď ve formě konstant (7, „Stanislav“) nebo proměnných.

Chceme-li spustit proceduru z nějakého SQL klienta, použijeme následující syntaxi:

EXECUTE PROCEDURE name [: param [[INDICATOR]: indicator]]
[, : param [[INDICATOR]: indicator] ...]
[RETURNING_VALUES : param [[INDICATOR]: indicator]
[, : param [[INDICATOR]: indicator]...]];

Pomocí klauzule RETURNING_VALUES zadáváme výstupní proměnné, tj. proměnné, do nichž v těle procedury zapíšeme nějakou hodnotu.

Následující příklad demonstruje zavolání procedury DEPT_BUDGET. Proceduře předáme jako parametr konstantu (100) a výsledek chceme zapsat do výstupní proměnné :sumb.

EXECUTE PROCEDURE DEPT_BUDGET 100 RETURNING_VALUES :sumb;

Následující řádka ještě ukáže, jak ze SQL klienta zavolat uloženou proceduru, přičemž parametr nezadáváme jako konstantu, ale jako proměnnou:

EXECUTE PROCEDURE DEPT_BUDGET :rdno RETURNING_VALUES :sumb;

Vytváříme uloženou proceduru

Nyní si krok za krokem ukážeme, jak vytvořit a použít uloženou proceduru v naší Delphi aplikaci. Je nutné, abyste měli vytvořenou databázi Platy (jejímu vytváření v platformě Interbase jsme se věnovali v 90. dílu seriálu). Připomeňme, že tato databáze se skládá ze tří tabulek (Zamestnanec, Oddeleni a Profese) a jejím účelem je uchovávat všechny údaje nutné pro výpočet platu jednotlivých zaměstnanců.

Dále je nutné, aby na vašem počítači běžel Interbase/Firebird server. Pokud jste server instalovali společně s námi (viz 89. díl seriálu), měl by běžet. Přesvědčíte se o tom tak, že v Ovládacích panelech Windows najdete Služby (ve Windows 2000 je tato položka skrytá ještě v Nástrojích pro správu) a přesvědčíte se, že běží služba Interbase Server.

Nejprve si ukážeme, jak uloženou proceduru vytvořit. Pro demonstrační účely napíšeme proceduru, která projde tabulku Zamestnanec a pro každý záznam (pro každého zaměstnance) provede následující činnost:

  • má-li zaměstnanec nulové osobní ohodnocení a nulový profesní příplatek, přidá mu osobní ohodnocení 500 Kč,
  • má-li zaměstnanec nenulové osobní ohodnocení, zvýší mu jej o 10%.

Vytvoříme postupně dvě verze této procedury – první bude výběrová (SELECT). Ta zároveň vrátí jména a příjmení všech zaměstnanců, u nichž došlo ke změně platu.

Vytváříme výběrovou proceduru

Uloženou proceduru musíme v každém případě napsat pomocí jazyka SQL, žádný vizuální nástroj ani sebelepší utilita nám nyní nepomůže. Otevřete proto nástroj IBConcole (popis viz 89. díl seriálu) a otevřete dialog Tools – Interactive SQL (viz obrázek). Do dialogu pak zapište níže uvedený zdrojový kód.

Klepněte pro větší obrázek

Zdrojový kód:

SET TERM !! ;

CREATE PROCEDURE upravplatyv
RETURNS (prijmeni VARCHAR(30), jmeno VARCHAR(20) ) AS
  DECLARE VARIABLE IDZAMEST INTEGER;
  DECLARE VARIABLE OHODNOCENI INTEGER;
  DECLARE VARIABLE PRIPLATEK FLOAT;

BEGIN
  FOR SELECT idzamest, jmeno, prijmeni, ohodnoceni, priplatek
      FROM Zamestnanec Z JOIN Profese P
      ON Z.idprofese = P.idprofese
      INTO :IDZAMEST, :JMENO, :PRIJMENI, :OHODNOCENI, :PRIPLATEK

  DO
  BEGIN
    IF (:OHODNOCENI=0 AND :PRIPLATEK = 0) THEN
    BEGIN
      UPDATE Zamestnanec SET Ohodnoceni=500 WHERE idzamest=:IDZAMEST;
      suspend;
    END
    ELSE
      IF (:OHODNOCENI>0) THEN
      BEGIN
        UPDATE Zamestnanec SET Ohodnoceni=(1.1*:OHODNOCENI) WHERE idzamest=:IDZAMEST;
        suspend;
      END
  END
END!!

SET TERM ; !!

V Interactive SQL následně zvolte položku Query – Execute. SQL kód bude proveden a procedura bude uložena do databáze.

Než vytvoříme aplikaci, která tuto proceduru spustí a použije, vysvětlíme si trochu uvedený zdrojový kód.

Pomocí příkazu SET TERM pouze změníme dočasně oddělovač jednotlivých příkazů pro SQL utilitu. Důsledkem bude bezproblémové spouštění uvedeného SQL skriptu ve všech utilitách pracujících se SQL.

Následně vytvoříme proceduru (CREATE PROCEDURE), která bude vracet dva údaje (přesněji řečeno – řádky dvousloupcových údajů) – příjmení a jméno pracovníků, jimž jsme zvedli mzdu.

Poté definujeme některé pomocné proměnné, do nichž bude příkaz SELECT použitý uvnitř procedury ukládat své výsledky. Použijeme-li totiž v uložených procedurách příkaz SELECT, musíme tomuto příkazu umožnit uložení výsledků. Příkaz se proto za tím účelem rozšiřuje o klíčové slovo INTO, za nímž následuje seznam proměnných (buďto deklarovaných jako výstupní parametry nebo definovaných jako pomocné pomocí DECLARE VARIABLE), do nichž se vždy uloží jedna (aktuální) řádka výsledku. Pozor, seznam proměnných za klíčovým slovem INTO musí odpovídat sloupcům uvedeným za SELECT.

Pro rozlišení jmen sloupců od jmen proměnných uvádíme před jmény proměnných dvojtečky.

Celý příkaz SELECT je „uzavřen“ v cyklu FOR. Cyklus se „posouvá“ po jednotlivých řádcích původního dotazu. Pro každý řádek, který vrátí SELECT, proběhne jednou tělo cyklu FOR. Pokud v dané obrátce cyklu zjistíme, že aktuálnímu pracovníku máme zvednout mzdu (neboť splňuje některou ze stanovených podmínek), upravíme příslušný záznam v tabulce Zamestnanec (UPDATE Zamestnanec) a zavoláme příkaz suspend, který zajistí předání aktuálních hodnot výstupních parametrů (tedy jména a příjmení zaměstnance, jemuž jsme právě upravili plat) volajícímu programu. Jinak řečeno – aplikace v Delphi, která tuto proceduru spustí, dostane v tomto okamžiku jméno a příjmení aktuálního pracovníka.

Tímto způsobem se projde celá tabulka Zamestnanec a upraví se příslušné hodnoty mezd. Poté běh uložené procedury končí.

Za týden si ukážeme, jak tuto uloženou proceduru zavolat z aplikace v Delphi. Nebude to obtížné (můžete si to zkusit již teď samostatně) – protože jsme pro začátek vytvořili výběrovou proceduru, stačí použít komponentu Query a výsledky procedury získáme stejně, jako bychom se dotazovali na data uložená v některé z tabulek. V příštím dílu seriálu si vše ukážeme prakticky.

Příště opět do Delphi

V dnešním dílu seriálu jsme se dostali zase o trochu blíže v popisu uložených procedur. Poznali jsme, jaký vztah mají uložené procedury k databázové technologii BDE, vysvětlili jsme si hlavní rysy výběrových a spustitelných procedur a nakonec jsme vytvořili ukázkovou uloženou proceduru pracující s databází Platy. V příští části seriálu budeme pokračovat tam, kde jsme dnes skončili – ukážeme si, jak s touto uloženou procedurou pracovat v prostředí Delphi.

Váš názor Další článek: Amazon.com: tržby rychle rostou, pomohl prý Harry Potter

Témata článku: Software, Programování, Následující aplikace, Používané data, Klíčový parametr, Ukázkový příklad, Paradox, Select, Term, Díl, Následující řádek, Možný důsledek, Plat, Zavolání, From, Uložení, Původní dotaz, Nejrůznější způsoby, Nejrůznější nástroje, Delphi, Stejné tělo

Určitě si přečtěte


Aktuální číslo časopisu Computer

Zachraňte nefunkční Windows

Jak nakupovat a prodávat kryptoměny

Otestovali jsme konvertibilní notebooky

Velký test 14 herních myší