一 基本语法
– 声明SQL语句执行结束标志符号为 $(默认是分号,不修改这里,会影响存储过程内的整体执行)
delimiter $
– 创建存储过程
create PROCEDURE set_sc_student_age()
begin
– 这里实现存储过程的业务
end$
– delimiter ; 恢复SQL默认结束符号为分号;
delimiter ;
– 调用存储过程
call set_sc_student_age();
二 存储实战应用
2.1 需求背景
2.1.1 存在科目表,存储了科目名称和它的id
表名: course
字段 :
课程主键: id_cource
课程名称: course_name
2.1.2 存在成绩表,存储所有学生的所有课程的成绩;
表名: score
字段:
学生id,学生名字(与本次需求无关)
科目主键: course_id
科目份数: course_score
2.2 需求
需要往成绩表score内增加冗余字段 科目名称course_name,方便单表查询就可拿到对应的数据;
2.3 分析确定方案
新增的字段初始化值,使用SQL脚本实现(存储过程);
查询课程表拿到所有课程id和名称
SELECT c.course_id,c.course_name FROM course c;
根据课程id到程表内更新所有数据的课程名称字段;
这里涉及到的技术难点有两项:
存储过程的创建与调用;
存储过程内遍历结果集;
2.4 方案实现
ALTER TABLE score ADD course_name VARCHAR(32);
-- 删除已经存在名字为set_course_name 存储过程;
DROP PROCEDURE IF EXISTS set_course_name;
-- 声明SQL语句执行结束标志符号为 $$(默认是分号,不修改这里,会影响存储过程内的整体执行)
DELIMITER $$
-- 创建存储过程
CREATE PROCEDURE set_course_name()
BEGIN
-- 声明局部变量 id 和 age; 用于接收结果集的遍历结果值;更新到sc表;
DECLARE courseId INT;
DECLARE courseName VARCHAR(32);
-- 声明while循环标志
DECLARE while_end_flag INT DEFAULT 0;
-- 声明游标,执行后边SQL的结果集
DECLARE score_cursor CURSOR FOR SELECT c.course_id,c.course_name FROM course c;
-- 声明handler 运行报错代码为1329时 赋值标志while_end_flag为1;用于终止循环
-- 1329 代表fetch遍历结果集,找不到数据时报错代码;
DECLARE CONTINUE HANDLER FOR 1329 SET while_end_flag = 1;
-- 开启游标
OPEN score_cursor;
-- 从游标内取出一条数据,并且赋值给局部变量courseId,courseName
FETCH score_cursor INTO courseId,courseName;
-- 循环更新数据;
WHILE while_end_flag <> 1 DO
UPDATE score SET course_name = courseName WHERE course_id = courseId;
-- 在上一次基础上,取下一条数据;并且扶着给局部变量courseId,courseName
FETCH score_cursor INTO courseId,courseName;
END WHILE;
-- 关闭游标
CLOSE score_cursor;
-- 存储过程创建结束
END$$
-- delimiter ; 恢复SQL默认结束符号为分号;
DELIMITER ;
-- 调用存储过程
CALL set_course_name();
(可能有些人觉得,使用存储过程实现这个没必要,使用java代码实现比较简单;毕竟我们都是java开发嘛;
但是; 实现情况在,你不可能为了做一次数据的初始化,开发一个新的接口,在生产上会出现安全问题;还有一点是类似这样的历史数据处理,项目中是很多的,特别是更新迭代比较频繁;都搞接口,那会多出很多只需要执行一次的接口,领导是不会同意的,所以SQL脚本才是做好的选择)