参考:Re: Temp Table in Select of a Cursor
方法一:
delimiter $$ create procedure test_temp() begin drop temporary table if exists tmp; create temporary table tmp(id int unsigned); insert into tmp (id) values (1); begin -- (主要增加Begin与End) declare v_id int unsigned; declare csr1 cursor for select * from tmp; open csr1; fetch csr1 into v_id; select v_id; close csr1; end; -- (主要增加Begin与End)
end;
$$
-- 测试使用
call test_temp() ;
$$
方法二:
delimiter $$
create procedure test_temp() begin declare v_id int unsigned; declare csr1 cursor for select * from tmp; -- 先定义游标
-- 后定义临时表并填充数据 drop temporary table if exists tmp; create temporary table tmp(id int unsigned); insert into tmp (id) values (1); open csr1; fetch csr1 into v_id; select v_id; close csr1; end; $$
-- 测试使用
call test_temp() ;
$$