mysql> CREATE PROCEDURE `NewProcedure`(in int_role_id int(11))
-> BEGIN
-> declare str_sql,str_h1,str_camp1 varchar(100) default '';
-> declare int_1 ,int_tmp int(11) default 0;
-> select @int_role_id,@int_tmp,int_role_id,int_tmp;
-> SET int_tmp=1;
-> select @int_role_id,@int_tmp,int_role_id,int_tmp;
-> SET @int_tmp=2;
-> select @int_role_id,@int_tmp,int_role_id,int_tmp;
-> SET @str_sql=concat('SELECT id,name,camp into @int_1,@str_h1,@str_camp1 FROM hero WHERE `role_id`=? AND `status`=0 LIMIT ?,1;');
-> PREPARE stmt FROM @str_sql;
-> EXECUTE stmt USING @int_role_id,@int_tmp;
-> delete from err_log;
-> insert into err_log set data=concat('int_1=',@int_1,',',int_1,',str_h1=',@str_h1,',int_role_id=',int_role_id,',int_tmp=',int_tmp);
->
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> call NewProcedure(46)//
+--------------+----------+-------------+---------+
| @int_role_id | @int_tmp | int_role_id | int_tmp |
+--------------+----------+-------------+---------+
| 46 | 1 | 46 | 0 |
+--------------+----------+-------------+---------+
1 row in set (0.00 sec)
+--------------+----------+-------------+---------+
| @int_role_id | @int_tmp | int_role_id | int_tmp |
+--------------+----------+-------------+---------+
| 46 | 1 | 46 | 1 |
+--------------+----------+-------------+---------+
1 row in set (0.00 sec)
+--------------+----------+-------------+---------+
| @int_role_id | @int_tmp | int_role_id | int_tmp |
+--------------+----------+-------------+---------+
| 46 | 2 | 46 | 1 |
+--------------+----------+-------------+---------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> select * from err_log order by id desc//
+----+-------------+------+------------------------------------------------------+---------+
| id | create_time | type | data | user_id |
+----+-------------+------+------------------------------------------------------+---------+
| 4 | 0 | | int_1=82,0,str_h1=王语嫣1_4,int_role_id=46,int_tmp=1 | 0 |
+----+-------------+------+------------------------------------------------------+---------+
1 row in set (0.00 sec)
1 存储过程如果传进一个参数 int_id，值为1
则在存储过程中int_id和@int_id的值都是1
2 如果在存储过程中 set @int_id2=1;
则@int_id2=1， int_id2 如果没有declare要报错，定义了此变量则它的值还是它的默认值
3 如果在存储过程中 set int_id3=1
则@int_id3=NULL， int_id2 =1
4 @int_id3不需要declare就可以使用，而int_id3必须先定义
总结：@a_var和a_var是两个不同的变量，当存储过程中传入变量a_var则 a_var和@a_var都有了值
