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



Komentar