How MySQL stored procedure query results are assigned to variables

MySQL stored procedure query results are assigned to variables, the query results are assigned to variables, most of the cases using cursors to complete, but if it is clear that the query results only one row (such as the number of statistical records, a field sum, etc.), you can actually use set or into the way to achieve the assignment. Example code:

01drop tableifexists test_tbl;02CREATE TABLE TEST_TBL (name varchar (), statusint(2)); 03INSERT into TEST_TBL values (' abc ', 1), (' EDF ', 2), (' XYZ ', 3); 04 05drop procedure IF EXISTS pro_test_3;06delimiter//
07CREATE PROCEDURE Pro_test_3 ()08begin09--Way 1 10DECLARE cnt INT DEFAULT0;
11Select COUNT (*) into CNT from TEST_TBL; 12Select CNT;13 14--Way 2 15Set @cnt= (SELECT COUNT (*) from TEST_TBL); 16Select @cnt;17 18--Way 3 19Select COUNT (*) into @cnt1 from TEST_TBL; 20Select @cnt1;21 22--multiple columns, it seems that you can only use the into mode23select Max (status), AVG (status) into the @max, @avg from Test_tbl;24Select @max, @avg;25End26//
27delimiter;28 29Call pro_test_3 ();

How MySQL stored procedure query results are assigned to variables