SQL从入门到放弃系列——存储过程[十一]

2019年7月11日16:56:08 发表评论 22
摘要

1.如何创建存储过程。
2.如何使用流控制语句。
3.如何使用存储过程。

什么是存储过程,如何创建存储过程

存储过程(Stored Procedure),是SQL中另一重要应用,和视图一样,都是对SQL代码进行封装,可以反复利用。它和视图有相同的优点,清晰、安全,还可以减少网络传输量。和视图不同的是,视图是虚拟表,通常不对底层数据表进行直接操作;而存储过程是程序化的SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。存储过程可以认为是SQL语句和流控制语句构成的语句集合,可以接收输入参数,也可以返回计算结果。

创建存储过程

SQL从入门到放弃系列——存储过程[十一]

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

更新、删除存储过程

和创建视图语法相同。

示例一

创建一个累加运算的存储过程,计算1+2+3+。。。+n的和,代码如下:

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

调用存储过程时,使用CALL add_num(50);即可。

mysql> call add_num(50);
+------+
| sum  |
+------+
| 1275 |
+------+
1 row in set (0.00 sec)
delimiter的作用

如果使用Navicat工具创建存储过程,直接执行创建语句即可,如果用的是MySQL,需要用delimiter临时定义新的结束符。新的结束符用(//)或($$)。如:

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 ;

Navicat会自动设置delimiter为其他符号,不需要再进行delimiter的操作。

存储过程的3种参数类型
参数类型 是否返回 作用
IN 向存储过程传入参数,存储过程中修改该参数的值,不能被返回。
OUT 把存储过程计算的结果放到该参数中,调用者可以得到返回的值。
INOUT IN和OUT的结合,既用于存储过程的传入参数,同时把计算结果放到参数中,调用者得到结果。
示例二

创建存储过程get_hero_scores,用于查询某一类型英雄中最大生命值,最小的最大魔法值,以及平均最大攻击值。代码:

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

定义了4个参数,3个为OUT类型,1个为IN类型。

调用存储过程:

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

 

流控制语句

流控制语句是用于做流程控制的,上面的两个示例中,用到的控制语句:

1.BEGIN...END:中间用到了多个语句,每个语句用分号为结束符;

2.DECLARE:用于声明变量;

3.SET:赋值语句;

4.SELECT...INFO:把从数据表中查询的结果放到变量中,即为变量赋值。

此外,其他常用的流控制语句:

1.IF...THEN...ENDIF:条件判断语句。

2.CASE:多条件的分支判断。

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

3.LOOP LEAVE ITERATE:loop是循环语句,使用leave跳出循环,使用iterate可以进入下一次循环。可以把leave理解为break,把iterate理解为continue。

4.REPEAT...UNTIL...END REPEAT:循环语句。

5.WHILE...DO...END WHILE:循环语句。

 

存储过程使用争议

存储过程的优点

  1. 一次编译,多次使用。
  2. 安全性强。
  3. 减少网络传输量。

存储过程缺点

  1. 可移植性差。MySQL、Oracle和SQLServer编写的存储过程换作其他数据库都要重新编写。
  2. 调试困难。
  3. 版本管理困难。比如数据表的索引发生变化,可能会导致存储过程失效。
  4. 不适合高并发场景。高并发场景需要减少数据库的压力,有时数据库会采用分库分表的方式,而且可扩展性要求很高,这种情况下,存储过程会很难维护,增加数据库的压力。

 

思考

针对heros表,编写存储过程get_sum_score,查询某一类英雄的最大生命值的总和。

DELIMITER //
create procedure `get_sum_score`(IN roleMain VARCHAR(255),OUT total FLOAT)
BEGIN
select sum(hp_max) as sum_hp_max from heros where role_main=roleMain INTO total;
END//
DELIMITER ;

call get_sum_score('刺客',@total);
select @total

 

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: