delimiter $$
dropprocedureifexists create_data_for_test2;createprocedure create_data_for_test2(in num int,out total int)begindeclare count intdefault0;declare batch_num intdefault0;
dd:loopSTARTTRANSACTION;--手动提交事务,提高效率
batch:loopinsertinto test2(b,c,d,e)values(round(rand()*10),round(rand()*10),round(rand()*10),round(rand()*count));set count=count+1;set batch_num:=batch_num+1;if count>=num thenleave dd;endif;if batch_num>=10thencommit;set batch_num=0;leave batch;endif;endloop batch;endloop dd;commit;selectcount(*)into total from test2;end;$$
delimiter;showprocedurestatus;dropprocedure create_data;
函数
delimiter $$
createfunction get_b_by_a(a1 int)returnsintbegindeclare b_test2 intdefault0;select b into b_test2 from test2 where a=a1;return b_test2;end$$
delimiter;showfunctionstatus\G;
变量局部变量:declare:|用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中用户变量:@变量名:eg:@a 定义用户变量,作用域在整个链接会话变量:在每次连接成功后会将全局变量拷贝一份到当前回话:set session var_name = value; show variables like “autocommit”;...