Ciele
- Vytvoriť vlastnú PL/pgSQL funkciu a storovanú procedúru.
- Použiť lokálne premenné a vyskúšať si ich naplnenie použitím SELECT ... INTO ...
- Vyskúšať vytvorenie vlastného typu a typu odvodeného z definície tabuľky.
- Použitie cyklu.
Úvod
Dnešné cvičenie je venované rozšíreniu jazyka SQL o možnosť tvorby imperatívnych procedúr a funkcií. V PostgreSQL je takéto rozšírenie implementované ako jazyk PL/pgSQL.
PL/pgSQL procedurálny jazyk rozširuje štandardný SQL o mnohé procedurálne prvky ako napríklad: riadiace štruktúry, cykly, alebo možnosť komplexných výpočtov. Umožňuje vyvíjať aj komplexné funkcie a storované procedúry v PostgreSQL, ktoré nie je možné napísať v čistom SQL.
Hlavným rozdielom medzi funkciou a storovanou procedúrov v PostgreSQL je, že funkcia vracia výsledok, pričom storovaná procedúra nie. Zámerom takejto implementácie je vykonať určitý druh činnosti, dokončiť činnosť a vrátiť volajúcemu kontrolu. Do verzie PostgreSQL 11, boli storované procedúry implementované ako funkcie, ktoré nevracali údaje. V aktuálnej verzii existuje spôsob, ako priamo deklarovať storované procedúry, ktorých výhodou je tiež to, že sú schopné vo svojom kóde začať novú transakciu.
Poznámka
V PostgreSQL je možné využiť viaceré jazyky (PL/Tcl, PL/Perl a PL/Python) na definovanie funkcií a procedúr avšak je potrebné ich nainštalovať. V rámci cvičení budeme pracovať z jazykom PL/pgSQL, ktorý je v PostgreSQL predinštalovaný.
Postup
Krok 1
Pracujeme na databáze Chinook s nasledovnou schémou.
Syntax funkcií v jazyku PL/pgSQL je nasledovná:
CREATE [OR REPLACE] FUNCTION functionName(someParameter 'parameterType')
RETURNS 'DATATYPE'
AS $_block_name_$
DECLARE
--declare variables
BEGIN
--do something
--return something
END;
$_block_name_$
LANGUAGE plpgsql;
Volanie funkcie vykonáte pomocou príkazu:
SELECT functionName(someParameter);
Úloha 1.1
Napíšte funkciu, ktorá vráti počet skladieb v tabuľke Track, ktoré majú uvedeného skladateľa. Pokúste sa funkciu napísať samostatne, až v prípade problémov zobrazte riešenie.
Riešenie
CREATE OR REPLACE FUNCTION tracks_with_composer()
RETURNS INT AS $total$
DECLARE
total INT;
BEGIN
SELECT count(composer) into total FROM track;
RETURN total;
END;
$total$ LANGUAGE plpgsql;
Úloha 1.2
Napíšte funkciu, ktorá bude mať dva parametre, z tabuľky track vrátí počet pesniciek ktorých dĺžka je v intervale sekúnd od_sek do do_sek.
Syntax storovaných procedúr v jazyku PL/pgSQL je nasledovná:
CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
AS $_block_name_$
DECLARE
-- variable declaration
BEGIN
-- stored procedure body
END;
$_block_name_$
LANGUAGE plpgsql;
Volanie storovanej procedúry vykonáte pomocou príkazu:
CALL procedure_name(someParameter);
Úloha 1.3
Z tabuľky invoice odstráňte ľubovoľný záznam.
Riešenie
Údaje v tabuľke InvoiceLine sa cudzím kľúčom odkazujú na tabuľku Invoice. Z tohto dôvodu sa táto operácia nevykonala. Tabuľky boli totiž vytvorené s použitím predvoleného správania sa pri zabezpečení dodržania referenčnej integrity, ktoré je v PostgreSQL ON DELETE NO ACTION (reštrikčný mód). Toto správanie nedovolí vymazanie faktúry, ak sa na ňu odkazujú nejaké položky.
Úloha 1.4
Napíšte procedúru deleteInvoice, ktorá z databázy odstráni faktúru s uvedeným id, a to aj v prípade, že sa na ňu odkazujú nejaké položky. Procedúra bude brať ako parameter jednu hodnotu:
- 'invoice' typu int - id faktúry, ktorá má byť zmazaná.
Poznámka
V tele procedúry stačí napísať dva príkazy DELETE, prvý vymaže položky na danej faktúre, druhý samotnú faktúru.
Poznámka
Pred ukončením procedúry vypíšte hlášku o zmazaní faktúry, napr.: 'Invoice with id -> 1 removed.'. Použite funkciu raise. Na overenie si môžete do databázy manuálne vložiť nejakú novú faktúru, a následne ju zmazať zavolaním procedúry deleteInvoice.
Krok 2
Máme pripravenú procedúru na mazanie. Ďalšou úlohou napísanie procedúry na kompletné vytvorenie faktúry. V prvom kroku je potrebné pripraviť procedúru na prepojenie konkrétnej existujúcej faktúry a skladby, ktorú neskôr využijete na prepojenie novovytvorenej faktúry a kupovaných skladieb.
Úloha 2.1
Napíšte procedúru addTrackToInvoice, ktorá bude slúžiť na vkladanie kupovaných skladieb na faktúru (vloženie nového záznamu do tabuľky InvoiceLine). Procedúra bude brať ako parametre tri hodnoty:
- 'invoice' typu int - id faktúry, na ktorú sa ma skladba kúpiť,
- 'boughtTrack' typu int - id skladby, ktorú chce používateľ kúpiť, a
- 'trackQuantity' typu int - počet kupovaných skladieb.
Jednotkovú cenu, ktorú v InvoiceLine tabuľke je nutné tiež uviesť, nájdete v tabuľke track pre danú skladbu (teda na faktúre sa použije aktuálna cena skladby v čase).
Poznámka
Na načítanie jednotkovej ceny si deklarujte premennú trackUnitPrice a v tele procedúry ju inicializujte použitím konštrukcie 'SELECT ... INTO unitPrice FROM ...', napr.:
SELECT count(*)
INTO pocet
FROM track
WHERE name like 'M%';
Hodnota unitPrice môže obsahovať desatinné miesta - hodnotu nezaokrúhľovať.
invoiceLineId nového záznamu určte ako ďalšie číslo nasledujúce za najvyšším existujúcim invoiceLineId.
Poznámka
Obdobne načítajte aktuálne maximálne invoiceLineId v tabuľke InvoiceLine, ktorého inkrementáciou získaš nové invoiceLineId.
Poznámka
Po vykonaní vloženia nového záznamu do InvoiceLine vypíšte na výstup hlášku sumarizujúcu nový záznam, napr.
"Inserted new invoiceLine (invoiceLineId -> 2248, invoiceId -> 324, trackId -> 329,
unitPrice -> 0,99, quantity -> 2)"
pre volanie procedúry:
call addTrackToInvoice(324, 329, 2);
Krok 3
V ďalšej úlohe pokračujte v našej práci tým, že pripravíte procedúru, prostredníctvom ktorej sa v databáze vytvorí faktúra a naplní sa príslušnými záznamami.
Úloha 3.1
Napíšte procedúru buyTracks, ktorá bude slúžiť na vytvorenie faktúry pre daný nákupný košík. Procedúra bude mať dva parametre:
- 'custId' typu int - identifikátor nakupujúceho zákazníka, a
- 'boughtTracks' typu pole celych čísel (o maximálnej dĺžke 10)- zoznam identifikátorov kupovaných skladieb.
Okrem pomocnej premennej newInvoiceId pre vytvorenie identifkátora novej faktúry (podobne ako v predchádzajúcej úlohe načítaním aktuálne najvyššieho invoiceId v databáze a jeho inkrementáciou) budete potrebovať premennú pre načítanie celej adresy zákazníka, ktorú potom použijete ako faktúrovaciu adresu v tabuľke invoice.
Poznámka
V tomto prípade existuje viacero možností. Môžete si vytvoriť 5 premenných, po jednu pre adresu, mesto, štát, krajinu a PSČ. Pohodlnejšie však bude načítať si do premennej rovno celý záznam o zákazníkovi a potom z neho vybrať len tie podstatné údaje. Použite preto len jednu premennú customerData, ktorej typ deklarujete pomocou %ROWTYPE (premenná bude kompozitného typu záznam - RECORD, definovaná typom jedného riadku v tabuľke Customer). K údajom po načítaní sa pristupuje objektovou notáciou - napr.: 'customerData.address'.
Po načítaní týchto informácií vložte do tabuľky Invoice nový záznam, pričom invoiceDate bude aktuálny systémový dátum a faktúrovacia adresa bude nakopírovaná aktuálna adresa zákazníka.
Poznámka
Ak ste v predchádzajúcich úlohách nezmazali stĺpec total, je potrebné vypočítať aj ten.
Poznámka
Opäť si pre overenie na záver procedúry napíšte príkaz, ktorý vypíše oznam o úspešnom vložení záznamu do databázy (pre overenie vypíšte aj hodnoty vkladaného záznamu).
Na overenie splnenia úlohy použite nasledujúce volanie procedúry:
call buyTracks(23,'{44, 601, 1045, 1054, 3056}' );
To zákazníkovi John Gordon vytvorí novú faktúru.
Krok 4
V predchádzajúcej úlohe v rámci procedúry sa nevytvorili záznamy aj v tabuľke InvoiceLine, len záznam v Invoice.
Úloha 4.1
Upravte procedúru buyTracks tak, aby zároveň vytvorila potrebné záznamy v InvoiceLine, ktoré prepoja faktúru s kúpenými skladbami.
Poznámka
Na vloženie skladby na faktúru využite už vytvorenú procedúru addTrackToInvoice. V cykle FOREACH stačí prejsť všetkými identifikátormi kupovaných skladieb v poli boughtTracks a pre každý z nich zavolať addTrackToInvoice s identifikátorom novovytvorenej faktúry, skladby, a počet kupovaných skladieb určite staticky na jednu skladbu.
Volaním procedúry deleteInvoice zmažte pridanú faktúru a znova zavolajte blok:
call buyTracks(23,'{44, 601, 1045, 1054, 3056}' );
Čo zákazníkovi John Gordon vytvorí novú faktúru pre piesne You Learn, Walkin', Fly Me To The Moon, Mack The Knife, Intruder.
Úloha 4.2
Vhodným selectom over, že v databáze je pridaná nová faktúra pre danú zákazníčku s uvedenými skladbami.
Úloha 4.3
Pouvažujte nad vytvorením vlastného typu pre sprehľadnenie kódu niektorej z vytvorených procedúr.
Zdroje
Doplňujúce úlohy
Úloha A.1
Podnik
Napíšte funkciu, ktorá bude mať dva parametre, z tabuľky podnik vráti počet zamestnancov ktorých mzda je v rozpätí od_mzda do do_mzda.