mysql 存储过程（临时表、循环、游标综合运用）

实现：从一张表里根据输入的值查询4条不同的记录，如果少于4条，从第一条开始查询，补足四条（注：如果碰到补足的这几条有已在原先的那几条里面的则忽略这几条）

表内容：

实现后的效果：

输入 99

call pr_youhui('99')

输入  299

call pr_youhui('299')

如果不满足4条记录则如下

表内容

输入199

call pr_youhui('199')

存储过程

create  PROCEDURE pr_youhui(in number VARCHAR(32))
BEGIN
DECLARE countnum INT;
DECLARE pandun INT;
DECLARE lis INT;
DROP TABLE if  exists youhuitable1;
DROP TABLE if  exists youhuitable2;
DROP TABLE if exists youhuitable;
create temporary table if not exists youhuitable1(id VARCHAR(32),begins VARCHAR(32),endse VARCHAR(32));//创建临时表
create temporary table if not exists youhuitable2(id VARCHAR(32),begins VARCHAR(32),endse VARCHAR(32));
create temporary table if not exists youhuitable(id VARCHAR(32),begins VARCHAR(32),endse VARCHAR(32));


set countnum=(select  count(1) from test1 t where CAST(t.`end` as decimal(10,2)) >=number ORDER BY CAST(t.`begin` as decimal(10,2)) ASC LIMIT 4);
set pandun=0;
set lis=4-countnum;


if countnum !=4 THEN  //少于4条记录
insert into  youhuitable1 select  t1.`id`,t1.`begin` ,t1.`end`  from  test1 t1 where CAST(t1.`end` as decimal(10,2)) >=number 
ORDER BY CAST(t1.`begin`  as decimal(10,2)) ASC LIMIT countnum;
insert into youhuitable2 select  t2.`id`,t2.`begin` ,t2.`end`  from  test1 t2  
ORDER BY CAST(t2.`begin` as decimal(10,2)) ASC LIMIT lis;
insert into  youhuitable select  t3.`id`,t3.`begin` ,t3.`end`  from  test1 t3 where CAST(t3.`end` as decimal(10,2)) >=number ORDER BY CAST(t3.`begin`  as decimal(10,2)) ASC LIMIT countnum ;
begin
declare ids2 VARCHAR(32);
  declare begins2 VARCHAR(32);
declare endse2 VARCHAR(32);
declare i int DEFAULT 0;
DECLARE curt2 cursor for select  y2.`id`,y2.`begins` ,y2.`endse`  from  youhuitable2 y2;//定于游标
DECLARE CONTINUE HANDLER FOR not  FOUND SET i = NULL;//定义游标要加上这个要不然会报“No data - zero rows fetched, selected, orprocessed”
open curt2 ;
while i<lis DO//循环
set i=i+1;
FETCH curt2 into ids2,begins2,endse2;
BEGIN
declare ids1 VARCHAR(32);
declare begins1 VARCHAR(32);
declare endse1 VARCHAR(32);
declare j int DEFAULT 0;
DECLARE curt1 cursor for select  y1.`id`,y1.`begins` ,y1.`endse` from  youhuitable1 y1  ;//嵌套游标
DECLARE CONTINUE HANDLER FOR not  FOUND SET j = null;
open curt1;
WHILE j<countnum DO
set j=j+1;
FETCH curt1 INTO ids1,begins1,endse1;
if ids2 =ids1 THEN
set pandun=1;
end if;
end WHILE;
  close curt1;
if pandun=0 THEN
insert into youhuitable(id,begins,endse)VALUES(ids2,begins2,endse2);
  end if;
END;
end WHILE;
close curt2;
end;
else//等于4条记录
insert into youhuitable select  t3.`id`,t3.`begin` ,t3.`end`    from  test1 t3 where CAST(t3.`end`  as decimal(10,2)) >=number 
ORDER BY CAST(t3.`begin` as decimal(10,2)) ASC LIMIT 4;
end if;
select * from youhuitable;
end