Úvod do PL/SQL

Ciele
  1. Vytvoriť vlastnú PL/SQL procedúru.
  2. Použiť lokálne premenné a vyskúšať si ich naplnenie použitím SELECT ... INTO ...
  3. Vyskúšať vytvorenie vlastného typu, a typu odvodeného z definície tabuľky (%ROWTYPE).
  4. Použitie cyklu.
Úvod

    PL/SQL je procedurálne rozšírenie jazyka SQL implementované v Oracle databázovom systéme. Umožňuje nám písať imperatívny kód, ktorý je vykonávaný systémom riadenia bázy dát (SRBD).

    Vďaka tomu je použitím PL/SQL napr. možné napísať časť logiky aplikácie priamo v databáze, čo môže mať význam ak daný proces intenzívne pracuje s údajmi. V prípade, že je potrebné spracovať veľké objemy údajov, použitie PL/SQL dovolí nenaťahovať tie údaje z databázového systému do aplikácie - potrebné operácie sa vykonajú skompilovaným (teda rýchlym) kódom priamo databázovým systémom.

    Našou úlohou na tomto cvičení bude napísať procedúru na vytvorenie faktúry pre zákazníka. Povedzme, že aplikácia nad touto databázou umožňuje používateľovi vyklikať si nákupný košík, v ktorom si vyberie skladby, ktoré chce kúpiť. Potom budeme chcieť pre jedného zákazníka a zoznam skladieb, ktoré chce kúpiť, zavolať jedinú procedúru, ktorej tieto informácie odovzdáme, a procedúra sa postará o vytvorenie správnych záznamov v databáze.

