温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

mysql存储过程是怎么样的

发布时间:2021-11-20 09:25:32 来源:亿速云 阅读:104 作者:柒染 栏目:MySQL数据库

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存储过程是怎么样的问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注亿速云行业资讯频道了解更多相关知识。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI