Foutafhandeling

In het laatste voorbeeld van het hoofdstuk Cursor zien we een foutmelding op het einde. Deze fout treedt op omdat de lus, zoals getoond, geen uitgangsvoorwaarde bevat en als gevolg daarvan bereikt de cursor het einde van de gegevensset en blijft het proberen records te benaderen die niet bestaan.

Een oplossing voor dit probleem zou een extra SELECT COUNT () … query aan het begin van de procedure kunnen zijn en dit resultaat gebruiken om de lus slechts een bepaald aantal keren te laten uitvoeren. Echter verschillen de dbms systemen hoe ze omgaan met het afhandelen van fouten. Zo kan men in MySQL zonder extra overhead een error handler definiëren via een DECLARE … CONDITION-blok. In MSSQL kunnen eventuele fouten opgevangen door een TRY … CATCH-blok.

In MySQL zijn er twee stappen nodig voor het definiëren van een error handler voor een opgeslagen procedure, zoals zal worden uitgelegd in de volgende paragrafen.

1. Bepaal de foutvoorwaarde die afgehandeld moet worden
2. Bepaal een Handler voor de benoemde foutvoorwaarde

Bepaal de foutvoorwaarde die afgehandeld moet worden

De eerste stap is om te bepalen welke foutcode we willen opvangen en een unieke naam aan die fout toewijzen. Hiervoor gebruikt men een DECLARE CONDITION … FOR instructie.

Hier is een voorbeeld, dat een naam bepaalt voor MySQL error code 1050 (tabel bestaat reeds):

BEGIN TRY
      
END TRY
BEGIN CATCH
    IF @@error = 2714 
    BEGIN
 
    END
END CATCH

DECLARE err_table_exists CONDITION FOR 1050;

In plaats van de MySQL error code, is het mogelijk om fouten op te vangen met behulp van hun SQLSTATE code. Hier is een voorbeeld van deze aanpak:

DECLARE err_table_exists CONDITION 
    FOR SQLSTATE '42S01';

Een volledige lijst van MySQL foutcodes en hun equivalente SQLSTATE waarden kunnen worden verkregen uit de MySQL-handleiding op http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html.

Bepaal een Handler voor de benoemde foutvoorwaarde

De tweede stap is het definiëren van een handler voor de foutconditie. Dit wordt bereikt door een DECLARE … HANDLER FOR instructie, welk de SQL-opdrachten bevat die moeten worden uitgevoerd wanneer de fout optreedt. Hier is een voorbeeld, die aan een variabele een nieuwe waarde toewijst wanneer de “tabel al bestaat” fout optreedt en vervolgens de routine zal verlaten:

DECLARE EXIT HANDLER FOR err_table_exists
BEGIN
SET @table=-1;
END;

Het is ook mogelijk om de op te vangen foutcondities in de DECLARE … HANDLER zelf op te nemen.. Om dit te illustreren, zijn de volgende twee groepen van instructies equivalent:

DECLARE err_table_exists CONDITION FOR 1050;
DECLARE EXIT HANDLER FOR err_table_exists
BEGIN
SET @table=-1;
END;

DECLARE EXIT HANDLER FOR 1050
BEGIN
SET @table=-1;
END;

Zodra de handler code uitgevoerd is, zal MySQL ofwel de opgeslagen routine verlaten of verder gaan met verwerken, afhankelijk van het gebruikte handler type. Binnen een opgeslagen routine zijn twee soorten handlers mogelijk:

• Een EXIT handler, die ervoor zorgt dat de opgeslagen routine stopt met uitvoeren wanneer de fout plaatsvindt, en
• een CONTINUE handler, die ervoor zorgt dat de opgeslagen routine verder zal gaan met uitvoeren nadat de fout heet paatsgevonden.

MySQL is kieskeurig over de volgorde waarin de DECLARE-instructies in opgeslagen routines verschijnen. Om foutmeldingen te voorkomen: Plaats de variabelen- en conditiedeclaraties voor de cursor en handler declaraties.

De EXIT Handler

Een EXIT handler zorgt ervoor dat MySQL de verwerking van een opgeslagen routine beëindigd als de opgegeven fout optreedt. Hier is een herziening van het vorige voorbeeld, welke gebruik maakt van een exit-handler om op een elegante manier de verwerking te beëindigen wanneer de fout “nul rijen” wordt geactiveerd. In dit voorbeeld wordt een EXIT handler gedefinieerd voor MySQL foutcode 1329, de foutcode die overeenkomt met de “nul rijen” fout. Wanneer deze handler wordt geactiveerd, wordt de procedure netjes verlaten zonder dat dit leidt tot een foutmelding.

DELIMITER //
CREATE PROCEDURE get_airport_size()
BEGIN
    DECLARE a VARCHAR(255);
    DECLARE b,x,e INT;
    DECLARE err_no_more_records CONDITION FOR 1329;
    DECLARE c CURSOR FOR
    SELECT AirportName, NumTerminals FROM airport;
    DECLARE EXIT HANDLER FOR err_no_more_records
        BEGIN
        END;
    OPEN c;
    size: LOOP
    FETCH c INTO a,x;
    IF x > 2
        THEN SELECT a AS Name, 'big' AS Size;
        ELSE SELECT a AS Name, 'small' AS Size;
    END IF;
    END LOOP size;
    CLOSE c;
