mysql - 存储过程

定义

存储过程(stored procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在服务器端的数据库中，利用存储过程可以加速SQL语句的执行。

分类

存储过程分为系统存储过程和自定义存储过程。
1）系统存储过程在master数据库中，但是在其他的数据库中可以直接调用，并且在调用时不必在存储过程前加上数据库名，因为在创建一个新数据库时，系统存储过程在新的数据库中会自动创建
2）自定义存储过程，由用户创建并能完成某一特定功能的存储过程，存储过程既可以有参数又有返回值，但是它与函数不同，存储过程的返回值只是指明执行是否成功，并不能像函数那样被直接调用，只能利用execute来执行存储过程。

优点

1）提高应用程序的通用性和可移植性：存储过程创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。并且数据库专业人员可以随时对存储过程进行修改，且对程序源代码没有影响，这样就极大的提高了程序的可移植性。
2）可以更有效的管理用户操作数据库的权限：在SqlServer数据库中，系统管理员可以通过对执行某一存储过程的权限进行限制，从而实现对相应的数据访问进行控制，避免非授权用户对数据库的访问，保证数据的安全。
3）可以提高SQL的速度，存储过程是编译过的，如果某一个操作包含大量的SQL代码或分别被执行多次，那么使用存储过程比直接使用单条SQL语句执行速度快的多。
4）减轻服务器的负担：当用户的操作是针对数据库对象的操作时，如果使用单条调用的方式，那么网络上还必须传输大量的SQL语句，如果使用存储过程，则直接发送过程的调用命令即可，降低了网络的负担。

参数类型

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1、没有参数（无参数无返回）
2、仅仅带 IN 类型（有参数无返回）
3、仅仅带 OUT 类型（无参数有返回）
4、既带 IN 又带 OUT（有参数有返回）
5、带 INOUT（有参数有返回）
注意： IN、OUT、INOUT 都可以在一个存储过程中带多个。

创建存储过程

语法如下：

 因为MySQL默认的语句结束符号为分号是“;”，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符

 

调用存储过程

查看存储过程

SHOW PROCEDURE STATUS   -- 列出当前所有的存储过程

 

删除存储过程

DROP PROCEDURE IF EXISTS hah1 -- 删除名字为hah1的存储过程

 

例子：

写一个存储过程，查找Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询结果应为 null

DELIMITER $$
CREATE PROCEDURE getNthHighestSalary1(IN N INT, OUT NthHighestSal FLOAT)
BEGIN
  SET N = N - 1;
  SELECT DISTINCT salary INTO NthHighestSal FROM tbl_salary ORDER BY salary DESC LIMIT N, 1;
    
END $$
DELIMITER ;


CALL getNthHighestSalary1(3, @NthHighestSal); # 查询第3高的薪资
SELECT @NthHighestSal;

CALL getNthHighestSalary1(4, @number);  # 查询第4高的薪资
SELECT @number;