MySQL存储过程

意义

由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，一个存储过程在程序在网络中交互时可以替代大堆的sql语句，所以也能降低网络的通信量，提高通信速率。但是不同数据库语法不同，迁移需要重新编写，把过多业务逻辑写在存储过程不好维护，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大。

创建过程

DELIMITER //
DROP PROCEDURE IF EXISTS getAllUser//
CREATE PROCEDURE getAllUser()
BEGIN
SELECT * FROM t_sys_users;
END//
DELIMITER ;

查看过程状态（包括时间，数据库，创建人等等）

SHOW PROCEDURE STATUS;

调用过程

CALL getAllUser

生成后的存过如下：

CREATE DEFINER=`cms`@`%` PROCEDURE `getAllUser`()
BEGIN  
SELECT * FROM t_sys_users;  
END

cms@%应该是用户名密码

再来一个测试例子

DELIMITER //
CREATE PROCEDURE testone()
BEGIN
DECLARE i INT DEFAULT 0;
 
WHILE i<100 DO
    INSERT INTO t_payout(userId,username,money,date) VALUES(1,"wangxz",1,"2016-12-21");
    SET i=i+1;
END WHILE;
END//
DELIMITER ;
CALL testone
DROP PROCEDURE testone

当然上面需要先建表

DROP TABLE IF EXISTS t_payout;
CREATE TABLE `t_payout` (
  `userId` INT(11) NOT NULL,
  `username` VARCHAR(256) DEFAULT NULL,
  `money` INT(11) DEFAULT NULL,
  `date` DATETIME DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

语法

1，变量 DECLARE与SET 
使用DECLARE来声明，DEFAULT赋默认值，SET赋值 

DECLARE counter INT DEFAULT 0;   
SET counter = counter+1;  

2、参数 IN、OUT、INOUT
IN为默认类型，值必须在调用时指定，值不能返回（值传递） 
OUT值可以返回（指针传递）
INOUT值必须在调用时指定，值可以返回 
3、条件判断 IF THEN、ELSEIF、ELSE、END IF 
IF THEN与END IF要成对出现 

DELIMITER $$   
  
DROP PROCEDURE IF EXISTS discounted_price$$   
CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2))   
BEGIN   
    IF (normal_price > 500) THEN   
        SET discount_price = normal_price * .8;   
    ELSEIF (normal_price > 100) THEN   
        SET discount_price = normal_price * .9;   
    ELSE   
        SET discount_price = normal_price;   
    END IF;   
END$$   
  
DELIMITER ; 

4、循环 LOOP、END LOOP 

DELIMITER $$   
  
DROP PROCEDURE IF EXISTS simple_loop$$   
  
CREATE PROCEDURE simple_loop(OUT counter INT)   
BEGIN   
    SET counter = 0;   
    my_simple_loop: LOOP   
        SET counter = counter+1;   
        IF counter = 10 THEN   
            LEAVE my_simple_loop;   
        END IF;   
    END LOOP my_simple_loop;   
END$$   
  
DELIMITER ;  

调用该存过:CALL simple_loop(5);报错如下
OUT or INOUT argument 1 for routine komectbackend.simple_loop is not a variable or NEW pseudo-variable in BEFORE trigger
提示传入的参数不对，*.*的存储过程参数为输出（或输入）参数，可能传的不对。改为CALL simple_loop(@5);
5、循环 WHILE DO、END WHILE 

DELIMITER $$   
  
DROP PROCEDURE IF EXISTS simple_while$$   
  
CREATE PROCEDURE simple_while(OUT counter INT)   
BEGIN   
    SET counter = 0;   
    WHILE counter != 10 DO   
        SET counter = counter+1;   
    END WHILE;   
END$$   
  
DELIMITER ;  

6、REPEAT、UNTILL 

DELIMITER $$   
  
DROP PROCEDURE IF EXISTS simple_repeat$$   
  
CREATE PROCEDURE simple_repeat(OUT counter INT)   
BEGIN   
    SET counter = 0;   
    REPEAT   
        SET counter = counter+1;   
    UNTIL counter = 10 END REPEAT;   
END$$   
  
DELIMITER ;  

7、异常处理 
如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结 
如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结 
8、CURSOR用于处理多行记录的查询结果

DELIMITER $$   
  
DROP PROCEDURE IF EXITS cursor_example$$   
CREATE PROCEDURE cursor_example()   
    READS SQL DATA   
BEGIN   
    DECLARE l_employee_id INT;   
    DECLARE l_salary NUMERIC(8,2);   
    DECLARE l_department_id INT;   
    DECLARE done INT DEFAULT 0;   
    DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
  
    OPEN cur1;   
    emp_loop: LOOP   
        FETCH cur1 INTO l_employee_id, l_salary, l_department_id;   
        IF done=1 THEN   
            LEAVE emp_loop;   
        END IF;   
    END LOOP emp_loop;   
    CLOSE cur1;   
END$$   
DELIMITER ;  

参考文章：

http://blog.csdn.net/juanna_ding/article/details/5381188