189 8069 5689
MySQL存储过程是怎么样的，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
成都创新互联公司是一家专注于成都网站设计、网站制作与策划设计,宜都网站建设哪家好?成都创新互联公司做网站,专注于网站建设十余年,网设计领域的专业建站公司;建站业务涵盖:宜都等地区。宜都做网站价格咨询:13518219792
存储过程内绑定变量
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存储过程是怎么样的问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注创新互联行业资讯频道了解更多相关知识。