笔记仅供参考
目录
一、存储过程概述
1.1 理解
1.1.1、含义
存储过程的英文是 Stored Procedure 。它的思想很简单,就是一组经过 预先编译 的 SQL 语句 的封装。 执行过程:存储过程预先存储在 MySQL 服务器上,需要执行的时候,客户端只需要向服务器端发出调用 存储过程的命令,服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
1.1.2 、好处
1、简化操作,提高了sql语句的重用性,减少了开发程序员的压力 2、减少操作过程中的失误,提高效率3、减少网络传输量(客户端不需要把所有的 SQL 语句通过网络发给服务器) 4、减少了 SQL 语句暴露在 网上的风险,也提高了数据查询的安全性
1.1.3、和视图、函数的对比
它和视图有着同样的优点,清晰、安全,还可以减少网络传输量。不过它和视图不同,视图是 虚拟表 , 通常不对底层数据表直接操作,而存储过程是程序化的 SQL,可以 直接操作底层数据表 ,相比于面向集 合的操作方式,能够实现一些更复杂的数据处理。 一旦存储过程被创建出来,使用它就像使用函数一样简单,我们直接通过调用存储过程名即可。相较于 函数,存储过程是 没有返回值 的。
1.2 分类
存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下:
1、没有参数(无参数无返回) 2、仅仅带 IN 类型(有参数无返回) 3、仅仅带 OUT 类型(无参数有返 回) 4、既带 IN 又带 OUT(有参数有返回) 5、带 INOUT(有参数有返回) 注意:IN、OUT、INOUT 都可以在一个存储过程中带多个。
二、创建存储过程
语法
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) [characteristics ...]
BEGIN
存储过程体
END
2.1、无参数无返回值类型
#创建存储过程
#无参数无返回值
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER;
#存储过程调用
CALL select_all_data();
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary) FROM emps;
END //
DELIMITER;
CALL avg_employee_salary();
DELIMITER //
CREATE PROCEDURE show_max_salary()
BEGIN
SELECT MAX(salary) FROM emps;
END //
DELIMITER;
CALL show_max_salary()
2.2、使用out参数
#创建
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms FROM emps;
END //
DELIMITER;
#调用
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms;
2.3、使用in参数
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary FROM emps WHERE last_name = empname;
END //
DELIMITER;
#调用
CALL show_someone_salary('Abel');
2.4、使用in和out参数
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT sal DOUBLE)
BEGIN
SELECT salary INTO sal FROM emps WHERE last_name = empname;
END //
DELIMITER;
#调用
CALL show_someone_salary2('Abel',@sal);
SELECT @sal;
2.5、使用inout参数
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
SELECT last_name INTO empname FROM emps WHERE employee_id = (
SELECT manager_id FROM emps WHERE last_name = empname);
END //
DELIMITER;
#调用
SET @empname := 'Abel';
CALL show_mgr_name(@empname);
SELECT @empname;
三、如何调试
在 MySQL 中,存储过程不像普通的编程语言(比如 VC++、Java 等)那样有专门的集成开发环境。因 此,你可以通过 SELECT 语句,把程序执行的中间结果查询出来,来调试一个 SQL 语句的正确性。调试 成功之后,把 SELECT 语句后移到下一个 SQL 语句之后,再调试下一个 SQL 语句。这样 逐步推进 ,就可 以完成对存储过程中所有操作的调试了。当然,你也可以把存储过程中的 SQL 语句复制出来,逐段单独 调试。
四、存储函数
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END
1、参数列表:指定参数为IN、OUT或INOUT只对PROCEDURE是合法的,FUNCTION中总是默认为IN参数。
2、RETURNS type 语句表示函数返回数据的类型;RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同,这里不再赘述。
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句,也可以省略 BEGIN…END。
#创建存储函数
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
RETURN (SELECT email FROM emps WHERE last_name = 'Abel');
END //
DELIMITER;
#调用存储函数
SELECT email_by_name();
#解决创建存储函数报错
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
RETURN (SELECT email FROM emps WHERE employee_id = emp_id);
END //
DELIMITER;
SELECT email_by_id(100);
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM emps WHERE department_id = dept_id);
END //
DELIMITER;
SELECT count_by_id(30);
五、存储过程与函数查看
#使用SHOW CREATE语句查看存储过程和函数的创建信息
show create procedure show_min_salary;
show create function count_by_id;
#使用SHOW STATUS语句查看存储过程和函数的状态信息
show procedure status;
SHOW PROCEDURE STATUS like 'show_min_salary';
SHOW function STATUS LIKE 'count_by_id';
#从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id' and ROUTINE_TYPE = 'FUNCTION';
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary' AND ROUTINE_TYPE = 'PROCEDURE';
六、存储过程与函数修改
#存储函数与过程修改
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '查询最高工资';
七、存储过程与函数删除
#存储过程与函数删除
DROP FUNCTION IF EXISTS count_by_id;
DROP PROCEDURE IF EXISTS show_min_salary;
八、存储过程与函数的优缺点
8.1、优点
1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译,之后的使用都不需要重新编译,这就提升了 SQL 的执行效率。
2、可以减少开发工作量。将代码封装成模块,实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以重复使用,在减少开发工作量的同时,还能保证代码的结构清晰。
3、存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限,这样就和视图一样具有较强的安全性。
4、可以减少网络传输量。因为代码封装到存储过程中,每次使用只需要调用存储过程即可,这样就减少了网络传输量。
5、良好的封装性。在进行相对复杂的数据库操作时,原本需要使用一条一条的 SQL 语句,可能要连接多次数据库才能完成的操作,现在变成了一次存储过程,只需要连接一次即可。
8.2、缺点
1、可移植性差。存储过程不能跨数据库移植,比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程,在换成其他数据库时都需要重新编写。
2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说,开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试,但要收费。
3、存储过程的版本管理很困难。比如数据表索引发生变化了,可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理,但是存储过程本身没有版本控制,版本迭代更新的时候很麻烦。
4、它不适合高并发的场景。高并发的场景需要减少数据库的压力,有时数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了。