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
//

[@more@]