Stored Procedure

There is recorded something of MySQL stored procedure.

Reference

本文大部分参考自以下文章:
https://www.cnblogs.com/mark-chan/p/5384139.html
仅做个人笔记使用

什么是存储过程?

一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行
存储过程可以用来封装特定功能,以便应用于不同的平台,可以看做对面向对象编程的模拟

存储过程的优点

  • 增强SQL语言的功能和灵活性
    存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算
  • 标准组件式编程
    存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句
    数据库管理人员可随时对存储过程进行修改,对应用程序源代码毫无影响
  • 较快的执行速度
    如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理执行速度快很多,因为存储过程是预编译的。在首次运行一个存储过程时查询优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transation-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些
  • 减少网络流量
    针对同一个数据库对象的操作(如:查询、修改),如果这一操作所涉及的Transation-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载
  • 作为一种安全机制来充分利用
    通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全

Create

语法

1
2
3
4
5
6
7
DELIMITER //
CREATE PROCEDURE PROCEDURE_NAME([[IN | OUT | INOUT] ARG_NAME TYPE [, [INT | OUT | INOUT] ARG_NAME TYPE...]])
BEGIN
PROCEDURE BODY
END
//
DELIMITER ;

分隔符

MySQL默认以”;”为分隔符,如果没有声明分隔符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要先用“DELIMITER //”声明当前段分隔符,让编译器把两个“//“之间的内容当做存储过程的代码,不会执行这些代码,“DELIMITER ;”意为把分隔符还原

参数

存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用“,”分隔开。MySQL存储过程的参数用在存储过程的定义,共有三种类型:IN、OUT、INOUT

  • IN
    参数的值必须在调用存储过程前指定,在存储过程中修改该参数的值不能被返回
    即类似JAVA中的值传递,传递的相当于参数的值得拷贝,而不是参数本身
  • OUT
    该值可在存储过程内部被改变,并可返回,未在存储过程中赋值的话,则在存储过程中值为空
    即类似JAVA中的引用传递,传递的是该参数本身的地址,该参数在存储过程中被修改相当于直接修改该参数本身
  • INOUT
    具备IN、OUT的特性,在调用时指定,并可被改变和返回
  • 过程体(PROCEDURE BODY)
    过程体的开始与结束使用BEGINEND进行标识

