Naast het opslaan en ophalen van waarden in variabelen, laat MySQL toe om programmeurs verschillende omstandigheden te evalueren die zich voordoen tijdens het uitvoeren van een routine en beslissingen te nemen op basis van deze condities. Deze voorwaarden kunnen worden uitgedrukt met behulp van twee soorten voorwaardelijke constructies: de IF constructie en de CASE constructie.
De IF Constructie
De MySQL IF constructie biedt een handige manier om de sturing binnen een opgeslagen routine te veranderen. In zijn eenvoudigste vorm, zal het een conditie testen en voert het een reeks opdrachten uit als de voorwaarde waar is. Als volgt zijn de volgende drie algemene vormen van deze constructie:
IF [val 1]
THEN [result 1];
ELSE [result 2];
END IF;
IF [val 1]
THEN [result 1]
ELSEIF [val 2]
THEN [result 2]
ELSEIF [val 3]
THEN [result 3]
...
ELSEIF [val n]
THEN [result n]
ELSE [default result]
END IF;
In het volgende voorbeeld, welke de eerste vorm illustreert, zal de functie enkel een bericht teruggeven op weekdagen.
MSSQL
CREATE FUNCTION what_is_today()
RETURNS VARCHAR(255)
BEGIN
DECLARE @message VARCHAR(255);
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
BEGIN
SET @message = 'Today is a weekday';
END
RETURN @message;
END;
Command(s) completed successfully.
SELECT [dbo].[what_is_today]();
(1 row(s) affected)
(No column name) | |
1 | Today is a weekday |
MySQL
DELIMITER //
CREATE FUNCTION what_is_today()
RETURNS VARCHAR(255)
BEGIN
DECLARE message VARCHAR(255);
IF DAYOFWEEK(NOW()) BETWEEN 2 AND 6
THEN SET message = 'Today is a weekday';
END IF;
RETURN message;
END //
(0 row(s) affected)
SELECT what_is_today();
(1 row(s) returned)
what_is_today() | |
1 | Today is a weekday |
Met een iets meer complexe versie van de IF constructie kunt u een alternatieve reeks acties te definiëren voor wanneer de voorwaarde onwaar is. Beschouw het volgende voorbeeld, waarbij de functie verschillende berichten op weekdagen en weekends teruggeeft.
MSSQL
CREATE FUNCTION what_is_today()
RETURNS VARCHAR(255)
BEGIN
DECLARE @message VARCHAR(255);
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
BEGIN
SET @message = 'Today is a weekday';
END
ELSE
BEGIN
SET @message =
'Today is a Saturday or Sunday';
END
RETURN @message;
END;
Command(s) completed successfully.
SELECT [dbo].[what_is_today]();
(1 row(s) affected)
(No column name) | |
1 | Today is a weekday |
MySQL
DELIMITER //
CREATE FUNCTION what_is_today()
RETURNS VARCHAR(255)
BEGIN
DECLARE message VARCHAR(255);
IF DAYOFWEEK(NOW()) BETWEEN 2 AND 6
THEN SET message = 'Today is a weekday';
ELSE SET message =
'Today is a Saturday or Sunday';
END IF;
RETURN message;
END //
(0 row(s) affected)
SELECT what_is_today();
(1 row(s) returned)
what_is_today() | |
1 | Today is a weekday |
De IF constructie kan van pas komen bij het schrijven van opgeslagen procedures die tabelgegevens bijwerken of invoegen. Neem bijvoorbeeld de volgende procedure, die enkel een nieuw vliegtuigtyperecord invoegt als deze nog niet eerder bestaat. De procedure accepteert een vliegtuigtypenaam als invoer argument. Het controleert vervolgens de tabel van de vliegtuigsoorten om te zien of er een record met dezelfde gegevens al bestaat en voegt de nieuwe record alleen in als het nog niet bestaan. Een IF constructie wordt gebruikt om deze beslissing te nemen, waarbij de retourwaarde van de procedure ingesteld is op 0 of 1, afhankelijk van de vraag of de INSERT plaatsvond.
MSSQL
CREATE PROCEDURE add_aircraft_type(@aname VARCHAR(255))
AS
DECLARE @count INT, @lastid INT, @retval INT;
SELECT @count = COUNT(AircraftTypeID)
FROM aircrafttype
WHERE AircraftName = @aname;
IF @count = 0
BEGIN
SELECT @lastid = MAX(AircraftTypeID)
FROM aircrafttype;
INSERT INTO aircrafttype(
AircraftTypeID, AircraftName)
VALUES ((@lastid+1), @aname);
SET @retval = 1;
END
ELSE
BEGIN
SET @retval = 0;
END
SELECT @retval;
Command(s) completed successfully.
EXEC add_aircraft_type @aname = 'Boeing 747';
(1 row(s) returned)
HTML clipboard
(No column name) | |
1 | 0 |
EXEC add_aircraft_type @aname = 'Cessna C60';
(1 row(s) returned)
SELECT AircraftName FROM aircrafttype;
(7 row(s) returned)
AircraftName | |
1 | Boeing 747 |
2 | Boeing 767 |
3 | Airbus A300/310 |
4 | Airbus A330 |
5 | Airbus A340 |
6 | Airbus A380 |
7 | Cessna C60 |
MySQL
DELIMITER //
CREATE PROCEDURE add_aircraft_type(
IN aname VARCHAR(255))
BEGIN
DECLARE count, lastid, retval INT;
SELECT COUNT(AircraftTypeID) INTO count
FROM aircrafttype
WHERE AircraftName = aname;
IF count = 0
THEN SELECT MAX(AircraftTypeID) INTO lastid
FROM aircrafttype;
INSERT INTO aircrafttype(AircraftTypeID,
AircraftName)
VALUES ((lastid+1), aname);
SET retval = 1;
ELSE SET retval = 0;
END IF;
SELECT retval;
END //
(0 row(s) affected)
CALL add_aircraft_type('Boeing 747');
(1 row(s) returned)
HTML clipboard
retval | |
1 | 0 |
CALL add_aircraft_type('Cessna C60');
(1 row(s) returned)
CALL add_aircraft_type('Cessna C60');
(7 row(s) returned)
AircraftName | |
1 | Boeing 747 |
2 | Boeing 767 |
3 | Airbus A300/310 |
4 | Airbus A330 |
5 | Airbus A340 |
6 | Airbus A380 |
7 | Cessna C60 |
Zoals reeds is aangetoond, is het met de IF-ELSE-versie van de IF constructie mogelijk om twee condities te definiëren: een ware toestand en een onware toestand. In werkelijkheid, is het echter waarschijnlijk dat u te maken krijgt met meer dan alleen twee uitkomsten. Voor deze situaties biedt MySQL een complexere uitvoering van de IF constructie. Beschouw het volgende voorbeeld, dat voor iedere dag van de week een andere boodschap illustreert.
MSSQL
CREATE FUNCTION todays_child()
RETURNS VARCHAR(255)
BEGIN
DECLARE @message VARCHAR(255);
IF DATEPART(WEEKDAY, GETDATE()) = 2
BEGIN
SET @message =
'Monday''s child is fair of face.';
END
ELSE IF DATEPART(WEEKDAY, GETDATE()) = 3
BEGIN
SET @message =
'Tuesday''s child is full of grace.';
END
ELSE IF DATEPART(WEEKDAY, GETDATE()) = 4
BEGIN
SET @message =
'Wednesday''s child is full of woe.';
END
ELSE IF DATEPART(WEEKDAY, GETDATE()) = 5
BEGIN
SET @message =
'Thursday''s child has far to go.';
END
ELSE IF DATEPART(WEEKDAY, GETDATE()) = 6
BEGIN
SET @message =
'Friday''s child is loving and giving.';
END
ELSE IF DATEPART(WEEKDAY, GETDATE()) = 7
BEGIN
SET @message =
'Saturday''s child works hard
for a living.';
END
ELSE
BEGIN
SET @message =
'Sunday''s child is bonny and blithe
and good and gay.';
END
RETURN @message;
END;
Command(s) completed successfully.
SELECT [dbo].[todays_child]();
(1 row(s) affected)
(No column name) | |
1 | Tuesday’s child is full of grace. |
MySQL
DELIMITER //
CREATE FUNCTION todays_child()
RETURNS VARCHAR(255)
BEGIN
DECLARE message VARCHAR(255);
IF DAYOFWEEK(NOW()) = 2
THEN SET message =
'Monday\'s child is fair of face.';
ELSEIF DAYOFWEEK(NOW()) = 3
THEN SET message =
'Tuesday\'s child is full of grace.';
ELSEIF DAYOFWEEK(NOW()) = 4
THEN SET message =
'Wednesday\'s child is full of woe.';
ELSEIF DAYOFWEEK(NOW()) = 5
THEN SET message =
'Thursday\'s child has far to go.';
ELSEIF DAYOFWEEK(NOW()) = 6
THEN SET message =
'Friday\'s child is loving and giving.';
ELSEIF DAYOFWEEK(NOW()) = 7
THEN SET message =
'Saturday\'s child works hard for a
living.';
ELSE
SET message =
'Sunday\'s child is bonny and blithe
and good and gay.';
END IF;
RETURN message;
END //
(0 row(s) affected)
SELECT todays_child();
(1 row(s) returned)
todays_child() | |
1 | Tuesday’s child is full of grace. |
De CASE constructie
Een alternatief voor de IF-ELSE ELSEIF– versie van de IF constructie is de CASE constructie, wat ook toelaat omverschillende omstandigheden te testen. Het formaat van de CASE constructie is enigszins complex, en ziet er meestal als volgt uit:
CASE [expression to be evaluated]
WHEN [val 1]
THEN [result 1];
WHEN [val 2]
THEN [result 2];
...
WHEN [val n]
THEN [result n];
ELSE [default result];
END CASE;
Hier is het eerste argument de waarde of expressie die moet worden geëvalueerd; Dit wordt gevolgd door een reeks ALS-DAN blokken, die elk de waarde specificeren waartegen het eerste argument moet worden vergeleken en het resultaat dat moet worden geretourneerd als de vergelijking waar is. De volledige reeks van ALS-DAN blokken wordt beëindigd door een ELSE blok, dat het standaard resultaat geeft in het geval dat geen van de voorgaande blokken een overeenkomst geeft. Het hele blok moet worden afgesloten met een END CASE. In het geval dat er geen ELSE blok is gespecificeerd en geen van de ALS-DAN vergelijkingen een ‘waar’ retourneert, retourneert MySQL een NULL. Het volgende voorbeeld is een revisie van het eerdere voorbeeld uit de IF-constructie waar we nu gebruik zullen maken van de CASE-constructie. MSSQL behandelt een CASE constructie als een expressie en niet als een opdracht (zie http://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression).
MSSQL
CREATE FUNCTION todays_child()
RETURNS VARCHAR(255)
BEGIN
DECLARE @message VARCHAR(255),
@today INT = DATEPART(WEEKDAY, GETDATE());
SET @message =
CASE @today
WHEN 2
THEN 'Monday''s child is fair of face.'
WHEN 3
THEN 'Tuesday''s child is full of grace.'
WHEN 4
THEN 'Wednesday''s child is full of woe.'
WHEN 5
THEN 'Thursday''s child has far to go.'
WHEN 6
THEN 'Friday''s child is loving and giving.'
WHEN 7
THEN 'Saturday''s child works
hard for a living.'
ELSE 'Sunday''s child is bonny and
blithe and good and gay.'
END;
RETURN @message;
END;
Command(s) completed successfully.
SELECT [dbo].[todays_child]();
(1 row(s) affected)
(No column name) | |
1 | Tuesday’s child is full of grace. |
MySQL
DELIMITER //
CREATE FUNCTION todays_child()
RETURNS VARCHAR(255)
BEGIN
DECLARE message VARCHAR(255);
CASE DAYOFWEEK(NOW())
WHEN 2
THEN SET message =
'Monday\'s child is fair of face.';
WHEN 3
THEN SET message =
'Tuesday\'s child is full of grace.';
WHEN 4
THEN SET message =
'Wednesday\'s child is full of woe.';
WHEN 5
THEN SET message =
'Thursday\'s child has far to go.';
WHEN 6
THEN SET message =
'Friday\'s child is loving and giving.';
WHEN 7
THEN SET message =
'Saturday\'s child works hard for a
living.';
ELSE SET message =
'Sunday\'s child is bonny and blithe
and good and gay.';
END CASE;
RETURN message;
END //
(0 row(s) affected)
SELECT todays_child();
(1 row(s) returned)
todays_child() | |
1 | Tuesday’s child is full of grace. |
Het volgende voorbeeld maakt in de opgeslagen procedure gebruik van een CASE-contstructie om via een UPDATE-opdracht de status van een route te wisselen. De ingangen ‘red’ en ‘green’ worden gebruikt om de status van een route in te stellen op respectievelijk 0 of 1. In MySQL moet men opletten dat de ELSE clausule een leeg BEGIN … END blok bevat om te voorkomen dat er een fout wordt weergegeven als er een niet overeenkomende invoerparameter wordt meegegeven aan de procedure.
MSSQL
CREATE PROCEDURE change_route_status
(
@rid INT, @color VARCHAR(10)
)
AS
UPDATE route
SET Status = CASE @color
WHEN 'red'
THEN 0
WHEN 'green'
THEN 1
ELSE
Status
END
WHERE RouteID = @rid;
Command(s) completed successfully.
SELECT RouteID, Status
FROM route
WHERE RouteID = 1192;
(1 row(s) affected)
RouteID | Status | |
1 | 1192 | 0 |
EXEC [dbo].[change_route_status]
@rid = 1192, @color = 'green';
(1 row(s) affected)
SELECT RouteID, Status
FROM route
WHERE RouteID = 1192;
(1 row(s) affected)
RouteID | Status | |
1 | 1192 | 1 |
EXEC [dbo].[change_route_status]
@rid = 1192, @color = 'blue';
(1 row(s) affected)
MySQL
DELIMITER //
CREATE PROCEDURE change_route_status
(
IN rid INT, IN color VARCHAR(10)
)
BEGIN
CASE color
WHEN 'red'
THEN UPDATE route
SET Status = 0
WHERE RouteID = rid;
WHEN 'green'
THEN UPDATE route
SET Status = 1
WHERE RouteID = rid;
ELSE
BEGIN
END;
END CASE;
END //
(0 row(s) affected)
SELECT RouteID, Status
FROM route
WHERE RouteID = 1192;
(1 row(s) returned)
RouteID | Status | |
1 | 1192 | 0 |
CALL change_route_status(1192, 'green');
(0 row(s) affected)
SELECT RouteID, Status
FROM route
WHERE RouteID = 1192;
(1 row(s) returned)
RouteID | Status | |
1 | 1192 | 1 |
CALL change_route_status(1192, 'blue');
(0 row(s) affected)