Monday, 18 October 2021

If statement example 2 stored procedure sql videos in telugu 94

 https://youtu.be/tOAi2zgXZeU

-----------------------------------------------------------------------------------
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.