MySQL : If Statement
The IF statement implements a basic conditional construct within a stored programs and must be terminated with a semicolon. There is also an IF() function, which is different from the IF statement. Here is the syntax of if statement
IF condition THEN statement(s)
[ELSEIF condition THEN statement(s)] ...
[ELSE statement(s)]
END IF
https://www.mysqltutorial.org/mysql-if-statement/
DELIMITER $$
CREATE PROCEDURE vote(IN age INT, OUT result VARCHAR(20))
BEGIN
IF age >= 18 THEN SET result = 'Permision ok';
ELSE SET result = 'reJected';
END IF;
END $$
DELIMITER ;
delimiter $$
create procedure vote1(in age int, out result varchar(20))
begin
if age > 18 then set result = "Eligible";
elseif age=18 then set result="he can apply";
else set result="Not eligible";
end if;
end $$
delimiter ;
------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> delimiter $$
mysql> create procedure vote(in age int, out result varchar(10))
-> begin
-> if age >= 18 then set result = "Eligible";
-> else set result="Not eligible";
-> end if;
-> end $$
Query OK, 0 rows affected (0.12 sec)
mysql> delimiter ;
mysql> call vote(16 ,@res);
ERROR 1406 (22001): Data too long for column 'result' at row 1
mysql> drop procedure vote;
Query OK, 0 rows affected (0.15 sec)
mysql> delimiter $$
mysql> create procedure vote(in age int, out result varchar(20))
-> begin
-> if age >= 18 then set result = "Eligible";
-> else set result="Not eligible";
-> end if;
-> end $$
Query OK, 0 rows affected (0.14 sec)
mysql> delimiter ;
mysql> call vote(16,@res);
Query OK, 0 rows affected (0.00 sec)
mysql> select @res;
+--------------+
| @res |
+--------------+
| Not eligible |
+--------------+
1 row in set (0.00 sec)
mysql> call vote(20,@res);
Query OK, 0 rows affected (0.00 sec)
mysql> select @res;
+----------+
| @res |
+----------+
| Eligible |
+----------+
1 row in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure vote1(in age int, out result varchar(20))
-> begin
-> if age > 18 then set result = "Eligible";
-> elseif age=18 then set result="he can apply";
-> else set result="Not eligible";
-> end if;
-> end $$
Query OK, 0 rows affected (0.14 sec)
mysql> delimiter ;
mysql> call vote1(19,@res);
Query OK, 0 rows affected (0.02 sec)
mysql> select @res;
+----------+
| @res |
+----------+
| Eligible |
+----------+
1 row in set (0.00 sec)
mysql> call vote1(18,@res);
Query OK, 0 rows affected (0.00 sec)
mysql> select @res;
+--------------+
| @res |
+--------------+
| he can apply |
+--------------+
1 row in set (0.00 sec)
mysql> call vote1(16,@res);
Query OK, 0 rows affected (0.00 sec)
mysql> select @res;
+--------------+
| @res |
+--------------+
| Not eligible |
+--------------+
1 row in set (0.00 sec)
mysql>
-------------
select customerNumber,creditlimit from customers limit 20;
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 ;
CALL GetCustomerLevel(447, @level);
SELECT @level;