MySQL 复习存储过程&游标使用

关于存储过程

	待补充。。。

关于游标

	待补充。。。

存储过程&游标实例

创建数据库test

create database test charset='utf8';

创建数据表teacher

create table if not exists teacher(
	id varchar(4) primary key,
	name varchar(8),
	tele varchar(11)
	gender boolean);

teacher表插入数据

insert into teacher values("0001",'Mary','15008475721',1);
insert into teacher values("0001",'Tom','15008475722',0);

创建数据表students

create table if not exists students(
	stu_id varchar(4) primary key,
	stu_name varchar(8),
	stu_gender boolean,
	stu_birth datetime,
	teacher_id varchar(4));

students表与teacher建立外键

alter table students add foreign key(teacher_id) references teacher(id);

students表插入数据

insert into students values("s001",'小红',1,'2000-1-1',1);
insert into students values("s001",'小刚',0,'2008-1-1',1);
insert into students values("s001",'小茜',1,'2012-1-1',2);
insert into students values("s001",'小青',1,'2014-1-1',1);

实现根据老师ID查找其带领的学生，学生性别值为1显示为女，性别值为0显示男。

CREATE PROCEDURE `sp_stuInfo`(
	IN `tea_id` VARCHAR(4)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Accroding to teacher`s id to find his/her students'
BEGIN
	DECLARE sid VARCHAR(4);
	DECLARE sname VARCHAR(8);
	DECLARE sgender boolean;
	DECLARE sex VARCHAR(2);
	DECLARE stuInfo varchar(32) default "";
	
	DECLARE stu_cursor cursor FOR 
		SELECT stu_id, stu_name, stu_gender
		FROM test.students WHERE students.teacher_id=tea_id;
	
	OPEN stu_cursor;
	
	fetch stu_cursor INTO sid, sname, sgender;
	
	-- gender 值为1，则显示为女；值为0，则显示为男
	if sgender =1 
		then SET sex = "女";
	ELSE SET sex = "男";
	END if;
	SET stuInfo = CONCAT("学号：",sid, " 姓名：", sname, " 性别：", sex);
	SELECT sid,sname, sex, stuInfo;
	
	close stu_cursor;  
END

结果如图所示：