MySQL · 存储过程

发表于 | 更新于 | 分类于 | 阅读次数：

简介

存储过程处理的是一组 SQL 语句，且没有返回值。

优点

  • 简单：把单条 SQL 封装起来，简化复杂的操作。
  • 安全
  • 高性能

缺点

  • 移植困难，不同数据库的语法差别很大
  • 不易扩展，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱

权限：CREATE ROUTINE 、ALTER ROUTINE 、EXECUTE

语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 创建
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_mysql_while_loop`()
BEGIN
 DECLARE x  INT;
 DECLARE str  TEXT;

 SET x = 1000000001;
 SET str =  '';

 WHILE x  <= 1100000001 DO
 SET  str = CONCAT(str,x,',');
 SET  x = x + 1;
 END WHILE;

 SELECT str;
 END ;;
DELIMITER ;

# 修改
ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  
# 删除
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

# 调用
CALL sp_name([parameter[,...]])
CALL sp_name[()]

# 查询
show procedure status;
show create procedure proc_name;

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)