Alcuni strumenti forniti dal motore di database MySQL sono i procedura di archiviazione, funzioni e trigger, che vengono utilizzati per eseguire transazioni o operazioni come l'inserimento o la modifica di record.
Le stored procedure sono piccoli programmi sviluppati in codice SQL. Una stored procedure è un insieme di comandi SQL che vengono archiviati insieme al database.
Il vantaggio di una stored procedure è che possiamo crearla in qualsiasi editor di testo e anche sul server, viene eseguita dal motore del database e non è accessibile agli utenti ma solo all'amministratore.
Una stored procedure invia i suoi risultati a un'applicazione in modo che li visualizzi sullo schermo evitando di sovraccaricare il server, nel tutorial:
- Stored procedure MYSQL - Creazione, query e inserimento dati
Avevo spiegato come crearli, qui aggiungeremo funzioni e trigger. Vedremo un esempio su un database di un immobile che chiameremo locazione e poi creeremo le tabelle.
- Struttura della tabella per la tabella `real estate` CREATE TABLE IF NOT EXISTS` real estate` (`id` int (11) NOT NULL,` userid` int (11) DEFAULT NULL, `idtype property` int (6) DEFAULT '0 ', `prezzo` decimale (10,2) DEFAULT' 0.00 ',` commissione` decimale (10,0) NOT NULL, `descrizione` testo,` highdate` data DEFAULT' 0000-00-00 ', `ID provincia` int (10) DEFAULT NULL, `idlocalidad` int (10) DEFAULT NULL,` address` varchar (150) DEFAULT NULL, `piano e appartamento` 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, `old` varchar (100) DEFAULT NULL,` mt2covered` int (11) DEFAULT NULL, `lot_surface` int (11) DEFAULT NULL,` attivato` enum ('yes',' no ') NOT NULL DEFAULT' si ') ENGINE = MyISAM AUTO_INCREMENT = 196 DEFAULT CHARSET = latin1; - Indici della tabella `real estate` ALTER TABLE` real estate` ADD PRIMARY KEY (`id`);
Svilupperemo ora una stored procedure per ogni transazione per interrogare, inserire, modificare ed eliminare un record.
Possiamo usare Phpmyadmin o un manager come Heidisql che è gratuito e funziona su Windows o Linux con Wine.
Creiamo una stored procedure per interrogare la tabella immobiliare:
DELIMITER // CREATE PROCEDURE pa_listinmuebles () BEGIN SELECT * FROM proprietà; FINE // DELIMITATORE;MYSQL comprende che un'istruzione termina con un punto e virgola. Il Dichiarazione DELIMITER cambia il carattere finale con qualsiasi altro carattere, per convenzione // viene utilizzato per indicare la fine della procedura memorizzata in modo che MySQL non termini la procedura memorizzata quando incontra il primo punto e virgola.
Possiamo andare al Scheda Routine per vedere ogni transazione che abbiamo creato e da lì possiamo modificare, eseguire, esportare o eliminare il codice.
Per eseguire una procedura memorizzando utilizziamo il comando CHIAMA dal Scheda SQL o anche da un linguaggio di programmazione come .NET o Java. Successivamente invochiamo la stored procedure creata con il comando.
CALL pa_listinmuebles ();
Successivamente creeremo una stored procedure per inserire una proprietà, per questo avremo bisogno di parametri di tipo IN, cioè assegneremo dati e variabili di input alla stored procedure per effettuare una transazione, in questo caso salvarli nel database.
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`) VALUES (id, userid` ) , prezzo, commissione) END // DELIMITER;
INGRANDIRE
Quindi possiamo eseguire la stored procedure invocando e assegnando i parametri.
CALL `pa_newinmueble` ('12 ',' 15 ',' 10.00 ',' 0.05 ')Possiamo anche inserire i dati eseguendo la routine da Phpmyadmin.
INGRANDIRE
Successivamente creeremo la stored procedure per modificare una proprietà dall'editor di Phpmyadmin, in questo caso modificheremo solo il prezzo.
Possiamo creare ruoli dal campo Definer dove possiamo assegnare un utente definito nel server Mysql, in questo caso l'utente root dell'host localhost, in modo che possa accedere alla stored procedure.
Se vogliamo farlo da codice SQL, dobbiamo eseguire i seguenti comandi:
CREATE DEFINER = `root` @` localhost` PROCEDURA `pa_editarinmueble` (IN` nuova proprietà` DECIMAL (10,2), IN `id proprietà` INT (11)) BEGIN UPDATE proprietà SET prezzo = nuova proprietà WHERE id = id proprietà; FINELo esegui e il gioco è fatto.
Utilizzo di Trigger o Trigger in Mysql
Un Trigger o Trigger in MySQL è un insieme di istruzioni SQL che dipendono da una stored procedure e vengono utilizzate per essere eseguite automaticamente quando si verifica un determinato evento nel nostro database. Questi eventi vengono attivati da transazioni o istruzioni come INSERT, UPDATE e DELETE.
Un esempio è quando una modifica viene salvata in un registro, eseguiamo automaticamente un backup o registriamo un file di controllo per sapere quali dati sono stati modificati, quando e chi li ha modificati. Possono essere utilizzati per qualsiasi manipolazione che influisca sui dati, per supportare o generare nuove informazioni.
Creeremo la tabella di audit immobiliare di seguito:
CREATE TABLE `audit` (` user` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` date` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDBCreeremo un trigger che salva un messaggio in audit se qualcuno cambia il prezzo di una proprietà.
CREATE DEFINER = `root` @` localhost` TRIGGER `real estate_after_update` DOPO UPDATE ON` real estate` PER OGNI RIGA INSERT INTO audit (utente, descrizione, data) VALUES (utente (), CONCAT ('Prezzo immobiliare modificato', NEW.id, '(', OLD.price, ') di (', NEW.price, ')'), ORA ())Questo trigger viene eseguito automaticamente dopo che si verifica un aggiornamento del prezzo, possiamo includere più campi se lo desideriamo, con OLD specifichiamo il campo con il valore prima della modifica e con NEW specifichiamo il nuovo valore inserito, con NOW () specifichiamo la data e l'ora corrente.
Creiamo un trigger che avrà come evento After Update sulle proprietà, cioè dopo che si è verificato un aggiornamento nella tabella delle proprietà, in tal caso aggiungeremo l'utente che ha apportato la modifica, il nuovo prezzo e il prezzo precedente.
Eseguo un aggiornamento su una proprietà:
CALL `pa_editarinmueble` ('80000', '170')Quindi andiamo alla tabella di audit e possiamo vedere il cambiamento:
Possiamo anche vedere i risultati in un report in visualizzazione stampa da Phpmyadmin. Possiamo vedere come sono stati salvati i dati che identificano l'immobile, la modifica effettuata e l'utente che l'ha apportata, abbiamo anche la data e l'ora in cui è stata apportata la modifica.
Successivamente vedremo un'altra possibile applicazione se un immobile viene affittato, che cambia automaticamente il suo stato in non attivo o lo renderemo non disponibile.
Per questo dobbiamo avere una semplice tabella dove memorizzare quale immobile si affitta, per un esempio pratico non faremo molto rigore nei dati.
CREATE TABLE `affitti` (` id` INT (10) NOT NULL, `ID proprietà` INT (10) NOT NULL,` tenant id` INT (11) NOT NULL, PRIMARY KEY (`id`)) COLLATE = 'latin1_swedish_ci ' MOTORE = InnoDB;Successivamente creeremo la stored procedure per inserire un nuovo record nella tabella dei noleggi.
CREATE DEFINER = `root` @` localhost` PROCEDURA `pa_newrental` (IN` id proprietà` INT, IN `id tenant` INT) LANGUAGE SQL NON DETERMINISTICO CONTIENE SQL SQL SECURITY DEFINER COMMENTO '' INSERT INTO` affitti` (`id proprietà `,` id tenant`) VALUES (id tenant, id tenant)
E poi il trigger per modificare le proprietà attivato:
CREATE DEFINER = `root` @` localhost` TRIGGER `rentals_after_insert` AFTER INSERT ON`affitti` PER OGNI AGGIORNAMENTO DI RIGA SET immobiliare attivato = 'no' dove id = NUOVO.Quindi invochiamo la stored procedure in cui assegniamo l'id della proprietà e l'id del cliente o inquilino che affitto.
CHIAMA per nuovo noleggio (170.11)Successivamente andiamo alla tabella immobiliare e dovremmo vedere che il campo attivato cambia di stato SE è attivo a NON è attivo.
Abbiamo visto i vantaggi dell'utilizzo di trigger con stored procedure in MySQL per:
- Controlla e registra eventi o attività di modifica dei dati in una tabella.
- Modificare lo stato di un campo attivando o negando permessi e azioni su una tabella
- Consente inoltre di preservare la consistenza dei dati eseguendo azioni in base ad eventi che interessano una o più tabelle.
