"Go" MySQL stored procedure syntax example

Original address: http://blog.chinaunix.net/uid-540802-id-138873.html

-------------------------automatically generate random data stored procedures-------------------------
Drop PROCEDURE if exists genrand;
Delimiter//
Create PROCEDURE Genrand (in rank int, in Add_num int, in Stattime char (10))
-Rank: Random Maximum number of messages, Add_num: Number of generated items, StartTime: statistics date
Begin
declare mobile long;
declare TMP int;
Set mobile = 13600000000;
Set tmp = 1;
--delete from Ippush_stats_email;
While TMP <= Add_num do
INSERT into Ippush_stats_email VALUES (MOBILE + TMP, 1, now (), Stattime,
Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank),
Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank),
Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank),
Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank),
Random_int (rank), Random_int (rank), Random_int (rank), Random_int (rank));
Set TMP = tmp + 1;
End while;
end//
delimiter;
Call Genrand (10, 10, ' 2010-10-6 ');

-------------------------statistics-------------------------from time1 to time2 hours
Drop PROCEDURE if exists dostats;
Delimiter//
Create PROCEDURE dostats (in Time1 char (Ten), in Time2 char (10))
Begin
DECLARE _mobile char (11);
declare _sums int;
declare _stat_time date;
declare _count int;
declare _user_type int;
declare Fetchseqok int;
DECLARE fetchseqcursor cursor FOR
Select Mobile, COUNT (*) as Count, User_type, Stat_time, Max ((clock_1 + clock_2 + clock_3+clock_4+clock_5+clock_6+clock_7+ clock_8+clock_9+
clock_10+clock_11+clock_12+clock_13+clock_14+clock_15+clock_16+clock_17+clock_18+clock_19+
CLOCK_20+CLOCK_21+CLOCK_22+CLOCK_23+CLOCK_24)) as sums
From Ippush_stats_email
where Stat_time >= time1 and Stat_time <= time2
GROUP by Mobile;
Declare CONTINUE HANDLER for not FOUND SET fetchseqok = 0;

Set fetchseqok = 1;
Open fetchseqcursor;
While Fetchseqok = 1 do
Fetch fetchseqcursor into _mobile, _count, _user_type, _stat_time, _sums;
If _sums >
Call Onelinestat (1, _user_type, time1, time2, _mobile);
Else
Call Onelinestat (2, _user_type, time1, time2, _mobile);
End If;
End while;
Close fetchseqcursor;
end//
delimiter;
Call Dostats (' 2010-10-01 ', ' 2010-11-05 ');

-------------------------a line of business statistics--------------------------
Drop PROCEDURE if exists onelinestat;
Delimiter//
Create PROCEDURE Onelinestat (in type int, in Inuser_type int, in Time1 char (Ten), in Time2 char (Ten), in Inmobile char (11))
Begin
If Inuser_type = 1 Then--Is MAS user
If type = 1 then--was level 1
INSERT INTO Ippush_strategy (select Mobile, Inuser_type, Concat (Max (clock_1) >=4,
Max (clock_2) >=4,
Max (clock_3) >=4,
Max (clock_4) >=4,
Max (clock_5) >=4,
Max (clock_6) >=4,
Max (clock_7) >=4,
Max (clock_8) >=4,
' 111111111 ',
Max (clock_19) >=4,
Max (clock_20) >=4,
Max (clock_21) >=4,
Max (clock_22) >=4,
Max (clock_23) >=4,
Max (clock_24) >=4), now (), time1, time2
From Ippush_stats_email
where mobile = Inmobile and Stat_time >= time1 and Stat_time <= time2 and user_type = Inuser_type
Group by mobile);
Else
INSERT INTO Ippush_strategy (select Mobile, Inuser_type, Concat (Max (clock_1) >=4,
Max (clock_2) >=4,
Max (clock_3) >=4,
Max (clock_4) >=4,
Max (clock_5) >=4,
Max (clock_6) >=4,
Max (clock_7) >=4,
Max (clock_8) >=4,
Max (Clock_9) >=4,
Max (clock_10) >=4,
Max (Clock_11) >=4,
Max (Clock_12) >=4,
Max (clock_13) >=4,
Max (clock_14) >=4,
Max (clock_15) >=4,
Max (clock_16) >=4,
Max (clock_17) >=4,
Max (clock_18) >=4,
Max (clock_19) >=4,
Max (clock_20) >=4,
Max (clock_21) >=4,
Max (clock_22) >=4,
Max (clock_23) >=4,
Max (clock_24) >=4), now (), time1, time2
From Ippush_stats_email
where mobile = Inmobile and Stat_time >= time1 and Stat_time <= time2 and user_type = Inuser_type
Group by mobile);
End If;
Else--is MIG user
If type = 1 Then
INSERT into ippush_strategy values (Inmobile, Inuser_type, ' 111111111111111111111111 ', now, time1, time2);
End If;
End If;
end//
delimiter;

------------------Call--------------------
Call Onelinestat (1, 1, ' 2010-10-01 ', ' 2010-11-05 ', ' 13600000001 ');

"Go" MySQL stored procedure syntax example