1.什么是存储过程
很多时候,SQL语句都是针对一个或多个表的单条语句。但是也有时候有的查询语句需要进行多次联表查询才能完成,此时就需要用到存储过程了。
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中经过第一次编译后,再次调用不需要再次编译。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
2.为什么要使用存储过程
1.存储过程增强了SQL语言的功能和灵活性。存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
2.存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
3.存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
4.存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
5.存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
3.定义存储过程
语法:
create procedure 过程名(参数1,参数2....)
begin
sql语句;
end
创建存储过程之前我们必须修改mysql语句默认结束符;
3.1 使用delimiter可以修改执行符号
DELIMITER是分割符的意思,因为MySQL默认以";"为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错。
所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将";"当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。
例子:
# delimiter 新执行符号
MariaDB [book]> delimiter % # 使用delimter后,每条SQL语句的结束符就变为%
MariaDB [book]> create procedure selCg()
-> begin
-> select * from category;
-> end %
Query OK, 0 rows affected (0.08 sec)
4.调用存储过程
语法:
call 过程名(参数1,参数2);
例子:
MariaDB [book]> call selCg() % # 调用上面定义的存储过程 selCg
+---------+---------------+
| bTypeId | bTypeName |
+---------+---------------+
| 1 | windows应用 |
| 2 | 网站 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
| 10 | AutoCAD技术 |
+---------+---------------+
9 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [book]> delimiter ; # 使用delimter把SQL语句的结束符改回原来的';'
5.存储过程参数类型
5.1 In参数
特点:读取外部变量值,且有效范围仅限存储过程内部
例子一:
MariaDB [book]> delimiter // # 使用delimter把SQL语句的结束符改为的'//'
MariaDB [book]> create procedure pin(in p_in int) # 定义存储过程,读取外部的整型变量p_in
-> begin
-> select p_in; # 读取参数中传递的p_in的值,p_in来自存储过程外部
-> set p_in = 2; # 更改参数p_in的值为2
-> select p_in; # 再次查询参数p_in的值
-> end//
Query OK, 0 rows affected (0.01 sec)
MariaDB [book]> delimiter ; # 使用delimter把SQL语句的结束符改回原来的';'
MariaDB [book]> set @num = 1; # 在console中设定一个变量num,值为1
Query OK, 0 rows affected (0.01 sec)
MariaDB [book]> call pin(@num); # 把前面定义的变量num做为参数,调用存储过程pin
+------+ # 第一次查询到的值为1,经过存储过程为参数重新赋值,第二次查询到的值为2
| p_in |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
+------+
| p_in |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
MariaDB [book]> call pin(6); # 再次调用存储过程pin,传入参数为整数6
+------+ # 同样第一次查到的值为6,第二次查到的值改为2
| p_in |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
+------+
| p_in |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [book]> select @num; # 在console中查询num变量的值,仍为1,存储过程的作用范围只在存储过程内部有效
+------+
| @num |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
例子二:定义存储过程 getOneBook,当输入某书籍 id 后,可以调出对应书籍记录
MariaDB [book]> delimiter // # 修改SQL语句结束符为'//'
MariaDB [book]> create procedure get_book_by_id(in b int) # 定义存储过程get_book_by_id,传入book的id,查询对应book的信息记录
-> begin
-> select * from books where bId=b;
-> end//
Query OK, 0 rows affected (0.00 sec)
MariaDB [book]> delimiter ; # 修改SQL语句结束符为';'
MariaDB [book]> call get_book_by_id(4); # 查询book的id为4的记录
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
| bId | bName | bTypeId | publishing | price | pubDate | author | ISBN |
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
| 4 | pagemaker 7.0短期培训教程 | 9 | 中国电力出版社 | 43 | 2005-01-01 | 孙利英 | 7121008947 |
+-----+---------------------------------+---------