7,8K

In poche parole, le procedure memorizzate ("SP") sono procedure archiviate in un database (scritto utilizzando SQL e altre istruzioni) che può essere chiamato motore di database o linguaggio di programmazione associato.

In questo articolo ti dirò come creare HP utilizzando MySQL ed eseguirlo sul server MySQL e tramite PHP.

Nota: qui non tratteremo tutti gli aspetti di HP. Per ottenere aiuto su questioni non trattate nell'articolo è sempre possibile utilizzare il sito ufficiale Documentazione MySQL.

HP è supportato anche su altri server di database comuni (Postgre, ad esempio), quindi ciò di cui parleremo oggi vale anche per loro.

Molti di noi hanno molta familiarità con le consuete impostazioni che ci consentono di creare un'applicazione database: creazione di database, creazione di tabelle, creazione di indici, dati CRUD, generazione di query lato client e ulteriore elaborazione se necessaria.

Questo processo funziona perfettamente nella maggior parte dei casi, ma c'è un aspetto importante che la programmazione del database non copre: la procedura memorizzata.

Mi vengono in mente almeno quattro buoni motivi per utilizzare HP nelle applicazioni di database. Innanzitutto, riduce il traffico di rete e il carico del server.

Ci sono quattro componenti in un'applicazione di database web PHP standard:

• Livello cliente, che solitamente è rappresentato da un browser web. Fornisce l'interazione dell'utente e consente l'immissione di dati tramite un'interfaccia utente;
• Livello del server Web, dove le richieste degli utenti vengono elaborate e le risposte vengono rinviate al livello del client;
• Il livello PHP, che elabora tutti i componenti PHP, crea la logica dell'applicazione e genera la parte PHP della risposta;
• Livello della banca dati, che gestisce tutte le query del database, incluse (ma non limitate a) query SELECT, istruzioni INSERT, ecc.

In genere, quando si tratta di grande applicazione, molto probabilmente questi elementi non si trovano sulla stessa macchina, è addirittura possibile che non si trovino sulla stessa rete.

Sebbene la velocità della rete sia aumentata in modo significativo negli ultimi anni, è ancora il canale di trasferimento dati più lento e inaffidabile (rispetto ad altri) (cache della CPU, memoria, disco rigido eccetera.)

Pertanto, per aumentare la velocità dell'applicazione e migliorare l'affidabilità, in alcuni casi si ricorre a una maggiore elaborazione dei dati e alla logica eseguita sul lato server (in particolare un server MySQL) e a meno dati trasferiti sulla rete.

In secondo luogo, migliora la produttività. Gli HP vengono archiviati e avviati direttamente sul server MySQL. Possono essere precompilati e analizzati sul server del database.

Ciò è molto diverso dall'elaborazione della stessa query sul lato client, dove la query viene analizzata dai driver del database, analizzata e ottimizzata (se possibile) ogni volta che viene richiamata l'istruzione della query.

Ciò è simile all'esecuzione di un linguaggio interpretato (lato client) e di un linguaggio compilato (lato server del database). E sappiamo che il programma compilato verrà eseguito più velocemente.

In terzo luogo, una volta scritto HP può essere eseguito ovunque. SQL è standard e indipendente dalla piattaforma al 100%. Si basa solo sul server del database. Pensa a quante lingue/librerie diverse esistono utilizzate per lavorare con un database.

Si concentrano sul miglioramento dell'efficienza del recupero e dell'elaborazione dei dati sul lato server, invece di specificare la stessa logica di elaborazione dei dati, che è descritta sintatticamente in modo diverso da tutti questi linguaggi/librerie. Poiché questa logica di elaborazione dei dati è così ampiamente utilizzata.

Ultimo ma non meno importante, HP è un aspetto fondamentale della sicurezza del database.

consideriamo installazione facile per la banca dati. Diciamo dentro sistema informativo Il sistema di gestione delle risorse umane (HRIS) dispone di una tabella contenente informazioni sullo stipendio di ciascun dipendente. Il dipendente delle risorse umane dovrebbe essere in grado di ottenere alcuni dati da questa tabella: importi salariali totali, stipendio medio, ecc.

Ma questo dipendente non dovrebbe avere accesso informazioni dettagliate sulla retribuzione di ciascun dipendente, poiché queste informazioni sono confidenziali e potrebbero essere disponibili solo ad alcuni dirigenti.

Sappiamo che MySQL dispone di un sistema completo di gestione dei privilegi. Ovviamente, in questo caso, non possiamo concedere a questo dipendente HR nemmeno il privilegio SELECT (perché se lo facciamo, vorrà dire che potrà vedere lo stipendio dettagliato di ciascun dipendente).

Ma se non può accedere alla tabella degli stipendi, come potrà ottenere il riepilogo degli stipendi? Come possiamo fornire al dipendente delle risorse umane l'accesso a queste informazioni senza compromettere la politica delle risorse umane dell'azienda?

