Introduzione
Le stored procedure MySQL raggruppano più attività in uno e salva l'attività sul server per un uso futuro.
Le stored procedure semplificano la gestione del database e riducono il traffico di rete. Ad esempio, l'invio di una query al server MySQL elabora la query e restituisce i risultati. L'uso delle procedure memorizzate salva le query sul server in modo che possano essere eseguite in un secondo momento.
In questo tutorial imparerai a creare, elencare, modificare e rilasciare stored procedure.
Prerequisiti
- MySQL Server e MySQL Workbench installati
- Un account utente MySQL con privilegi di root
Cosa sono le stored procedure in MySQL?
Le stored procedure MySQL sono precompilate Istruzioni SQL memorizzato in un database. Sono subroutine contenenti un nome, un elenco di parametri e istruzioni SQL.
Tutti i sistemi di database relazionali supportano le stored procedure e non richiedono alcun pacchetto di ambiente di runtime aggiuntivo.
Come utilizzare le stored procedure?
Per richiamare le stored procedure, puoi utilizzare il CALL dichiarazione o altre procedure memorizzate. La prima volta che viene invocata una stored procedure, MySQL la ricerca nel catalogo del database, compila il codice, lo inserisce nella memoria cache e lo esegue.
Le esecuzioni successive nella stessa sessione eseguono stored procedure dalla memoria cache, rendendole estremamente utili per attività ripetitive.
Le stored procedure utilizzano parametri per passare valori e personalizzare i risultati. I parametri vengono utilizzati per specificare le colonne di una tabella in cui la query opera e restituisce risultati.
Le stored procedure possono anche includere IF , CASE e LOOP dichiarazioni di flusso di controllo che attuano proceduralmente il codice.
Crea procedura memorizzata
Crea una stored procedure in due modi:
1. Usa MySQL Shell
Utilizzare la seguente sintassi per creare una stored procedure in MySQL:
DELIMITER //
CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )
BEGIN
SQL statements
END //
DELIMITER ;
Per impostazione predefinita, la sintassi è associata al database in uso, ma puoi anche usare la sintassi per un altro database specificando il nome del database nel modo seguente:database_name.procedure_name .
Ecco il primo DELIMITER argomento imposta il delimitatore predefinito su // , mentre l'ultimo DELIMITER argomento lo riporta al punto e virgola ; . Per utilizzare più istruzioni, specifica diversi delimitatori come $$ .
Il nome della procedura viene dopo il CREATE PROCEDURE discussione. Dopo il nome della procedura, utilizzare le parentesi per specificare i parametri da utilizzare nella procedura, il nome del parametro, il tipo di dati e la lunghezza dei dati. Separa ogni parametro con una virgola.
Le modalità parametro sono:
IN– Utilizzare per passare un parametro come input. Una volta definita, la query passa un argomento alla stored procedure. Il valore del parametro è sempre protetto.OUT– Utilizzare per passare un parametro come output. È possibile modificare il valore all'interno della procedura memorizzata e il nuovo valore viene restituito al programma chiamante.INOUT– Una combinazione diINeOUTparametri. Il programma chiamante passa l'argomento e la procedura può modificareINOUTparametro, restituendo il nuovo valore al programma.
Ad esempio:
Eseguire la stored procedure chiamandola:
CALL procedure_name;
La query restituisce risultati per la stored procedure.
2. Utilizza MySQL Workbench
Un altro modo per creare una procedura memorizzata consiste nell'utilizzare MySQL Workbench Wizard. La procedura guidata è intuitiva e semplifica il processo poiché non devi posizionare delimitatori o preoccuparti del formato.
Segui questi passaggi:
Passaggio 1: Fare clic con il pulsante destro del mouse su Procedure archiviate nella finestra Navigatore di MySQL Workbench e scegli Crea stored procedure... per avviare la procedura guidata.
Passaggio 2: Specificare il nome della procedura e inserire il codice all'interno di BEGIN … FINE blocco.
Passaggio 3: Rivedi il codice e fai clic su Applica .
Passaggio 4: Conferma l'esecuzione facendo clic su Applica e crea la procedura cliccando su Fine .
Passaggio 5: Esegui la procedura per vedere se funziona. Crea una nuova scheda SQL per l'esecuzione di query.
Passaggio 6: CALL la procedura nella scheda SQL e fare clic su Esegui .
Se non vengono restituiti errori, MySQL esegue la procedura memorizzata e visualizza i risultati.
Elenca le procedure memorizzate
Esistono tre modi per visualizzare un elenco di tutte le stored procedure:
1. Usa MySQL Shell
Per ottenere un elenco di tutte le stored procedure a cui hai accesso, comprese le relative caratteristiche, utilizza la seguente sintassi:
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
Lo SHOW PROCEDURE STATUS istruzione restituisce un output lungo. L'istruzione visualizza i nomi e le caratteristiche delle procedure archiviate a cui si ha accesso sul server.
Scorri l'output per trovare le procedure attualmente sul server.
Il LIKE argomento trova le procedure memorizzate contenenti una parola specifica nel loro nome. Usa % per sostituire qualsiasi numero di caratteri, incluso zero.
Ad esempio:
Il WHERE argomento consente di elencare le stored procedure solo in un determinato database.
Ad esempio:
In questo esempio, l'istruzione restituisce solo le procedure memorizzate per 'elenco_clienti ' database.
2. Utilizza il dizionario dei dati
Lo schema_informativo database contiene una tabella chiamata routine , che contiene informazioni sulle procedure memorizzate e sulle funzioni relative a tutti i database sul server MySQL corrente.
Utilizzare la seguente sintassi per visualizzare tutte le stored procedure per un database:
SELECT
routine_name
FROM
information_schema.routines
WHERE
routine_type = 'PROCEDURE'
AND routine_schema = 'database_name';
3. Utilizza MySQL Workbench
Per un approccio GUI alla visualizzazione delle stored procedure, utilizzare MySQL Workbench. Segui questi passaggi per visualizzare le stored procedure:
Passaggio 1 :fai doppio clic sul database che desideri utilizzare nel Navigatore sezione.
Passaggio 2 :Espandi le stored procedure elemento a discesa.
Questo elemento mostra tutte le stored procedure per il database corrente.
Modifica procedura memorizzata
Alterare una stored procedure significa cambiarne le caratteristiche di una procedura. Non c'è nessuna dichiarazione in MySQL per modificare i parametri o il corpo di una procedura memorizzata. Per modificare i parametri o il corpo, rilascia la stored procedure e creane una nuova.
Modificare una procedura memorizzata in due modi:
1. Usa MySQL Shell
Modificare una caratteristica di una procedura utilizzando ALTER PROCEDURE dichiarazione. Ad esempio, possiamo aggiungere un commento a una procedura che abbiamo creato in precedenza. La sintassi è:
ALTER PROCEDURE procedure_name
COMMENT 'Insert comment here';
2. Utilizza MySQL Workbench
MySQL Workbench GUI consente agli utenti di modificare una procedura memorizzata in cui gli utenti possono aggiungere parametri o modificare il codice. MySQL Workbench cali la stored procedure esistente e crea uno nuovo dopo che sono state apportate le modifiche.
Segui questi passaggi:
Passaggio 1: Nella sezione Navigatore, fare clic con il pulsante destro del mouse sulla procedura memorizzata che si desidera modificare. Seleziona la Procedura di modifica archiviata... elemento.
Passaggio 2: Quando si apre la scheda, apportare le modifiche desiderate alla stored procedure esistente e fare clic su Applica .
Passaggio 3: Una finestra di revisione di SQL Script appare mostrando il processo, eliminando la stored procedure esistente e creandone una nuova contenente le modifiche.
Fai clic su Applica e poi Fine nella finestra successiva per eseguire lo script.
Procedura archiviata di rilascio
Per eliminare (eliminare) una procedura:
1. Usa MySQL Shell
Elimina una procedura memorizzata dal server utilizzando la DROP PROCEDURE dichiarazione.
La sintassi di base è:
DROP PROCEDURE [IF EXISTS] stored_procedure_name;
Il IF EXISTS parametro elimina la procedura memorizzata solo se esiste sul server. Immettere il nome della procedura memorizzata al posto di stored_procedure_name sintassi.
Ad esempio:
Poiché non esiste una procedura denominata "test ' sul server, l'output indica che 0 righe sono state interessate e che la procedura specificata non esiste.
Eliminazione di una procedura inesistente senza il IF EXISTS il parametro restituisce un errore.
2. Utilizza MySQL Workbench
Per eliminare una stored procedure con MySQL Workbench, attenersi alla seguente procedura:
Passaggio 1: Espandi la voce Stored Procedure nella sezione Navigatore. Fare clic con il pulsante destro del mouse sulla stored procedure che si desidera eliminare e scegliere Elimina stored procedure... nel menu contestuale.
Passaggio 2: Nella finestra di conferma, fai clic su Rilascia ora per eliminare la procedura memorizzata.
Questa azione elimina definitivamente la procedura.
Vantaggi e svantaggi delle stored procedure MySQL
Le stored procedure presentano numerosi vantaggi e svantaggi in quanto si adattano a esigenze specifiche. Di seguito sono riportati alcuni vantaggi e svantaggi.
Vantaggi dell'utilizzo delle stored procedure
I vantaggi delle stored procedure sono:
Riduzione del traffico di rete
Le stored procedure aiutano a ridurre il traffico di rete tra le applicazioni e MySQL Server mantenendo tutta la logica di programmazione sul server. Invece di inviare più risultati di query attraverso la rete, le app inviano solo il nome della procedura e l'input del parametro.
Sicurezza migliorata
L'amministratore del database concede alle app i privilegi per chiamare e accedere solo a procedure archiviate specifiche senza concedere loro l'accesso diretto alle tabelle. Le stored procedure aiutano a prevenire gli attacchi di script injection poiché i parametri di input vengono trattati come valori e non come codice eseguibile.
Logica aziendale centralizzata
Le stored procedure incapsulano la logica aziendale riutilizzabile da più applicazioni. Ciò aiuta a ridurre la duplicazione della stessa logica in molte applicazioni diverse e rende il database più coerente.
Svantaggi dell'utilizzo delle stored procedure
Gli svantaggi delle stored procedure sono:
Utilizzo delle risorse
L'utilizzo di molte procedure memorizzate e operazioni logiche fa aumentare notevolmente l'utilizzo della memoria e della CPU per ogni connessione.
Nessuna portabilità
Non è facile trasferire le stored procedure scritte in una lingua specifica da un'installazione all'altra. Affidarsi a una procedura memorizzata lega anche l'utente a un determinato database.
Risoluzione dei problemi e test
MySQL non fornisce utilità per il test e il debug delle procedure memorizzate, quindi può essere difficile eseguirne il debug. Lo sviluppo e il mantenimento di stored procedure richiedono una conoscenza approfondita. Questa è una sfida per i nuovi sviluppatori e comporta costi di manutenzione aggiuntivi.