Proceduri și declanșatoare stocate în MySQL

Unele instrumente furnizate de motorul de baze de date MySQL sunt proceduri stocate, funcții și declanșator, care sunt utilizate pentru a efectua tranzacții sau operațiuni precum inserarea sau modificarea înregistrărilor.

Procedurile stocate sunt programe mici dezvoltate în cod SQL. O procedură stocată este un set de comenzi SQL care sunt stocate împreună cu baza de date.

Avantajul unei proceduri stocate este că îl putem crea în orice editor de text și chiar pe server, este executat de motorul bazei de date și nu este accesibil utilizatorilor, ci doar administratorului.

O procedură stocată își trimite rezultatele către o aplicație, astfel încât să le afișeze pe ecran evitând supraîncărcarea serverului, în tutorial:

• Proceduri stocate MYSQL - Creare, interogări și inserții de date

V-am explicat cum să le creați, aici vom adăuga funcții și declanșatoare. Vom vedea un exemplu pe o bază de date a unui imobil pe care îl vom numi loc de închiriere și apoi vom crea tabelele.

 - Structura tabelului pentru tabelul „imobiliare” CREAȚI TABEL DACĂ NU EXISTĂ „imobiliare” („id” int (11) NOT NULL, „userid” int (11) DEFAULT NULL, „idtype property” int (6) DEFAULT '0 "," preț "zecimal (10,2) DEFAULT" 0,00 "," comision "zecimal (10,0) NU NULL, text" descriere "," highdate "data DEFAULT" 0000-00-00 "," provincia id " int (10) DEFAULT NULL, `idlocalidad` int (10) DEFAULT NULL,` address` varchar (150) DEFAULT NULL, `etaj și apartament` varchar (100) DEFAULT NULL, text` between_streets`, „idoperation` int (100 ) DEFAULT NULL, `featured` char (3) DEFAULT 'nu',` image1` varchar (255) DEFAULT NULL, `image2` varchar (255) DEFAULT NULL,` image3` varchar (255) DEFAULT NULL, `image4` varchar (255) DEFAULT NULL, `old` varchar (100) DEFAULT NULL,` mt2covered` int (11) DEFAULT NULL, `lot_surface` int (11) DEFAULT NULL,„ activate` enum („da”, „nu”) NU NULL DEFAULT 'si') MOTOR = MyISAM AUTO_INCREMENT = 196 DEFAULT CHARSET = latin1; - Indici ai tabelului „imobiliar` ALTER TABLE` imobiliar` ADAUGĂ CHEIE PRIMARĂ („ id ”);

Acum vom dezvolta o procedură stocată pentru fiecare tranzacție pentru a interoga, insera, modifica și șterge o înregistrare.

Putem folosi Phpmyadmin sau un manager precum Heidisql care este gratuit și funcționează pe Windows sau Linux cu Wine.

Creăm o procedură stocată pentru a interoga tabelul imobiliar:

 DELIMITER // CREATE PROCEDURE pa_listainmuebles () BEGIN SELECT * FROM properties; END // DELIMITER;

MYSQL înțelege că o declarație se termină cu punct și virgulă. Declarație DELIMITER schimbați caracterul final la orice alt caracter, prin convenție // este folosit pentru a indica sfârșitul procedurii stocate, astfel încât MySQL să nu încheie procedura stocată atunci când întâlniți primul punct și virgulă.

Putem merge la Fila Rutine pentru a vedea fiecare tranzacție pe care am creat-o și de acolo putem modifica, executa, exporta sau șterge codul.

Pentru a executa o procedură prin stocare folosim Comanda CALL de la Fila SQL sau, de asemenea, dintr-un limbaj de programare precum .NET sau Java. Apoi invocăm procedura stocată creată cu comanda.

 CALL pa_listainmuebles ();

Apoi vom crea o procedură stocată pentru a insera o proprietate, pentru aceasta vom avea nevoie de parametri de tip IN, adică vom atribui date și variabile de intrare procedurii stocate pentru a efectua o tranzacție, în acest caz salvându-le în baza de date.

 DELIMITER // CREATE PROCEDURE pa_nuevoinmueble (IN id INT, IN userid INT, IN price DECIMAL, IN commission DECIMAL) BEGIN INSERT INTO property “(„ id ”,„ userid ”,„ price ”,“ commission ”) VALORI (id, userid ), preț, comision) END // DELIMITER;

MARI

Apoi putem executa procedura stocată prin invocarea și atribuirea parametrilor.

 CALL `pa_newinmueble` ('12 ',' 15 ',' 10.00 ',' 0.05 ')

De asemenea, putem introduce date rulând rutina de la Phpmyadmin.

MARI

Apoi vom crea procedura stocată pentru a edita o proprietate din editorul Phpmyadmin, în acest caz vom modifica doar prețul.

Putem crea roluri din câmpul Definer unde putem atribui un utilizator definit în serverul Mysql, în acest caz utilizatorul rădăcină al gazdei localhost, astfel încât să poată accesa procedura stocată.
Dacă vrem să o facem din codul SQL, trebuie să executăm următoarele comenzi:

 CREATE DEFINER = `root` @` localhost` PROCEDURA `pa_editarinmueble` (IN` new property` DECIMAL (10,2), IN` property id` INT (11)) BEGIN UPDATE property SET price = new property WHERE id = property id; SFÂRȘIT

Îl rulezi și ai terminat.

Folosind Trigger sau Triggers în Mysql
Un Trigger sau Trigger în MySQL este un set de instrucțiuni SQL care depind de o procedură stocată și sunt utilizate pentru a rula automat atunci când apare un anumit eveniment în baza noastră de date. Aceste evenimente sunt declanșate de tranzacții sau declarații precum INSERT, UPDATE și DELETE.

Un exemplu este când o modificare este salvată într-un registru, facem automat o copie de rezervă sau înregistrăm un fișier de audit pentru a ști ce date au fost modificate, când și cine le-a modificat. Ele pot fi utilizate pentru orice manipulare care afectează datele, pentru a sprijini sau genera informații noi.

Vom crea tabelul de audit imobiliar de mai jos:

 CREATE TABLE `audit` (` user` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` date` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_sw Swedish_ci' ENGINE = InnoDB

Vom crea un declanșator care salvează un mesaj în audit dacă cineva schimbă prețul unei proprietăți.

 CREATE DEFINER = "root" @ "localhost" TRIGGER `real estate_after_update` DUPĂ ACTUALIZARE ACTIVĂ` real estate` PENTRU FIECARE RÂND INSERAȚI ÎN audit (utilizator, descriere, dată) VALORI (utilizator (), CONCAT („Prețul imobilului modificat”, NEW.id, '(', OLD.price, ') de (', NEW.price, ')'), ACUM ())

Acest declanșator se execută automat după o actualizare a prețului, putem include mai multe câmpuri dacă dorim, cu VECHI specificăm câmpul cu valoarea înainte de modificare și cu NOU specificăm noua valoare introdusă, cu ACUM () specificăm data și ora curentă.

Creăm un declanșator care va avea After Update pe proprietăți ca eveniment, adică după ce apare o actualizare în tabelul cu proprietăți, în acest caz vom adăuga utilizatorul care a făcut modificarea, noul preț și prețul anterior.

Realizez o actualizare a unei proprietăți:

 CALL `pa_editarinmueble` ('80000', '170')

Apoi mergem la masa de audit și putem vedea schimbarea:

Putem vedea, de asemenea, rezultatele într-un raport în tipărire de la Phpmyadmin. Putem vedea cum au fost salvate datele care identifică proprietatea, modificarea făcută și utilizatorul care a realizat-o, avem și data și ora la care a fost făcută modificarea.

În continuare vom vedea o altă posibilă aplicație dacă o proprietate este închiriată, care își schimbă automat starea în inactivă sau o vom face indisponibilă.

Pentru aceasta trebuie să avem un tabel simplu unde să depozităm ce proprietate este închiriată, pentru un exemplu practic nu vom lua multă rigoare în date.

 CREAȚI TABELUL `închirieri` (` id` INT (10) NOT NULL, `property id` INT (10) NOT NULL,` locatar id` INT (11) NOT NULL, CHEIE PRIMARĂ (`id`)) COLLATE = 'latin1_sw Swedish_ci 'MOTOR = InnoDB; 

Apoi vom crea procedura stocată pentru a insera o nouă înregistrare în tabelul de închirieri.

 CREATE DEFINER = "root" @ "localhost" PROCEDURA `pa_newrental` (IN` id de proprietate` INT, ÎN„ id chiriaș` INT) LIMBAJ SQL NU DETERMINISTIC CONȚINE SQL SQL COMENTAR DEFINITOR DE SIGURANȚĂ „INSERARE ÎN„ închirieri „ `,` id chiriaș`) VALORI (id chiriaș, id chiriaș)

Și apoi declanșatorul pentru a modifica proprietățile activate:

 CREATE DEFINER = `root` @` localhost` TRIGGER `locations_after_insert` DUPĂ INSERARE ON` închirieri` PENTRU FIECARE RAND ACTUALIZARE SET imobiliar activat = 'nu' unde id = NOU. Propertyid

Apoi invocăm procedura stocată în care atribuim ID-ul proprietății și ID-ul clientului sau chiriașului pe care îl închiriez.

 APEL pentru închiriere nouă (170.11)

Apoi mergem la tabelul imobiliar și ar trebui să vedem că schimbarea de câmp activată de stare DACĂ este activă la NU este activă.

Am văzut avantajele utilizării declanșatorului cu proceduri stocate în MySQL pentru:

• Auditați și înregistrați evenimente sau activități de modificare a datelor într-un tabel.
• Schimbați starea unui câmp activând sau refuzând permisiunile și acțiunile de pe o masă
• De asemenea, permite păstrarea consistenței datelor prin executarea acțiunilor în funcție de evenimente care afectează unul sau mai multe tabele.

Într-un alt tutorial, vom continua cu programarea structurilor condiționale și a structurilor repetitive în procedurile stocate.

V-a plăcut și ați ajutat acest tutorial?Puteți recompensa autorul apăsând acest buton pentru a-i oferi un punct pozitiv