什么是存储过程,在实际项目中用得多么?-阿里云开发者社区

开发者社区> 数据库> 正文

什么是存储过程,在实际项目中用得多么?

简介: 首发公众号:码农架构

存储过程是程序化的 SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。存储过程可以说是由 SQL 语句和流控制语句构成的语句集合,它和我们之前学到的函数一样,可以接收输入参数,也可以返回输出参数给调用者,返回计算结果。

存储过程像是函数.

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

定义一个存储过程:

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

删除已经创建的存储过程:

DROP PROCEDURE

更新存储过程:

ALTER PROCEDURE

实现一个简单的存储过程:

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

mysql> call study_stored_procedure(50);
+------+
| sum  |
+------+
| 1275 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

使用这个存储过程:

CALL add_num(50);

DELIMITER

如果你使用 Navicat 这个工具来管理 MySQL 执行存储过程,那么直接执行上面这段代码就可以了。如果用的是 MySQL,你还需要用 DELIMITER 来临时定义新的结束符。因为默认情况下 SQL 采用(;)作为结束符,这样当存储过程中的每一句 SQL 结束之后,采用(;)作为结束符,就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体,我们不希望 SQL 逐条执行,而是采用存储过程整段执行的方式,因此我们就需要临时定义新的 DELIMITER,新的结束符可以用(//)或者($$)。如果你用的是 MySQL,那么上面这段代码,应该写成下面这样:

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 ;

存储过程的 3 种参数类型

image.png
IN 参数必须在调用存储过程时指定,而在存储过程中修改该参数的值不能被返回。而 OUT 参数和 INOUT 参数可以在存储过程中被改变,并可返回。

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;

流控制语句

  1. BEGIN…END:BEGIN…END 中间包含了多个语句,每个语句都以(;)号为结束符。
  2. DECLARE:DECLARE 用来声明变量,使用的位置在于 BEGIN…END 语句中间,而且需要在其他语句使用之前进行变量的声明。
  3. SET:赋值语句,用于对变量进行赋值。
  4. SELECT…INTO:把从数据表中查询的结果存放到变量中,也就是为变量赋值。
  5. IF…THEN…ENDIF:条件判断语句,我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。
  6. CASE:CASE 语句用于多条件的分支判断,使用的语法是下面这样的。
  7. LOOP、LEAVE 和 ITERATE:LOOP 是循环语句,使用 LEAVE 可以跳出循环,使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验,你可以把 LEAVE 理解为 BREAK,把 ITERATE 理解为 CONTINUE。
  8. REPEAT…UNTIL…END REPEAT:这是一个循环语句,首先会执行一次循环,然后在 UNTIL 中进行表达式的判断,如果满足条件就退出,即 END REPEAT;如果条件不满足,则会就继续执行循环,直到满足退出条件为止。
  9. WHILE…DO…END WHILE:这也是循环语句,和 REPEAT 循环不同的是,这个语句需要先进行条件判断,如果满足条件就进行循环,如果不满足条件就退出循环。
CASE 
  WHEN expression1 THEN ...
  WHEN expression2 THEN ...
  ...
    ELSE 
    --ELSE语句可以加,也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

关于存储过程使用的争议

存储过程有很多好处:
• 存储过程可以一次编译多次使用
• 存储过程的安全性强
• 可以减少网络传输量
缺点也是很明显的:
• 可移植性差
• 调试困难
• 版本管理也很困难
• 不适合高并发的场景

码农架构-公众号2.jpg

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章