共计 3581 个字符，预计需要花费 9 分钟才能阅读完成。
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;
END;
//
delimiter //
CREATE PROCEDURE changetable(in inscript1 varchar(210),in inscript2 varchar(1000))
begin
declare i int(5);
declare d char(2);
declare stmt varchar(4000);
set i = 0;
while (i 100) do
set d = lpad(i,2, 0
set @sqlstr=concat(inscript1,d, ,inscript2);
select @sqlstr;
prepare stmt from @sqlstr;
execute stmt;
set i = i + 1;
end while;
end
//
– 事务
delimiter //
drop procedure if exists sp_inbosscache //
create procedure sp_inbosscache()
begin
declare i int;
declare d char(2);
declare stmt varchar(4000);
DECLARE EXIT HANDLER FOR SQLEXCEPTION rollback;
set i=0;
select begin
while(i 100) do
set d=lpad(i,2, 0
— START TRANSACTION;
— (regtype,source,type,p_id,ismf,mp,oprcode,spid,bizcode,efftt,status,syncstatus,starttime,updatetime,synctime)
— 255 , BOSS , order ,p_id,0,mp,oprcode, ,1,DATE_FOMAT(NOW(), %Y%m%d%H%i%s ),0,2,now(),now(),starttime
— from gcs_cache_boss_tmp
— id how to do ?
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 存储过程是怎么样的问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。
