Changes between Version 2 and Version 3 of DatabaseProgramming


Ignore:
Timestamp:
06/14/26 18:55:25 (5 days ago)
Author:
231025
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v2 v3  
    11= Фаза 4: Функции, процедури и тригери =
    22
    3 Во оваа фаза се надоградуваме со функции и процедури што ќе помогнат дел од честите операции во базата — отворање записник, додавање прекршок, наплата на казна, додавање категорија на возачка — да се извршуваат преку еден повик, со сите потребни проверки веќе вградени во нив. Исто така искористивме и тригери за работите што треба да се случуваат автоматски, како пресметката на вкупниот износ во уплатата, без рачно ажурирање.
    4 
     3
     4
     5Во оваа фаза се надоградуваме со функции и процедури што ќе помогнат дел од честите операции во базата — отворање записник, додавање прекршок, наплата на казна, додавање категорија на возачка — да се извршуваат преку еден повик, со сите потребни проверки веќе вградени во нив. Исто така искористивме и тригери за работите што треба да се случуваат автоматски, како пресметката на вкупниот износ во уплатата и строга контрола на интегритетот, без рачно ажурирање.[[BR]]
     6[attachment:programming.sql programming.sql]
    57
    68[[BR]]
    79== 1. Тригери ==
    810
    9 === Тригер: Автоматска пресметка на износот во Uplata ===
     11=== Тригер 1: ===
    1012
    1113Секој пат кога ќе се додаде, измени или избрише ставка во `Stavka_Zapisnik`, тригерот ја пресметува вкупната сума на сите казни за тој записник и ја запишува во соодветната уплата. Така износот во `Uplata` секогаш е точен и не мора рачно да го ажурираме.
    … …  
    1315{{{#!sql
    1416CREATE OR REPLACE FUNCTION azuriraj_iznos_uplata()
    15     RETURNS TRIGGER
    16     LANGUAGE plpgsql
    17 AS $$
    18 DECLARE
    19     v_id_zapisnik int;
    20     v_nov_iznos   numeric(10,2);
    21 BEGIN
    22     IF TG_OP = 'DELETE' THEN
    23         v_id_zapisnik := OLD.id_na_zapisnik;
    24     ELSE
    25         v_id_zapisnik := NEW.id_na_zapisnik;
     17RETURNS TRIGGER
     18LANGUAGE plpgsql
     19AS $$
     20DECLARE
     21v_id_zapisnik int;
     22v_nov_iznos   numeric(10,2);
     23BEGIN
     24IF TG_OP = 'DELETE' THEN
     25v_id_zapisnik := OLD.id_na_zapisnik;
     26ELSE
     27v_id_zapisnik := NEW.id_na_zapisnik;
     28END IF;
     29
     30SELECT COALESCE(SUM(k.iznos_kazna), 0)
     31INTO v_nov_iznos
     32FROM Stavka_Zapisnik sz
     33JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
     34JOIN Kazna    k ON p.id_kazna        = k.id_kazna
     35WHERE sz.id_na_zapisnik = v_id_zapisnik;
     36
     37UPDATE Uplata
     38SET iznos = v_nov_iznos
     39WHERE id_zapisnik = v_id_zapisnik
     40  AND status = 'Neplateno';
     41
     42IF TG_OP = 'DELETE' THEN
     43    RETURN OLD;
     44ELSE
     45    RETURN NEW;
     46END IF;
     47END;
     48$$;
     49
     50CREATE OR REPLACE TRIGGER trigger_azuriraj_iznos_uplata
     51AFTER INSERT OR UPDATE OR DELETE ON Stavka_Zapisnik
     52FOR EACH ROW
     53EXECUTE FUNCTION azuriraj_iznos_uplata();
     54}}}
     55
     56=== Тригер 2: ===
     57
     58Овој тригер се извршува пред секое вметнување или измена на податоците во табелата `Raboti_vo` со цел да се зачува бизнис логиката на системот. Тој гарантира дека полицаецот кој се доделува навистина постои во базата и дека датумот на започнување со работа не е празен. Дополнително, тригерот спречува еден полициски службеник да има активен работен однос (каде `datum_do IS NULL`) во повеќе од една полициска станица истовремено.
     59
     60{{{#!sql
     61CREATE OR REPLACE FUNCTION proverka_raboti_vo_integritet()
     62RETURNS TRIGGER
     63LANGUAGE plpgsql
     64AS $$
     65BEGIN
     66IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = NEW.EMBG_P) THEN
     67RAISE EXCEPTION 'Полицаецот со ЕМБГ % не постои во системот!', NEW.EMBG_P;
     68END IF;
     69
     70IF NEW.datum_od IS NULL THEN
     71    RAISE EXCEPTION 'Почетокот на работниот однос (datum_od) мора да биде дефиниран!';
     72END IF;
     73
     74IF NEW.datum_do IS NULL THEN
     75    IF EXISTS (
     76        SELECT 1 FROM Raboti_vo
     77        WHERE EMBG_P = NEW.EMBG_P AND datum_do IS NULL AND id_stanica <> NEW.id_stanica
     78    ) THEN
     79        RAISE EXCEPTION 'Полицаецот со ЕМБГ % веќе има активен работен ангажман во друга полициска станица!', NEW.EMBG_P;
    2680    END IF;
    27 
    28     SELECT COALESCE(SUM(k.iznos_kazna), 0)
    29     INTO v_nov_iznos
    30     FROM Stavka_Zapisnik sz
    31     JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
    32     JOIN Kazna    k ON p.id_kazna        = k.id_kazna
    33     WHERE sz.id_na_zapisnik = v_id_zapisnik;
    34 
    35     UPDATE Uplata
    36     SET iznos = v_nov_iznos
    37     WHERE id_zapisnik = v_id_zapisnik
    38       AND status = 'Neplateno';
    39 
    40     IF TG_OP = 'DELETE' THEN
    41         RETURN OLD;
    42     ELSE
    43         RETURN NEW;
    44     END IF;
    45 END;
    46 $$;
    47 
    48 CREATE OR REPLACE TRIGGER trigger_azuriraj_iznos_uplata
    49     AFTER INSERT OR UPDATE OR DELETE ON Stavka_Zapisnik
    50     FOR EACH ROW
    51     EXECUTE FUNCTION azuriraj_iznos_uplata();
    52 }}}
    53 
     81END IF;
     82
     83RETURN NEW;
     84END;
     85$$;
     86
     87CREATE OR REPLACE TRIGGER trigger_proverka_raboti_vo
     88BEFORE INSERT OR UPDATE ON Raboti_vo
     89FOR EACH ROW
     90EXECUTE FUNCTION proverka_raboti_vo_integritet();
     91}}}
     92
     93=== Тригер 3: ===
     94
     95Овој тригер е задолжен за проверка на интегритетот на податоците пред да се изврши вметнување или ажурирање во табелата `Registracija`. Тој прави соодветна валидација за постоење на сопственикот (граѓанинот) и самото возило преку нивните примарни клучеви. Како клучен дел од валидацијата, тригерот користи регуларен израз (REGEX) за да осигура дека регистарската табличка го следи официјалниот македонски стандард (на пр. SK-1234-AA или BT-888-ZZ).
     96
     97{{{#!sql
     98CREATE OR REPLACE FUNCTION proverka_registracija_integritet()
     99RETURNS TRIGGER
     100LANGUAGE plpgsql
     101AS $$
     102BEGIN
     103IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = NEW.EMBG) THEN
     104RAISE EXCEPTION 'Граѓанинот со ЕМБГ % не постои во системот!', NEW.EMBG;
     105END IF;
     106
     107IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = NEW.broj_na_sasija) THEN
     108    RAISE EXCEPTION 'Возилото со број на шасија % не постои во системот!', NEW.broj_na_sasija;
     109END IF;
     110
     111IF NEW.registerska_tablica IS NOT NULL AND NOT (UPPER(NEW.registerska_tablica) ~ '^[A-Z]{2}-[0-9]{3,4}-[A-Z]{2}$') THEN
     112    RAISE EXCEPTION 'Регистарската табличка "%" не е во валиден формат! Очекуван формат: ZZ-0000-ZZ (напр. SK-1234-AA)', NEW.registerska_tablica;
     113END IF;
     114
     115RETURN NEW;
     116END;
     117$$;
     118
     119CREATE OR REPLACE TRIGGER trigger_proverka_registracija
     120BEFORE INSERT OR UPDATE ON Registracija
     121FOR EACH ROW
     122EXECUTE FUNCTION proverka_registracija_integritet();
     123}}}
    54124
    55125[[BR]]
    … …  
    57127
    58128=== Процедура 1: kreiraj_zapisnik_so_prekrsok ===
    59 Отвора нов записник со прв прекршок и веднаш креира празна уплата со статус `Neplateno`. Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.
     129Отвора нов записник со прв прекршок и веднаш креира празна уплата со статус 'Neplateno'. Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.
    60130
    61131{{{#!sql
    62132CREATE OR REPLACE PROCEDURE kreiraj_zapisnik_so_prekrsok(
    63     p_embg_prekrsuvac char(13),
    64     p_broj_sasija     varchar(17),
    65     p_embg_policaec   char(13),
    66     p_lokacija        varchar(100),
    67     p_id_slucaj       int,
    68     p_id_prekrsok     int,
    69     p_vreme           time    DEFAULT CURRENT_TIME,
    70     p_datum           date    DEFAULT CURRENT_DATE,
    71     p_potpis          boolean DEFAULT false
     133p_embg_prekrsuvac char(13),
     134p_broj_sasija     varchar(17),
     135p_embg_policaec   char(13),
     136p_lokacija        varchar(100),
     137p_id_slucaj       int,
     138p_id_prekrsok     int,
     139p_vreme           time    DEFAULT CURRENT_TIME,
     140p_datum           date    DEFAULT CURRENT_DATE,
     141p_potpis          boolean DEFAULT false
    72142)
    73     LANGUAGE plpgsql
    74 AS $$
    75 DECLARE
    76     v_id_zapisnik int;
    77 BEGIN
    78     IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = p_embg_prekrsuvac) THEN
    79         RAISE EXCEPTION 'Gragjaninot so EMBG % ne postoi!', p_embg_prekrsuvac;
    80     END IF;
    81     IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = p_embg_policaec) THEN
    82         RAISE EXCEPTION 'Policaecot so EMBG % ne postoi!', p_embg_policaec;
    83     END IF;
    84     IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = p_broj_sasija) THEN
    85         RAISE EXCEPTION 'Voziloto so broj na sasija % ne postoi!', p_broj_sasija;
    86     END IF;
    87     IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
    88         RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
    89     END IF;
    90     IF NOT EXISTS (SELECT 1 FROM Slucaj WHERE id_slucaj = p_id_slucaj) THEN
    91         RAISE EXCEPTION 'Slucajot so id % ne postoi!', p_id_slucaj;
    92     END IF;
    93 
    94     INSERT INTO Zapisnik (vreme, datum, lokacija, Potpis,
    95         id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
    96     VALUES (p_vreme, p_datum, p_lokacija, p_potpis,
    97         p_id_slucaj, p_embg_prekrsuvac, p_broj_sasija, p_embg_policaec)
    98     RETURNING id_na_zapisnik INTO v_id_zapisnik;
    99 
    100     INSERT INTO Uplata (iznos, status, Uplatil_Gragjanin, id_zapisnik)
    101     VALUES (0, 'Neplateno', p_embg_prekrsuvac, v_id_zapisnik);
    102 
    103     INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
    104     VALUES (1, v_id_zapisnik, p_id_prekrsok);
    105 
    106     RAISE NOTICE 'Kreiran zapisnik so id % so prv prekrsok %.', v_id_zapisnik, p_id_prekrsok;
     143LANGUAGE plpgsql
     144AS $$
     145DECLARE
     146v_id_zapisnik int;
     147BEGIN
     148IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = p_embg_prekrsuvac) THEN
     149RAISE EXCEPTION 'Gragjaninot so EMBG % ne postoi!', p_embg_prekrsuvac;
     150END IF;
     151IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = p_embg_policaec) THEN
     152RAISE EXCEPTION 'Policaecot so EMBG % ne postoi!', p_embg_policaec;
     153END IF;
     154IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = p_broj_sasija) THEN
     155RAISE EXCEPTION 'Voziloto so broj na sasija % ne postoi!', p_broj_sasija;
     156END IF;
     157IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
     158RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
     159END IF;
     160IF NOT EXISTS (SELECT 1 FROM Slucaj WHERE id_slucaj = p_id_slucaj) THEN
     161RAISE EXCEPTION 'Slucajot so id % ne postoi!', p_id_slucaj;
     162END IF;
     163
     164INSERT INTO Zapisnik (vreme, datum, lokacija, Potpis,
     165    id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
     166VALUES (p_vreme, p_datum, p_lokacija, p_potpis,
     167    p_id_slucaj, p_embg_prekrsuvac, p_broj_sasija, p_embg_policaec)
     168RETURNING id_na_zapisnik INTO v_id_zapisnik;
     169
     170INSERT INTO Uplata (iznos, status, Uplatil_Gragjanin, id_zapisnik)
     171VALUES (0, 'Neplateno', p_embg_prekrsuvac, v_id_zapisnik);
     172
     173INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
     174VALUES (1, v_id_zapisnik, p_id_prekrsok);
     175
     176RAISE NOTICE 'Kreiran zapisnik so id % so prv prekrsok %.', v_id_zapisnik, p_id_prekrsok;
    107177END;
    108178$$;
    … …  
    112182{{{#!sql
    113183CALL kreiraj_zapisnik_so_prekrsok(
    114     '1508004480145', 'CE8C5F1D5AA4C0871', '1609966470237',
    115     'Bulevar Partizanski Odredi', 1, 1
     184'1508004480145', 'CE8C5F1D5AA4C0871', '1609966470237',
     185'Bulevar Partizanski Odredi', 1, 1
    116186);
    117187}}}
    118 
    119188
    120189=== Процедура 2: dodadi_stavka_zapisnik ===
    … …  
    123192{{{#!sql
    124193CREATE OR REPLACE PROCEDURE dodadi_stavka_zapisnik(
    125     p_id_zapisnik int,
    126     p_id_prekrsok int
     194p_id_zapisnik int,
     195p_id_prekrsok int
    127196)
    128     LANGUAGE plpgsql
    129 AS $$
    130 DECLARE
    131     v_reden_broj     int;
    132     v_status_uplata  varchar(30);
    133 BEGIN
    134     IF NOT EXISTS (SELECT 1 FROM Zapisnik WHERE id_na_zapisnik = p_id_zapisnik) THEN
    135         RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
    136     END IF;
    137     IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
    138         RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
    139     END IF;
    140 
    141     SELECT status INTO v_status_uplata
    142     FROM Uplata WHERE id_zapisnik = p_id_zapisnik;
    143 
    144     IF v_status_uplata = 'Plateno' THEN
    145         RAISE EXCEPTION 'Zapisnik % e vekje platen, ne moze da se dodavaat novi stavki!', p_id_zapisnik;
    146     END IF;
    147 
    148     SELECT COALESCE(MAX(reden_broj), 0) + 1 INTO v_reden_broj
    149     FROM Stavka_Zapisnik WHERE id_na_zapisnik = p_id_zapisnik;
    150 
    151     INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
    152     VALUES (v_reden_broj, p_id_zapisnik, p_id_prekrsok);
    153 
    154     RAISE NOTICE 'Dodadena stavka so reden_broj % vo zapisnik %.', v_reden_broj, p_id_zapisnik;
     197LANGUAGE plpgsql
     198AS $$
     199DECLARE
     200v_reden_broj     int;
     201v_status_uplata  varchar(30);
     202BEGIN
     203IF NOT EXISTS (SELECT 1 FROM Zapisnik WHERE id_na_zapisnik = p_id_zapisnik) THEN
     204RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
     205END IF;
     206IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
     207RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
     208END IF;
     209
     210SELECT status INTO v_status_uplata
     211FROM Uplata WHERE id_zapisnik = p_id_zapisnik;
     212
     213IF v_status_uplata = 'Plateno' THEN
     214    RAISE EXCEPTION 'Zapisnik % e vekje platen, ne moze da se dodavaat novi stavki!', p_id_zapisnik;
     215END IF;
     216
     217SELECT COALESCE(MAX(reden_broj), 0) + 1 INTO v_reden_broj
     218FROM Stavka_Zapisnik WHERE id_na_zapisnik = p_id_zapisnik;
     219
     220INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
     221VALUES (v_reden_broj, p_id_zapisnik, p_id_prekrsok);
     222
     223RAISE NOTICE 'Dodadena stavka so reden_broj % vo zapisnik %.', v_reden_broj, p_id_zapisnik;
    155224END;
    156225$$;
    … …  
    162231}}}
    163232
    164 
    165233=== Процедура 3: plati_kazna ===
    166 Ја плаќа казната за даден записник — го менува статусот во `Plateno`, го запишува датумот и начинот на плаќање. Проверува дали уплатата постои, дали веќе е платена, дали има износ за наплата и дали начинот на плаќање е валиден.
     234Ја плаќа казната за даден записник — го менува статусот во 'Plateno', го запишува датумот и начинот на плаќање. Проверува дали уплатата постои, дали веќе е платена, дали има износ за наплата и дали начинот на плаќање е валиден.
    167235
    168236{{{#!sql
    169237CREATE OR REPLACE PROCEDURE plati_kazna(
    170     p_id_zapisnik    int,
    171     p_nacin_plakanje varchar(30)
     238p_id_zapisnik    int,
     239p_nacin_plakanje varchar(30)
    172240)
    173     LANGUAGE plpgsql
    174 AS $$
    175 DECLARE
    176     v_status varchar(30);
    177     v_iznos  numeric(10,2);
    178 BEGIN
    179     SELECT status, iznos INTO v_status, v_iznos
    180     FROM Uplata WHERE id_zapisnik = p_id_zapisnik;
    181 
    182     IF v_status IS NULL THEN
    183         RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
    184     END IF;
    185     IF v_status = 'Plateno' THEN
    186         RAISE EXCEPTION 'Kaznata za zapisnik % e vekje platena!', p_id_zapisnik;
    187     END IF;
    188     IF v_iznos <= 0 THEN
    189         RAISE EXCEPTION 'Zapisnikot % nema stavki/kazni za naplata!', p_id_zapisnik;
    190     END IF;
    191     IF p_nacin_plakanje NOT IN ('E-bankarstvo', 'Platezna karticka', 'Gotovo/Uplatnica') THEN
    192         RAISE EXCEPTION 'Nepoznat nacin na plakanje: %.', p_nacin_plakanje;
    193     END IF;
    194 
    195     UPDATE Uplata
    196     SET status         = 'Plateno',
    197         datum_uplata   = CURRENT_DATE,
    198         nacin_plakanje = p_nacin_plakanje
    199     WHERE id_zapisnik = p_id_zapisnik;
    200 
    201     RAISE NOTICE 'Uspesno naplatena kazna od % za zapisnik % (%).', v_iznos, p_id_zapisnik, p_nacin_plakanje;
     241LANGUAGE plpgsql
     242AS $$
     243DECLARE
     244v_status varchar(30);
     245v_iznos  numeric(10,2);
     246BEGIN
     247SELECT status, iznos INTO v_status, v_iznos
     248FROM Uplata WHERE id_zapisnik = p_id_zapisnik;
     249
     250IF v_status IS NULL THEN
     251    RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
     252END IF;
     253IF v_status = 'Plateno' THEN
     254    RAISE EXCEPTION 'Kaznata za zapisnik % e vekje platena!', p_id_zapisnik;
     255END IF;
     256IF v_iznos <= 0 THEN
     257    RAISE EXCEPTION 'Zapisnikot % nema stavki/kazni za naplata!', p_id_zapisnik;
     258END IF;
     259IF p_nacin_plakanje NOT IN ('E-bankarstvo', 'Platezna karticka', 'Gotovo/Uplatnica') THEN
     260    RAISE EXCEPTION 'Nepoznat nacin na plakanje: %.', p_nacin_plakanje;
     261END IF;
     262
     263UPDATE Uplata
     264SET status         = 'Plateno',
     265    datum_uplata   = CURRENT_DATE,
     266    nacin_plakanje = p_nacin_plakanje
     267WHERE id_zapisnik = p_id_zapisnik;
     268
     269RAISE NOTICE 'Uspesno naplatena kazna od % za zapisnik % (%).', v_iznos, p_id_zapisnik, p_nacin_plakanje;
    202270END;
    203271$$;
    … …  
    208276CALL plati_kazna(30000005, 'E-bankarstvo');
    209277}}}
    210 
    211278
    212279=== Процедура 4: dodadi_kategorija_na_vozacka ===
    … …  
    215282{{{#!sql
    216283CREATE OR REPLACE PROCEDURE dodadi_kategorija_na_vozacka(
    217     p_broj_dozvola    varchar(30),
    218     p_id_kategorija   int,
    219     p_datum_polaganje date DEFAULT CURRENT_DATE
     284p_broj_dozvola    varchar(30),
     285p_id_kategorija   int,
     286p_datum_polaganje date DEFAULT CURRENT_DATE
    220287)
    221     LANGUAGE plpgsql
    222 AS $$
    223 DECLARE
    224     v_datum_vaznost_do date;
    225     v_datum_izdavanje  date;
    226     v_kod_kategorija   varchar(3);
    227 BEGIN
    228     SELECT datum_vaznost_do, datum_izdavanje
    229     INTO v_datum_vaznost_do, v_datum_izdavanje
    230     FROM Vozacka_dozvola WHERE broj_dozvola = p_broj_dozvola;
    231 
    232     IF v_datum_vaznost_do IS NULL THEN
    233         RAISE EXCEPTION 'Vozackata dozvola so broj % ne postoi!', p_broj_dozvola;
    234     END IF;
    235     IF v_datum_vaznost_do < CURRENT_DATE THEN
    236         RAISE EXCEPTION 'Vozackata dozvola % e istechena (vaznost do %)!', p_broj_dozvola, v_datum_vaznost_do;
    237     END IF;
    238 
    239     SELECT kod INTO v_kod_kategorija
    240     FROM Kategorija WHERE id_kategorija = p_id_kategorija;
    241 
    242     IF v_kod_kategorija IS NULL THEN
    243         RAISE EXCEPTION 'Kategorijata so id % ne postoi!', p_id_kategorija;
    244     END IF;
    245 
    246     IF EXISTS (
    247         SELECT 1 FROM Kategorija_Vozacka_dozvola
    248         WHERE broj_dozvola = p_broj_dozvola AND id_kategorija = p_id_kategorija
    249     ) THEN
    250         RAISE EXCEPTION 'Kategorijata % vekje e dodadena na vozackata %!', v_kod_kategorija, p_broj_dozvola;
    251     END IF;
    252 
    253     IF p_datum_polaganje > CURRENT_DATE THEN
    254         RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide vo idnina!', p_datum_polaganje;
    255     END IF;
    256     IF p_datum_polaganje < v_datum_izdavanje THEN
    257         RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide pred izdavanjeto na vozackata (%).',
    258             p_datum_polaganje, v_datum_izdavanje;
    259     END IF;
    260 
    261     INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
    262     VALUES (p_id_kategorija, p_broj_dozvola, p_datum_polaganje);
    263 
    264     RAISE NOTICE 'Uspesno dodadena kategorija % na vozackata %.', v_kod_kategorija, p_broj_dozvola;
     288LANGUAGE plpgsql
     289AS $$
     290DECLARE
     291v_datum_vaznost_do date;
     292v_datum_izdavanje  date;
     293v_kod_kategorija   varchar(3);
     294BEGIN
     295SELECT datum_vaznost_do, datum_izdavanje
     296INTO v_datum_vaznost_do, v_datum_izdavanje
     297FROM Vozacka_dozvola WHERE broj_dozvola = p_broj_dozvola;
     298
     299IF v_datum_vaznost_do IS NULL THEN
     300    RAISE EXCEPTION 'Vozackata dozvola so broj % ne postoi!', p_broj_dozvola;
     301END IF;
     302IF v_datum_vaznost_do < CURRENT_DATE THEN
     303    RAISE EXCEPTION 'Vozackata dozvola % e istechena (vaznost do %)!', p_broj_dozvola, v_datum_vaznost_do;
     304END IF;
     305
     306SELECT kod INTO v_kod_kategorija
     307FROM Kategorija WHERE id_kategorija = p_id_kategorija;
     308
     309IF v_kod_kategorija IS NULL THEN
     310    RAISE EXCEPTION 'Kategorijata so id % ne postoi!', p_id_kategorija;
     311END IF;
     312
     313IF EXISTS (
     314    SELECT 1 FROM Kategorija_Vozacka_dozvola
     315    WHERE broj_dozvola = p_broj_dozvola AND id_kategorija = p_id_kategorija
     316) THEN
     317    RAISE EXCEPTION 'Kategorijata % vekje e dodadena na vozackata %!', v_kod_kategorija, p_broj_dozvola;
     318END IF;
     319
     320IF p_datum_polaganje > CURRENT_DATE THEN
     321    RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide vo idnina!', p_datum_polaganje;
     322END IF;
     323IF p_datum_polaganje < v_datum_izdavanje THEN
     324    RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide pred izdavanjeto na vozackata (%).',
     325        p_datum_polaganje, v_datum_izdavanje;
     326END IF;
     327
     328INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
     329VALUES (p_id_kategorija, p_broj_dozvola, p_datum_polaganje);
     330
     331RAISE NOTICE 'Uspesno dodadena kategorija % na vozackata %.', v_kod_kategorija, p_broj_dozvola;
    265332END;
    266333$$;
    … …  
    273340}}}
    274341
    275 
    276342[[BR]]
    277 == 3. Дополнителна функција: azuriraj_kazna_plakanja ==
    278 Функција што ги обработува задоцнетите неплатени казни: на неплатените постари од '''8 дена''' им го зголемува износот за 50% (само ако сè уште не се зголемени), а на тие постари од '''2 месеци''' им го менува статусот во `Sudska_postapka`. Враќа порака дека постапката е успешно извршена.
    279 
    280 Прво ја прошируваме табелата `Uplata` со новиот статус и со колона за обележување дали казната е веќе зголемена:
     343== 3. Дополнителна функција ==
     344Функција што ги обработува задоцнетите неплатени казни: на неплатените постари од 8 дена им го зголемува износот за 50% (само ако сè уште не се зголемени), а на тие постари од 2 месеци им го менува статусот во 'Sudska_postapka'. Враќа порака дека постапката е успешно извршена.
     345
     346Прво ја прошируваме табелата Uplata со новиот статус и со колона за обележување дали казната е веќе зголемена:
    281347{{{#!sql
    282348ALTER TABLE Uplata DROP CONSTRAINT IF EXISTS uplata_status_check;
    283349ALTER TABLE Uplata ADD CONSTRAINT uplata_status_check
    284     CHECK (status IN ('Plateno', 'Neplateno', 'Sudska_postapka'));
     350CHECK (status IN ('Plateno', 'Neplateno', 'Sudska_postapka'));
    285351
    286352ALTER TABLE Uplata ADD COLUMN IF NOT EXISTS kazna_zgolemena boolean DEFAULT false;
    … …  
    289355{{{#!sql
    290356CREATE OR REPLACE FUNCTION azuriraj_kazna_plakanja()
    291     RETURNS text
    292     LANGUAGE plpgsql
    293 AS $$
    294 BEGIN
    295     UPDATE Uplata u
    296     SET iznos = u.iznos + (u.iznos * 0.50),
    297         kazna_zgolemena = true
    298     FROM Zapisnik z
    299     WHERE u.id_zapisnik = z.id_na_zapisnik
    300       AND u.status = 'Neplateno'
    301       AND u.kazna_zgolemena = false
    302       AND z.datum < CURRENT_DATE - INTERVAL '8 days';
    303 
    304     UPDATE Uplata u
    305     SET status = 'Sudska_postapka'
    306     FROM Zapisnik z
    307     WHERE u.id_zapisnik = z.id_na_zapisnik
    308       AND u.status = 'Neplateno'
    309       AND z.datum < CURRENT_DATE - INTERVAL '2 months';
    310 
    311     RETURN 'Постапката за автоматско ажурирање на неплатените казни е успешно извршена.';
     357RETURNS text
     358LANGUAGE plpgsql
     359AS $$
     360BEGIN
     361UPDATE Uplata u
     362SET iznos = u.iznos + (u.iznos * 0.50),
     363kazna_zgolemena = true
     364FROM Zapisnik z
     365WHERE u.id_zapisnik = z.id_na_zapisnik
     366AND u.status = 'Neplateno'
     367AND u.kazna_zgolemena = false
     368AND z.datum < CURRENT_DATE - INTERVAL '8 days';
     369
     370UPDATE Uplata u
     371SET status = 'Sudska_postapka'
     372FROM Zapisnik z
     373WHERE u.id_zapisnik = z.id_na_zapisnik
     374  AND u.status = 'Neplateno'
     375  AND z.datum < CURRENT_DATE - INTERVAL '2 months';
     376
     377RETURN 'Постапката за автоматско ажурирање на неплатените казни е успешно извршена.';
    312378END;
    313379$$;
    … …  
    320386}}}
    321387
    322 Оваа функција е наменета да се извршува автоматски како '''закажана (scheduled)''' задача што периодично ги обработува задоцнетите неплатени казни — ги зголемува застарените и најстарите ги префрла во судска постапка без рачна интервенција.
     388Оваа функција е наменета да се извршува автоматски како закажана задача што периодично ги обработува задоцнетите неплатени казни — ги зголемува застарените и најстарите ги префрла во судска постапка без рачна интервенција.