Postup
  1. Ak by si sa pokúšal zmazať faktúru a predtým by si nezmazal všetky položky na faktúre z tabuľky InvoiceLine, ktoré sa cudzím kľúčom odkazujú na tabuľku Invoice, databáza ti to nedovolí. 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 Oracle ON DELETE NO ACTION (restrikčný mód). Toto správanie nedovolí vymazanie faktúry, ak sa na ňu odkazujú nejaké položky.

    Poznámka: SQLDeveloper sa pri programovaní PL/SQL občas správa trošku zvláštne. Odporúčam ti písať v jednom otvorenom pracovnom hárku (SQL Worksheet) vždy práve jednu procedúru (resp. trigger), a pre testovanie si otvor nový SQL hárok (kontextové menu nad pripojením).

    Úloha: Napíš 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:
    1. 'invoice' typu int - id faktúry, ktorá má byť zmazaná.
    Poznámka: V tele procedúry ti stačí napísať dva príkazy DELETE, prvý vymaže položky na danej faktúre, druhý samotnú faktúru. Nezabudni zavolať príkaz COMMIT na potvrdenie transakcie.
    Poznámka: Pred ukončením procedúry vypíš hlášku o zmazaní faktúry, napr.: 'Invoice with id -> 1 removed.'. Použi procedúru DBMS_OUTPUT.PUT_LINE.
    Na overenie si môžeš do databázy manuálne vložiť nejakú novú faktúru, a následne ju zmazať zavolaním procedúry deleteInvoice. Volať procedúru môžeš dvoma spôsobmi, priamo volaním:
    execute deleteInvoice(413);
    alebo v rámci anonymného PL/SQL bloku:
    BEGIN
      deleteInvoice(413);
    END;
  2. Keď máme pripravenú procedúru na mazanie, môžeme začať pripravovať databázu na procedúru na kompletné vytvorenie faktúry. Začneme tým, že si pripravíme procedúru na prepojenie konkrétnej existujúcej faktúry a skladby, ktorú neskôr využijeme na prepojenie novovytvorenej faktúry a kupovaných skladieb.
    Úloha: Napíš 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:
    1. 'invoice' typu int - id faktúry, na ktorú sa ma skladba kúpiť,
    2. 'boughtTrack' typu int - id skladby, ktorú chce používateľ kúpiť, a
    3. 'trackQuantity' typu int - počet kupovaných skladieb.
    Jednotkovú cenu, ktorú v InvoiceLine tabuľke musíš tiež uviesť, nájdeš 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 deklaruj premennú trackUnitPrice a v tele procedúry ju inicializuj použitím konštrukcie 'SELECT ... INTO unitPrice FROM ...', napr.:
    SELECT count(*)
      INTO pocet
      FROM track
      WHERE name like 'M%';
    Daj si pozor na to, že hodnota unitPrice môže obsahovať desatinné miesta - hodnotu nechceme zaokrúhliť.
    invoiceLineId nového záznamu urč ako ďalšie číslo nasledujúce za najvyšším existujúcim invoiceLineId.
    Poznámka: Obdobne načítaš 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íš na výstup hlášku sumarizujúcu nový záznam, napr. "Inserted new invoiceLine (invoiceLineId -> 2243, invoiceId -> 324, trackId -> 329, unitPrice -> ,99, quantity -> 2)" pre volanie "execute addTrackToInvoice(324, 329, 2);".
  3. V ďalšej úlohe uzavrieme našu prácu tým, že pripravíme procedúru, prostredníctvom ktorej sa v databáze vytvorí faktúra a naplní sa príslušnými záznamami. V rámci danej procedúry však budeme potrebovať nasledujúci typ (/ vyprázdni aktuálny buffer a tým vynúti vytvorenie typu - tento oddeľovač použijte ak hneď za vytvorenie typu chcete písať vytvorenie procedúry a chete to spúšťať ako skript):
    CREATE OR REPLACE TYPE listOfTracksType IS VARRAY(10) OF INT;
    /
    Typ listOfTracksType predstavuje pole maximálne desiatich čísel. Tento typ budeme využívať na vymenovanie identifikátorov skladieb, ktoré majú byť na danú faktúru kúpené.
    Poznámka: Spustením uvedeného kódu typ v databáze vytvoríte.
    Poznámka: Tým, že používame typ VARRAY, musíme uviesť maximálny počet skladieb, ktoré sme schopní spracovať (teraz sme to obmedzili na 10). V PL/SQL existujú ešte tzv. asociatívne polia a vnorené tabuľky, pri ktorých nie je maximálna dĺžka obmedzená, a je ich teda možné použiť, ak dopredu nevieme povedať maximálny rozmer kolekcie. Ak sa chceš téme venovať viac, môžeš skúsiť úlohu prepísať s použitím takého typu (viď. Kolekcie). Použitie asociatívneho poľa si však vyžaduje deklaráciu balíka.
    Úloha: Napíš procedúru buyTracks, ktorá bude slúžiť na vytvorenie faktúry pre daný nákupný košík. Procedúra bude mať dva parametre:
    1. 'custId' typu int - identifikátor nakupujúceho zákazníka, a
    2. 'boughtTracks' typu listOfTracksType - zoznam identifká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) budeš potrebovať premennú pre načítanie celej adresy zákazníka, ktorú potom použiješ ako faktúrovaciu adresu v tabuľke invoice.
    Poznámka: V tomto prípade máš viacero možností. Môžeš 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ži preto len jednu premennú customerData, ktorej typ deklaruješ pomocou %ROWTYPE (premenná bude teda typu záznam - RECORD; definovaná typom jedného riadku v tabuľke Customer). K údajom po načítaní pristúpiš objektovou notáciou - napr.: 'customerData.address'.
    Po načítaní týchto informácií vlož 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 si si v predchádzajúcich úlohách nezmazal stĺpec total, musíš vypočítať aj ten.
    Poznámka: Opäť si pre overenie na záver procedúry napíš príkaz, ktorý ti vypíše oznam o úspešnom vložení záznamu do databázy (pre overenie si vypíš aj hodnoty vkladaného záznamu).
    Na overenie splnenia úlohy použi nasledujúci anonymný PL/SQL blok:
    DECLARE
      tracks listOfTracksType := listOfTracksType();
    BEGIN
      tracks.extend(4);
      tracks(1) := 3429;
      tracks(2) := 3346;
      tracks(3) := 335;
      tracks(4) := 338;
      buyTracks(59, tracks);
    END;
    Ten zákazníčke Puja Srivastava vytvorí faktúru pre piesne 'The Return', 'Ji Yeon', 'My Love', a 'I Can't Quit You Baby'.
  4. Avšak zatiaľ sme v rámci procedúry nevytvorili záznamy aj v tabuľke InvoiceLine, len záznam v Invoice.
    Úloha: Uprav procedúru buyTracks tak, aby zároveň vytvoril potrebné záznamy v InvoiceLine, ktoré prepoja faktúru s kúpenými skladbami.
    Poznámka: Na vloženie skladby na faktúru využi procedúru addTrackToInvoice, ktorú si napísal v predchádzajúcich úlohách. Stačí ti v cykle FOR prejsť všetkými identifikátormi kupovaných skladieb a pre každý z nich zavolať addTrackToInvoice s identifikátorom novovytvorenej faktúry, skladby, a počet kupovaných skladieb urči staticky na jednu skladbu.
    Poznámka: Počet prvkov v poli VARRAY získaš volaním 'boughtTracks.count'. K prvku na konkrétnej pozícii sa dostaneš cez 'boughtTracks(counter)', kde counter je číslo indexu (1 až počet prvkov v poli).
    Volaním procedúry deleteInvoice zmaž pridanú faktúru a znova zavolaj blok:
    DECLARE
      tracks listOfTracksType := listOfTracksType();
    BEGIN
      tracks.extend(4);
      tracks(1) := 3429;
      tracks(2) := 3346;
      tracks(3) := 335;
      tracks(4) := 338;
      buyTracks(59, tracks);
    END;
    Úloha: Vhodným selectom over, že v databáze je pridaná nová faktúra pre danú zákazníčku s uvedenými skladbami.
Doplňujúce úlohy
    Úloha:

    Chinook

    Nájdi najdlhšie video v databáze (napíš select).
    Úloha:

    Chinook

    Uprav vytvorené procedúry tak, aby zabezpečovali integritu databázy.
    1. uprav procedúru addtracktoinvoice tak, aby v prípade, že jej parametre odkazujú na neexistujúcu faktúru, alebo skladbu, vyhodí novú výnimku (vytvor vlastný typ výnimiek)
    2. uprav procedúru buyTracks tak, aby v prípade, že niektoré volanie procedúry addTrackToInvoice vyhodí výnimku, procedúra vypíše chybu na výstup, zruší všetky dovtedy vykonané zmeny v databáze (tzn. ak už vytvorí novu faktúru a priradí na ňu dve skladby, ale pri priradzovaní tretej dôjde k chybe, zrušia sa aj tie predchádzajúce vloženia a databáza bude po volaní v stave v akom bola na začiatku), a vyhodí výnimku ďalej
    Poznámka: Nezabudni, že ak voláš COMMIT v každom volaní addTrackToInvoice, ROLLBACK ti už COMMIT-nuté zmeny nevráti.
comments powered by Disqus