I Cursori di MariaDB/MySQL: un breve Tutorial

English version

In MariaDB e MySQL, i Cursori possono essere utilizzati solo dentro gli Stored Program (Stored Procedure e Function, Trigger, Eventi), sono lenti, e hanno funzionalità molto limitate. Detto questo, in alcuni casi possono comunque essere utili. Questa pagina spiega come utilizzarli, con un esempio triviale.

Ecco una Stored Procedure che usa un Cursore:

DELIMITER ||
    
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
    (1),
    (2),
    (3);
    
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
    READS SQL DATA
BEGIN
    DECLARE `eof` BOOL;
    DECLARE `val` INT;
    
    DECLARE `crs` CURSOR FOR
        SELECT `c`
            FROM `test`.`tab1`;
    
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET eof = TRUE;
    
    OPEN `crs`;
    `lp`: LOOP
        FETCH `crs` INTO `val`;
        IF `eof` IS TRUE THEN
            LEAVE `lp`;
        END IF;
        
        SELECT `val`;
    END LOOP;
    CLOSE `crs`;
    
    -- workaround for server bug
    DO (SELECT `c` FROM `test`.`tab1` WHERE FALSE);
END;
    
||
DELIMITER ;


Questa Procedura sarebbe completamente inutile nel Mondo Reale, ma ci serve come esempio: restituisce un diverso ResultSet per ogni riga che si trova nella tabella in test.tab1. Ora vado a spiegare come l’ho implementata.

Prima di tutto, dichiaro (DECLARE) alcune variabili. eof è molto importante, perché indica se il Cursore ha già letto tutte le righe restituite dalla query; per ora, naturalmente, è FALSE. Lo useremo più tardi. Per inciso, il tipo BOOL non esiste in Maria e My, e viene trattato come un TINYINT, così come TRUE è sinonimo di 1 e FALSE è sinonimo di 0. Ma utilizzare le parole chiave BOOL, TRUE e FALSE è un modo per rendere il codice più leggibile. val è la variabile che contiene il valore che ci accingiamo a leggere dalla tabella.

Poi dichiaro il Cursore. In questa istruzione vediamo il nome del Cursore (crs) e la query a cui è associato. Questa query deve essere scritta per esteso in questo punto, perché non è possibile utilizzare l’SQL dinamico con i Cursori. Vi è però un workaround: vedi il mio post Cursors for Dynamic SQL (prima o poi lo tradurrò in italiano).

A questo punto dichiaro l’HANDLER per gli errori che ricadono nella classe NOT FOUND. Qui ci interessa l’errore 1329, che si verifica quando il Cursore ha già letto tutte le righe restituite dalla query e tenta di leggerne un’altra. Ma questo lo spiegherò meglio più avanti.

A questo punto apro (OPEN) il Cursore. E’ a questo punto che la query viene eseguita.

E poi c’è il ciclo LOOP sulle righe. Che cosa accade qui? Beh, prima cerchiamo di estrarre una nuova riga dal ResultSet, con FETCH. Se l’operazione ha successo, il valore dell’unico campo presente nella SELECT viene copiato nella variabile val. Controlliamo il valore di eof e, siccome è FALSE, saltiamo la IF. E produciamo un ResultSet con il valore che abbiamo letto, che viene inviato al client. Questo avviene per tre volte, perché abbiamo creato una tabella con tre righe.

La quarta volta, l’istruzione FETCH fallisce, perché non ci sono più righe da leggere. Viene allora generato un errore, e quindi viene eseguito l’HANDLER che abbiamo definito prima. Qui la variabile eof viene impostata a TRUE. Siccome è un CONTINUE HANDLER, dopo questa istruzione, l’esecuzione della Routine riprende da dov’era rimasta e troviamo la IF. Questa volta eof è TRUE, perciò entriamo nella IF. E troviamo un’istruzione LEAVE associata all’etichetta lp. lp è il LOOP, perciò a questo punto ne usciamo.

Ora possiamo chiudere (CLOSE) il Cursore.

Ma ora abbiamo un piccolo problema. Le versioni di MySQL più vecchie della 5.6, e MariaDB, hanno un bug (che però MariaDB sta risolvendo) a causa del quale l’Errore 1329 sopravvive dopo l’HANDLER.Se non facciamo niente per rimuoverlo, verrà inviato al client. Ma se eseguiamo una query su una tabella fisica, l’Errore viene dimenticato (il motivo esatto per cui questo accade va oltre gli scopi di questo post). Non vogliamo che venga restituito un altro ResultSet, perciò utilizziamo la SELECT come subquery dell’istruzione DO (che non restituisce mai nulla). Usiamo WHERE FALSE per evitare di appesantire la Procedura.

Esercizi

Questa volta vi darò qualche semplice esercizio. Se li risolvete potete postare il vostro codice nei commenti, se volete. Se non riuscite a risolverli potete postare delle domande, in modo che io possa aiutarvi. Vi suggerisco di svolgere gli esercizi, perché non si impara molto senza la pratica.

1) Modificate la Procedura demo in modo che termini se incontra più di un valore NULL.

2) Scrivete una nuova Procedura che legge le righe dalla tabella PROCESSLIST del database information_schema, e uccide (KILL) tutte le connessioni che sono in idle ed esistono da più di N secondi – dove N è un parametro della Procedura.

Divertitevi!

About these ads

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...