mysql储存过程

#概念

存储过程是存储在数据库目录中的一段声明性SQL语句。

#优点

高性能:MySQL存储过程按需编译,在编译存储过程之后,MySQL将其放入缓存中,应用程序在单个连接中多次使用存储过程,则使用编译版本,否则存储过程的工作方式类似于查询。
节约流量:应用程序不必发送多个冗长的SQL语句,而只能发送存储过程的名称和参数。
重用性:存储过程将数据库接口暴露给所有应用程序,开发人员不必开发存储过程中已支持的功能。
安全性:数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限,而不向基础数据库表提供任何权限。

#缺点

消耗内存和CPU:大量存储过程会导致每个连接的内存使用量将会大大增加;存储过程中过度使用大量逻辑操作,则CPU使用率也会增加。
开发维护成本高
很难调试
存储过程的构造不利于复杂的业务逻辑开发

#创建一个储存过程
[code]
DELIMITER //
CREATE PROCEDURE procedureName()
BEGIN
SELECT * FROM test;
END //
DELIMITER ;
CALL procedureName();
[/code]

#创建一个带参数的储存过程 in
[code]
DELIMITER //
CREATE PROCEDURE procedureName(in p_id int(11))
BEGIN
SELECT * FROM test WHERE id=p_id;
END //
DELIMITER ;
CALL procedureName(1);
[/code]

#创建一个带参数的储存过程 out
[code]
DELIMITER //
CREATE PROCEDURE procedureName(OUT total int)
BEGIN
SELECT count(*) INTO total FROM test;
END //
DELIMITER ;
CALL procedureName(@total);
SELECT @total;
[/code]
#创建一个带参数的储存过程 inout
[code]
DELIMITER //
CREATE PROCEDURE procedureName(INOUT total int)
BEGIN
SET total=total+1;
END //
DELIMITER ;

SET @countTotal=1;
CALL procedureName(@countTotal);
SELECT @countTotal;
[/code]

#创建一个带参数的储存过程 返回多个值
[code]
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
[/code]

#IF 判断
[code]
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
[/code]

#CASE WHEN 单条件
[code]
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
[/code]

#CASE WHEN 多条件
[code]
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
[/code]

#WHILE DO 满足条件的循环
[code]
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;
[/code]

#REPEAT UNTIL 不满足条件的循环
[code]
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;
[/code]

#loop LEAVE等于break ITERATE等于continue
[code]
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;
[/code]

#创建一个存储过程来获取用户的电子邮箱
[code]
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;
[/code]

#删除一个储存过程
[code]
DROP procedure IF EXISTS `procedureName`;
[/code]

#查询demo库中的存储过程
[code]
SHOW PROCEDURE STATUS WHERE db=’demo';
[/code]

#查询存储过程的创建语句
[code]
SHOW CREATE PROCEDURE `procedureName`;
[/code]

#储存过程中的异常处理
[code]
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);
[/code]

#mysql自定义函数
自定义函数可以在sql中调用,而存储过程只能在单独调用;下面是一个返回用户等级的函数
[code]
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);
[/code]

#删除函数
[code]
DROP FUNCTION IF EXISTS `CustomerLevel`;
[/code]

#预先执行的建表预计
[code]
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;
[/code]