MySQL 存储过程（详细学习）

概述

从 MySQL 5.0 版本开始支持存储过程。

如果需要编写一组复杂的SQL语句来满足某些用户的需求，我们可以提前将这组复杂的SQL语句写入数据库中，并通过JDBC调用来执行。对 SQL 语句进行分组。写入数据库的一组 SQL 语句称为存储过程。

存储过程：（PROCEDURE）是预先编译并存储在数据库中的SQL语句的集合。调用存储过程可以简化应用程序开发人员的工作，减少数据库和应用服务器之间的数据传输，对于提高数据处理效率非常有用。

是数据库SQL语言层面的代码封装和复用。

存储过程与Java方法类似。使用它们时必须首先定义和调用它们。存储过程可以指定参数，参数分为三种：IN、OUT、INOUT。

1. IN类型参数表示接受调用者发送的信息；
2. OUT类型参数表示向调用者返回数据；
3. INOUT类型参数可以接受调用者传递的参数，也可以返回给调用者。调用者返回信息。

优点

1. 存储过程通过将处理封装到易于使用的单元中来简化复杂的操作。
2. 简化变更管理。如果表名、列名或者业务逻辑发生变化。只需要更改存储过程代码。使用它的人不需要更改他们的代码。
3. 通常存储的函数有助于提高应用程序性能。一旦生成的存储过程被编译，它就被存储在数据库中。
4. 然而，MySQL 以稍微不同的方式实现存储函数。
5. MySQL 存储过程是按需编译的。编译存储过程后，MySQL 将其缓存。
6. MySQL 为每个连接维护自己的存储过程缓存。如果应用程序在单个上下文中多次使用存储过程，请使用编译版本，否则存储过程将充当查询。
7. 存储操作有助于减少应用程序和数据库服务器之间的流量。
8. 因为

   应用程序不需要发送多个长SQL语句，只需要发送存储过程的名称和参数。

9. 存储过程可重用并且对所有应用程序透明。存储过程向所有应用程序公开数据库接口，以便开发人员不必开发存储过程已支持的功能。
10. 保存的程序是安全的。数据库管理员可以向使用数据库中的存储过程的应用程序授予必要的权限，而无需向基础数据库表授予任何权限。

缺点

1. 如果使用大量存储过程，则使用这些存储过程的每个连接的内存使用量会显着增加。
2. 另外，如果存储过程中过度使用大量逻辑运算，CPU 占用率也会增加，因为 MySQL 数据库最初的设计重点是高效查询而不是逻辑运算。
3. 存储过程的结构使得开发包含复杂业务逻辑的存储过程变得困难。
4. 调试存储的函数很困难。很少有数据库管理系统允许调试存储过程。不幸的是，MySQL 不提供调试存储过程的能力。
5. 开发和维护存储过程并不容易。
6. 开发和维护存储过程通常需要特殊技能，但并非所有应用程序开发人员都具备这些技能。这可能会导致应用程序开发和维护阶段出现问题。
7. 对数据库依赖度高，可移植性差。

MySQL存储过程定义

存储过程基本语句格式

DELIMITER $$
CREATE    /*[DEFINER = { user | CURRENT_USER }]*/    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])    /*LANGUAGE SQL    | [NOT] DETERMINISTIC    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }    | SQL SECURITY { DEFINER | INVOKER }    | COMMENT 'string'*/  BEGIN    [DECLARE 变量名 类型 [DEFAULT 值];]    存储过程的语句块;  END$$
DELIMITER ;

存储过程参数有in、out、inout类型；

1. in 表示一个输入参数（默认为in参数），这意味着调用程序必须指定该参数的值。
2. ou 表示输出参数，表示存储过程计算出该参数的值后，将out参数的计算结果返回给调用程序。
3. inout 表示立即输入参数和输出参数，这意味着调用程序可以格式化参数的值，并且可以将 inout 参数的计算结果返回给调用程序。

存储过程中的语句必须包括开始和结束。

DECLARE 用于声明变量。定义默认变量使用 DEFAULT 值。要更改语句块中变量的值，请使用 SET variable = value;

存储过程的使用

定义存储过程

DELIMITER $$
CREATE    PROCEDURE `demo`.`demo1`()  -- 存储过程体  BEGIN    -- DECLARE声明 用来声明变量的    DECLARE de_name VARCHAR(10) DEFAULT '';        SET de_name = "jim";        -- 测试输出语句（不同的数据库，测试语句都不太一样。    SELECT de_name;  END$$
DELIMITER ;

调用存储过程

CALL demo1();

定义带参数的存储过程

首先确定有多少个♸我们定义❀多少个♸我们首先询问学生数据库表：N个学生表人员性别均为男性。

DELIMITER $$
CREATE    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)  -- 存储过程体  BEGIN    -- 把SQL中查询的结果通过INTO赋给变量    SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;    SELECT s_count;      END$$DELIMITER ;

调用此存储过程

-- @s_count表示测试出输出的参数CALL demo2 ('男',@s_count);

定义过程控制语句 IF ELSE

