SQL存储过程学习笔记

keep hungry keep foolish
——乔布斯演讲

前言

记录SQL存储过程基本概念，语法，书籍案例和本人测试案例，持续更新中。

一、基本概念

存储过程就是为以后使用二保存的一条或多条SQL语句（比如循环执行之类）。可将其视为批文件，虽然它们的作用不仅限于批处理。

二、执行存储过程

Oracle版本

EXECUTE 存储过程名称(参数);

Mysql版本

CALL 存储过程名称(参数);

三、创建存储过程

Oracle版本

CREATE PROCEDURE MailingListCount(
  Listcount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := vrows;
END;

Mysql版本

-- 从学生表中查出某个学生id对应的学生姓名
CREATE PROCEDURE user_xxx(
in a int
)
BEGIN
	SELECT student_name
	FROM t_student
	WHERE student_id = a;
END;

--执行上面存储过程, 查询学生id为2的学生名称
CALL user_xxx(2);

四、存储过程优缺点

（一）优点

1. 提高SQL的灵活性和功能，比如可以写循环。
2. 保证数据的安全性和完整性。
3. 改善SQL语句的性能，在运行前，数据库会对语句进行优化，执行快。
4. 减低网络通信量，类似于用参数调用封装好的SQL集。

（二）缺点

1. 调试麻烦
2. 可移植性差
3. 如果带有引用关系的对象发生改变时，受影响的存储过程，需要重新编译。
4. 维护困难

五、存储过程循环处理数据案例

场景描述：
现有学生信息表和学生成绩表，需求为汇总每个学生的总分，记录到学生总成绩表里。

-- 学生信息表
CREATE TABLE t_student
(
    student_id INT COMMENT '学生id'
   ,student_name VARCHAR(50) COMMENT '学生姓名'
);

-- 学生成绩表
CREATE TABLE t_score
(
    id INT NOT NULL AUTO_INCREMENT COMMENT '主键'
   ,subject VARCHAR(2) NOT NULL COMMENT '科目（01：语文，02：数学，03：英语）'
   ,score INT NOT NULL COMMENT '分数'
   ,student_id INT NOT NULL COMMENT '所属学生id'
   ,PRIMARY KEY (id)
);

-- 学生总成绩表
CREATE TABLE t_student_total_score
(
   id INT NOT NULL AUTO_INCREMENT COMMENT '主键'
  ,student_id INT NOT NULL COMMENT '所属学生id'
  ,total_score INT NOT NULL COMMENT '总分'
  ,PRIMARY KEY (id)
);

-- 插入测试数据
INSERT INTO t_student VALUES
(1,'小明'),
(2,'小红'),
(3,'老王');

INSERT INTO t_score(subject, score, student_id) VALUES
('01',90,1),
('02',91,1),
('03',89,1),
('01',99,2),
('02',98,2),
('03',96,2),
('01',100,3),
('02',100,3),
('03',100,3);

学生信息表和学生成绩表数据如下：

编写存储过程：

CREATE PROCEDURE summary_score()
BEGIN
	-- 定义循环是否完成
	DECLARE done BOOLEAN default false;
    -- 定义变量传递studentid
	DECLARE studentid INT;
    -- 定义游标
    DECLARE cursor_student cursor for SELECT student_id FROM t_student;
    -- 定义CONTINUE HANDLER
    DECLARE CONTINUE HANDLER for sqlstate '02000' set done=true;
    OPEN cursor_student;
    REPEAT
        FETCH cursor_student INTO studentid;
        INSERT INTO t_student_total_score(student_id, total_score)
        SELECT
            student_id
           ,SUM(score)
        FROM t_score
        WHERE student_id = studentid
        AND NOT EXISTS
        (
            SELECT 1 
            FROM t_student_total_score
            WHERE student_id = studentid
        )
        GROUP BY student_id;
    UNTIL DONE end repeat;
    SELECT studentid;
    CLOSE cursor_student;
END;

结果如下：

CALL summary_score();

六、参考书籍与文章

1. 《SQL必知必会（第4版）》 【美】Ben Forta 著 钟鸣 刘晓霞 译
2. MySql存储过程循环的使用分析详解

总结

keep hungry keep foolish 出自乔布斯斯坦福大学的毕业典礼演讲。意为保持求知的心态，保持谦逊的态度。
假期七天，我也要争取学习一下，查漏补缺。