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; ##关闭游标