王秀文的博客

MySQL 存储过程

发布
分类
MySQL
篇幅
642 字 / 1 分钟
阅读
409

存储过程


-- 创建
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END 

-- eg1
DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

-- 调用

CALL add_num(50);


-- eg2

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

-- 调用

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;


-- 更新

ALTER PROCEDURE

-- 删除
DROP PROCEDURE

流控制语句

BEGIN…END：BEGIN…END中间包含了多个语句，每个语句都以（;）号为结束符。
DECLARE：DECLARE用来声明变量，使用的位置在于BEGIN…END语句中间，而且需要在其他语句使用之前进行变量的声明。
SET：赋值语句，用于对变量进行赋值。
SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

除了上面这些用到的流控制语句以外，还有一些常用的流控制语句：

IF…THEN…ENDIF：条件判断语句，我们还可以在IF…THEN…ENDIF中使用ELSE和ELSEIF来进行条件判断。
CASE：CASE语句用于多条件的分支判断，使用的语法是下面这样的。

CASE 
	WHEN expression1 THEN ...
	WHEN expression2 THEN ...
	...
    ELSE 
    --ELSE语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

LOOP、LEAVE和ITERATE：LOOP是循环语句，使用LEAVE可以跳出循环，使用ITERATE则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把LEAVE理解为BREAK，把ITERATE理解为CONTINUE。
REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在UNTIL中进行表达式的判断，如果满足条件就退出，即END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
WHILE…DO…END WHILE：这也是循环语句，和REPEAT循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

模拟两张百万量级的数据表

user 表

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_user`(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, interval RAND()*60 second);
INSERT INTO user(user_id, user_name, create_time)
VALUES((start+i), CONCAT('user_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

product_comment 表

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_product_comments`(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE user_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = substr(MD5(RAND()),1, 20);
SET user_id = FLOOR(RAND()*1000000);
INSERT INTO product_comment(comment_id, product_id, comment_text, comment_time, user_id)
VALUES((START+i), 10001, comment_text, date_temp, user_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END
MySQL