The DECLARE ... HANDLER statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified statement executes.
drop procedure withdraw;
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;
start transaction;
select balance into current_balance from accounts where id=account_id for update;
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;
commit;
end$$
delimiter ;
call withdraw(2, 251, @success);
select @success;
--------------------------
drop procedure withdraw;
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;
declare exit handler for sqlexception
begin
show errors;
end;
start transaction;
select balance into current_balance from accounts1 where id=account_id for update;
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;
commit;
end$$
delimiter ;
call withdraw(2, 251, @success);
select @success;
---------------------
drop procedure withdraw;
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;
declare exit handler for sqlexception
begin
show errors;
end;
declare exit handler for sqlwarning
begin
show warnings;
end;
start transaction;
select balance into current_balance from accounts where id=account_id for update;
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;
commit;
end$$
delimiter ;
call withdraw(21, 251, @success);
select @success;
declare exit handler for sqlexception
begin
show errors;
end;
declare exit handler for sqlwarning
begin
show warnings;
end;
-------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
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 withdraw(in account_id int, in amount numeric(7, 2), out success varchar(50))
-> begin
-> declare current_balance numeric(7, 2) default 0.0;
-> start transaction;
-> select balance into current_balance from accounts where id=account_id for update;
-> 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;
-> commit;
-> end$$
ERROR 1304 (42000): PROCEDURE withdraw already exists
mysql> delimiter ;
mysql> drop procedure withdraw;
Query OK, 0 rows affected (0.15 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;
-> start transaction;
-> select balance into current_balance from accounts where id=account_id for update;
-> 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;
-> commit;
-> end$$
Query OK, 0 rows affected (0.38 sec)
mysql> delimiter ;
mysql> call withdraw(32, 251, @success);
Query OK, 0 rows affected (0.04 sec)
mysql> select @success;
+------------------------+
| @success |
+------------------------+
| try with Lower amounts |
+------------------------+
1 row in set (0.00 sec)
mysql> drop procedure withdraw;
Query OK, 0 rows affected (0.13 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;
-> start transaction;
-> select balance into current_balance from accounts1 where id=account_id for update;
-> 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;
-> commit;
-> end$$
Query OK, 0 rows affected (0.42 sec)
mysql> delimiter ;
mysql> call withdraw(2, 251, @success);
ERROR 1146 (42S02): Table 'classicmodels.accounts1' doesn't exist
mysql> drop procedure withdraw;
Query OK, 0 rows affected (0.13 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;
-> declare exit handler for sqlexception
-> begin
-> show errors;
-> end;
-> start transaction;
-> select balance into current_balance from accounts1 where id=account_id for update;
-> 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;
-> commit;
-> end$$
Query OK, 0 rows affected (0.21 sec)
mysql> delimiter ;
mysql> call withdraw(2, 251, @success);
+-------+------+-----------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------+
| Error | 1146 | Table 'classicmodels.accounts1' doesn't exist |
+-------+------+-----------------------------------------------+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.06 sec)
mysql> drop procedure withdraw;
Query OK, 0 rows affected (0.23 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;
-> declare exit handler for sqlexception
-> begin
-> show errors;
-> end;
->
-> declare exit handler for sqlwarning
-> begin
-> show warnings;
-> end;
->
-> start transaction;
-> select balance into current_balance from accounts where id=account_id for update;
-> 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;
-> commit;
-> end$$
Query OK, 0 rows affected (0.13 sec)
mysql> delimiter ;
mysql> call withdraw(21, 251, @success);
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> call withdraw(2, 251, @success);
Query OK, 0 rows affected (0.09 sec)
mysql> select @success;
+------------+
| @success |
+------------+
| With drawn |
+------------+
1 row in set (0.00 sec)
mysql> select * from accounts;
+------+----------+
| ID | balance |
+------+----------+
| 1 | 29998.50 |
| 2 | 4249.50 |
| 3 | 500.11 |
+------+----------+
3 rows in set (0.00 sec)
mysql> call withdraw(2, 251, @success);
Query OK, 0 rows affected (0.12 sec)
mysql> select * from accounts;
+------+----------+
| ID | balance |
+------+----------+
| 1 | 29998.50 |
| 2 | 3998.50 |
| 3 | 500.11 |
+------+----------+
3 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.