MySQL学习——操作存储过程
摘要:本文主要学习了使用DDL语句操作存储过程的方法。
了解存储过程
是什么
存储过程是一组为了完成特定功能的SQL语句集合。
使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程。
优点
1)封装性
存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。
2)可增强SQL语句的功能和灵活性
存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
3)可减少网络流量
由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。
4)高性能
存储过程执行一次后,产生的二进制代码就驻留在缓冲区,在以后的调用中,只需要从缓冲区中执行二进制代码即可,从而提高了系统的效率和性能。
5)提高数据库的安全性和数据的完整性
使用存储过程可以完成所有数据库操作,并且可以通过编程的方式控制数据库信息访问的权限。
创建存储过程
语法
1 create procedure 存储过程名([参数]) 存储过程体
说明
1)存储过程名
存储过程的名称,默认在当前数据库中创建。若需要在特定数据库中创建存储过程,则要在名称前面加上数据库的名称,即 数据库名.存储过程名 。需要注意的是,名称应当尽量避免选取与MySQL内置函数相同的名称,否则会发生错误。
2)参数
存储过程的参数列表。格式如下:
1 [in|out|inout] 参数名 参数类型
MySQL存储过程支持三种类型的参数,即输入参数、输出参数和输入/输出参数,分别用in、out和inout三个关键字标识。其中,输入参数可以传递给一个存储过程,输出参数用于存储过程需要返回一个操作结果的情形,而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是,参数的取名不要与数据表的列名相同,否则尽管不会返回出错信息,但是存储过程的SQL语句会将参数名看作列名,从而引发不可预知的结果。
当有多个参数时,参数列表中彼此间用逗号分隔。存储过程可以没有参数(此时存储过程的名称后仍需加上一对括号),也可以有1个或多个参数。
3)存储过程体
存储过程的主体部分,包含在过程调用的时候必须执行的SQL语句。这个部分以关键字begin开始,以关键字end结束。若存储过程体中只有一条SQL语句,则可以省略begin和end。
delimiter命令
在MySQL中,服务器处理SQL语句默认是以分号作为语句结束标志的。然而,在创建存储过程时,存储过程体可能包含有多条SQL语句,这些SQL语句如果仍以分号作为语句结束符,那么MySQL服务器在处理时会以遇到的第一条SQL语句结尾处的分号作为整个程序的结束符,而不再去处理存储过程体中后面的SQL语句,这样显然不行。
为解决这个问题,通常可使用delimiter命令将结束命令修改为其他字符。
当使用delimiter命令时,应该避免使用反斜杠“\”字符,因为它是MySQL的转义字符。
实例
创建不带参数的存储过程:
1 mysql> delimiter //
2 mysql> create procedureshowScore()3 -> begin
4 -> select * fromscore;5 -> end //
6 Query OK, 0 rows affected (0.00sec)7
8 mysql>
创建带有参数的存储过程:
1 mysql> delimiter //
2 mysql> create procedure showStuScore(in stu varchar(20))3 -> begin
4 -> select * from score where student =stu;5 -> end //
6 Query OK, 0 rows affected (0.00sec)7
8 mysql>
使用存储过程
语法
1 call 存储过程名称([参数]);
实例
1 mysql>call showScore();2 +----+---------+-----------+-------+
3 | id | student | course | grade |
4 +----+---------+-----------+-------+
5 | 2 | 张三 | 英语 | 53 |
6 | 3 | 李四 | 计算机 | 48 |
7 | 4 | 李四 | 中文 | 38 |
8 | 5 | 王五 | 中文 | 95 |
9 | 6 | 赵六 | 计算机 | 70 |
10 | 7 | 赵六 | 英语 | 92 |
11 | 8 | 赵六 | 中文 | 73 |
12 | 9 | 孙七 | 英语 | 94 |
13 | 10 | 周八 | 计算机 | 90 |
14 | 11 | 周八 | 英语 | 85 |
15 | 12 | 吴九 | 计算机 | 90 |
16 | 13 | 吴九 | 中文 | 55 |
17 +----+---------+-----------+-------+
18 12 rows in set (0.00sec)19
20 Query OK, 0 rows affected (0.00sec)21
22 mysql>
查看存储过程
查看所有存储过程
1 mysql> show procedurestatus;2 +------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
4 +------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
5 | demo | showScore | PROCEDURE | root@localhost | 2019-09-09 21:03:42 | 2019-09-09 21:03:42 | DEFINER | | utf8 | utf8_general_ci | gb2312_chinese_ci |
6 | demo | showStuScore | PROCEDURE | root@localhost | 2019-09-09 21:06:10 | 2019-09-09 21:06:10 | DEFINER | | utf8 | utf8_general_ci | gb2312_chinese_ci |
7 +------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
8 2 rows in set (0.00sec)9
10 mysql>
查看存储过程的创建语句
1 mysql> show create procedureshowScore;2 +-----------+--------------------------------------------+------------------------------------------------------------+----------------------+----------------------+--------------------+
3 | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
4 +-----------+--------------------------------------------+------------------------------------------------------------+----------------------+----------------------+--------------------+
5 | showScore | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE`showScore`()6 begin
7 select * fromscore;8 end | utf8 | utf8_general_ci | gb2312_chinese_ci |
9 +-----------+--------------------------------------------+------------------------------------------------------------+----------------------+----------------------+--------------------+
10 1 row in set (0.00sec)11
12 mysql>
修改存储过程
可以通过先删除存储过程,然后重新创建存储过程的方法实现修改的操作。
删除存储过程
语法
1 drop procedure 存储过程名称
实例
1 mysql> drop procedureshowScore;2 Query OK, 0 rows affected (0.00sec)3
4 mysql>