一、游标的使用
1.定义游标
语法:declare 游标名 cursor for select语句
2.打开游标
语法:open游标名
3.提取数据
语法:fetch游标名into 变量1,变量2.....
或fetch next from 游标名 into @variable1, @variable2; -- 假设@variable1, @variable2是已经声明的变量
4.关闭游标
语法:close游标名
例1:创建函数教f_mame,能够根据教师名字获得所国完系的负责人名字和所国完系扁号。存在一种情况,当教师的名字出观重复时,院系偏号为多个返
回值,请用游标来解决,显示所有数据。
delimiter //
drop function if exists f_tname;
create FUNCTION f_tname(x char(10))
returns char(50)
DETERMINISTIC
BEGIN
declare v_tname varchar(10);
declare v_deptno varchar(2);
declare str varchar(20);
declare m int default false;
declare cur_tea cursor for
select tname,deptno from teacher where
deptno in(select deptno from teacher where tname=x)
and title='院长';
declare continue handler for not found set m=true;
set str='';
open cur_tea;
read_loop:loop
fetch cur_tea into v_tname,v_deptno;
if m then
leave read_loop;
end if;
set str=concat(str,v_tname,v_deptno,';');
end loop read_loop;
close cur_tea;
if str='' THEN
set str='未找到信息';
end if;
return str;
END;
select f_tname('王翠茂');
例3.创建函的f_tname,能够根据教师名字获得所属院系的负责人名字。存在一种错误情况,当教师的名字出现重复时,院系编号为多个返回值,其错误具体描述为:
delimiter //
drop function if exists f_tname;
create function f_tname(x char(10))
returns char(6)
DETERMINISTIC
begin
declare n varchar(10);
-- declare exit handler for 1242 return '返回多个值';
-- declare exit handler for sqlstate'21000' return '返回多个值';
declare exit handler for sqlexception return '返回多个值';
select tname into n from teacher where
deptno=(select deptno from teacher where tname=x) and title='院长';
if n is null then
return '查无此人';
else
return n;
end if;
END;
select f_tname('王翠茂');