Parlando in modo estremamente semplice potremmo dire che le Stored Procedures (dette anche SQL-invoked routines o più semplicemente "procedure memorizzate") sono dei programmi, scritti generalmente in linguaggio SQL (ma non solo), che vengono memorizzati nei database; ad essi viene associato un nome, cioè un identificativo univoco che verrà utilizzato dal database server per effettuarne la chiamata.
Per la creazione di Stored Procedures alcuni Database Managers hanno a disposizione dei veri e propri linguaggi procedurali come per esempio PL/pgSQL per PostgreSQL, PL/SQL per Oracle o Informix Stored Procedure Language (SPL) che è un'estensione di SQL appositamente dedicata a questo scopo.
In MySQL le Stored Procedures sono disponibili soltanto a partire dalla versione 5, questo aggiornamento ha permesso di colmare una lacuna che in un DBMS così diffuso non poteva essere ignorata.
Quando si lavora a progetti di un certo livello, può essere infatti molto importante avere gli strumenti necessari per eseguire alcune parti della logica di un'applicazione all'interno dello spazio stesso dei processi. Il linguaggio SQL è per natura dichiarativo, quindi le Stored Procedures rappresentano una sua estensione procedurale.
Per chiarire quanto appena esposto è necessario fare un piccolo passo indietro e introdurre una distinzione concettuale fondamentale per i linguaggi:
- Un linguaggio viene definito dichiarativo o logico quando le istruzioni descrivono le relazione che intercorrono tra i dati; lo sviluppatore in questo caso descrive l'insieme delle relazioni che sussistono tra i dati e il risultato atteso.
- Un linguaggio viene invece denominato procedurale quando per esso non si ragiona in termini di relazioni tra dati ma in termini di assegnazione di valori ad uno spazio di memoria; i linguaggi procedurali fanno infatti parte della più ampia famiglia dei linguaggi imperativi.
Il vantaggio relativo all'introduzione delle Stored Procedures è quindi evidente, esse consentono infatti di creare e riutilizzare applicazioni che sono sempre disponibili per il DBMS e le operazioni di manipolazione dei dati.
Vantaggi e svantaggi nell'uso delle Stored Procedures
Le Stored Procedures possono essere suddivise in due gruppi di sotto-programmi dotati di caratteristiche differenti:
- Procedure: non devono restituire valori ma accettare parametri di input e di ouput.
- Funzioni (User Defined Functions o più semplicemente UDF): restituiscono un valore e accettano parametri di input ed output. Le abbiamo viste in un nostro precedente articolo.
In entrambi i casi questi sotto-programmi portano alcuni vantaggi sostanziali nell'amministrazione dei dati in particolare per quanto riguarda la semplificazione delle comunicazioni tra client e server:
- Le Stored Procedures vengono compilate solo una volta nel momento in cui vengono inserite; da quel momento in poi esse potranno essere eseguite attraverso una semplice chiamata con non pochi vantaggi a livello di performances.
- Una Stored Procedures è una libreria di funzioni sempre disponibile; questo vuol dire che sarà possibile utilizzarla per interrogare strutture di dati anche molto complesse avendone soltanto una conoscenza parziale.
- Se sono disponibili le Stored Procedures non sarà più necessario assegnare agli utenti i permessi per la lettura e la modifica delle tabelle, basterà concedere loro l'utilizzo delle routines.
- Le Stored Procedures risolvono il problema della riscrittura delle interrogazioni permettendo al client di effettuare chiamate a loro carico anche per operazioni molto complesse; ciò implica un minore flusso di dati con il DBMS e un conseguente aumento delle risorse disponibili.
Vediamo ora qualche svantaggio nell'utilizzo delle Stored Porcedures:
- In generale ogni DBMS (parliamo almeno dei più diffusi) ha il suo linguaggio appositamente dedicato per la creazione di procedure e funzioni, quindi se si è abituati a lavorare con uno di essi questo difficilmente potrà essere riutilizzato per altri Database Manager.
- Se è possibile lavorare sulle tabelle esclusivamente tramite Stored Procedures le operazioni consentite saranno soltanto quelle previste dalle routine.
- Ricordiamoci che stiamo parlando di "programmi", la logica dell'applicazione verrà quindi spostata sul DBMS che dovrà essere in grado di sostenere il carico di lavoro previsto.
- Parlando in particolare di MySQL, è bene tener conto del fatto che esso consente di definire soltanto Stored Procedures scritte in linguaggio SQL.
Definizione delle Stored Procedures
Nella definizione delle Stored Pocedures è prevista l'introduzione di tre diversi parametri.
- IN: rappresenta gli argomenti in ingresso della routine; a questo parametro viene assegnato un valore quando viene invocato il sotto-programma; il parametro utilizzato non subirà in seguito modifiche.
- OUT: è il parametro relativo ai valori che vengono assegnati con l'uscita dalla procedura; questi parametri diventano disponibili per gli utenti
- INOUT: rappresenta una combinazione tra i due parametri precedenti.
Per definire una Stored Procedure sarà possibile utilizzare una sintassi simile alla seguente:
#definizione di una procedura CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE nome_procedura ([parametri[,...]]) [caratteristiche ...] corpo_della_routine #definizione UDF CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION nome_procedura ([parametri[,...]]) RETURNS type [caratteristiche ...] corpo_della_routine
Il comando CREATE svolge una funzione abbastanza conosciuta dato che presiede a tutte le operazioni di creazione ex novo in SQL. DEFINER non è un parametro obbligatorio, serve però per assegnare un proprietario alla routine, nel caso in cui questo non venga specificato verrà considerato come owner predefinito l'utente corrente.
Il nome della procedura viene introdotto da PROCEDURE, nella UDF si utilizza invece FUNCTION, esse si differenziano anche per i parametri accettati che saranno le tre voci precedentemente elencate per le procedure, mentre lo standard non prevede il passaggio di OUT e INOUT per le funzioni.
La clausola RETURNS è valida per la sintassi delle UDF, queste infatti restituiscono un valore e per questo ad esso è associato il relativo tipo di dato.
Un altro elemento importante da introdurre è quello relativo ai delimitatori, infatti, per definire una Stored Procedure da linea di comando è necessario introdurre un nuovo delimitatore per terminare il blocco di istruzioni:
mysql> delimiter // mysql> CREATE PROCEDURE nome_procedura (p1 INT) -> BEGIN -> blocco istruzioni -> END -> // mysql> delimiter ;
In pratica l'istruzione DELIMITER iniziale ha lo scopo di comunicare a MySQL che (fino a quando non verrà ordinato diversamente) il delimitatore utilizzato alla fine dell'istruzione non sarà più il "punto e virgola", diversamente infatti il DBMS interpreterebbe quale terminazione il primo "punto e virgola" presente nel blocco di istruzioni; solo alla fine delle istruzioni DELIMITER reintroduce il delimitatore ";" per permettere l'utilizzo dei normali comandi SQL.
Esempi di procedure ed utilizzo dei parametri
Come per tutti i programmi, daremo il battesimo alle Stored Procedures con il consueto "Ciao Mondo":
mysql> CREATE PROCEDURE proc_ciao () SELECT 'Ciao Mondo' // mysql> CALL proc_ciao ()// +----------------+ | Ciao Mondo | +----------------+ | Ciao Mondo | +----------------+
Tutto molto semplice, alla procedura non sono stati passati parametri, il suo scopo è solamente quello di stampare una stringa e ciò avviene attraverso l'evocazioni della routine con il comando CALL di cui parleremo tra breve
Ma vediamo ora un semplicissimo esempio per la creazione di una routine basata sul parametro IN:
mysql> CREATE PROCEDURE proc_in (p INT(3)) SET @x = p//
Da notare che IN è considerato come parametro di default quindi in questo caso non è necessario specificarlo.
A questo punto è invece necessario invocare la nostra procedura; le routine vengono richiamate attraverso la seguente sintassi basata sul comando CALL:
CALL nome_procedura ([parametro[,...]])
In MySQL, al momento dell'invocazione è richiamato anche il comando USE associato al nome del database corrente; questo compito non viene delegato all'utilizzatore, il comando verrà infatti richiamato implicitamente e una volta eseguita la procedura richiesta esso verrà annullato. Se invece si desidera invocare procedure archiviate in altri database sarà necessario specificare il nome di entrambi.
Nel caso del nostro semplice esempio, richiameremo la procedura denominata proc_in in questo modo:
mysql> CALL proc_in (123)//
Dove 123 è il parametro che viene passato alla procedura; a questo punto non resta che utilizzare l'output della nostra routine:
mysql> SELECT @x// +----------+ | @x | +----------+ | 123 | +----------+
Niente di complicato, come si può facilmente osservare; la procedura che abbiamo creato prevede un parametro di input; nel corpo del sotto-programma è stata impostata una variabile chiamata semplicemente x il cui valore sarà qualsiasi valore associato al parametro p.
Quindi, passando come parametro alla procedura l'intero 123 (che viene associato a p), sarà possibile ottenere lo stesso valore interrogando il database con una SELECT applicata alla variabile x.
Analizziamo ora un esempio relativo all'utilizzo del parametro OUT:
mysql> CREATE PROCEDURE proc_out (OUT p INT) -> SET p = -2 // mysql> CALL proc_out (@y)// mysql> SELECT @y// +------+ | @y | +------+ | -2 | +------+
In questo caso p rappresenta il nome di un parametro di output che viene passato come valore alla variabile y introdotta nel momento in cui viene espresso il comando che invoca la procedura. Nel corpo della routine il valore del parametro viene indicato come pari all'intero negativo -2, a questo punto spetta ad OUT segnalare a MySQL che il valore sarà associato tramite la procedura; in questo modo si ottiene un risultato analogo all'istruzione SET @y = -2.
In entrambi i casi appena presentati il corpo della procedura non prevede interrogazioni, ma basta creare un blocco di istruzioni leggermente più complesso per integrarle:
CREATE PROCEDURE p_sel () BEGIN SET @x = 2; SET @y = 2; INSERT INTO tbl VALUES (@x); SELECT field * @x FROM tbl WHERE field >= @y; END; //
Il blocco di istruzioni delimitato da BEGIN ed END contiene una serie di comandi SQL che hanno il compito di impostare (SET) il valore di due variabili, da riutilizzare per due operazioni di manipolazione dei dati: un inserimento (statement INSERT) e un query di selezione (statement SELECT).
Tutte le operazioni elencate nel blocco verranno eseguite semplicemente richiamando la procedura e non dovranno essere quindi riscritte.
Conclusioni
In questo breve articolo abbiamo introdotto il discorso riguardante le Stored Procedures in MySQL proponendo, a scopo introduttivo, alcuni semplici esempi per comprenderne la sintassi, i comandi e i costrutti ricorrenti.
Ma il discorso su questi importanti sotto-programmi scritti in linguaggio SQL non si conclude qui; vi sono altri argomenti fondamentali da analizzare come la dichiarazione della variabili, il loro utilizzo, il ruolo dei cursori, e i costrutti per il controllo del flusso nelle procedure.
Tutti argomenti a cui dedicheremo presto degli approfondimenti.
