mysql之储存过程

发布时间:2020/05/03 作者:天马行空 阅读(964)

#概念
    存储过程是存储在数据库目录中的一段声明性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;


关键字mysql