mysql存储进程是怎么样的
发布时间：2021-12-16 15:32:59 所属栏目：MySql教程 来源：互联网
导读：mysql存储过程是怎么样的，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。 存储过程内绑定变量 delimiter // drop procedure testprocbind // create procedure testprocbind(in modn
|
mysql存储过程是怎么样的，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。 存储过程内绑定变量 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 @c=1; 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,@c,@sj; 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 a int(11); DECLARE b int(11); set a=beginno; select (endno-beginno+1)/modnum into b; truncate table test.tmp_r; while (a <= endno) do insert into test.tmp_r values(a); set a = a+modnum; end while; call insertfriend() ; END; // call productmetadata(2000,1,100000) // ---------------------------------------------------------------------------- delimiter // drop procedure addrosterusers // CREATE PROCEDURE addrosterusers (in modnum int(11),in inbeginno int(11),in endno int(11)) BEGIN DECLARE b int(11); DECLARE c int(11); set c=1; call productmetadata(modnum,inbeginno,endno); insert into test.tmp_rosterusers select * from test.tmp_friend; while (c < modnum) do insert into test.tmp_rosterusers select aid+c,bid+c from test.tmp_friend; set c=c+1; end while; set @sqlstr=concat('insert into gcs_db.gcs_cache_boss_',d,'(id,regtype,source,type,p_id,ismf,mp,oprcode,spid,bizcode,efftt,status,syncstatus,starttime,updatetime,synctime) select concat(DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'),id),'255','BOSS','order',p_id,0,mp,oprcode,' ',1,DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'),0,2,now(),now(),starttime from gcs_db.gcs_cache_boss_tmp where mod(mp,100)=',i); -- select @sqlstr; prepare stmt from @sqlstr; execute stmt; -- COMMIT; set i=i+1; end while; end // 关于mysql存储过程是怎么样的问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。 （编辑：源码门户网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