游标的使用——mysql

CREATE DEFINER=`root`@`%` PROCEDURE `split_category_all`()
BEGIN
declare categ varchar(10); ##套餐列
declare str varchar(10);
declare num int;
declare split_key_,id_ bigint;
declare flage int; ##看看是不是纯数字 ，如果是数字为 0；
declare n,n1 int; ##计数器

DECLARE cur_1 CURSOR FOR
select id,split_key,category from `study-projectcenter`.el_pc_course where split_key = 21088161705;
SET n1 = (select count(id) from `study-projectcenter`.el_pc_course where split_key = 21088161705);
SET n=0;
##DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_1;

## FETCH cur_1 INTO id_,split_key_,categ; /*获取第一条记录*/

while n< n1 do
FETCH cur_1 INTO id_, split_key_, categ; /*取下一条记录*/
if categ is not null then
set categ=TRIM(categ);
set num = length(categ);

set flage=(SELECT categ REGEXP '[^0-9.]'); ##如果有字符 返回1

if flage =1 then
if str='' then
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, 1
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;
else ##其他类型都插 0；
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, 0
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;
end if;

else
if num = 1 then
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, cast(category as SIGNED)
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;

elseif num = 2 then
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, cast(left(category,1) as SIGNED)
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, cast(right(category,1) as SIGNED)
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;

elseif num = 3 then
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, cast(left(category,1) as SIGNED)
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, cast(substring(category,2,1) as SIGNED)
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, cast(right(category,1) as SIGNED)
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;

else
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, 0
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;

end if;

end if;
else
insert into el_pc_course_temp(split_key,course_id,course_name,category) select split_key,id,course_name, 1
from `study-projectcenter`.el_pc_course where split_key=split_key_ and id=id_;


end if;

set n=n+1;

end while;

close cur_1;

END;

 

以上是游标在存储过程中的使用。

DECLARE cur_1 CURSOR FOR
select id,split_key,category from `study-projectcenter`.el_pc_course where split_key = 21088161705; ##定义游标 并赋值；
SET n1 = (select count(id) from `study-projectcenter`.el_pc_course where split_key = 21088161705); ##定义while 循环上限；
SET n=0;　　##定义while循环下限；
OPEN cur_1;  ##打开游标
while n< n1 do   ##while 开始
FETCH cur_1 INTO id_, split_key_, categ; /*取一条记录到变量  id_, split_key_, categ 中*/ 注意： 一定要放在while下面，紧贴着

set n=n+1;  ##n 要自增
end while;  ##while循环结束

close cur_1; ##关闭游标