OUT parameters
The value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program.
delimiter $$
create procedure total_Credit_limit(in cname VARCHAR(255),out totalcr int)
begin
select sum(creditLimit) into totalcr from customers where country=cname;
end $$
delimiter ;
call total_Credit_limit("USA",@total);
select @total;
-------------------------------
delimiter $$
create procedure total_Credit_limit3(out totalcr int)
begin
select @total1;
select sum(creditLimit) into totalcr from customers;
end $$
delimiter ;
call total_Credit_limit2(@total1);
select @total1;
select sum(creditLimit) from customers;
-------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
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>
mysql> delimiter $$
mysql> create procedure total_Credit_limit2(out totalcr int)
-> begin
-> select sum(creditLimit) into totalcr from customers;
-> end $$
Query OK, 0 rows affected (0.40 sec)
mysql> delimiter ;
mysql> call total_Credit_limit2(@summ);
Query OK, 1 row affected (0.03 sec)
mysql> select @summ;
+---------+
| @summ |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)
mysql> set @total=50000;
Query OK, 0 rows affected (0.00 sec)
mysql> select @total;
+--------+
| @total |
+--------+
| 50000 |
+--------+
1 row in set (0.00 sec)
mysql> call total_Credit_limit2(@total);
Query OK, 1 row affected (0.04 sec)
mysql> select @total;
+---------+
| @total |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)
mysql> select @total;
+---------+
| @total |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure total_Credit_limit3(out totalcr int)
-> begin
-> select @total1;
-> select sum(creditLimit) into totalcr from customers;
-> end $$
Query OK, 0 rows affected (0.11 sec)
mysql> delimiter ;
mysql> all total_Credit_limit3(@total1);
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 'all total_Credit_limit3(@total1)' at line 1
mysql> call total_Credit_limit3(@total1);
+------------------+
| @total1 |
+------------------+
| NULL |
+------------------+
1 row in set (0.03 sec)
Query OK, 1 row affected (0.05 sec)
mysql> select @total1;
+---------+
| @total1 |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)
mysql> call total_Credit_limit3(@total1);
+---------+
| @total1 |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> set @total1=700;
Query OK, 0 rows affected (0.00 sec)
mysql> call total_Credit_limit3(@total1);
+---------+
| @total1 |
+---------+
| 700 |
+---------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> select @total1;
+---------+
| @total1 |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)
mysql> drop procedure total_Credit_limit;
Query OK, 0 rows affected (0.21 sec)
mysql> delimiter $$
mysql> create procedure total_Credit_limit(in cname VARCHAR(255),out totalcr int)
-> begin
-> select sum(creditLimit) into totalcr from customers where country=cname;
-> end $$
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter ;
mysql> call total_Credit_limit("usa",@tc);
Query OK, 1 row affected (0.04 sec)
mysql> select * tc;
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 'tc' at line 1
mysql> select @tc;
+---------+
| @tc |
+---------+
| 2811700 |
+---------+
1 row in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.