Una soluzione alternativa a questo problema consiste nell'utilizzare una procedura memorizzata che rilascia le informazioni richieste e le concede a un dipendente che dispone del privilegio EXECUTE. (Per un elenco dettagliato e una descrizione dei privilegi MySQL, puoi trovare nella documentazione ufficiale.

In questo caso HP funge da ponte tra l'utente (il nostro dipendente delle risorse umane) e la tabella (stipendio), alla quale l'utente non ha accesso diretto:

È tutto! Con l'aiuto di HP, possiamo fornire all'utente la possibilità di completare un'attività senza compromettere la sicurezza del database (e la politica del personale)!

Svantaggi dell'utilizzo delle procedure memorizzate

Dopo aver elencato tutti i vantaggi dell'utilizzo di HP, dovremmo comprendere chiaramente alcuni degli svantaggi e vedere se esistono modi per migliorare la situazione:

• Mancanza di controllo della versione dell'HP stesso. Quando la versione HP cambia, cambia senza salvare la cronologia delle azioni per versione precedente sul lato server. Ciò potrebbe creare qualche inconveniente quando l'utente desidera ripristinare le modifiche.

  In questi casi, suggerisco di scrivere ad HP sul lato client e di fornire qui il controllo della versione. Quando HP è pronto, il codice può essere facilmente copiato, ad esempio, in MySQL Workbench e creato una procedura lato server. In questo modo possiamo ottenere un certo grado di controllo della versione.

• Assenza modo semplice « sincronizzare» implementare le modifiche e obbligare tutte le entità a utilizzarle ultima versione, in particolare quando ogni membro del team ha il proprio database locale per lo sviluppo e il test.

  Il controllo della versione può essere una soluzione, ma è comunque necessario un intervento manuale aggiornando la copia locale di HP a server locale DB. Un altro modo è usare " oggetto condizionale" I membri del team possono essere divisi in modo che almeno una persona sia responsabile della manutenzione di HP e delle soluzioni utilizzando il relativo codice call-through.

  Il resto del gruppo che necessita dei risultati dell'esecuzione HP può sviluppare e testare la propria parte utilizzando un oggetto condizionale, presupponendo che le chiamate “condizionali” all'HP produrranno il risultato desiderato. In una fase successiva è possibile combinare le diverse parti rimuovendo il codice condizionale.

• Difficoltà nella creazione di backup/esportazione. HP è dal lato server. Gli sviluppatori avranno solo privilegi di base (SELECT, EXECUTE, ecc.) e nessun diritto di amministratore per il backup e l'esportazione. In un certo senso questo non è affatto uno svantaggio, bensì uno degli aspetti fondamentali della sicurezza dei database.

  E non puoi aggirare questa limitazione e non è consigliabile. Si prevede che al team venga assegnato un DBA dedicato assegnato a questo lavoro. Regolare backup Il database può anche servire a scopi di esportazione (e importazione).

Creazione di una procedura memorizzata in MySQL

Diamo un'occhiata a come creare HP nel server MySQL, creare un utente, assegnargli i privilegi ed eseguire (sotto questo login) HP per verificare se i dati vengono elaborati correttamente. Nel mio ambiente di lavoro utilizzo MySQL Workbench.

Esistono anche altri strumenti (PHPMyAdmin, ad esempio), quindi puoi scegliere quello che funziona meglio per te.

Diciamo che abbiamo una tabella come questa:

CREATE TABLE `stipendio` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, CHIAVE PRIMARIA (`empid`)) MOTORE=InnoDB DEFAULT CHARSET=utf8;

