drop procedure if exists add_test;
# 创建存储过程 add_test
CREATE PROCEDURE add_test()
BEGIN
#定义 变量 DECLARE a bigint;
#此变可有可无,为了给个该存储函数执行成功后给个提示,运行下便知道 DECLARE str VARCHAR(300);
DECLARE x int;
#这个用于处理游标到达最后一行的情况 DECLARE s int default0;
#声明游标cursor_name(cursor_name是个多行结果集) DECLARE cursor_name CURSOR FORselect id from category;
#设置一个终止标记 DECLARECONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; set str = "--";
#打开游标
OPEN cursor_name;
#获取游标当前指针的记录,读取一行数据并传给变量a
fetch cursor_name into a;
#开始循环,判断是否游标已经到达了最后作为循环条件 while s <> 1doset str = concat(str,x);
#insert into to_data(id,name) values(a,b); #读取下一行的数据 if (SELECT count(1) cnt from category_att_name WHERE category_id = a and att_name_id = 3) = 0then#SELECT concat('myvar is ', a);
INSERT INTO category_att_name(category_id,att_name_id,sort_value,type,is_required)VALUES(a,3,0,1,1);
endif;
if (SELECT count(1) cnt from category_att_name WHERE category_id = a and att_name_id = 4) = 0then#SELECT concat('myvar is ', a);
INSERT INTO category_att_name(category_id,att_name_id,sort_value,type,is_required)VALUES(a,4,0,1,1);
endif;
fetch cursor_name into a;
endwhile;
#关闭游标
CLOSE cursor_name ;
select str;
#语句执行结束 END;
#调用存储函数add_test CALL add_test()