MySQL Stored procedure example

CREATE definer= ' root ' @ '% ' PROCEDURE ' sp_getwangingscount ' (in ' Pcode ' VARCHAR (), ' Fromday ' DATE, ' Cycle ' INT, in ' D Aycount ' int, out ' Rlt ' int., out ' Daystr ' VARCHAR (520))

LANGUAGE SQL

Not deterministic

CONTAINS SQL

SQL SECURITY definer

COMMENT "

BEGIN

If Date_add (fromday, Interval DayCount-1 day) < Date (now ()) then

Set Rlt = 0;  

Else

Set @n = Floor ((DateDiff (now (), fromday) + 1)/Cycle);

Set Rlt = 0;

Set daystr = ';

set @i=1;

while @i <= @n do

Set @_fromday = Date_add (Fromday, Interval daycount/cycle* (@i-1)-1 day);

If @_fromday <= date (now ()) then

If @i < @n Then

Set @_today = Date_add (Date (fromday), Interval daycount/cycle*@i day);

Else

Set @_today = Date_add (Date (fromday), Interval DayCount-1 day);

End If;

If DateDiff (@_today, Now ()) > 0 Then

Set @_today = Date (now ());

End If;

Select @k:=if (sum (Sa_money)/datediff (@_today, @_fromday) < 1000, 1, 0)

from ' T_sale '

where Sa_code = Pcode and sa_date between @_fromday and @_today;

Set Rlt = Rlt + @k;

Select @i, @_fromday, @_today, @k, Rlt;

If @k = 1 Then

If daystr = "Then

Set daystr = Concat (Daystr, @_fromday, '-', @_today);

Else

Set daystr = concat (Daystr, ', ', @_fromday, '-', @_today);

End If;

End If;

End If;

Set @i = @i + 1;

End while;

End If;

END

CREATE definer= ' root ' @ '% ' PROCEDURE ' showwarnings ' ()

LANGUAGE SQL

Not deterministic

CONTAINS SQL

SQL SECURITY definer

COMMENT ' Showwarnigsday '

BEGIN

--Traversal data End flag

DECLARE done INT DEFAULT FALSE;

DECLARE a_code varchar (50);

Declare cur cursor FOR SELECT * from ' t_temp ';

--Bind the end flag to the cursor

DECLARE CONTINUE HANDLER for don't FOUND SET done = TRUE;

CREATE TABLE T_tmp

Select

B.ps_name Ps_name,

A.p_code P_code,

A.p_name P_name,

A.p_scale P_scale,

(select SUM (C.sa_money) from T_sale C where C.sa_code=a.p_code) as Sa_money,

p_scale-(select sum (C.sa_money) from T_sale C where C.sa_code=a.p_code) as Sa_left,

DateDiff (p_enddate,p_begindate) as Dayleft,

Floor (DateDiff (a.p_enddate, A.p_begindate)/7) as Leftweek,

A.p_period P_period,

A.p_begindate P_egindate,

(select SUM (C.sa_money) from T_sale C where C.sa_code=a.p_code)/a.p_scale*100 as Percent,

1 as ' alert Times ',

1 as ' broken line count ',

' 2014-08-10 ' as ' broken line Date ',

(select W_color from T_warning D where D.w_code=a.p_code) as W_color,

1 as ' broken line '

From T_product A

Left joins T_product_sort B on a.p_sort=b.ps_id and A.p_sort in (' 1 ', ' 2 ');

--Open cursor

OPEN cur;

--Start cycle

Read_loop:loop

--Extract the data in the cursor, there is only one, and many words are the same;

FETCH cur into a_code;

-At the end of the declaration

IF do Then

LEAVE Read_loop;

END IF;

Call ' Spgetwangingscount ' (A_code, ', ', ');

Update t_temp set ' alert count ' [email protected];

END LOOP;

--Close cursor

CLOSE cur;

END

MySQL Stored procedure example