Method _ Mysql

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.