Mysql-存储过程简单入门

定义：

存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

创建存储过程模板语法：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]

BEGIN

 存储过程体

END

参数解释

参数前符号

IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN ，表示输入参数。

OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

INOUT ：当前参数既可以为输入参数，也可以为输出参数。

存储过程的约束条件

characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下

1. LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

2. [NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

3. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制：

1）CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；

2）NO SQL表示当前存储过程的子程序中不包含任何SQL语句；

3）READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；

4）MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。

5）默认情况下，系统会指定为CONTAINS SQL。

4.SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

1）DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；

2）INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

3）如果没有设置相关的值，则MySQL默认指定值为DEFINER。

4）COMMENT 'string' ：注释信息，可以用来描述存储过程。

关键字解释

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。

2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。

3. SET：赋值语句，用于对变量进行赋值。

4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

Demo示例

sql脚本，在本地自己任意一个练习的数据库下直接运行就好，包括表结构和数据

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `EMPLOYEE_ID` int(12) NOT NULL,
  `LAST_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `MANAGER_ID` int(11) NULL DEFAULT NULL,
  `SALARY` decimal(10, 2) NULL DEFAULT NULL,
  `department_id` int(11) NULL DEFAULT NULL,
  `location` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (100, 'STEVEN', 100, 1000.00, 50, 'jiangsu');
INSERT INTO `employees` VALUES (101, 'Kochhar', 100, 2000.00, 10, 'shenzhen');
INSERT INTO `employees` VALUES (102, 'De Haan', 100, 1000.00, 50, 'jiangsu');
INSERT INTO `employees` VALUES (103, 'Hunold', 102, 2000.00, 20, 'shenzhen');
INSERT INTO `employees` VALUES (104, 'Emst', 103, 1000.00, 30, 'jiangsu');
INSERT INTO `employees` VALUES (107, 'Lorentz', 103, 3000.00, 20, 'shenzhen');
INSERT INTO `employees` VALUES (124, 'Mourgos', 100, 1000.00, 40, 'beijing');

SET FOREIGN_KEY_CHECKS = 1;

数据效果：

存储过程使用及其解释

需求：

创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，#输出领导的姓名。

#使用 DELIMITER 
#DELIMITER 语句只是用于帮助解析器正确解释存储过程定义的语法，并不会影响代码块中使用的实际 SQL 语句。
#在存储过程定义完成后，使用 DELIMITER ; 将分隔符改回默认的分号 (;)。
delimiter //
# 创建存储过程 show_mgr_name 指定为inout类型，即输如输出都是empname
create procedure show_mgr_name(inout empname varchar(25))
begin
    # 查询领导的名字，并将查询的LAST_NAME结果为变量empname赋值
    select LAST_NAME into empname
    from dbtest15.employees
        where EMPLOYEE_ID =
              #根据输入的员工姓名查询到领导的id
              (
            select MANAGER_ID
            from dbtest15.employees
            where LAST_NAME = empname
            );
end //
delimiter ;
#设置传入参数为Hunold，
set @empname :='Hunold';
# 使用call语法调用show_mgr_name存储过程
call show_mgr_name(@empname);
#查询结果：根据上面效果图片可以知道Hunold的领导是De Haan
select @empname;