MySQL IF Statement

Pada pertemuan kali ini kita akan belajar mengenai IF statement dalam MySQL. IF statement memiliki tiga froms:  simple IF-THEN statement, IF-THEN-ELSE statement, and IF-THEN-ELSEIF-ELSE statement.

1. Simple IF-THEN

Disini kita akan menggunakan contoh table customers dari sample classicmodels. 

Berikut Query nya:

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    IN  pCustomerNumber INT, 

    OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL(10,2) DEFAULT 0;

    SELECT creditLimit 

    INTO credit

    FROM customers

    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    END IF;

END$$

DELIMITER ;

Hasil Outputnya:

2. IF-THEN-ELSE statement

Query nya:

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    IN  pCustomerNumber INT, 

    OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL DEFAULT 0;

    SELECT creditLimit 

    INTO credit

    FROM customers

    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    ELSE

        SET pCustomerLevel = 'NOT PLATINUM';

    END IF;

END$$

DELIMITER ;

Outputnya:

3. IF-THEN-ELSEIF-ELSE statement

Query nya:

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    IN  pCustomerNumber INT, 

    OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL DEFAULT 0;

    SELECT creditLimit 

    INTO credit

    FROM customers

    WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    ELSEIF credit <= 50000 AND credit > 10000 THEN

        SET pCustomerLevel = 'GOLD';

    ELSE

        SET pCustomerLevel = 'SILVER';

    END IF;

END $$

DELIMITER ;

Outputnya:

By Syifa Ilafiah