Mysql stored procedure query result assigned to variable method bitsCN.com
How to assign values to variables in Mysql stored procedure query results
Assign the query result to a variable. In most cases, the cursor is used to complete the query. However, if you explicitly know that the query result has only one row (such as the number of statistics records and the sum of a field ), in fact, you can use set or into to assign values. Sample code:
01 drop table if exists test_tbl; 02 create table test_tbl (name varchar (20), status int (2); 03 insert into test_tbl values ('ABC', 1 ), ('def', 2), ('XYZ', 3); 0405 drop procedure if exists pro_test_3; 06 delimiter // 07 create procedure pro_test_3 () 08begin09 -- method 110 DECLARE cnt int default 0; 11 select count (*) into cnt from test_tbl; 12 select cnt; 1314 -- method 215 set @ cnt = (select count (*) from test_tbl); 16 select @ cnt; 1718 -- method 319 select count (*) into @ cnt1 from test_tbl; 20 select @ cnt1; 2122 -- when multiple columns exist, it seems that only the into method 23 select max (status), avg (status) into @ max, @ avg from test_tbl; 24 select @ max, @ avg; 25end26 // 27 delimiter; 2829 call pro_test_3 ();
BitsCN.com