流程控制综合运用
【示例11-11】循环访问光标操作,访问光标中的所有记录,代码如下:
mysql> use school; #选择数据库school
mysql> DELIMITER $$
mysql> create procedure query_all_students (IN sid int, OUT cname varchar(128), OUT cid int)
BEGIN
declare tmp_name varchar(128); #必须定义在声明光标之前
declare tmp_cid int;
declare done int default 0;
declare cur_student CURSOR FOR SELECT name, class_id FROM student ;
declare continue handler for not found set done = 1; #将结束标志绑定到游标上
open cur_student;
read_loop:LOOP #循环读取
fetch cur_student into tmp_name, tmp_cid;
IF done=1 then
Leave read_loop;
END IF;
select tmp_name, tmp_cid; #打印从光标中获取到的值
END LOOP read_loop;
close cur_student;
set cname = tmp_name, cid = tmp_cid;
END;
mysql> $$
mysql> DELIMITER ;
【示例11-12】在学生表中插入一条记录,并返回记录的自增长id
mysql> use school; #选择数据库school
mysql> DELIMITER $$
mysql> create procedure fetch_insert_student_id (IN p_name varchar(128), in p_class_id int, IN p_sex char(1), OUT rid int)
BEGIN
Insert into student (name, class_id, sex) values(p_name, p_class_id, p_sex);
select last_insert_id() as rid;
END;
mysql> $$
mysql> DELIMITER ;
具体操作
结语:
掌握这两个示例, 就行了, 不变应万变, 就是多条语句的组合
时间: 2020-07-15