Innanzitutto, creiamo un utente "tr" per il dipendente delle risorse umane, che deve ottenere informazioni di riepilogo sullo stipendio da questa tabella (stipendio medio per l'azienda, massimo, minimo, ecc.):

CREARE UTENTE "tr"@"localhost" IDENTIFICATO DA "mypass";

Assegniamo solo il privilegio EXECUTE a questo utente per la tabella che contiene i dati sullo stipendio:

concedere l'esecuzione su hris.* a tr@`%`

Possiamo chiarire la necessità di fornire determinati privilegi leggendo la sezione “ Utenti e privilegi» Documentazione MySQL:

Ora creiamo l'HP come segue:

DELIMITER $$ CREA PROCEDURA `avg_sal`(out avg_sal decimale) INIZIA seleziona avg(sal) in avg_sal dallo stipendio; FINE

NOTA: tutte le operazioni di cui sopra richiedono i diritti di amministratore sul server MySQL.

Dopo aver eseguito il comando in MySQL Workbench, avg_sal HP verrà creato e sarà pronto per essere chiamato. Produce lo stipendio medio dalla tabella degli stipendi.

Per verificare se l'utente tr può eseguire HP senza accedere alla tabella degli stipendi, possiamo modificare il nostro ruolo accedendo al server MySQL come utente tr. Questo può essere fatto creando una nuova connessione in MySQL Workbench con un nome utente e una password diversi.

Dopo aver effettuato l'accesso come utente tr, la prima cosa che vedremo è che non possiamo vedere le tabelle, solo HP è a nostra disposizione:

Chiaramente, l'utente tr non sarà in grado di recuperare alcun dato da nessuna tabella (cioè non sarà in grado di vedere gli importi salariali specifici dalla tabella degli stipendi), ma sarà in grado di eseguire l'HP che abbiamo appena creato per determinare lo stipendio medio per l'azienda :

chiama avg_sal(@out); seleziona @uscita;

Il risultato è lo stipendio medio.

A questo punto abbiamo completato tutto il lavoro preparatorio: abbiamo creato un utente, gli abbiamo assegnato i privilegi, creato l'HP e verificato la sua esecuzione. Successivamente mostreremo come chiamare questo HP da PHP.

Chiamare una procedura memorizzata da PHP

Utilizzando PDO, chiamare HP è abbastanza semplice. Il codice PHP è simile al seguente:

$dbms = "mysql"; //Sostituisci i parametri di connessione seguenti per adattarli al tuo ambiente $host = "192.168.1.8"; $db = "ora"; $utente = "tr"; $pass = "mio pass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=nuovo PDO($dsn, $utente, $pass); $q=$cn->exec("chiama avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); stampa_r($res);

$res conterrà il valore dello stipendio medio dalla tabella degli stipendi. Successivamente, l'utente potrà elaborare ulteriormente i dati in uscita.

Conclusione

In questo articolo abbiamo esaminato una componente integrale del database MySQL: le procedure memorizzate.

I vantaggi derivanti dall'utilizzo di HP sono evidenti, ma permettetemi di sottolinearli ancora: Le procedure archiviate consentono di fornire un controllo rigoroso dell'accesso a determinati dati nel database per soddisfare i requisiti aziendali.

Abbiamo inoltre mostrato i passaggi fondamentali per la creazione di procedure memorizzate, la creazione di un utente e l'assegnazione dei privilegi, nonché come chiamare HP tramite PHP.

Questo articolo non copre tutti gli aspetti relativi alle procedure memorizzate. Alcuni punti importanti, come parametri I/O, istruzioni di controllo, cursori, sintassi completa, ecc. Non ne abbiamo parlato in questo breve articolo.

Se sei interessato a questo articolo, lascia un commento e saremo lieti di pubblicare materiale più approfondito su questo utile e potente elemento di MySQL.

MySQL 5 presenta molte nuove funzionalità, una delle più significative delle quali è la creazione di procedure memorizzate. In questo tutorial parlerò di cosa sono e di come possono semplificarti la vita.

introduzione

Una procedura memorizzata è un modo per incapsulare azioni ripetitive. Le procedure memorizzate possono dichiarare variabili, manipolare il flusso di dati e utilizzare altre tecniche di programmazione.

Il motivo della loro creazione è chiaro ed è confermato dall'uso frequente. D’altronde, se si parla con chi lavora con loro in modo irregolare, i pareri si divideranno su due fronti completamente opposti. Non dimenticarlo.

Dietro

• Condivisione della logica con altre applicazioni. Le procedure memorizzate incapsulano la funzionalità; ciò fornisce connettività per l'accesso e la gestione dei dati tra diverse applicazioni.
• Isolamento degli utenti dalle tabelle del database. Ciò consente di concedere l'accesso alle procedure memorizzate, ma non ai dati della tabella stessa.
• Fornisce un meccanismo di protezione. Come al punto precedente, se puoi accedere ai dati solo tramite procedure memorizzate, nessun altro potrà cancellare i tuoi dati tramite il comando SQL DELETE.
• Prestazioni migliorate come conseguenza della riduzione traffico di rete. Utilizzando le procedure memorizzate è possibile combinare più query.

Contro

• Aumento del carico sul server del database dovuto al fatto che la maggior parte del lavoro viene eseguito sul lato server e meno sul lato client.
• Dovrai imparare molto. Dovrai imparare la sintassi delle espressioni MySQL per scrivere le tue procedure memorizzate.
• Stai duplicando la logica dell'applicazione in due punti: codice server e codice per procedure memorizzate, complicando così il processo di manipolazione dei dati.
• La migrazione da un DBMS a un altro (DB2, SQL Server, ecc.) può causare problemi.

Lo strumento con cui lavoro si chiama MySQL Query Browser, che è piuttosto standard per interagire con i database. Attrezzo riga di comando MySQL è un'altra scelta eccellente. Il motivo per cui te lo dico è perché phpMyAdmin preferito da tutti non supporta l'esecuzione di procedure memorizzate.

A proposito, sto utilizzando una struttura di tabella di base per facilitare la comprensione di questo argomento. Sto parlando di procedure memorizzate e sono abbastanza complesse da richiedere l'approfondimento dell'ingombrante struttura della tabella.

Passaggio 1: posizionare un limitatore

Un delimitatore è un carattere o una stringa di caratteri utilizzata per indicare al client MySQL che hai finito di scrivere l'espressione SQL. Per secoli il punto e virgola è stato il delimitatore. Tuttavia, potrebbero verificarsi problemi poiché in una procedura memorizzata potrebbero essere presenti più espressioni, ognuna delle quali deve terminare con un punto e virgola. In questo tutorial utilizzo la stringa "//" come delimitatore.

Passaggio 2: come utilizzare le procedure memorizzate

Creazione di una procedura memorizzata

DELIMITER // CREATE PROCEDURE `p2` () LINGUA SQL SQL DETERMINISTICO SECURITY DEFINER COMMENTO "Una procedura" BEGIN SELECT "Hello World !"; FINE//

La prima parte del codice crea una procedura memorizzata. Il successivo contiene parametri opzionali. Poi arriva il nome e, infine, il corpo della procedura stessa.

I nomi delle procedure memorizzate fanno distinzione tra maiuscole e minuscole. Inoltre, non è possibile creare più procedure con lo stesso nome. Non possono esserci espressioni all'interno di una procedura memorizzata che modificano il database stesso.

4 caratteristiche di una procedura memorizzata:

• Lingua: per motivi di portabilità, l'impostazione predefinita è SQL.
• Deterministico: se la procedura restituisce sempre lo stesso risultato e accetta gli stessi parametri di input. Questo è per il processo di replica e registrazione. Il valore predefinito è NON DETERMINISTICO.
• Sicurezza SQL: i diritti dell'utente vengono controllati durante la chiamata. INVOKER è l'utente che chiama la procedura memorizzata. DEFINER è il “creatore” della procedura. Il valore predefinito è DEFINITORE.
• Commento: a scopo di documentazione, il valore predefinito è ""

Chiamare una procedura memorizzata

Per chiamare una procedura memorizzata è necessario digitare la parola chiave CALL, seguita dal nome della procedura, seguito dai parametri (variabili o valori) tra parentesi. Le parentesi sono obbligatorie.

CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10, "parametro stringa", @parameter_var);

