Blog programistkiMiejsce na moje programistyczne notatki

Triggery zwane inaczej wyzwalaczami, ponieważ ich wywołanie zwykle uzależnione jest od jakiegoś zdarzenia. Zdarzeniem tym zwykle jest jakaś operacja na danych – INSERT, DELETE bądź UPDATE. Wyzwalacz może być wywoływany albo przed (BEFORE) albo po (AFTER) zdarzeniu (włożeniu do bazy rekordu, usunięciu go lub zmienieniu)

Przykładowo utworzony trigger:

CREATE TRIGGER nazwa_triggera
BEFORE UPDATE ON nazwa_tabeli
FOR EACH ROW SET NEW.nazwa_pola="jakis tekst";

Trigger ten jest może średnio przydatny ale dobrze ilustruje możliwości. Co powoduje – otóż przed każdym insertem do tabeli „nazwa_tabeli” w pole nazwa_pola wpisywany jest „jakis_tekst”. Nawet jeśli polecenie INSERT będzie wyglądać mniej więcej tak:

CREATE TRIGGER nazwa_trigger
AFTER DELETE ON nazwa_tabeli
FOR EACH ROW SET @oidy=CONCAT(@oidy,','OLD.id);

To trigger spowoduje, że zamiast tekstu „tralalalala” zostanie w bazie umieszcziony tekst „jakis tekst”. Przyda się to jeśli już po stronie serwera baz danych chcemy w jakiś sposób weryfikować dane zapisywane do bazy. Oczywiście zamiast możemy też zrobić trigger reagujący na UPDATE danych:

CREATE TRIGGER nazwa_triggera
BEFORE UPDATE ON nazwa_tabeli
FOR EACH ROW SET NEW.nazwa_pola="jakis tekst";

Wówczas jeśli wywołamy następujący UPDATE:

UPDATE nazwa_tabeli SET id=id+12 WHERE id>12;

Wszystkie wiersze przetworzone przez to zapytanie, czyli te gdzie w polu id jest liczba większa od 12 w polu nazwa_pola będą miały tekst postaci „jakis tekst”.

Można by też zrobić trigger, który zwróci nam id wszystkich usuniętych z bazy elementów:

CREATE TRIGGER nazwa_triggera
AFTER DELETE ON nazwa_tabeli
FOR EACH ROW SET @oidy=CONCAT(@oidy,','OLD.id);

Następnie usuwamy to co potrzebujemy uprzednio ustawiając zmienną @oidy na pustą:

SET @oidy='';
DELETE FROM nazwa_tabeli WHERE id>100;

Żeby dowiedzieć się jakie id zostały usunięte:

SELECT @oidy

Wówczas otrzymamy ciąg w postaci np.:125,240,2134

Kolejna rzecz, której nie było wcześniej to kursory. Świetnie nadają się one do pobierania danych z bazy i przemieszczanie się po nich.

Aby zacząć używać kursora trzeba go najpierw zadeklarować:

DECLARE nazwa_kursora CURSOR FOR SELECT pole FROM tabela;

Następnie należy go otworzyć:

OPEN nazwa_kursora

Aby pobrać kolejne wiersze uzyskane przez zapytanie zdefiniowane w kursorze używamy:

FETCH nazwa_kursora INTO a;

W tym przypadku a jest wcześniej zadeklarowaną zmienną. Powyższe polecenie zwykle występuje wewnątrz instrukcji REPEAT która umożliwia przejście po wszystkich wierszach pobranych przez kursor.

Gdy kursor przestanie być potrzebny zamykamy go:

CLOSE nazwa_kursora

Przykład użycia w procedurze:

CREATE PROCEDURE proccur()
 
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE a INT;
    DECLARE kursor CURSOR FOR SELECT id FROM dane;
    DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000′ SET done = 1;
 
    OPEN kursor;
    REPEAT FETCH kursor INTO a;
        IF a > 2 THEN
            UPDATE dane SET nazwa=”a” WHERE id=a;
        END IF;
    UNTIL done END REPEAT;
    CLOSE kursor;
END;

Zmienna done potrzebna jest aby wiedzieć kiedy kursor zakończy pobieranie danych z bazy. Instrukcja REPEAT przechodzi przez wszystkie wiersze tablicy dane i jeśli id danego rekordu jest większe od 2 zmienia wartość pola nazwa na „a”. Kiedy wszystkie wiersze zostaną przeglądnięte REPEAT zostaje zakończone, zamykamy kursor.

Utrwalam sobie różnice między wersjami;)

Procedury składowane:

W wersji bez parametrów przekazywanych do procedury:

CREATE procedure select_all()
SELECT * FROM tabela;

Wywołanie procedury select_all:

CALL select_all()

W wersji z parametrami:

CREATE procedure select_one(IN szukane_id INT(11))
    SELECT * FROM tabela WHERE id=szukane_id;

Wywołanie procedury:

CALL select_one(3)

Usunięcie procedury:

DROP procedure select_one

W procedurach można korzystać z instrukcji warunkowej IF:

CREATE procedure ifik(IN zmienna INT)
 
BEGIN
    IF zmienna > 5
        SELECT * FROM tabela LIMIT zmienna;
    ELSE
        SELECT * FROM tabela LIMIT 5;
    END IF;
END;

Można zrobić coś w rodzaju instrukcji switch, tutaj nazywanej instrukcją CASE:

CREATE procedure casik(IN zmienna INT)
 
CASE zmienna
    WHEN 0 THEN SELECT * FROM tabela
    WHEN 1 THEN SELECT COUNT(*) FROM tabela
    ELSE SELECT FROM tabela LIMIT 5
END CASE;

Jak widać odpowiednikiem case’ów są konstrukcje WHEN, a zamiast default mamy ELSE.

Jest również pętla WHILE:

CREATE procedure whilik()
 
BEGIN
    DECLARE indeks INT;
    SET indeks = 1;
    WHILE indeks < 10 DO
        SELECT * FROM tabela WHERE id = indeks;
        SET indeks = indeks + 1;
    END WHILE;
END;

Niejako odwrotnością WHILE jest REPEAT:

CREATE procedure repeacik()
 
BEGIN
    DECLARE indeks INT DEFAULT 1;
    REPEAT
        SET indeks = indeks + 2;
        UNTIL j > 10
    END REPEAT
END;

Coś jak continue:

CREATE procedure iteracik()
 
BEGIN
    DECLARE indeks INT DEFAULT 1;
    etykieta: REPEAT
        SET indeks = indeks + 5;
        IF indeks = 20 THEN ITERATE etykieta; END IF;
        UNTIL indeks = 100
    END REPEAT etykieta;
END;

Procedury mogą prawie wszystko – jednak nie mogą zmieniać innych procedur, funkcji czy triggerów.