我在学习数据库 游标 的时候做了一个procedure：输入一个数值，然后输出以这个数为边界的两个表（比如输入60，就是查询及格和不及格的人）
/*
First, create a table containing the names, ages, genders, score and student numbers of students.
*/
create table student_info
(ID varchar(12), name varchar(24), age tinyint, gender tinyint(1), score int);
/*
Next insert some data.
*/
insert student_info values(01, 'chenjiale', 22, 1, 98);
insert student_info values(02, 'zhangshan', 24, 0, 54);
insert student_info values(03, 'lishi', 18, 1, 100);
insert student_info values(04, 'wangwu', 34, 0, 69);
insert student_info values(05, 'zhaoliu', 26, 1, 12);
insert student_info values(06, 'chenqi', 15, 1, 96);
insert student_info values(07, 'liuba', 64, 0, 59);
insert student_info values(08, 'qiujiu', 74, 0, 49);
/*
create a procedure, the function is to input a score and display two parts of information
with the score as the boundary.
*/
delimiter //
create procedure get_two_parts(in in_score int)
begin
/*
建立游标，保存in_score 的ID 集合
*/
declare save_id varchar(12);
declare sub_cur cursor
for select ID from student_info where scoredeclare meta_cur cursor
for select ID from student_info where score>=in_score;
/*
创建两张表 用作显示结果
*/
create table sub_table like student_info;
create table meta_table like student_info;
/*
打开游标并进行表的插入操作
*/
open sub_cur;
while !done do
fetch sub_cur into save_id;
insert into sub_table select * from student_info where ID=save_id;
end while;
open meta_cur;
while !done do
fetch meta_cur into save_id;
insert into meta_table select * from student_info where ID=save_id;
end while;
/*
完事之后收拾手尾
*/
close meta_cur;
close sub_cur;
select * from sub_table;
select * from meta_table;
drop table sub_table;
drop table meta_table;
end //
delimiter ;
call get_two_parts(60);
然后报了这个错误
ERROR 1054 (42S22): Unknown column 'done' in 'field list'
我也试过在insert 之前先判断
if save_id != done then
insert into meta_table select * from student_info where ID=save_id;
end if;
还是不行
while 条件中的done未定义。
你需要在存储过程开头定义declare done。