I cursors in MySQL

Quello di "cursore" è un concetto molto diffuso in informatica, si pensi per esempio al cursore del mouse. Per quanto riguarda i database il concetto a cui è legata questa parola è un po' differente: con esso è infatti possibile definire un puntatore verso uno specifico recordset appartenente ad una tabella.

In parole povere si tratta di un'istruzione scritta in linguaggio SQL, definibile all'interno di una stored procedure, utilizzabile per svolgere alcune operazioni anche complesse in modo semplice e veloce; grazie ai cursori potremmo per esempio:

• iterare i record presenti in una tabella;
• effettuare la copia selettiva tra due tabelle di dati modificati;
• modificare tabelle di grandi dimensioni tramite un'unica chiamata.

L'ultimo caso descritto è particolarmente significativo, vi sono tabelle che richiederebbero un lavoro molto lungo e complesso se per il loro aggiornamento si potessero sfruttare unicamente le istruzioni basate sul comando UPDATE; ecco quindi che può tornarci molto utile fare riferimento ai cursors.

L'utilizzo dei cursors non è sempre consigliato, si tratta di una tecnica che non tutti gli amministratori di basi di dati apprezzano, per alcuni di essi i cursori non sono un sinonimo di buona programmazione; fatto sta che a partire dalla versione 5 di MySQL essi sono disponibili anche per gli utilizzatori di questo DBMS e che quindi vale la pena di affrontare l'argomento se non altro per proporre un'alternativa alle procedure ritenute più "ortodosse".

I limiti dei cursors in MySQL

Oltre alle avvertenze riguardanti le opinioni non sempre positive espresse su di essi dagli utilizzatori, è anche necessario dire che in MySQL i cursors, introdotti solo da poco tempo, sono affetti da limiti non riscontrabili in altri Database Server; tra di essi ve ne sono alcuni che vale la pena di sottolineare:

• i cursors sono di sola lettura, non sarà quindi possibile utilizzarli in associazione con comandi impiegati per la modifica dei dati come UPDATE WHERE CURRENT OF oppure DELETE WHERE CURRENT OF;
• sono non scrollable, cioè agiscono solo in senso mono-direzionale, a causa di questo sarà quindi possibile elaborare le informazioni soltanto nell'ordine in cui esse vengono fornite dal DBMS;
• sono asensitive (letteralmente "insensibili"), non sarà quindi possibile effettuare modifiche alla tabella a cui è riferito un cursore quando i record che le appartengono sono in fase di lettura; non rispettare la logica asensitive dei cursors in MySQL non porta all'impossibilità di concludere l'esecuzione di una procedura ma all'ottenimento di risultati incoerenti e non prevedibili;
• hanno una vita breve, non vengono memorizzati permanentemente dal sistema e durano soltanto fino a quando non viene lanciato un comando COMMIT, per questo motivo sono chiamati anche non holdable o "effimeri";
• sono "anonimi", nel loro caso infatti vengono utilizzati gli handlers delle istruzioni come ID per i cursori e tramite essi sono gestiti.
• in MySQL non esiste un linguaggio appositamente dedicato alla creazione e alla definizione dei cursori come nel caso di T-SQL e PL/SQL, vengono invece utilizzate le comuni istruzioni SQL.

Infine, limite non meno importante da segnalare, è necessario dire che soprattutto nel caso di operazioni che coinvolgono grandi quantità di dati i cursors possono essere veramente molto lenti nella restituzione degli output.

Dichiarazione e sintassi dei cursors: OPEN, FETCH e CLOSE

Se la scarsa considerazione che alcuni nutrono per questo strumento e i tanti limiti che i cursors presentano in MySQL non hanno spaventato il lettore, si potrà approfondire l'argomento introducendo la semplice sintassi relativa alla loro dichiarazione.

Seguendo la stessa procedura utilizzata per altri costrutti come per esempio le variabili o le condizioni, un cursor deve essere dichiarato precedentemente al suo utilizzo; per far questo è necessario utilizzare la seguente sintassi:

DECLARE nome_cursore CURSOR FOR istruzione_SELECT;

Come è semplice notare, la prima cosa da fare è dare un nome al cursor che vogliamo introdurre, questo passaggio potrebbe risultare oscuro a chi ha letto il precedente paragrafo in cui i cursors vengono definiti come "anonimi"; in realtà non vi è nulla di incoerente, il nome del cursor viene utilizzato per la sua definizione ma per il DBMS esso diviene riconoscibile attraverso l'handler dell'istruzione che funge come suo ID.

Non vi sono regole particolari per i nomi ma è comunque bene tenere conto che una stored procedure può contenere anche più di un cursore, quindi è buona norma scegliere nomi riferiti all'azione svolta in modo da non generare confusione.

CURSOR FOR introduce l'istruzione basata sul comando SELECT associato al cursore e sarà possibile utilizzare qualsiasi forma di SELECT purché sintatticamente valida, inoltre, è consentito introdurre la clausola WHERE argomentata tramite variabili o parametri.

Dopo aver dichiarato un cursore esso diventa utilizzabile, ma per far questo esso dovrà essere aperto tramite un comando estremamente semplice:

