Databases_SO 2023/24 spring

Protseduuride loomine

Protseduurid kirjutavad/muudavad (sisestavad, uuendavad, kustutavad) andmebaasi. Protseduure harilikult kasutaja otse ei käivita vaid protseduur käivitatakse harilikult taustal mingi kokkulepitud tegevuse korral. Protseduurid realiseerivad tihti ärireegleid ja väljastavad teateid. Kuigi ka funktsioonid saavad sisestada, muuta, kustutada kirjeid kasutades RETURNS void (vaata loenguslaididelt näidet), siis enamasti jäetakse need ikka protseduuride kanda.

Protseduuri loomine

Protseduuride loomist vaatame samuti nii SQL kui ka protseduurilises SQL keeles. Alustame SQL keele protseduuride näidetest.

CREATE [ OR REPLACE ] PROCEDURE <protseduuri_nimi> ([{parameetrid koos andmetüübiga}]) 
LANGUAGE SQL
AS ......

Protseduuri loomise formaat sarnaneb suures osas funktsiooni loomisele omale. Põhiline erinevus seisneb selles, et protseduur ei tagasta tulemust, seega ei ole ka loomisel vaja täpsustada tagastatavat struktuuri.

Ütleme, et ärireegel nõuab, et kõik eesnimed peavad algama suure tähega. Loome protseduuri, mis käivitamisel muudab kõikide eesnimede esimesed tähed suurtähtedeks. Seda saame kasutada kui sisestamisel on tehtud viga ja mõni eesnimi on ikkagi sisestatud väikese tähega. Samas kui tahame otsida konkreetse eesnimega inimesi, siis PostgreSQL eristab suur- ja väiketähti.

CREATE PROCEDURE sp_esitaht_suureks() 
LANGUAGE SQL
AS $$ UPDATE isikud SET eesnimi = initcap(eesnimi); $$

Uuendame isikud tabelis kõik Urmased väikese tähega.

UPDATE isikud SET eesnimi = 'urmas' WHERE eesnimi = 'Urmas'; 

Kontrollige, et kõik Urmased on tabelis väikese tähega.
Nüüd kasutame loodud protseduuri, mis muudab kõikide eesnimede esimese tähe suureks isikute tabelis.

CALL sp_esitaht_suureks();

Kontrollige, kas Urmas eesnimed on taas kõik suure algustähega.

Paneme tähele, et funktsiooni kutsusime välja SELECT käsuga, siis protseduuri poole pöördume CALL käsuga. Protseduuride kasutamist rakendame ka pärast triggerite õppimist.

Protseduuri loomine protseduurilises SQL'is:

CREATE [ OR REPLACE ] PROCEDURE <protseduuri_nimi> ([{parameetrid koos andmetüübiga}]) 
LANGUAGE plpgsql
AS $$
[DECLARE...]
BEGIN
    ...
END
$$

Koostame protseduuri, mis võrdleb kahte täisarvu ja väljastab teate, kumb neist on suurem või on tegemist võrdsete arvudega.

CREATE PROCEDURE  sp_vordlus(a integer, b integer ) 
LANGUAGE plpgsql
AS $$
DECLARE
   note   varchar(40);
BEGIN
   IF a > b 
   THEN
       note = 'Esimene arv on suurem kui teine.';
   ELSIF a < b 
       THEN 
       note = 'Teine arv on suurem kui esimene.';
       ELSE
       note = 'Need arvud on võrdsed.';
   END IF;
RAISE NOTICE '%', note;     
END;
$$ ;

Kordame siin loengu näidet, kus koostame protseduuri, mis lisab uue klubi ja kui klubi asulat pole asulate tabelis, siis ka selle. Aga teeme selle protseduuri veidi keerukamaks nõudes ka, et see protseduur väljastaks teate, mis ütleks, mis klubi lisati, mis asulasse ja mis sai selle uue klubi id'ks.

CREATE PROCEDURE sp_uus_klubi(a_nimi varchar(100), a_asula varchar(100))
LANGUAGE plpgsql
AS $$
    DECLARE i_id integer; -- defineerime muutuja, kuhu paneme asula id
    arv integer:=0; -- defineerime muutuja, milleks oleks sellenimeliste asulate arv
    uusid integer; -- defineerime muutuja, kuhu paneme uue lisatud klubi id
BEGIN
    SELECT count(*) INTO arv FROM asulad WHERE nimi=a_asula; --loendame kas sellise nimega asulaid on olemas asulate tabelis
    IF arv=0 THEN -- kui pole, siis lisame
    	INSERT INTO asulad (nimi) VALUES (a_asula);
    END IF; --kui on, siis ei tehta midagi ehk siis asulat ei lisata
    SELECT asulad.id INTO i_id FROM asulad WHERE asulad.nimi=a_asula; --asulate tabelist väljastame asula id eelnevalt defineeritud muutujasse
    INSERT INTO Klubid(nimi, asula) VALUES (a_nimi, i_id); --sisestame klubi, mille nimi on parameetrina antud ja id saime asulate tabelist
    SELECT id INTO uusid FROM klubid WHERE nimi=a_nimi;
RAISE NOTICE 'Lisati klubi %, mis asub % asulas ja selle id on %.', a_nimi, a_asula, uusid;    
END;
$$;

Näiteks kutsudes välja protseduuri

CALL sp_uus_klubi('Ohtlik Oda', 'Otepää');

Saame ka teate: Lisati klubi Ohtlik Oda, mis asub Otepää asulas ja selle id on 5.

Teateid võib edastada ka käskudega RAISE INFO, RAISE WARNING jms. Sisuliselt teevad need sama, lihtsalt hõlbustavad programmeerijatel programmikoodi lugemist, et millega on tegu. Erinev on RAISE EXCEPTION ehk vaikimisi RAISE katkestab transaktsiooni.

Protseduuri sees saab luua ka kordusi LOOP ja WHILE (sama saab ka funktsiooni sees).

Kontrollimaks, kas said protseduuride loomisest aru ava soovi korral test.
AVA TEST

Vaata videot protseduuri loomisest:

Protseduuri muutmine ja kustutamine

Kasutades OR REPLACE muudame protseduuri sp_esitaht_suureks() selliseks, et ta muudaks ka perenime esitähe suureks

CREATE OR REPLACE  procedure sp_esitaht_suureks() 
LANGUAGE SQL
AS $$ UPDATE isikud SET eesnimi = initcap(eesnimi), perenimi = initcap(perenimi); $$;

Võite ka seda protseduuri kontrollida muutes mõne või mõned nimed väikse tähega algavaks ning siis kutsudes välja protseduuri.

Protseduuri kustutamine

DROP PROCEDURE  protseduuri_nimi;

Lisalugemist: Link PostgreSQL dokumentatsioonile

Funktsioonide loomine

Indeksite loomine