Translate

Monday, 18 October 2021

If statement in stored procedure sql videos in telugu 93

 https://youtu.be/eDIPxJ2oDhc

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

No comments:

Post a Comment

Note: only a member of this blog may post a comment.