通过一个应用案例让读者熟悉在实际开发中,创建并使用存储过程的完整过程。
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语句,并且要有创建存储过程的权限。但是使用存储过程可以在实际开发中简化操作,减少冗余的操作步骤,同时还可以减少过程中的失误,提高效率,因此存储过程是非常有用的,应该学会使用,并熟练运用。