发布时间：2020/05/03 作者：天马行空 阅读（1348）
#概念
存储过程是存储在数据库目录中的一段声明性SQL语句。
#优点
高性能：MySQL存储过程按需编译，在编译存储过程之后，MySQL将其放入缓存中，应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。
节约流量：应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。
重用性：存储过程将数据库接口暴露给所有应用程序，开发人员不必开发存储过程中已支持的功能。
安全性：数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。
#缺点
消耗内存和CPU：大量存储过程会导致每个连接的内存使用量将会大大增加；存储过程中过度使用大量逻辑操作，则CPU使用率也会增加。
开发维护成本高
很难调试
存储过程的构造不利于复杂的业务逻辑开发
1、创建一个储存过程
DELIMITER // CREATE PROCEDURE procedureName() BEGIN SELECT * FROM test; END // DELIMITER ; CALL procedureName();
#创建一个带参数的储存过程 in
DELIMITER // CREATE PROCEDURE procedureName(in p_id int(11)) BEGIN SELECT * FROM test WHERE id=p_id; END // DELIMITER ; CALL procedureName(1);
#创建一个带参数的储存过程 out
DELIMITER // CREATE PROCEDURE procedureName(OUT total int) BEGIN SELECT count(*) INTO total FROM test; END // DELIMITER ; CALL procedureName(@total); SELECT @total;
#创建一个带参数的储存过程 inout
DELIMITER // CREATE PROCEDURE procedureName(INOUT total int) BEGIN SET total=total+1; END // DELIMITER ; SET @countTotal=1; CALL procedureName(@countTotal); SELECT @countTotal;
#创建一个带参数的储存过程 返回多个值
DELIMITER // CREATE PROCEDURE procedureName( OUT idcount1 int, OUT idcount2 int, OUT idcount3 int, OUT idcount4 int ) BEGIN SELECT COUNT(*) INTO idcount1 FROM test WHERE id>=1; SELECT COUNT(*) INTO idcount2 FROM test WHERE id>=2; SELECT COUNT(*) INTO idcount3 FROM test WHERE id>=4; SELECT COUNT(*) INTO idcount4 FROM test WHERE id>=4; END // DELIMITER ; CALL procedureName(@idcount1,@idcount2,@idcount3,@idcount4) SELECT @idcount1,@idcount2,@idcount3,@idcount4
#IF 判断
DELIMITER // CREATE PROCEDURE procedureName(in p_id int(11),OUT res VARCHAR(20)) BEGIN if(p_id>0 and p_id<=2) THEN SET res='aaa'; ELSEIF(p_id>2 and p_id<=4) THEN SET res='bbb'; ELSE SET res='ggg'; END IF; END // DELIMITER ; CALL procedureName(5,@res); SELECT @res
#CASE WHEN 单条件
DELIMITER // CREATE PROCEDURE procedureName(in p_id int(11),OUT res VARCHAR(20)) BEGIN CASE p_id WHEN 1 THEN SET res='aaa'; WHEN 2 THEN SET res='bbb'; WHEN 3 THEN SET res='ccc'; ELSE SET res='no'; END CASE; END // DELIMITER ; CALL procedureName(1,@res); SELECT @res
#CASE WHEN 多条件
DELIMITER // CREATE PROCEDURE procedureName(in p_id int(11),OUT res VARCHAR(20)) BEGIN CASE WHEN p_id>=1 AND p_id<=5 THEN SET res='aaa'; WHEN p_id>=6 AND p_id<=10 THEN SET res='bbb'; WHEN p_id>=11 AND p_id<=15 THEN SET res='ccc'; ELSE SET res='no'; END CASE; END // DELIMITER ; CALL procedureName(44,@res); SELECT @res
#WHILE DO 满足条件的循环
DELIMITER // CREATE PROCEDURE procedureName(OUT res VARCHAR(255)) BEGIN DECLARE x INT; SET x = 1; SET res = ''; WHILE x <= 5 DO SET res = CONCAT(res,x,','); SET x = x + 1; END WHILE; #SELECT res; END // DELIMITER ; CALL procedureName(@res); SELECT @res;
#REPEAT UNTIL 不满足条件的循环
DELIMITER // CREATE PROCEDURE procedureName(OUT res VARCHAR(255)) BEGIN DECLARE x INT; SET x = 1; SET res = ''; REPEAT SET res = CONCAT(res,x,','); SET x = x + 1; UNTIL x > 5 END REPEAT; #SELECT res; END // DELIMITER ; CALL procedureName(@res); SELECT @res;
#loop LEAVE等于break ITERATE等于continue
DELIMITER // CREATE PROCEDURE procedureName(OUT res VARCHAR(255)) BEGIN DECLARE x INT; SET x = 1; SET res = ''; loop_label: LOOP IF x > 10 THEN LEAVE loop_label; END IF; SET x = x + 1; IF (x mod 2) THEN ITERATE loop_label; ELSE SET res = CONCAT(res,x,','); END IF; END LOOP; END // DELIMITER ; CALL procedureName(@res); SELECT @res;
#创建一个存储过程来获取用户的电子邮箱
DELIMITER // CREATE PROCEDURE procedureName (INOUT email_list varchar(4000)) BEGIN -- 声明两个变量，是否是末尾，当前的邮箱账号 DECLARE v_finished INTEGER DEFAULT 0; DECLARE v_email varchar(100) DEFAULT ""; -- 声明游标 DEClARE email_cursor CURSOR FOR SELECT email FROM users_info; -- 游标找不到数据的时候设置v_finished=1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; -- 打开游标 OPEN email_cursor; -- LOOP 循环 get_email: LOOP -- 读取游标中的电子邮箱账号 FETCH email_cursor INTO v_email; -- 未找到数据的时候跳槽循环 IF v_finished = 1 THEN LEAVE get_email; END IF; -- 拼接邮箱账号 SET email_list = CONCAT(v_email,";",email_list); END LOOP get_email; CLOSE email_cursor; END// DELIMITER ; CALL procedureName(@res); SELECT @res;
2、删除一个储存过程
DROP procedure IF EXISTS `procedureName`;
3、查询demo库中的存储过程
SHOW PROCEDURE STATUS WHERE db='demo';
4、查询存储过程的创建语句
SHOW CREATE PROCEDURE `procedureName`;
5、储存过程中的异常处理
DELIMITER $$
CREATE PROCEDURE procedureName(IN article_id INT, IN tag_id INT)
BEGIN
-- 主键冲突的时候抛出异常提示,并继续执行CONTINUE 终止执行EXIT
DECLARE CONTINUE HANDLER FOR 1062
SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;
INSERT INTO article_tags(article_id,tag_id) VALUES(article_id,tag_id);
SELECT COUNT(*) FROM article_tags;
END$$
DELIMITER ;
CALL procedureName(1,2);
CALL procedureName(1,3);
CALL procedureName(1,2);
6、mysql自定义函数
自定义函数可以在sql中调用，而存储过程只能在单独调用；下面是一个返回用户等级的函数
DELIMITER $$ CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10) DETERMINISTIC BEGIN DECLARE lvl varchar(10); IF p_creditLimit > 50000 THEN SET lvl = '1'; ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN SET lvl = '2'; ELSEIF p_creditLimit < 10000 THEN SET lvl = '3'; END IF; RETURN (lvl); END $$ DELIMITER ; SELECT CustomerLevel(100000);
7、删除函数
DROP FUNCTION IF EXISTS `CustomerLevel`;
#预先执行的建表语句
CREATE TABLE `article_tags` ( `article_id` int(11) NOT NULL, `tag_id` int(11) NOT NULL, PRIMARY KEY (`article_id`,`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `created` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; CREATE TABLE `users_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
