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

概述

由MySQL5.0 版本开始支持存储过程。

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

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

就是数据库 SQL 语言层面的代码封装与重用。

存储过程就类似于Java中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。

优点

缺点

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三种类型；

存储过程中的语句必须包含在BEGIN和END之间。

DECLARE中用来声明变量，变量默认赋值使用的DEFAULT，语句块中改变变量值，使用SET 变量=值；

存储过程的使用

定义一个存储过程

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

调用存储过程

CALL demo1();

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

先定义一个student数据库表：

现在要查询这个student表中的sex为男的有多少个人。

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是另一个条件判断的语句，类似于编程语言中的 choose、when语法。MySQL 中的 case语句有两种语法格式。

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

调用这个存储过程

CALL demo4(1);

2.第二种

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;

调用此函数

CALL demo5(0);

定义一个循环语句 WHILE

DELIMITER $$CREATE     PROCEDURE demo6(IN num INT,OUT SUM INT)  BEGIN       SET SUM = 0;       WHILE num=10    END REPEAT; -- 循环结束  END$$DELIMITER;

调用此函数

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

定义一个循环语句 LOOP

循环语句，用来重复执行某些语句。

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

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