IF 语句包含多个条件语句，根据 TRUE 和 FALSE 结果执行表达式。它类似于编程语言中的 if、else if、else 语法。 。

DELIMITER $$CREATE    PROCEDURE `demo`.`demo3`(IN `day` INT)  -- 存储过程体  BEGIN    IF `day` = 0 THEN    SELECT '星期天';    ELSEIF `day` = 1 THEN    SELECT '星期一';    ELSEIF `day` = 2 THEN    SELECT '星期二';    ELSE    SELECT '无效日期';    END IF;      END$$DELIMITER ;

调用此存储过程

CALL demo3(2);

定义条件控制语句CASE

case是另一种条件语句，类似于编程语言中的select和when语法。 MySQL 有两种形式的 case 语句语法。

1. 第一种类型

DELIMITER $$CREATE     PROCEDURE demo4(IN num INT)  BEGIN    CASE -- 条件开始      WHEN num<0 THEN       SELECT '负数';    WHEN num>0 THEN       SELECT '正数';    ELSE     SELECT '不是正数也不是负数';      END CASE; -- 条件结束  END$$DELIMITER;

调用此存储函数

CALL demo4(1);

2。另一种类型

DELIMITER $$CREATE PROCEDURE demo5(IN num INT) BEGIN CASE 编号 1 THE H 条件开始 SELECT '输入为1'； WHEN 0 THEN SELECT '输入为 0'; ELSE SELECT '既不是 1 也不是任何 C; -- 条件结束 END$$分隔符；

调用此函数

CALL demo5(0);

定义循环语句 WHILE

DELIMITER $$CREATE     PROCEDURE demo6(IN num INT,OUT SUM INT)  BEGIN       SET SUM = 0;       WHILE num<10 DO -- 循环开始           SET num = num+1;           SET SUM = SUM+num;           END WHILE; -- 循环结束  END$$DELIMITER;

调用此函数 UATPE♓♓♓❀ 用法 REPEATE...UNTLL 语句与 Java 语句 do...while 类似。两者都首先执行循环操作，然后评估条件。不同的是，执行循环函数时REPEATE语句为假，直到Stop语句为真。

-- 创建过程DELIMITER $$CREATE     PROCEDURE demo7(IN num INT,OUT SUM INT)  BEGIN       SET SUM = 0;       REPEAT-- 循环开始    SET num = num+1;    SET SUM = SUM+num ;    UNTIL num>=10    END REPEAT; -- 循环结束  END$$DELIMITER;

调用此函数

CALL demo7(9,@sum);
SELECT @sum;

定义循环语句LOOP

循环语句重复执行某些语句。

执行过程中可以使用LEAVE语句或ITEREATE语句跳出循环，也可以嵌入IF等判断语句。

1. LEAVE语句相当于Java中的break，用于终止循环；
2. ITERATE 语句相当于 Java 中的 continue，用来绕过这个循环。转到下一集。并且不再执行 ITERATE 语句。

DELIMITER $$CREATE     PROCEDURE demo8(IN num INT,OUT SUM INT)  BEGIN       SET SUM = 0;       demo_sum:LOOP-- 循环开始    SET num = num+1;    IF num > 10 THEN        LEAVE demo_sum; -- 结束此次循环    ELSEIF num <= 9 THEN        ITERATE demo_sum; -- 跳过此次循环    END IF;        SET SUM = SUM+num;    END LOOP demo_sum; -- 循环结束  END$$DELIMITER;

调用此函数

CALL demo8(0,@sum);
SELECT @sum;

使用存储函数增加数据

DELIMITER $$CREATE     PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))  BEGIN     -- 声明一个变量 用来决定这个名字是否已经存在     DECLARE s_count INT DEFAULT 0;     -- 验证这么名字是否已经存在     SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;       IF s_count = 0 THEN          INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);    SET s_result = '数据添加成功';     ELSE                SET s_result = '名字已存在，不能添加';                SELECT s_result;     END IF;  END$$DELIMITER;

调用此函数

CALL demo9("Jim","女",@s_result);

再次调用辅助函数

CALL demo9("Jim","女",@s_result)

重复管理存储函数♿♿❓ 查看存储过程特定数据库

SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';

状态存储过程

SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

显示存储过程源代码

SHOW CREATE PROCEDURE 存储过程名;

删除存储过程

DROP PROCEDURE 存储过程名;

实现后台程序调用存储过程

在 mybatis 存储过程中

<parameterMap type="savemap" id=“usermap">   <parameter property="name" jdbcType="VARCHAR" mode="IN"/>  <parameter property="sex" jdbcType="CHAR" mode="IN"/>  <parameter property="result" jdbcType="VARCHAR" mode="OUT"/></parameterMap>
<insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE"> {call saveuser(?, ?, ?)} </insert >

调用数据库管理

HashMap<String, Object> map = new HashMap<String, Object>();   map.put("name", "Jim");   map.put("sex","男");  userDao.saveUserDemo(map);   map.get(“result”);//获得输出参数

您可以通过这种方式将存储过程的结果调用到数据库中。