Shulou(Shulou.com)05/31 Report--

What is the mysql stored procedure? for this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Bind variables within a stored procedure

Delimiter / /

Drop procedure testprocbind / /

Create procedure testprocbind (in modnum int (11) in inbeginno int (11) in endno int (11) in obeginno int (11) in oendno int (11))

Begin

Declare b int (11)

Declare c int (11)

Declare d int (11)

Declare sj int (11)

Declare stmt varchar (2000)

Set @ center1

Set d=endno-inbeginno+1

Call productmetadatao (modnum,obeginno,oendno,inbeginno)

Insert into test.tmp_rosterusers select * from test.tmp_friend

While (@ c < d) do

Truncate table tmp_friend1

Select floor (rand () * count (*)) into @ sj from tmp_friend

/ *

Set @ sqlstr=concat ('insert into test.tmp_friend1 select aid+',c,',bid+',c,' from test.tmp_friend order by rand () limit', sj)

, /

Set @ sqlstr=concat ('insert into test.tmp_friend1 select aid+?,bid+? from test.tmp_friend order by rand () limit?')

Prepare stmt from @ sqlstr

Execute stmt using @ c _

Insert into test.tmp_rosterusers select aid,bid from test.tmp_friend1 where aid < endno and bid < oendno

Insert into test.tmp_rosterusers select bid,aid from test.tmp_friend1 where aid < endno and bid < oendno

Set @ c=@c+1

End while

Deallocate prepare stmt

End

/ /

-- cursor

Delimiter / /

Drop procedure insertfriend / /

Create procedure insertfriend ()

BEGIN

Declare b int default 0

Declare i int

Declare cnt int

Declare uname int (11)

DECLARE cur_1 CURSOR FOR select userid from test.tmp_r

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET b = 1

OPEN cur_1

Truncate table tmp_friend

Select count (*) into cnt from test.tmp_r

Set uname=0

Repeat

Fetch cur_1 into i

Set uname=uname+1

Insert into tmp_friend select i,userid from tmp_r where userid i

Until uname = cnt

End repeat

Close cur_1

End

/ /

-- while

CREATE PROCEDURE productmetadata (in modnum int (11), in beginno int (11), in endno int (11))

BEGIN

DECLARE an int (11)

DECLARE b int (11)

Set a=beginno

Select (endno-beginno+1) / modnum into b

Truncate table test.tmp_r

While (a)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.