MySQL存储过程

存储过程是一组SQL语句集实现比较复杂的逻辑功能，存储过程跟触发器类似，存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事出发后自动调用。

存储过程弊端

• 不同类型数据库语法有差别，移植困难。
• 过多业务逻辑写入存储过程不利于维护，不利于分层管理。

1.创建存储过程

案例1：计算两数之和

DROP PROCUDURE IF EXISTS `adder`;
DELIMITER;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `adder`(IN a int, IN b int)
BEGIN
  DECLARE c int;

  IF a IS NULL THEN SET a=0;
  END IF;

  IF b IS NULL THEN SET b=0;
  END IF;

  SET c=a+b;
  SELECT c AS sum;
END
;;
DELIMITER;

调用存储过程

call add(1,2);

执行存储过程

set @a = 1;
set @b = 2;
call add(@a,@b);

语法

CREATE PROCEDURE procedure_name(
  [int|out|inout] parameter datatype
)
BEGIN
  statement;
END

案例2：查询记录数

CREATE PROCEDURE num_from_employee(IN emp_id INT, OUT count_num INT)
READS SQL DATA
BEGIN
  SELECT COUNT(*) AS count_num FROM employee WHERE id=emp_id;
END

2.IF条件控制

DROP PROCEDURE IF EXISTS `procIf`;
DELIMITER;;
CREATE DEFINE=`root`@`localhost` PROCEDURE `procIf`(IN type int)
ENGIN
  DECLARE c varchar(255);

  IF type=0 THEN SET c='parameter is 0';
  ELSEIF type=1 THEN SET c='parameter is 1';
  ELSE SET c='parameter is others, not 0 or 1';
  
  SELECT c;
END
;;
DELIMITER;

执行

SET @type=1;
CALL procIf(@type);

3.CASE条件控制

DROP PROCEDURE IF EXISTS `procCase`;
DELIMITER;;
CREATE DEFINE='root'@'localhost' PROCEDURE `procCase`(IN type int)
BEGIN
  DECLARE c varchar(255);

  CASE type
  WHEN 0 THEN SET c='parameter is 0';
  WHEN 1 THEN SET c='parameter is 1';
  ELSE SET c='parameter is others, not 0 or 1';
  END CASE;

  SELECT c;
END
;;
DELIMITER;

4.WHILE循环控制语句

DROP PROCEDURE IF EXISTS `procWhile`;
DELIMTER;;
BEGIN
  DECLARE i int;
  DECLAR j int;

  SET i=0;
  SET j=0;
  WHILE i<=n DO
    SET s = s+i;
    SET i = i+1;
  END WHILE;

  SELECT s;
END
;;
DELIMITER;