Niektóre narzędzia dostarczane przez silnik bazy danych MySQL to: przechowywane procedury, funkcje i wyzwalacz, które służą do wykonywania transakcji lub operacji, takich jak wstawianie lub modyfikowanie rekordów.
Procedury składowane to małe programy opracowane w kodzie SQL. Procedura składowana to zestaw poleceń SQL przechowywanych razem z bazą danych.
Zaletą procedury składowanej jest to, że możemy ją utworzyć w dowolnym edytorze tekstu i nawet na serwerze, jest wykonywana przez silnik bazy danych i nie jest dostępna dla użytkowników, a jedynie dla administratora.
Procedura składowana wysyła swoje wyniki do aplikacji, aby wyświetlała je na ekranie, unikając przeciążenia serwera, w samouczku:
- Procedury składowane MYSQL - tworzenie, zapytania i wstawianie danych
Wyjaśniłem, jak je tworzyć, tutaj dodamy funkcje i wyzwalacze. Zobaczymy przykład na bazie danych nieruchomości, którą nazwiemy miejscem najmu, a następnie utworzymy tabele.
- Struktura tabeli dla tabeli `nieruchomości` CREATE TABLE IF NOT EXISTS` nieruchomości` (`id` int (11) NOT NULL,` userid` int (11) DEFAULT NULL, `idtype property` int (6) DEFAULT '0 ', `cena` dziesiętna (10,2) DOMYŚLNA' 0,00 ',` prowizja` dziesiętna (10,0) NIE NULL, `tekst opisu,` highdate` data DOMYŚLNA' 0000-00-00 ', `identyfikator prowincji` int (10) DEFAULT NULL, `idlocalidad` int (10) DEFAULT NULL,` adres` varchar (150) DEFAULT NULL, `piętro i mieszkanie` varchar (100) DEFAULT NULL,` between_streets` text, `idoperation` int (100) ) DEFAULT NULL , `featured` char (3) DEFAULT 'no',` image1` varchar (255) DEFAULT NULL, `image2` varchar (255) DEFAULT NULL,` image3` varchar (255) DEFAULT NULL, `image4` varchar (255) DEFAULT NULL, `stary` varchar (100) DEFAULT NULL,` mt2covered` int (11) DEFAULT NULL, `lot_surface` int (11) DEFAULT NULL,` aktywowany` enum ('tak',' nie ') NIE NULL DEFAULT' si ') ENGINE = MyISAM AUTO_INCREMENT = 196 DOMYŚLNY ZESTAW ZNAKÓW = latin1; - Indeksy tabeli `nieruchomości` ALTER TABLE` nieruchomości` ADD PRIMARY KEY (`id`);
Teraz opracujemy procedurę składowaną dla każdej transakcji, aby odpytywać, wstawiać, modyfikować i usuwać rekordy.
Możemy użyć Phpmyadmin lub menedżera takiego jak Heidisql, który jest darmowy i działa na Windows lub Linux z Wine.
Tworzymy procedurę składowaną do zapytania tabeli nieruchomości:
DELIMITER // CREATE PROCEDURE pa_listainmuebles () BEGIN SELECT * FROM właściwości; KONIEC // DELIMITER;MYSQL rozumie, że instrukcja kończy się średnikiem. ten Oświadczenie DELIMITER zmień końcowy znak na dowolny inny znak, zgodnie z konwencją // służy do wskazania końca procedury składowanej, aby MySQL nie zakończył procedury składowanej po napotkaniu pierwszego średnika.
Możemy iść do Zakładka rutyny aby zobaczyć każdą utworzoną przez nas transakcję i stamtąd możemy modyfikować, wykonywać, eksportować lub usuwać kod.
Aby wykonać procedurę poprzez przechowywanie, używamy ZADZWOŃ, polecenie od Karta SQL lub też z języka programowania, takiego jak .NET lub Java. Następnie wywołujemy procedurę składowaną utworzoną za pomocą polecenia.
ZADZWOŃ pa_listainmuebles ();
Następnie stworzymy procedurę składowaną do wstawienia właściwości, do tego będziemy potrzebować parametrów typu IN, czyli przypiszemy dane i zmienne wejściowe do procedury składowanej, aby dokonać transakcji, w tym przypadku zapiszemy je w bazie danych.
DELIMITER // TWORZENIE PROCEDURY pa_nuevoinmueble (IN id INT, IN userid INT, IN cena DECIMAL, IN prowizja DECIMAL) BEGIN INSERT INTO property` (`id`,` userid`, `price`,` prowizja`) VALUES (id, userid) ) , cena, prowizja) END // DELIMITER;
POWIĘKSZAĆ
Następnie możemy wykonać procedurę składowaną, wywołując i przypisując parametry.
ZADZWOŃ `pa_newinmueble` ('12','15','10.00','0,05')Możemy również wprowadzić dane, uruchamiając procedurę z Phpmyadmin.
POWIĘKSZAĆ
Następnie utworzymy procedurę składowaną do edycji właściwości z edytora Phpmyadmin, w tym przypadku zmodyfikujemy tylko cenę.
Możemy tworzyć role z pola Definer, gdzie możemy przypisać użytkownika zdefiniowanego na serwerze Mysql, w tym przypadku użytkownika root hosta localhost, tak aby miał dostęp do procedury składowanej.
Jeśli chcemy to zrobić z kodu SQL, musimy wykonać następujące polecenia:
CREATE DEFINER = `root` @` localhost` PROCEDURA `pa_editarinmueble` (IN` nowa właściwość` DECIMAL (10,2), IN `id właściwości` INT (11)) BEGIN UPDATE właściwość SET cena = nowa właściwość WHERE id = identyfikator właściwości ; KONIECUruchom go i gotowe.
Korzystanie z wyzwalacza lub wyzwalaczy w Mysql
Wyzwalacz lub wyzwalacz w MySQL to zestaw instrukcji SQL, które zależą od procedury składowanej i są używane do automatycznego uruchamiania, gdy w naszej bazie danych wystąpi określone zdarzenie. Zdarzenia te są wywoływane przez transakcje lub instrukcje, takie jak INSERT, UPDATE i DELETE.
Przykładem jest zapisanie zmiany w rejestrze, automatycznie wykonujemy kopię zapasową lub rejestrujemy plik audytu, aby wiedzieć, jakie dane zostały zmienione, kiedy i kto je zmienił. Mogą być używane do dowolnej manipulacji, która wpływa na dane, do obsługi lub generowania nowych informacji.
Poniżej stworzymy tabelę audytu nieruchomości:
CREATE TABLE `audit` (` użytkownik` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` data` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDBStworzymy wyzwalacz, który zapisze komunikat w audycie, jeśli ktoś zmieni cenę nieruchomości.
CREATE DEFINER = `root` @` localhost` TRIGGER `real estate_after_update` AFTER UPDATE ON` nieruchomości` DLA KAŻDEGO RZĘDU INSERT INTO (użytkownik, opis, data) VALUES (użytkownik (), CONCAT ('Zmieniona cena nieruchomości', NOWY.id, '(', STARA.cena, ') przez (', NOWA.cena, ')'), TERAZ ())Ten wyzwalacz jest wykonywany automatycznie po wystąpieniu aktualizacji ceny, możemy dołączyć więcej pól jeśli chcemy, przy STARY określamy pole z wartością przed modyfikacją, przy NOWYM określamy wprowadzoną nową wartość, przy TERAZ() określamy datę i aktualny czas.
Tworzymy wyzwalacz, który będzie miał After Update na właściwościach jako zdarzenie, czyli po wystąpieniu aktualizacji w tabeli właściwości, w takim przypadku dodamy użytkownika, który dokonał modyfikacji, nową cenę i poprzednią cenę.
Wykonuję aktualizację na nieruchomości:
ZADZWOŃ `pa_editarinmueble` ('80000', '170')Następnie przechodzimy do tabeli audytu i widzimy zmianę:
Możemy również zobaczyć wyniki w raporcie w widoku wydruku z Phpmyadmin. Widzimy, w jaki sposób zostały zapisane dane identyfikujące właściwość, dokonaną zmianę i użytkownika, który ją wprowadził, mamy też datę i godzinę dokonania zmiany.
Następnie zobaczymy kolejną możliwą aplikację, jeśli nieruchomość jest wynajmowana, to automatycznie zmieni jej status na nieaktywną lub sprawimy, że stanie się ona niedostępna.
W tym celu musimy mieć prostą tabelę, gdzie przechowywać, która nieruchomość jest wynajmowana, dla praktycznego przykładu nie będziemy zbyt rygorystyczni w danych.
CREATE TABLE `rentals` (`id` INT (10) NOT NULL, `property id` INT (10) NOT NULL,` tenant id` INT (11) NOT NULL, PRIMARY KEY (`id`)) COLLATE = 'latin1_swedish_ci ' SILNIK = InnoDB;Następnie utworzymy procedurę składowaną, aby wstawić nowy rekord w tabeli czynszów.
CREATE DEFINER = `root` @` localhost` PROCEDURA `pa_newrental` (IN` identyfikator właściwości` INT, IN `identyfikator najemcy` INT) JĘZYK SQL NOT DETERMINISTIC ZAWIERA SQL SQL SECURITY DEFINER KOMENTARZ „INSERT INTO” (identyfikator właściwości `,` identyfikator najemcy`) WARTOŚCI (identyfikator najemcy, identyfikator najemcy)
A następnie aktywowany wyzwalacz do modyfikacji właściwości:
CREATE DEFINER = `root` @` localhost` TRIGGER `rentals_after_insert` AFTER INSERT ON` rentals` FOR KAŻDY WIERSZ UPDATE ZESTAW nieruchomości active = 'no' gdzie id = NEW.Następnie wywołujemy procedurę składowaną, w której przypisujemy identyfikator nieruchomości oraz identyfikator klienta lub najemcy, którego wynajmuję.
ZAPROSZENIE na nowy wynajem (170.11)Następnie przechodzimy do tabeli nieruchomości i powinniśmy zobaczyć, że aktywowane pole zmienia stan JEŻELI jest aktywny na NIE jest aktywny.
Widzieliśmy zalety używania wyzwalacza z procedurami składowanymi w MySQL dla:
- Kontroluj i rejestruj zdarzenia lub czynności związane ze zmianą danych w tabeli.
- Zmień stan pola, aktywując lub odmawiając uprawnień i działań na tabeli
- Pozwala również na zachowanie spójności danych poprzez wykonywanie akcji zgodnie ze zdarzeniami, które mają wpływ na jedną lub więcej tabel.