OPEN nome_cursore;

dove nome_cursore è appunto il nome associato al cursore in fase di dichiarazione; l'apertura (o attivazione) permette di rendere disponibile il comando FETCH seguito dalle variabili che contengono i valori da elaborare:

FETCH nome_cursore INTO nome_variabile1 , nome_variabile2, ...

Ognuna delle variabili introdotte fa riferimento ad una colonna del record (la prima variabile per la prima colonna e così via), per poter essere utilizzate queste variabili dovranno essere state dichiarate in precedenza e di tipo corretto; non vi sono regole particolari per il loro nome ma è meglio che non vi sia corrispondenza tra i nomi delle variabili e quelli delle colonne.

La terminazione di un cursor viene espressa attraverso un semplice comando di chiusura:

CLOSE nome_cursore

Quest'ultimo passaggio può spesso essere omesso dato che il cursor viene generalmente terminato insieme al blocco BEGIN .. END.

Semplice esempio pratico di utilizzo di un cursor in MySQL

È ora arrivato il momento di mostrare un semplice esempio di utilizzo di un cursore all'interno di una stored procedure:

DROP PROCEDURE IF EXISTS proc
//

CREATE PROCEDURE proc(IN p_in INT, OUT p_out VARCHAR(20))

BEGIN

   DECLARE l_nome VARCHAR(20);

   DECLARE trova_nome CURSOR FOR SELECT nome FROM nomi WHERE nome_id = p_in;

   OPEN trova_nome;

   FETCH trova_nome INTO l_nome;

   CLOSE trova_nome;

   SET p_out = l_nome;

END;
//

Analizziamo ora la nostra stored procedure contenente il cursore in modo da elencarne i passaggi salienti:

• innanzitutto abbiamo controllato l'esistenza di una stored procedure omonima preesistente grazie alla clausola IF EXISTS, DROP PROCEDURE ci ha consentito di procedere alla sua eventuale eliminazione;
• grazie al carattere di delimitazione "//" abbiamo isolato la stored procedure dal resto dei comandi SQL normalmente delimitati tramite il consueto punto e virgola;
• abbiamo poi introdotto la stored procedure (denominata proc) e definiti i relativi parametri di input e output;
• il blocco BEGIN .. END è stato definito per l'introduzione delle variabili, del cursore e per delimitare le funzioni relative alla stored procedure;
• abbiamo definito la variabile 1_nome e dichiarato il suo tipo di dato (VARCHAR di lunghezza massima pari a 20);
• abbiamo poi dichiarato il cursor a cui viene associato un nome (trova_nome) e il comando SELECT; lo scopo della selezione è quello di estrarre dalla tabella nomi un valore nome corrispondente al record in cui il contatore nome_id è uguale al parametro di input;
• abbiamo attivato il cursor con OPEN rendendo disponibile il comando FETCH a cui è stato possibile passare la variabile precedentemente definita;
• il cursore è stato poi chiuso, una volta settato il valore della variabile di output è stato poi possibile chiudere il blocco BEGIN .. END e delimitare la chiusura della stored procedure.

Per quanto la piccola applicazione proposta sia piuttosto semplice, essa mostra tutti i passaggi necessari per l'introduzione di un cursore all'interno di una stored procedure; tra l'altro, si noti come in essa non ci siamo limitati ad associare al cursore una SELECT in forma semplice ma una query di estrazione resa più precisa dalla presenza della clausola WHERE che, nel nostro caso, ha avuto come termine di paragone un parametro di input introdotto nella definizione della stored procedure.

Per quanto riguarda invece la fase relativa alla chiamata, quest'ultima non viene effettuata naturalmente sul cursore ma sulla stored procedure che lo contiene:

set @a = 1 //
Query OK, 0 rows affected (0.02 sec)

call proc(@a,@b) //
Query OK, 0 rows affected (0.06 sec)

SELECT @b //
+-------+
| @b    |
+-------+
| Mario |
+-------+
1 row in set (0.00 sec)

Il cursor restituirà un valore sulla base della SELECT ad esso associata, nel nostro caso abbiamo operato su una sola riga ma sarà semplice implementare quanto prodotto per processare simultaneamente più righe.

Per dovere di completezza è necessario dire che una volta eseguita la SELECT prevista dal FETCH del cursor (cioè una volta letti tutti i risultati richiesti dall'interrogazione), il DBMS produce un errore di tipo 1329, o No data to fetch, corrispondente al valore 02000 di SQLESTATE. Per evitare che questi errori siano visibili in output bisognerà quindi far ricorso alla gestione delle eccezioni prevista per le stored procedures.

Conclusioni

In questa breve trattazioni abbiamo descritto le caratteristiche, la sintassi e le modalità di utilizzo dei cursors all'interno delle stored procedures; i cursors sono un nuovo strumento che MySQL mette a disposizione a partire dalla versione 5 del DBMS, con essi viene introdotta una tecnica che possiede molti limiti e che non gode dell'approvazione unanime degli amministratori di database, vale comunque la pena di trattare questo argomento per fornire uno strumento in più agli utilizzatori che potrebbe rivelarsi utile e valido nello svolgimento di numerose procedure.