"Go" MySQL stored procedure syntax example

Source: Internet
Author: User

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

Related Article

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.