Andmebaasid 2021/22 kevad

Funktsioonid ja protseduurid

Funktsioonid

Nagu rääkisime loengus, siis lisaks standardfunktsioonidele (juba olemasolevatele funktsioonidele) saab kasutaja ka ise funktsioone luua.

Funktsiooni loomine:

CREATE FUNCTION <funktsiooni_nimi> ([{parameetrid}])
RETURNS <andmetüüp>
    BEGIN
        ....
        RETURN [väärtus]
    END;

Loome esmalt lihtsa funktsiooni, mis väljastaks meile ühe veeruna komaga eraldatult perenime ja eesnime ning selle näite põhjal vaatame funktsiooni loomise sisse.

CREATE FUNCTION f_nimi (a_e varchar(100), a_p varchar(100))
RETURNS varchar(202)
    BEGIN
        DECLARE nimi varchar(202);
        SET nimi=a_p||', '|| a_e;
        RETURN nimi;
    END;

Siin esimesel real andsime loodavale funktsioonile nime (lepime kokku, et meie aines on funktsiooni nime ees f_, kuid see on erinevates töörühmades ja organisatsioonides erinev) ning määrame parameetrid koos andmetüübiga.

Parameetrid said funktsioonil olla ainult sisendparameetrid, mis siis antud näite puhul tähendab, et me peame ette andma kaks varchar(100) andmetüübiga parameetrit.

RETURNS real anname teada, millise andmetüübi funktsioon peab väljastama. NB! Veenduge, et andmetüüp saab korrektselt määratud! Näiteks antud näites ei piisaks sellest, et paneme varchar(200), sest lisaks ees- ja perenimele on vaja ruumi ka koma ja tühiku jaoks. Küll oleks võinud siin olla näiteks ka varchar(220).

BEGIN ja END vahele kirjutame, mida funktsioon peaks tegema.

Esmalt defineerime muutuja nimi, mis on varchar(202) andmetüübiga. Muutujate nimed (aga ka funktsiooni nimed) võiks olla tähenduslikud. Arvatavasti märkasite, et muutuja andmetüüp on sama, mis väljastatav andmetüüp. Alati ei pruugi see nii olla, sest mõnikord on meil vaja funktsiooni sees defineerida ka mitu muutujat, millest vaid üks on väljastatav. Sellele muutujale omistame (SET käsuga) väärtuse, kus kasutame funktsiooni defineerimisel toodud parameetreid. RETURN real kirjutame muutuja, mille väärtuse väljastame.

Loodud funktsiooni saame kasutada päringulauses:

SELECT f_nimi(eesnimi, perenimi) FROM isikud;

või paneme selle hoopis päringulause tingimusse:

SELECT eesnimi, perenimi FROM isikud
WHERE LENGTH(f_nimi(eesnimi, perenimi)) > 15;

Viimasel juhul saame sellise vastuse:

Päringute juures rääkisime, et kindlasti peab päringulauses olema kaks osa: SELECT ja FROM. Proovige aga päringut

SELECT f_nimi('Mikk','Saar');

Töötab. Samamoodi töötab selline

SELECT f_nimi('Anett','Kontaveit') FROM sys.dummy;

sys.dummy on üks süsteemitabelitest, milles on üks veerg ja üks rida.

SELECT * FROM sys.dummy; 

annab tulemuseks

Katsetage ka

SELECT USER, today() FROM sys.dummy;

SQL Anywheres töötab ka SELECT USER, today() ilma FROM osata, kuid mõnedes andmebaasi juhtimissüsteemides (näiteks Oracle, DB2) ei tööta. Selliste juhtimissüsteemide korral ongi vaja kasutada süsteemitabelit sys.dummy sellistes päringutes, kus ei ole FROM osa ehk siis kus me midagi konkreetsest tabelist ei vaja.

Kindlasti tuleb silmas pidada ka seda, et selle loodud funktsiooni f_nimi kasutamisel ei kontrollita, et me kindlasti liidaks ees- ja perenime. Näiteks töötab ka selline päring:

SELECT f_nimi(nimi, perenimi) 
FROM isikud KEY JOIN klubid;

Tulemus on selline

Mõelge, kas võiks töötada ka selline päring:

SELECT f_nimi(valge_tulemus, musta_tulemus) 
FROM partiid; 

Kontrollige oma oletust. Miks nii? Kui võimalik, siis teostatakse ilmutamata tüübiteisendus. Nii arve kui ka ajalisi andmetüüpe on võimalik teisendada sõnaliseks ning seepärast see funktsioon töötab ka sellistel juhtudel. Siiski tulemuseks on alati varchar andmetüüp. Samas alati pole tüübiteisendus võimalik. Näiteks kui funktsiooni parameetriks on integer andmetüüp, siis eesnime ei saa selle funktsiooni parameetriks panna. Küll saab aga näiteks isikukoodi, sest seda saab integer andmetüübiks teisendada.