IN示例

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
mysql> DELIMITER //
mysql> CREATE PROCEDURE in_param(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> SET @p_in=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_param(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| p_in |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

从运行结果可以得出,p_in虽然在存储过程中被修改了,但并不影响存储过程外的@p_in的值

OUT示例

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
mysql> DELIMITER //
mysql> CREATE PROCEDURE out_param(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SET @p_out=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL out_param(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
1 row in set (0.00 sec)

+-------+
| p_out |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

从运行结果可以得出,p_out在存储过程中没被赋值的情况下是读取不到数据的,赋值后再存储过程外的@p_out的值也被修改了

INOUT示例

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
mysql> DELIMITER //
mysql> CREATE PROCEDURE inout_param(inout p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SET @p_inout=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL inout_param(@p_inout);
+---------+
| p_inout |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

+---------+
| p_inout |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

从运行结果可以得出,inout参数结合了in和out的特点,传进去的参数既可以读取又可以修改

删除存储过程

  • 语法
    DROP PROCEDURE PROCEDURE_NAME;
  • Example
    1
    2
    mysql> DROP PROCEDURE p;
    Query OK, 0 rows affected (0.01 sec)

局部变量

DECLARE之前不能调用语句

  • 语法
    DECLARE FIELD_NAME TYPE;
  • 赋值
    SET FIELD_NAME=VALUE;
  • 实例
    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
    mysql> DELIMITER //
    mysql> CREATE PROCEDURE p()
    -> BEGIN
    -> DECLARE d INT;
    -> SET @d=111;
    -> SELECT @d;
    -> END;
    -> //
    Query OK, 0 rows affected (0.00 sec)

    mysql> DELIMITER ;
    mysql> CALL p;
    +------+
    | @d |
    +------+
    | 111 |
    +------+
    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    mysql> CALL p();
    +------+
    | @d |
    +------+
    | 111 |
    +------+
    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)
  • 数值类型(图片来自菜鸟教程
  • 日期和时间类型(图片来自菜鸟教程
  • 字符串类型(图片来自菜鸟教程

用户变量

用户变量一般以@开头,如果滥用用户变量会导致程序难以理解以及管理
实例

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
# 创建用户变量方式1
mysql> SELECT 'HELLO WORLD!' INTO @X;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @X;
+--------------+
| @X |
+--------------+
| HELLO WORLD! |
+--------------+
1 row in set (0.00 sec)

# 创建用户变量方式2
mysql> SET @y=12;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SET @y=1+2+3+4*5;
Query OK, 0 rows affected (0.00 sec)

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

在存储过程中使用用户变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> DELIMITER //
mysql> CREATE PROCEDURE p()
-> BEGIN
-> SELECT @global;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SET @global=12345;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p();
+---------+
| @global |
+---------+
| 12345 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

控制语句

if…[then…]elseif…else

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
39
40
41
42
43
44
45
46
mysql> DROP PROCEDURE IF EXISTS pro_if;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_if(IN type INT)
-> BEGIN
-> DECLARE c VARCHAR(500);
-> IF type = 0 THEN SET c = 'PARAM IS 0';
-> ELSEIF type = 1 THEN SET c = 'PARAM IS 1';
-> ELSE SET c = 'PARAM IS OTHERS';
-> END IF;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

mysql> CALL pro_if(0);
+------------+
| c |
+------------+
| PARAM IS 0 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL pro_if(1);
+------------+
| c |
+------------+
| PARAM IS 1 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL pro_if(2);
+-----------------+
| c |
+-----------------+
| PARAM IS OTHERS |
+-----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

case…when

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
mysql> DELIMITER //
mysql> CREATE PROCEDURE proc_case(type int)
-> BEGIN
-> DECLARE c VARCHAR(500);
-> case type
-> WHEN 0 THEN SET c = 'PARAM IS 0';
-> WHEN 1 THEN SET c = 'PARAM IS 1';
-> WHEN 2 THEN SET c = 'PARAM IS 2';
-> ELSE SET c = 'PARAM IS OTHERS';
-> SELECT c;
-> END CASE;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL proc_case(0);
+------------+
| c |
+------------+
| PARAM IS 0 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc_case(1);
+------------+
| c |
+------------+
| PARAM IS 1 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc_case(2);
+------------+
| c |
+------------+
| PARAM IS 2 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc_case(3);
+-----------------+
| c |
+-----------------+
| PARAM IS OTHERS |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

while

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
mysql> DELIMITER //
mysql> CREATE PROCEDURE proc_while(IN n INT)
-> BEGIN
-> DECLARE i INT;
-> DECLARE s INT;
-> SET i=0;
-> SET s=0;
-> WHILE i<=n DO
-> SET s = s + i;
-> SET i = i + 1;
-> END WHILE;
-> SELECT s;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL proc_while(2);
+------+
| s |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc_while(12);
+------+
| s |
+------+
| 78 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

loop

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
mysql> DROP PROCEDURE IF EXISTS proc_loop;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE proc_loop(IN n INT)
-> BEGIN
-> DECLARE i INT;
-> DECLARE s INT;
-> SET i=0;
-> SET s=0;
-> LOOP_LABEL:LOOP
-> SET s = s + i;
-> SET i = i + 1;
-> IF i > 10 THEN LEAVE LOOP_LABEL;
-> END IF;
-> END LOOP;
-> SELECT s;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL proc_loop(2);
+------+
| s |
+------+
| 55 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

repeat…end repeat

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
mysql> DROP PROCEDURE IF EXISTS proc_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE proc_repeat(IN n INT)
-> BEGIN
-> DECLARE i INT;
-> DECLARE s INT;
-> SET i=0;
-> SET s=0;
-> REPEAT
-> SET s = s + i;
-> SET i = i + 1;
-> UNTIL i>10
-> END REPEAT;
-> SELECT s;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL proc_repeat(2);
+------+
| s |
+------+
| 55 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

评论

Your browser is out-of-date!

Update your browser to view this website correctly.&npsb;Update my browser now

×