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/
-------------
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 > 100000 THEN
SET pCustomerLevel = 'PLATINUM';
ELSEIF credit <= 100000 AND credit > 50000 THEN
SET pCustomerLevel = 'GOLD';
ELSEIF credit <= 50000 AND credit > 0 THEN
SET pCustomerLevel = 'Silver';
ELSE
SET pCustomerLevel = 'No level';
END IF;
END $$
DELIMITER ;
CALL GetCustomerLevel(447, @level);
SELECT @level;
-----------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
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 classicmodesls;
ERROR 1049 (42000): Unknown database 'classicmodesls'
mysql> use classicmodels;
Database changed
mysql> select customerNumber,creditlimit from customers limit 20;
+----------------+-------------+
| customerNumber | creditlimit |
+----------------+-------------+
| 103 | 21000.00 |
| 112 | 71800.00 |
| 114 | 117300.00 |
| 119 | 118200.00 |
| 121 | 81700.00 |
| 124 | 210500.00 |
| 125 | 0.00 |
| 128 | 59700.00 |
| 129 | 64600.00 |
| 131 | 114900.00 |
| 141 | 227600.00 |
| 144 | 53100.00 |
| 145 | 83400.00 |
| 146 | 123900.00 |
| 148 | 103800.00 |
| 151 | 138500.00 |
| 157 | 100600.00 |
| 161 | 84600.00 |
| 166 | 97900.00 |
| 167 | 96800.00 |
+----------------+-------------+
20 rows in set (0.25 sec)
mysql> DELIMITER $$
mysql>
mysql> 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 > 100000 THEN
-> SET pCustomerLevel = 'PLATINUM';
-> ELSEIF credit <= 100000 AND credit > 50000 THEN
-> SET pCustomerLevel = 'GOLD';
-> ELSEIF credit <= 50000 AND credit > 0 THEN
-> SET pCustomerLevel = 'Silver';
-> ELSE
-> SET pCustomerLevel = 'No level';
-> END IF;
-> END $$
ERROR 1304 (42000): PROCEDURE GetCustomerLevel already exists
mysql>
mysql> drop procedure getcustomerlevel;
-> $$
Query OK, 0 rows affected (0.38 sec)
mysql> delimiter;
-> delimiter ;
-> $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter;delimiter' at line 1
mysql> DELIMITER $$
mysql>
mysql> 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 > 100000 THEN
-> SET pCustomerLevel = 'PLATINUM';
-> ELSEIF credit <= 100000 AND credit > 50000 THEN
-> SET pCustomerLevel = 'GOLD';
-> ELSEIF credit <= 50000 AND credit > 0 THEN
-> SET pCustomerLevel = 'Silver';
-> ELSE
-> SET pCustomerLevel = 'No level';
-> END IF;
-> END $$
Query OK, 0 rows affected (0.15 sec)
mysql>
mysql> DELIMITER ;
mysql> CALL GetCustomerLevel(447, @level);
Query OK, 1 row affected (0.10 sec)
mysql> SELECT @level;
+--------+
| @level |
+--------+
| Silver |
+--------+
1 row in set (0.00 sec)
mysql> CALL GetCustomerLevel(114, @level);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @level;
+----------+
| @level |
+----------+
| PLATINUM |
+----------+
1 row in set (0.00 sec)
mysql> CALL GetCustomerLevel(121, @level);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @level;
+--------+
| @level |
+--------+
| GOLD |
+--------+
1 row in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.