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; ENDcms@%应该是用户名密码

再来一个测试例子

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