Modifica di una procedura memorizzata

MySQL dispone di un'istruzione ALTER PROCEDURE per modificare le procedure, ma è adatta solo per modificare determinate caratteristiche. Se è necessario modificare i parametri o il corpo di una procedura, è necessario eliminarla e ricrearla.

Rimozione di una procedura memorizzata

PROCEDURA DI DROP SE ESISTE p2;

Questo è un comando semplice. L'istruzione IF EXISTS rileva un errore se tale procedura non esiste.

Passaggio 3: opzioni

Vediamo come possiamo passare i parametri ad una procedura memorizzata.

• CREATE PROCEDURE proc1(): elenco di parametri vuoto
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE): un parametro di input. La parola IN è facoltativa perché i parametri predefiniti sono IN (in).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): un parametro restituito.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): un parametro, sia input che return.

Naturalmente è possibile specificare diversi parametri di diverso tipo.

Esempio di parametro IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS risultato; FINE//

Esempio di parametro OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Questo è un test"; FINE //

Esempio di parametro INOUT

DELIMITER // CREA PROCEDURA `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; FINE //

Passaggio 4: variabili

Ora ti insegnerò come creare variabili e memorizzarle all'interno delle procedure. Devi dichiararli esplicitamente all'inizio del blocco BEGIN/END, insieme ai relativi tipi di dati. Una volta dichiarata una variabile, è possibile utilizzarla allo stesso modo delle variabili di sessione, dei valori letterali o dei nomi di colonna.

La sintassi della dichiarazione delle variabili è simile alla seguente:

DECLARE nomevar DATA-TYPE DEFAULT valore predefinito;

Dichiariamo alcune variabili:

DICHIARARE a, b INT DEFAULT 5; DICHIARARE str VARCHAR(50); DICHIARARE oggi TIMESTAMP DEFAULT CURRENT_DATE; DICHIARARE v1, v2, v3 TINYINT;

Lavorare con le variabili

Dopo aver dichiarato una variabile, puoi impostarne il valore utilizzando i comandi SET o SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DICHIARARE str VARCHAR(50); DICHIARARE oggi TIMESTAMP DEFAULT CURRENT_DATE; DICHIARARE v1, v2, v3 TINYINT; INSERIRE NELLA tabella1 VALORI (a); SET str = "Io sono una stringa"; SELECT CONCAT(str,paramstr), oggi FROM table2 WHERE b >=5; FINE //

