MySQL——事务与存储过程（四）综合案例——存储过程应用

        通过一个应用案例让读者熟悉在实际开发中，创建并使用存储过程的完整过程。

1.创建一个 stu 表

stu表结构

字段名	数据类型	主键	外键	非空	唯一	自增
id	INT(10)	是	否	是	是	否
name	VARCHAR(50)	否	否	是	否	否
class	VARCHAR(50)	否	否	是	否	否

stu表数据

id	name	class
1	Lucy	class1
2	Tom	class1
3	Rose	class2

        在数据库 chapter06 中创建表 stu，并向表中添加数据，SQL语句具体如下：

CREATE TABLE stu(id INT,name VARCHAR(50),class VARCHAR(50));
INSERT INTO stu VALUE (1, 'Lucy', 'class1'), (2, 'Tom', 'class1'), (3, 'Rose','class2');

        通过 DESC 命令查看表 stu结构，执行结果如下：

mysql> DESC stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| class | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

        通过 SELECT * FROM stu 来查看表数据，执行结果如下：

mysql> SELECT * FROM stu;
+------+------+--------+
| id   | name | class  |
+------+------+--------+
|    1 | Lucy | class1 |
|    2 | Tom  | class1 |
|    3 | Rose | class2 |
+------+------+--------+
3 rows in set (0.00 sec)

2.创建一个存储过程

CREATE PROCEDURE addcount (out count INT)
BEGIN
DECLARE itmp INT;
DECLARE cur_id CURSOR FOR SELECT id FROM stu;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
SELECT count(*)INTO count FROM stu;
SET @sum=0;
OPEN cur_id;
REPEAT
FETCH cur_id INTO itmp;
IF itmp<10
THEN SET @sum=@sum+itmp;
END IE;
UNTIL 0 END REPEAT;
CLOSE cur_id;
END;

        创建一个存储过程 addcount 能够获取表 stu 中的记录数和 id 的和，代码格式如下：
        上面的创建存储过程的代码中使用到了变量的声明、光标、流程控制等知识点。SQL语句的执行情况如下：

mysql> DELIMITER //
mysql> CREATE PROCEDURE addcount (out count INT)
    -> BEGIN
    -> DECLARE itmp INT;
    -> DECLARE cur_id CURSOR FOR SELECT id FROM stu;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
    -> SELECT count(*)INTO count FROM stu;
    -> SET @sum=0;
    -> OPEN cur_id;
    -> REPEAT
    -> FETCH cur_id INTO itmp;
    -> IF itmp<10
    -> THEN SET @sum=@sum+itmp;
    -> END IF;
    -> UNTIL 0 END REPEAT;
    -> CLOSE cur_id;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL addcount(@count) //
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @count,@sum //
+--------+------+
| @count | @sum |
+--------+------+
|      3 |    6 |
+--------+------+
1 row in set (0.00 sec)

mysql> DELIMITER;

        从调用存储过程的结果可以看出，stu表中共有三条数据，id之和为6。这个存储过程创建了一个 cur_id 的光标，使用这个光标来获取每条记录的 id，使用 REPEAT 循环语句来实现所有 id号相加。

        本案例演示了一个完整的存储过程，从设计表结构、创建表、创建存储过程到调用存储过程达到预想的查询结果。编写存储过程并不是件简单的事情，根据不同的业务需求可能会需要非常复杂的 SQL语句，并且要有创建存储过程的权限。但是使用存储过程可以在实际开发中简化操作，减少冗余的操作步骤，同时还可以减少过程中的失误，提高效率，因此存储过程是非常有用的，应该学会使用，并熟练运用。