Funktsiooni sees saame kasutada ka päringut. Loome funktsiooni, mis vastavalt id väärtusele tagastab mängija eesnime ja perenime.

CREATE FUNCTION f_nimed (a_id integer)
RETURNS varchar(103)
    BEGIN
        DECLARE d_enimi varchar(50);
        DECLARE d_pnimi varchar(50);
        SELECT eesnimi, perenimi INTO d_enimi, d_pnimi FROM isikud
	    WHERE id=a_id;
        RETURN d_enimi ||', '|| d_pnimi;
    END;

Selles funktsioonis defineerisime kaks muutujat (d_enimi ja d_pnimi), millele andsime päringuga väärtused. Siin funktsiooni loomises INTO näitab, millisele muutujale antud päringu väärtus omistada. Näites tegime seda ühe päringuga, kuid oleks võinud ka kahte päringut kasutada ja kumbagi muutujasse eraldi päringuga väärtus omistada:

CREATE FUNCTION f_nimed (a_id integer)
RETURNS varchar(103)
    BEGIN
        DECLARE d_enimi varchar(50);
        DECLARE d_pnimi varchar(50);
        SELECT eesnimi INTO d_enimi FROM isikud
	    WHERE id=a_id;
        SELECT perenimi INTO d_pnimi FROM isikud
	    WHERE id=a_id;
        RETURN d_enimi ||', '|| d_pnimi;
    END;

Funktsioonis võib küll kasutada päringut, mis väljastavad mitut rida, kuid silmas tuleb pidada, et INTO omistamisega päringutes peab olema üherealine päringu vastus.

Päring

SELECT f_nimed(83); 

annab tulemuseks

Funktsioone saab ka muuta

CREATE OR REPLACE FUNCTION <funktsiooni_nimi>....

Samuti saab funktsiooni kustutada.

DROP FUNCTION <funktsiooni_nimi>

Protseduurid

Nagu loengus rääkisime, siis kuigi nii funktsioonid kui ka protseduurid on alamprogrammid, siis SQLis me eristame neid. Protseduuri loomine:

CREATE PROCEDURE <protseduuri_nimi> ([{parameetrid}])
[RESULT ({veergude nimekiri})]
    BEGIN
        ....
    END;

Kus parameetrid defineeritakse järnevalt:

• sisendparameetrid IN nimi andmetüüp
• väljundparameetrid OUT nimi andmetüüp
• parameeter, mis võib olla nii sisend- kui väljundparameeter INOUT nimi andmetüüp

Tulemust väljastava protseduuri korral on tulemuseks tabel, mitte üks väärtus. Pange tähele, et siin RESULT osa on sulgudes, mis tähendab, et see võib ka puududa. Seega erinevalt funktsioonist võib protseduur olla ka selline, mis midagi ei tagasta.

Nagu rääkisime loengus, siis kuna funktsioonid saavad vaid sisaldada SELECT lauset, kuid ei saa sisaldada DML ja DDL käske, siis millegi loomiseks, lisamiseks, muutmiseks, kustutamiseks jms kasutatakse protseduure. Loome meie ka protseduuri, mis lisab tabelisse Asulad uue asula, väljastaks sellele id ja vastava teate

CREATE PROCEDURE sp_uus_asula(IN a_nimi varchar(100), OUT a_id integer)
    BEGIN
        DECLARE i_id integer;
        INSERT INTO asulad (nimi) VALUES (a_nimi);
        SELECT @@identity INTO i_id;
        -- teade kliendile
        MESSAGE 'Uus asula: ' || i_id TO CLIENT;
        SET a_id = i_id;
    END;

Vaatame protseduuri loomist selle näite põhjal. Luues anname protseduurile nime. Antud näiteks sp_uus_asula. Lepime kokku, et meie selles aines tähistame protseduuride nimesid eesliitega sp_. Nagu ikka, see ei ole üldlevinud, igal organisatsioonil on omad kokkulepped. Määrame ära sisendparameetri (parameetri, mille anname ette protseduurile, kui selle välja kutsume), milleks on nimi. Kuna soovisime, et väljastaks ka id, siis ka väljundparameetri, mis on siis integer tüüpi, sest kõik id’d meil on täisarvulised. Siin me ei taha, et ptotseduur annaks mingi tabeli, seega RESULT osa me ei kasuta.

BEGIN ja END vahele kirjutame, mida protseduur peaks tegema. Kõigepealt defineerime täisarvulise muutuja i_id. See on täisarvuline, sest defineerisime OUT parameetri täisarvulisena. Nagu aga ka funktsiooni korral rääkisime, siis võib olla vajalik defineerida mitmeid muutujaid ja sellisel juhul ei pea olema kõik sama andmetüübiga, mis on OUT parameeter.