Passaggio 5: strutture di controllo del thread

MySQL supporta i costrutti IF, CASE, ITERATE, LEAVE LOOP, WHILE e REPEAT per controllare i thread all'interno di una procedura memorizzata. Vedremo come utilizzare IF, CASE e WHILE poiché sono i più comunemente usati.

SE progettazione

Utilizzando il costrutto IF, possiamo eseguire attività contenenti condizioni:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variabile1 INT; SET variabile1 = param1 + 1; SE variabile1 = 0 THEN SELECT variabile1; FINISCI SE; SE param1 = 0 POI SELEZIONARE "Valore parametro = 0"; ELSE SELECT "Valore del parametro<>0"; FINE SE; FINE //

Progettazione della CASSA

CASE è un altro metodo per testare le condizioni e selezionare una soluzione adeguata. Questo ottimo modo sostituendo più costrutti IF. Il costrutto può essere descritto in due modi, garantendo flessibilità nella gestione di più espressioni condizionali.

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variabile1 INT; SET variabile1 = param1 + 1; CASE variabile1 QUANDO 0 ALLORA INSERT IN table1 VALUES (param1); QUANDO 1 POI INSERIRE NELLA tabella1 VALORI (variabile1); ELSE INSERIRE NELLA tabella1 VALORI (99); CASO FINE; FINE //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variabile1 INT; SET variabile1 = param1 + 1; CASO QUANDO variabile1 = 0 POI INSERIRE NELLA tabella1 VALORI (param1); QUANDO variabile1 = 1 POI INSERIRE I VALORI della tabella1 (variabile1); ELSE INSERIRE NELLA tabella1 VALORI (99); CASO FINE; FINE //

MENTRE progettazione

Tecnicamente, esistono tre tipi di cicli: il ciclo WHILE, il ciclo LOOP e il ciclo REPEAT. Puoi anche eseguire il loop utilizzando la tecnica di programmazione di Darth Vader: istruzioni GOTO. Ecco un ciclo di esempio:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variabile1, variabile2 INT; IMPOSTA variabile1 = 0; MENTRE variabile1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Passaggio 6: cursori

I cursori vengono utilizzati per attraversare l'insieme di righe restituite da una query ed elaborare ciascuna riga.

MySQL supporta i cursori nelle procedure memorizzate. Ecco una breve sintassi per creare e utilizzare un cursore.

DECLARE nome-cursore CURSOR FOR SELECT ...; /*Dichiarare un cursore e riempirlo */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Cosa fare quando non ci sono più record*/ OPEN nome-cursore; /*Apre cursore*/ FETCH nome-cursore INTO variabile [, variabile]; /*Assegna un valore a una variabile uguale al valore corrente della colonna*/ CHIUDI nome-cursore; /*Chiudi cursore*/

In questo esempio eseguiremo alcune semplici operazioni utilizzando un cursore:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DICHIARA cur1 CURSORE PER SELECT col1 FROM table1; DICHIARARE CONTINUA GESTORE PER SET NON TROVATO b = 1; APERTO cur1; IMPOSTA b = 0; IMPOSTARE c = 0; MENTRE b = 0 FARE FETCH cur1 IN a; SE b = 0 ALLORA IMPOSTA c = c + a; FINISCI SE; FINE MENTRE; CHIUDI cur1; IMPOSTA param1 = c; FINE //

I cursori hanno tre proprietà che devi comprendere per evitare di ottenere risultati imprevisti:

• Non sensibile: un cursore che si apre una volta non rifletterà le modifiche nella tabella che si verificano successivamente. In realtà MySQL non garantisce che il cursore verrà aggiornato, quindi non fare affidamento su di esso.
• Sola lettura: i cursori non possono essere modificati.
• Nessun riavvolgimento: il cursore può spostarsi solo in una direzione: in avanti, non potrai saltare le righe senza selezionarle.

Conclusione

In questo tutorial ti ho presentato le nozioni di base per lavorare con le procedure memorizzate e alcune delle proprietà specifiche ad esse associate. Naturalmente, dovrai approfondire le tue conoscenze in aree come la sicurezza, le espressioni SQL e l'ottimizzazione prima di diventare un vero guru delle procedure MySQL.

È necessario calcolare i vantaggi derivanti dall'utilizzo delle procedure memorizzate nell'applicazione specifica e quindi creare solo le procedure necessarie. In generale utilizzo le procedure; A mio parere, vale la pena implementarli nei progetti per la loro sicurezza, la manutenzione del codice e il design generale. Inoltre, tieni presente che le procedure MySQL sono ancora in fase di elaborazione. Aspettatevi miglioramenti in termini di funzionalità e miglioramenti. Non esitate a condividere le vostre opinioni.