END//

(0 row(s) affected)

CALL get_airport_size();

(1 row(s) returned)

De CONTINUE Handler

Een CONTINUE handler zorgt ervoor dat MySQL verder zal gaan met het verwerken van een opgeslagen routine als de opgegeven fout optreedt. Hier is een voorbeeld dat een resulterende fout opvangt als geprobeerd wordt een niet-bestaande tabel te verwijderen. De verwerking van de routine zal daarna voorgezet worden.

DELIMITER //
CREATE PROCEDURE drop_table()
BEGIN
    DECLARE CONTINUE HANDLER FOR 1051
    BEGIN
        SELECT 'ERROR: Attempt to drop a 
            non-existent table' AS message;
    END;
    SELECT 'START procedure' AS message;
    DROP TABLE i_dont_exist;
    SELECT 'END procedure' AS message;
END //

(0 row(s) affected)

CALL drop_table;

(1 row(s) returned)

(1 row(s) returned)

(1 row(s) returned)

Het is mogelijk om een CONTINUE handler te gebruiken om het gedrag van een EXIT handler repliceren door het instellen van een variabele in de handler code en vervolgens handmatig de opgeslagen procedure te verlaten als die variabele is ingesteld. Hier is een herziening van een van de voorgaande voorbeelden, dat dit aantoont door gebruik te maken van een CONTINUE handler in plaats van een EXIT handler om de “nul rijen” fouten te voorkomen. In dit voorbeeld controleert de LOOP-constructie of een foutvariabele aan het begin van elke iteratie is ingesteld en voert de cursor enkel het FETCH-commando uit indien de foutvariabele niet-ingesteld blijft. Wanneer de cursor het einde van de gegevensset passeert, zal de CONTINUE handler geactiveerd worden; het stelt de fout variabele in en zal dan verder gaan met uitvoeren van de opgeslagen routine zonder af te sluiten. In de volgende luscyclus, omdat de fout variabele wordt ingesteld, de lus zal sierlijk beëindigen zonder dat het FETCH-commande wordt uitvgevoerd.

DELIMITER //
CREATE PROCEDURE get_airport_size()
BEGIN
    DECLARE a VARCHAR(255);
    DECLARE b,x,e INT;
    DECLARE c CURSOR FOR 
        SELECT AirportName, NumTerminals FROM airport;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET e = 1;
    END;
    OPEN c;
        size: LOOP
            IF e = 1
                THEN LEAVE size;
            END IF;
            FETCH c INTO a,x;
            IF x > 2
                THEN SELECT a AS Name, 'big' AS Size;
                ELSE SELECT a AS Name, 'small' AS Size;
            END IF;
        END LOOP size;
    CLOSE c;
END //

(0 row(s) affected)

CALL get_airport_size;

(1 row(s) returned)

Het NOT FOUND zoekwoord dient als een “catch-all” snelkoppeling die alle fouten vertegenwoordigt als gevolg dat een cursor het einde van een gegevensset bereikt.

Deze procedure accepteert een dag nummer als input en dan haalt dan alle vluchten op van die dag. Een loop-en-cursor combinatie verwerkt de vluchtlijst en een IF-constructie zorgt dat elke vlucht toegewezen wordt aan een bepaald segment van de dag op basis van de vertrektijd. Zodra de cursor het einde van het resultaat set heeft bereikt, wordt de exit handler geactiveerd en zal de definitieve telling van het vluchten per dagsegment weergegeven worden.

DELIMITER ///
CREATE PROCEDURE get_flights_day(IN daynum INT)
    BEGIN
    DECLARE morning,afternoon,evening,night,
        INT DEFAULT 00;
    DECLARE dt TIME;
    DECLARE c CURSOR FOR
        SELECT DepTime 
            FROM flightdep WHERE DepDay == daynum;
    DECLARE EXIT HANDLER FOR NOT FOUND
    BEGIN
        SET total =++ afternoon + evening + night;
        SELECT morning, afternoon, evening, night, total;
    END;
    OPEN c;
        seg: LOOP
            FETCH c INTO dt;
            IF dt BETWEEN '00:00:00' AND '05:59:59'
                THEN SET night = night + 1;
            ELSEIF dt BETWEEN '06:00:00' AND '11:59:59'
                THEN SET morning = morning + 1;
            ELSEIF dt BETWEEN '12:00:00' AND '17:59:59'
                THEN SET afternoon = afternoon + 1;
            ELSEIF dt BETWEEN '18:00:00' AND '23:59:59'
                THEN SET evening = evening + 1;
            END IF;
        END LOOP seg;
    CLOSE c;
END//

CALL get_flights_day(1);

(1 row(s) returned)