MySQL存储过程（详细学习篇）

2023-07-2715:35:19数据库教程Comments1,402 views字数 5036阅读模式

概述

由MySQL5.0 版本开始支持存储过程。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

就是数据库 SQL 语言层面的代码封装与重用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

存储过程就类似于Java中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

  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三种类型；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

  1. in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。
  2. ou代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。
  3. inout代表即时输入参数，又是输出参数，表示该参数的值即可有调用程序制定，又可以将inout参数的计算结果返回给调用程序

存储过程中的语句必须包含在BEGIN和END之间。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

DECLARE中用来声明变量，变量默认赋值使用的DEFAULT，语句块中改变变量值，使用SET 变量=值；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

存储过程的使用

定义一个存储过程

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

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

调用存储过程文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

CALL demo1();

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

定义一个有参数的存储过程

先定义一个student数据库表：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

现在要查询这个student表中的sex为男的有多少个人。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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 ;

调用这个存储过程文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

定义一个流程控制语句 IF ELSE

IF 语句包含多个条件判断，根据结果为 TRUE、FALSE执行语句，与编程语言中的 if、else if、else 语法类似。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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 ;

调用这个存储过程文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

CALL demo3(2);

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

定义一个条件控制语句 CASE

case是另一个条件判断的语句，类似于编程语言中的 choose、when语法。MySQL 中的 case语句有两种语法格式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

  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;

调用这个存储过程文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

CALL demo4(1);

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

2.第二种文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

DELIMITER $$CREATE PROCEDURE demo5(IN num INT) BEGIN CASE num -- 条件开始 WHEN 1 THEN SELECT '输入为1'; WHEN 0 THEN SELECT '输入为0'; ELSE SELECT '不是1也不是0'; END CASE; -- 条件结束 END$$DELIMITER;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

调用此函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

CALL demo5(0);

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

定义一个循环语句 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;

调用此函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

-- 调用函数CALL demo6(0,@sum);
-- 查询函数SELECT @sum;

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

定义一个循环语句 REPEAT UNTLL

REPEATE…UNTLL 语句的用法和 Java中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是REPEATE 表达式值为 false时才执行循环操作，直到表达式值为 true停止。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

-- 创建过程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;

调用此函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

定义一个循环语句 LOOP文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

循环语句，用来重复执行某些语句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

  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;

调用此函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

使用存储过程插入信息

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;

调用此函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

再次调用次函数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

存储过程的管理

显示存储过程

SHOW PROCEDURE STATUS

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

显示特定数据库的存储过程

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

显示特定模式的存储过程

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

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

显示存储过程的源码

SHOW CREATE PROCEDURE 存储过程名;

MySQL存储过程（详细学习篇）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

删除存储过程

DROP PROCEDURE 存储过程名;

后端调用存储过程的实现

在mybatis当中，调用存储过程文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

<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 >

调用数据库管理文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

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

通过这样就可以调用数据库中的存储过程的结果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51989.html

  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/51989.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定