In poche parole, le procedure memorizzate ("SP") sono procedure archiviate in un database (scritte utilizzando SQL e altre istruzioni di controllo) che possono essere eseguite da un motore di database e richiamate dal codice di programma eseguito con quel motore. """ Leggi completamente

Procedure memorizzate in MySQL e PHP. Parte 2

Taylor Wren (Taylor Ren), 03.01.2014

Creazione di una procedura memorizzata in MySQL

Poiché gli HP sono archiviati sul server, si consiglia di crearli direttamente sul server, ad es. Non dovresti usare PHP o altri linguaggi di programmazione per eseguire comandi SQL per creare procedure memorizzate.

Diamo un'occhiata a come creare un HP su un server MySQL, come creare un utente e come assegnargli i privilegi per eseguire il nostro HP. Successivamente controlleremo la correttezza del risultato. Per questo utilizzerò MySQL Workbench. Puoi anche utilizzare altri programmi (ad esempio PHPMyAdmin). Puoi scegliere il kit di strumenti più adatto a te.

Diciamo che la nostra tabella è simile a questa:

CREATE TABLE `stipendio` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, CHIAVE PRIMARIA (`empid`)) MOTORE=InnoDB DEFAULT CHARSET=utf8;

Per il nostro dipendente che necessita di informazioni statistiche sugli stipendi (medio, massimo, minimo, ecc.) da questa tabella, creeremo un utente "tr" come segue:

CREARE UTENTE "tr"@"localhost" IDENTIFICATO DA "mypass";

Ora assegniamo a questo utente l'unico privilegio EXECUTE nello schema in cui si trova la tabella degli stipendi:

Concedi l'esecuzione su hris.* a tr@`%`

Possiamo verificare di aver assegnato il privilegio corretto aprendo "Utenti e privilegi" in MySQL Bench:

Ora creiamo l'HP stesso come segue:

DELIMITER $$ CREA PROCEDURA `avg_sal`(out avg_sal decimale) INIZIA seleziona avg(sal) in avg_sal dallo stipendio; FINE

Dopo aver eseguito questo comando in MySQL Workbench, verrà creato un avg_sal HP pronto all'uso. Restituisce lo stipendio medio dalla tabella degli stipendi.

Per verificare se l'utente tr può effettivamente eseguire HP e non ha accesso alla tabella degli stipendi, dobbiamo riconnetterci al server MySQL, accedendo come tr. In MySQL Workbench, ciò può essere fatto creando un'altra connessione e specificando l'utente desiderato e la sua password.

Dopo essersi connessi da sotto tr , la prima cosa che notiamo è che l'utente non vede alcuna tabella, vede solo HP:

Ovviamente, l'utente tr non può accedere a nessuna delle tabelle (e quindi non può vedere informazioni dettagliate sugli stipendi dalla tabella degli stipendi), ma può eseguire l'HP che abbiamo creato, che gli restituirà lo stipendio medio dell'azienda:

Chiama avg_sal(@out); seleziona @uscita;

Verrà visualizzato lo stipendio medio.

Quindi, abbiamo svolto tutto il lavoro preparatorio: creato un utente, assegnatogli i privilegi, creato un HP e testato. Ora vediamo come chiamare questo HP da PHP.

Chiamare una procedura memorizzata da PHP

Quando si utilizza PDO, chiamare HP è abbastanza semplice. Ecco il codice PHP corrispondente:

$dbms = "mysql"; // Sostituisci i seguenti parametri di connessione con quelli appropriati per il tuo ambiente: $host = "192.168.1.8"; $db = "ora"; $utente = "tr"; $pass = "mio pass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=nuovo PDO($dsn, $utente, $pass); $q=$cn->exec("chiama avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); stampa_r($res);

La variabile $res contiene lo stipendio medio dalla tabella degli stipendi. Ora l'utente può eseguire un'ulteriore elaborazione dell'output utilizzando PHP.

conclusioni

In questo articolo abbiamo esaminato un componente a lungo dimenticato dei database MySQL: le procedure memorizzate. I vantaggi derivanti dall'utilizzo di HP sono evidenti, ma lasciatemelo ricordare: le procedure memorizzate ci consentono di applicare un controllo di accesso più rigoroso a determinati dati quando la logica aziendale lo richiede.

Inoltre, abbiamo dimostrato i passaggi fondamentali per creare procedure memorizzate, utenti e assegnazione di privilegi appropriati, e abbiamo mostrato come viene chiamato HP da PHP.

Questo articolo non tratta l'intero argomento delle procedure memorizzate. Alcuni aspetti importanti come i parametri di I/O, le istruzioni di controllo, i cursori, la sintassi completa, ecc. non sono stati trattati in questo breve articolo.

Se sei interessato, lascia un commento qui. Se necessario, saremo lieti di offrire articoli più approfonditi sugli aspetti utili e potenti di MySQL, le procedure memorizzate.

Taylor Wren

Taylor è uno sviluppatore freelance di applicazioni web e desktop con sede a Suzhou, nella Cina orientale. Ha iniziato con gli strumenti di sviluppo Borland (C++Builder, Delphi), ha pubblicato un libro su InterBase. Dal 2003 è esperto certificato Borland. Poi sono passato allo sviluppo web in una tipica configurazione LAMP. Successivamente ho iniziato a lavorare con jQuery, Symfony, Bootstrap, Dart, ecc.

Pubblicazioni precedenti:

Dall'autore: Perché dormi al lavoro! Sei sveglio e stai aspettando che il DBMS esegua la query? Quindi è necessario accelerare. Hai utilizzato le procedure memorizzate MySQL? Non sai come? Bene, allora svegliati, perché ora considereremo esattamente questo argomento.

Quali altre procedure ci sono?

Se hai una fobia per le procedure mediche, allora queste strutture “non sono l’argomento giusto”. Quindi non devi avere paura. Ma seriamente, le procedure memorizzate sono una cosa comoda e utile per la “salute” di un DBMS. Sono anche chiamate "funzioni memorizzate MySQL", ma questa non è una definizione del tutto accurata. Anche se affrontiamo tutto in ordine.

Le procedure memorizzate possono ottimizzare significativamente le prestazioni del server e aumentarne la velocità, poiché il loro codice viene archiviato nella cache RAM dopo la prima esecuzione. Per tutte le chiamate successive, la procedura verrà recuperata dalla cache anziché inviata nuovamente per l'esecuzione.

In MySQL, chiamare una procedura memorizzata significa che le query scritte nel suo codice verranno elaborate solo a metà. E solo se i valori dei loro parametri vengono modificati. Ma non tutto è così perfetto. Descriviamo innanzitutto gli aspetti positivi dell'utilizzo delle procedure:

Incapsulamento delle funzionalità: tutto il codice viene archiviato in un unico posto, facilitando l'accesso ad altre applicazioni. In questo modo, le procedure memorizzate sono simili alle funzioni del programma.

Isolamento dell'accesso ai dati: tutti gli utenti non hanno accesso alle righe della tabella, ma solo alle procedure memorizzate. Il che a sua volta aumenta il livello di sicurezza di tutti i dati.

Aumentare la velocità del server memorizzando nella cache e unendo le richieste.

In MySQL, le procedure memorizzate, in teoria, sono strutture legate a "questioni più elevate": la programmazione DBMS. Quindi tu ed io (come professionisti) almeno lentamente, ma... Ma torniamo alle procedure e descriviamo gli aspetti negativi del loro utilizzo:

Il carico sul server del database aumenta: la maggior parte del codice della procedura viene eseguita sul lato server. Questo DBMS è costruito su un modello client-server, che coinvolge diversi dispositivi.

La manipolazione dei dati diventa più complicata: quando sviluppi applicazioni, dovrai scrivere parte del codice sul lato client.

Il processo di trasferimento dei database su altri binari (DBMS) sta diventando sempre più complicato.

Procedure in phpMyAdmin

Innanzitutto, diamo un'occhiata all'uso delle procedure memorizzate in MySQL utilizzando phpMyAdmin come esempio. In questo modo ci sarà più facile comprendere questo tipo di struttura. Iniziamo!

Lanciamo la shell del software, selezioniamo il database di test sulla destra. Il mio database è mondiale. Quindi nel menu principale in alto, vai alla scheda “Procedure”. Qui fare clic su “Aggiungi procedura”.

Successivamente verrà visualizzata la finestra di dialogo "Aggiungi procedura". Compiliamo tutti i campi indicati in immagine. Specificare il nome e il tipo della procedura. Nella colonna "Definizione", inserisci account utente, e nei commenti (facoltativi) indichiamo a noi stessi che questo è solo un esempio di stored procedure.

Già in questa fase conosciamo le peculiarità della sintassi per la creazione delle procedure memorizzate MySQL. Nel campo “Definizione” scriviamo il corpo della struttura. Tieni presente che la query in esecuzione è compresa tra parole chiave INIZIO e FINE:

INIZIA SELEZIONA "CIAO, PAROLA!"; FINE

INIZIO SELEZIONA "CIAO, PAROLA!" ;

Questa richiesta non esegue alcuna azione con il database, ma visualizza solo un'iscrizione. Lo abbiamo indicato nel campo “Accesso ai dati SQL”.

Per completare la creazione della nostra prima procedura, fare clic su "Ok" in basso. Successivamente, il programma visualizza un messaggio “verde” che indica che la richiesta è stata completata con successo. Il suo codice è presentato di seguito. In MySQL, le procedure e le funzioni memorizzate vengono create utilizzando lo speciale comando CREATE PROCEDURE. Ma ne parleremo più avanti.

Ora eseguiamo la struttura creata per l'esecuzione. Per fare ciò, nella sezione “Procedure”, fare clic sul collegamento “Esegui”. Ma che vergogna è questa! Dov’è finito il nostro “verde” preferito? Perché il programma “giura” e “urla” di non avere abbastanza memoria allocata?

Dove stava cercando l'autore di questa pubblicazione...! Scusa, sono un po' confuso. Dopotutto l'autore sono io. Tranquilli, ora sistemeremo tutto! Questo errore si verifica perché il valore del parametro thread_stack nel file di configurazione principale rimane invariato. Per impostazione predefinita, vengono allocati 128 Kb per ogni flusso. Il limite di RAM allocato è sufficiente per eseguire query semplici, ma non sufficiente per le procedure.

Ciò dimostra ancora una volta che in MySQL vengono spese più risorse per l'esecuzione di trigger e procedure memorizzate.

Vai al file di configurazione my.ini e aumenta il limite RAM impostato per ciascun thread a 256 kb. Ora esegui nuovamente la procedura creata. Questa volta tutto è andato come previsto e il programma ha restituito il risultato senza errori.

Da notare che la chiamata avviene tramite il comando CALL, indicando il nome della procedura ed i parametri accettati (tra parentesi).

Esempio più complesso

Tuttavia, le funzionalità di phpMyAdmin sono più adatte per creare rapidamente procedure. E per sviluppare una procedura memorizzata in MySQL con un numero dinamico di argomenti (ad esempio), avrai bisogno di un software più conveniente. Perché:

phpMyAdmin non vuole “capire” correttamente procedure che non siano state create tramite un apposito costruttore.

Il programma non esegue strutture avviate sotto root e una password vuota, e a Denver creare un nuovo utente e accedere a phpMyAdmin con esso è un vero problema.

Se segui attentamente le mie pubblicazioni e soddisfi tutti i "desideri" in esse espressi, dovresti già avere MySQL Administrator installato. A questo proposito, devi solo scaricare MySQL Query Browser da questo link. È meglio usare questi due programmi insieme: creare procedure nel primo e testarle nell'altro. Andare:

In alto a sinistra, vai alla scheda “Catalogo”.

Selezionare il database desiderato e nel menu in alto fare clic su “Stored procedure” e in basso su “Crea Stored Proc”

Nella finestra dell'editor che appare, inserisci il codice della procedura e fai clic su "Esegui SQL".

CREATE DEFINER=`roman`@`localhost` PROCEDURA `proc5`() BEGIN dichiara un int; imposta a="SELECT COUNT(*) FROM città come a"; se (a > 1000) POI SELEZIONA "<1000"; ELSE SELECT ">1000"; FINE SE; FINE

CREATE DEFINER = ` roman ` @ ` localhost ` PROCEDURA ` proc5 ` () INIZIO dichiarare un int ; imposta un = "SELEZIONA COUNT(*) DA città come"; se (a > 1000 ) ALLORA SELEZIONARE "<1000" ; ALTRO SELEZIONA ">1000" ; FINISCI SE ;

Potresti aver notato che non stiamo passando alcun valore all'interno della procedura. Inoltre, MySQL può avere un numero sconosciuto di parametri in una procedura memorizzata, che può quindi essere passata attraverso nuove dichiarazioni di variabili situate all'interno dei cicli.

Per avviare la procedura, vai su MySQL Query Browser. Innanzitutto, inserisci il tuo account e la tua password, quindi a sinistra in "Esplora oggetti" troviamo la cartella con il database richiesto. Il resto della sequenza di azioni è mostrato nella figura successiva.

Esecuzione di una procedura in PHP

Ora diamo un'occhiata a come viene chiamata una procedura memorizzata MySQL in PHP. Per fare ciò dovremo “ridisegnare” leggermente il codice del nostro esempio precedente. Aggiungeremo un parametro di output alla procedura e modificheremo anche il codice di richiesta:

CREATE DEFINER=`root`@`localhost` PROCEDURA `proc6`(out col decimale) BEGIN SELECT COUNT(*) in col FROM città; FINE

CREATE DEFINER = ` root ` @ ` localhost ` PROCEDURA ` proc6 ` (out col decimale) INIZIO SELECT COUNT (*) in col FROM città;

Per chiamare da File PHP procedura e output dei risultati utilizzeremo le funzionalità della classe PDOStatement, creata appositamente per lavorare con il database tramite SQL

Questa classe è stata implementata relativamente di recente ed è supportata da PHP dalla versione 5.1.0. Prima dell'uso, ti consiglio di verificare la versione della lingua che stai utilizzando utilizzando la funzione incorporata phpversion().