Kirje lisamise käsuga (INSERT INTO … VALUES) lisame kirje, milles uueks asula nimeks lisame nime, mis oli meil protseduuri sisendparameetriks. Kui me selle käsuga (INSERT INTO asulad (nimi) VALUES (a_nimi)) lisasime uue asula, siis süsteem tekitab lisatavale asulale automaatselt uue id (meil oli id autoincremediga).

@@identity on süsteemifunktsioon, mis genereerib viimasena lisatud id väärtuse ehk siis saamegi teada, mis sai selle lisatud asula id’ks. Omistame selle loodud muutujale i_id. Soovisime ka teadet kliendile (käsk MESSAGE 'Sõnumi sisu' TO CLIENT), kus sõnumi sisu väljastame kasutades sõne ja protseduuris väärtustatud muutuja liitmist ('Uus asula' || i_id).

Sõnum kliendile saadetakse kliendi rakendusse. Seega kui me kasutame seda protseduuri kuskil rakenduses, saab klient selle kaudu meie sisestatud sõnumi. Sõnumit võib saata ka andmebaasi serverisse (käsk MESSAGE 'Sõnumi sisu' TO CONSOLE), kus sõnum saadetakse andmebaasi serveri sõnumiaknasse ja logifaili, kui logifail on defineeritud.

Protseduuri loomise lõpus omistame defineeritud väljundparameetrile a_id loodud asula id ehk siis väärtustustatud muutuja i_id.

Protseduuri väljakutsumine käib käskudega CALL või EXECUTE. Kui meil on väljundiga protseduur, siis kõigepealt loome muutuja, kuhu väljundparameetri väärtus salvestatakse. Selles näites, kuhu salvestatakse uue asula id ehk siis see peaks olema täisarvulist tüüpi. Seejärel pärast protseduuri väljakutsumist saame SELECT abil vaadata, mis on uue asula id. Lisame näiteks uueks asulaks Haapsalu ja vaatame, mis on selle id.

CREATE VARIABLE uusid integer;
CALL sp_uus_asula('Haapsalu', uusid);
SELECT uusid;

Aga Haapsalu lisaks ka selline käsk (kui olete eelmise näite läbi teinud, siis Haapsalu enam lisada ei saa, sest asulate tabelis on nimi meil unikaalne):

CALL sp_uus_asula('Haapsalu');

Interactive SQL aknas History lehelt leiate ka teate, mida soovisime, et väljastataks:

Sama protseduuri saanuks koostada ka väljundita protseduurina:

CREATE PROCEDURE sp_uus_asula(IN a_nimi varchar(100))
    BEGIN
        DECLARE i_id integer;
        INSERT INTO asulad (nimi) VALUES (a_nimi);
        SELECT @@identity INTO i_id;
        -- teade kliendile
        MESSAGE 'Uus asula: ' || i_id TO CLIENT;
    END;

Nagu näete, siis erinevus on vaid kahes kohas. Me ei defineeri väljundparameetrit ja ei omista sellele muutuja väärtust. Kui tahaksime väljundita protseduuri korral saada teada, mis on asula id, peaksime seda päringulauses küsima. Näiteks:

SELECT id FROM asulad WHERE nimi='Haapsalu';

Sama päringulauset saaksime me muidugi kasutada ka väljundiga protseduuri korral.

Loome protseduuri, mis väljastab klubi nime alusel klubimängijate tabeli tähestikulises järjekorras. Kasutame nimede väljastamiseks eelpoolloodud funktsiooni f_nimi, mis liidab ees- ja perenime.

CREATE PROCEDURE sp_klubimangijad (IN a_klubi_nimi varchar(100))
RESULT (
    nimi varchar(202),
    kuupaev date)
    BEGIN
        SELECT f_nimi(eesnimi, perenimi), CURRENT DATE
        FROM isikud KEY JOIN klubid
        WHERE nimi = a_klubi_nimi 
        ORDER BY 1;
    END;

Vaatame erinevaid võimalusi selle protseduuri väljakutsumiseks.

CALL sp_klubimangijad('Ruudu Liine')
CALL sp_klubimangijad(a_klubi_nimi='Ruudu Liine')
EXECUTE sp_klubimangijad('Ruudu Liine')

Kõikidel juhtudel on tulemuseks klubiliikmete tähestikuline nimekiri

Kuna selle protseduuri korral on tulemuseks tabel, siis seda saame kasutada ka tabelina päringus. Ehk siis sama tabeli, mis ülal saaksime ka selliselt:

SELECT * FROM sp_klubimangijad('Ruudu Liine');

Analoogselt varasemalt räägituga protseduuride muutmine

CREATE OR REPLACE PROCEDURE <protseduuri_nimi>....

Ja protseduuri kustutamine

DROP PROCEDURE <protseduuri_nimi>