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, or
processed”
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