MySQL存储过程
1 MySQL存储过程简介

存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理
存储过程的优点:
- 增强SQL语句的功能和灵活性
- 实现较快的执行速度
- 减少网络流量
2 MySQL存储过程语法结构分析

参数
- IN ,表示该参数的值必须在调用存储过程时指定
- OUT , 表示该参数的值可以被存储过程改变,并且可以返回
- INOUT , 表示该参数的调用时指定,并且可以被改变和返回
特性
COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER }
特性
COMMENT:注释
CONTAINS SQL : 包含SQL语句,但不包含读或写数据的语句
NO SQL: 不包含SQL语句
READS SQL DATA: 包含读数据的语句
MODIFIES SQL DATA: 包含写数据的语句
SQL SECURITY { DEFINER | INVOKER} 指明谁有权限执行
2.1 过程体
- 过程体由合法的SQL语句构成
- 过程体可以使任意SQL语句
- 过程体如果为复合结构则使用BEGIN…END语句
- 复合结构可以包含声明,循环,控制结构
(但是实际上这个任意是加引号的;
我们不可能通过存储过程创建数据表,页不可能通过存储过程来创建数据库,
所谓的任意是对于记录的增删改查,以及多表连接的操作)
3 创建不带参数的存储过程
3.1 调用存储过程
- CALL sq_name([parameter[,…]])
- CALL sp_name[()]
--创建一个存储过程
mysql> create procedure sp1() select version();
Query OK, 0 rows affected (0.00 sec)
--调用不带参数的存储过程
mysql> CALL sp1;
+-------------------------+
| version() |
+-------------------------+
| 5.7.22-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
--调用不带参数的存储过程
mysql> CALL sp1();
+-------------------------+
| version() |
+-------------------------+
| 5.7.22-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
4 MySQL 创建带有IN类型参数的存储过程
mysql> desc stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| grade | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> select * from stu;
+----+----------+-----+-------+
| id | name | age | grade |
+----+----------+-----+-------+
| 1 | xiaoming | 18 | 50 |
| 2 | xiaofang | 16 | 90 |
| 3 | xiaojun | 17 | 67 |
| 4 | zuozhu | 100 | 99 |
| 5 | 张三8 | 78 | 61 |
| 6 | 张三15 | 6 | 69 |
| 7 | 张三28 | 27 | 71 |
+----+----------+-----+-------+
7 rows in set (0.00 sec)
--DELIMITER 修改定界符
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeStuById(IN id INT UNSIGNED)
-> BEGIN
-> DELETE FROM stu WHERE id = id;
-> END
-> //
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER ;
mysql> CALL removeStuById(3);
Query OK, 7 rows affected (0.00 sec)
--为什么7跳记录都被删除了呢?
--因为WHERE id = id;mysql不会主观认为等号左边的是mysql的id,
--等号右边是我们传进来的id
--所以我们在调用时传参的时候参数名最好不好与表中的字段名相同
mysql> select * from stu;
Empty set (0.00 sec)
4.1 修改存储过程
只能修改这几个简单的选项
注释,内容的类型等等
并不能修改我们的过程体
如果说想修改过程体的话,
只能是先将我们的存储过程删除,然后在进行重建
ALTER PROCEDURE sp_name [characteristic ...]
COMMENT ' string '
| { CONTAINS | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DENFINER|INVOKER }
4.2 删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
4.3 删除重建存储过程
mysql> DROP PROCEDURE removeStuById;
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeStuById(IN s_id INT UNSIGNED)
-> BEGIN
-> DELETE FROM stu WHERE id = s_id;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> select * from stu;
+----+-----------+-----+-------+
| id | name | age | grade |
+----+-----------+-----+-------+
| 8 | 卡卡西 | 27 | 77 |
| 9 | 麦克凯 | 28 | 77 |
| 10 | 鸣人 | 17 | 88 |
| 11 | 佐助 | 17 | 88 |
| 12 | 雏田 | 16 | 66 |
| 13 | 小樱 | 17 | 66 |
+----+-----------+-----+-------+
6 rows in set (0.00 sec)
mysql> CALL removeStuById(12);
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;
+----+-----------+-----+-------+
| id | name | age | grade |
+----+-----------+-----+-------+
| 8 | 卡卡西 | 27 | 77 |
| 9 | 麦克凯 | 28 | 77 |
| 10 | 鸣人 | 17 | 88 |
| 11 | 佐助 | 17 | 88 |
| 13 | 小樱 | 17 | 66 |
+----+-----------+-----+-------+
5 rows in set (0.00 sec)
5 MySQL 创建带有IN和OUT类型参数的存储过程
--删除stu表中指定id的记录,并返回剩余的记录数
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeStuAndReturnStuNums(IN p_id INT UNSIGNED,
-> OUT stuNums INT UNSIGNED)
-> BEGIN
-> DELETE FROM stu WHERE id = p_id;
-> SELECT COUNT(id) FROM stu INTO stuNums;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
--注意,这里的 INTO stuNums的INTO是将查询出来的COUNT(id)值赋给变量stuNums
--注意BEGIN 和 END 之间的变量叫做局部变量
mysql> DELIMITER ;
mysql> SELECT COUNT(id) FROM stu;
+-----------+
| COUNT(id) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
mysql> CALL removeStuAndReturnStuNums(11,@nums);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT @nums;
+-------+
| @nums |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)
--@nums,@i这个叫做用户变量;这个是跟我们的mysql客户端绑定的
mysql> set @i = 7;
Query OK, 0 rows affected (0.00 sec)
6 MySQL 创建带有多个OUT类型参数的存储过程
--根据年龄删除,返回删除的用户,返回剩余的用户
--ROW_COUNT()返回被前面语句升级的、插入的或删除的行数
--这个行数和 mysql 客户端显示的行数及
--mysql_affected_rows() C API 函数返回的值相同。
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
--插入3条数据,后使用ROW_COUNT()方法查看
mysql> INSERT stu(name) VALUES('A'),('B'),('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM stu;
+----+-----------+-----+-------+
| id | name | age | grade |
+----+-----------+-----+-------+
| 8 | 卡卡西 | 27 | 77 |
| 9 | 麦克凯 | 28 | 77 |
| 10 | 鸣人 | 17 | 88 |
| 13 | 小樱 | 17 | 66 |
| 14 | A | 15 | NULL |
| 15 | B | 15 | NULL |
| 16 | C | 15 | NULL |
+----+-----------+-----+-------+
7 rows in set (0.00 sec)
--更新id<=10,的数据,后ROW_COUNT()方法查看
mysql> UPDATE stu set name = CONCAT(name,'--imooc')
-> WHERE id <= 10;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
--删除stu表中age=27的记录,并返回,删除了多少条,剩余的记录数
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeStuByAgeAndReturnInfos(
-> IN p_age SMALLINT UNSIGNED,
-> OUT deleteStus SMALLINT UNSIGNED,
-> OUT stuCounts SMALLINT UNSIGNED)
-> BEGIN
-> DELETE FROM stu WHERE age = p_age;
-> SELECT ROW_COUNT() INTO deleteStus;
-> SELECT COUNT(id) FROM stu INTO stuCounts;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM stu;
-> //
+----+------------------+-----+-------+
| id | name | age | grade |
+----+------------------+-----+-------+
| 8 | 卡卡西--imooc | 27 | 77 |
| 9 | 麦克凯--imooc | 28 | 77 |
| 14 | A | 15 | NULL |
| 15 | B | 15 | NULL |
| 16 | C | 15 | NULL |
| 17 | 纲手 | 27 | NULL |
| 18 | 大蛇丸 | 26 | NULL |
+----+------------------+-----+-------+
7 rows in set (0.00 sec)
mysql> DELIMITER ;
mysql> SELECT * FROM stu;
+----+------------------+-----+-------+
| id | name | age | grade |
+----+------------------+-----+-------+
| 8 | 卡卡西--imooc | 27 | 77 |
| 9 | 麦克凯--imooc | 28 | 77 |
| 14 | A | 15 | NULL |
| 15 | B | 15 | NULL |
| 16 | C | 15 | NULL |
| 17 | 纲手 | 27 | NULL |
| 18 | 大蛇丸 | 26 | NULL |
+----+------------------+-----+-------+
7 rows in set (0.00 sec)
mysql> CALL removeStuByAgeAndReturnInfos(27,@a,@b);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @a as 删除的记录,@b as 剩余的记录;
+-----------------+-----------------+
| 删除的记录 | 剩余的记录 |
+-----------------+-----------------+
| 2 | 5 |
+-----------------+-----------------+
1 row in set (0.00 sec)
7 MySQL 存储过程与自定义函数的区别
- 存储过程实现的功能要复杂一些;而函数的针对性更强
- 存储过程可以返回多个值,函数只能有一个返回值
- 存储过程一般独立的来执行;而函数可以作为其他SQL语句的
- 组成部分来出现
3万+



被折叠的 条评论
为什么被折叠?



