一 基本语法

– 声明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脚本才是做好的选择)