If statement example 3 Implementing Account withdrawal sql videos in telugu 95

 https://youtu.be/PzIQaf9Ky1c

-----------------------------------------------------

delimiter $$

create procedure withdraw(in account_id int, in amount numeric(7, 2), out success varchar(50))

begin

declare current_balance numeric(7, 2) default 0.0;

select balance into current_balance from accounts where id=account_id;

if current_balance >= amount then update accounts set balance = balance - amount where id=account_id; 

set success= "With drawn";

else set success="try with Lower amounts";

end if;

end$$

delimiter ;

delimiter $$

create procedure withdraw(in account_id int ,in Amount decimal(10, 2),out  success varchar(50))

begin

declare current_balance decimal(10, 2) default 0.0;

select balance  into current_balance from accounts where id=account_id;

if current_balance >= amount then update accounts set balance = balance - amount where id=account_id; 

set success= "With drawn";

else set success="try with Lower amounts";

end if;

end$$

delimiter ;

call withdraw(1, 10000, @success);

select @success;

create table accounts(

ID int,

balance decimal(10,2)

);

INSERT INTO accounts ()

VALUES (1, 40500.50),(2, 4500.50) ,(3, 30500.11);

-------------------

Enter password: *******

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

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> drop table accounts;

ERROR 1051 (42S02): Unknown table 'classicmodels.accounts'

mysql> create table accounts(

    -> ID int,

    -> balance decimal(10,2)

    -> );

Query OK, 0 rows affected (1.47 sec)

mysql>

mysql> INSERT INTO accounts ()

    -> VALUES (1, 40500.50),(2, 4500.50) ,(3, 30500.11);

Query OK, 3 rows affected (0.28 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from accounts;

+------+----------+

| ID   | balance  |

+------+----------+

|    1 | 40500.50 |

|    2 |  4500.50 |

|    3 | 30500.11 |

+------+----------+

3 rows in set (0.00 sec)

mysql> drop table accounts;

Query OK, 0 rows affected (1.11 sec)

mysql> create table accounts(

    -> ID int,

    -> balance decimal(10,2)

    -> );

Query OK, 0 rows affected (1.70 sec)

mysql> INSERT INTO accounts ()

    -> VALUES (1, 40500.50),(2, 4500.50) ,(3, 30500.11);

Query OK, 3 rows affected (0.10 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from accounts;

+------+----------+

| ID   | balance  |

+------+----------+

|    1 | 40500.50 |

|    2 |  4500.50 |

|    3 | 30500.11 |

+------+----------+

3 rows in set (0.00 sec)

mysql> delimiter $$

mysql> create procedure withdraw(in account_id int, in amount numeric(7, 2), out success varchar(50))

    -> begin

    -> declare current_balance numeric(7, 2) default 0.0;

    -> select balance into current_balance from accounts where id=account_id;

    -> if current_balance >= amount then update accounts set balance = balance - amount where id=account_id;

    -> set success= "With drawn";

    -> else set success="try with Lower amounts";

    -> end if;

    -> end$$

Query OK, 0 rows affected (0.28 sec)

mysql> delimiter ;

mysql> call withdraw(3,30000,@scccess);

Query OK, 1 row affected (0.14 sec)

mysql> select success;

ERROR 1054 (42S22): Unknown column 'success' in 'field list'

mysql> select @success;

+--------------------+

| @success           |

+--------------------+

| NULL               |

+--------------------+

1 row in set (0.00 sec)

mysql> call withdraw(1, 10000, @success);

Query OK, 1 row affected (0.11 sec)

mysql>

mysql> select @success;

+------------+

| @success   |

+------------+

| With drawn |

+------------+

1 row in set (0.00 sec)

mysql> select * from accounts;

+------+----------+

| ID   | balance  |

+------+----------+

|    1 | 30500.50 |

|    2 |  4500.50 |

|    3 |   500.11 |

+------+----------+

3 rows in set (0.00 sec)

mysql> call withdraw(2, 10000, @success);

Query OK, 1 row affected (0.00 sec)

mysql> select @success;

+------------------------+

| @success               |

+------------------------+

| try with Lower amounts |

+------------------------+

1 row in set (0.00 sec)

mysql>