mysql 存储过程，函数，触发器

分类: MySQL
undefined

存储过程和函数

　

函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可

 

创建存储过程和函数详解

?
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
[DEFINER = { user | CURRENT_USER }]  #定义用户范围
Definer和sql security子句指定安全环境
简单理解就是定义这个存储过程可以在哪个范围用这个存储过程
Definer是MySQL的特殊的访问控制手段，当数据库当前没有这个用户权限时，执行存储过程可能会报错
sql secuirty的值决定了调用存储过程的方式，取值 ：definer（默认）或者invoker
definer:在执行存储过程前验证definer对应的用户如：user@主机 是否存在，以及
是否具有执行存储过程的权限，若没有则报错
invoker:在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限，若
没有则报错
 
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
存储过程参数
IN,OUT,INOUT三个参数前的关键词只适用于存储过程，对函数而言所有的参
数默认都是输入参数
IN输入参数用于把数值传入到存储过程中；
OUT输出参数将数值传递到调用者，初始值是NULL；
INOUT输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者
定义参数要写上参数类型
 
characteristic: 典型的
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
Comment 用来写入对存储过程和函数的注释
Language子句用来表示此存储过程和函数的创建语言
存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果，反之如果是not deterministic则表示相同参数不会是相同结果，默认是not deterministic
Contains sql表明此存储过程或函数不包含读或者写数据的语句，这是默认属性
 
以下相关属性短语只有咨询含义，并不是强制性的约束
NO SQL表示此存储过程或函数不包含SQL语句
Reads sql data表示此存储过程包含诸select的查询数据的语句，但不包含插入或删除数据的语句
Modifies sql data表示此存储过程包含插入或删除数据的语句
 
routine_body:
    Valid SQL routine statement
    可以包含一个简单的SQL语句，也可以包含多个SQL语句， 通过begin…end将这多个SQL语句包含在一起
 
 
 
func_parameter:
    param_name type
参数名和类型
函数没有IN ,OUT,INOUT
 
 
type:
    Any valid MySQL data type
任何mysql数据类型 

　　

例创建一个简易的函数和存储过程

Delimiter命令是改变语句的结束符， MySQL默认的结束符为;号，由于procedure和function中的;号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结束 
call proc_name() 调用存储过程　　

 

存储过程优缺点

优点： 
存储过程是一组预先创建并用指定的名称存储在数据库服务器上的 SQL 语句，将使用比较频繁或者比较复杂的操作，预先用 SQL 语句写好并存储起来，以后当需要数据库提供相同的服务时，只需再次执行该存储过程。 
1.具有更好的性能

存储过程是预编译的，只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，因此使用存储过程可以提高数据库执行速度。

2.功能实现更加灵活

存储过程中可以应用条件判断和游标等语句，有很强的灵活性，可以直接调用数据库的一些内置函数，完成复杂的判断和较复杂的运算。

3.减少网络传输

复杂的业务逻辑需要多条 SQL 语句，当客户机和服务器之间的操作很多时，将产生大量的网络传输。如果将这些操作放在一个存储过程中，那么客户机和服务器之间的网络传输就会减少，降低了网络负载。

4.具有更好的安全性

（1）数据库管理人员可以更好的进行权限控制，存储过程可以屏蔽对底层数据库对象的直接访问，使用 EXECUTE 权限调用存储过程，无需拥有访问底层数据库对象的显式权限。

（2）在通过网络调用过程时，只有对执行过程的调用是可见的。无法看到表和数据库对象名称，不能嵌入SQL 语句，有助于避免 SQL 注入攻击。

缺点： 
1 .架构不清晰，不够面向对象

存储过程不太适合面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，业务逻辑在存储层实现，增加了业务和存储的耦合，代码的可读性也会降低，

2 .开发和维护要求比较高

存储过程的编写直接依赖于开发人员，如果业务逻辑改动较多，需要频繁直接操作数据库，大量业务降维到数据库，很多异常不能在代码中捕获，出现问题较难排查，需要数据库管理人员的帮助。

3 .可移植性差

过多的使用存储过程会降低系统的移植性。在对存储进行相关扩展时，可能会增加一些额外的工作。

存储过程与SQL语句如何抉择？ 
架构设计没有绝对，只有在当前的场景下最合适的。 
因此： 
普通的项目开发中，不建议大量使用存储过程，对比SQL语句，存储过程适用于业务逻辑复杂，比较耗时，同时请求量较少的操作，例如后台大批量查询、定期更新等。

（1）当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时可以考虑应用存储过程

（2）在一个事务的完成需要很复杂的商业逻辑时可以考虑应用存储过程

（3）比较复杂的统计和汇总可以考虑应用后台存储过程

 

查看存储过程和函数

?
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
查询所有存储过程和函数
select routine_schema,routine_name,routine_type,routine_body from information_schema.routines where routine_schema='库名'
select name,type from mysql.proc where db='your_db_name'
 
select name,type from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'        #查看所有存储过程
select name,type from mysql.proc where db='your_db_name' and type='function';   #查看所有函数
 
show create procedure  proc_name;
show create function   func_name;
 
查看存储过程和函数详细信息
[例]
mysql> show create procedure simpleproc\G;
*************************** 1. row ***************************
           Procedure: simpleproc
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(in param1
    COMMENT '查询大于parmam1的 学生的个数'
begin
select count(*) into param2 from students where sid> param1;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)

　　

 

删除存储过程和函数

?
1
2
3
4
5
6
7
8
mysql> HELP DROP PROCEDURE
Name: 'DROP PROCEDURE'
Description:
Syntax:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
 
If exists关键词用来避免在删除一个本身不存在的存储过程或函数
时， MySQL返回错误

　　

begin … end 语句

Begin…end语句通常出现在存储过程、函数和触发器中，其中可以包含一个或多个语句，每个语句用;号隔开

?
1
2
3
4
5
6
7
mysql> help begin end;
Name: 'BEGIN END'
Description:
Syntax:
[begin_label:] BEGIN
    [statement_list]
END [end_label]

　　

标签语句

标签label可以加在begin…end语句以及loop, repeat和while语句 
loop语句中通过iterate和leave来控制流程， iterate表示返回指定标签位置， leave表示跳出标签

?
1
2
3
4
5
6
7
8
9
10
11
12
13
[begin_label:] LOOP
    statement_list
END LOOP [end_label]
 
[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]
 
 
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

　　

 

loop 语句例：

　　

declare语句

Declare语句通常用来声明本地变量、游标、条件或者handler
Declare语句只允许出现在begin … end语句中而且必须出现在第一行
Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler
 
本地变量可以通过declare语句进行声明
 
声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值
 
通过declare声明变量方法
?
1
2
3
4
5
6
DECLARE var_name [, var_name] ... type [DEFAULT value]
 
使用default指定变量的默认值，如果没有指定默认值则初始值为NULL
Type指明该变量的数据类型
声明的变量作用范围为被声明的begin … end语句块之间
声明的变量和被引用的数据表中的字段名要区分开来

　　

例：

　　

流程控制语句

MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句，另外return语句也是函数中的特定流程控制语句

流程控制case语句

Case语句在存储过程或函数中表明了复杂的条件选择语句

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> HELP CASE STATEMENT;
Name: 'CASE STATEMENT'
Description:
Syntax:
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
 
Or:
 
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE
 
第一个语句中case_value与后面各句的when_value依次做相等的对比， 如果碰到相等的，则执行对应的后面的statement_list，否则接着对比，如果都没有匹配，则执行else后面的statement_list
 
第二个语句中当search_condition满足true/1的结果时，则执行对应的statement_list，否则执行else对应的statement_list
 
Statement_list可以包含一个或多个SQL语句

　　

例：

　　

流程控制IF语句

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> help if statement;
Name: 'IF STATEMENT'
Description:
Syntax:
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF
IF语句中如果search_condition满足true/1的条件，则执行对应的statement_list，否则再判断elseif中的search_condition是否满足
true/1的条件，如果都不满足则执行else中的statement_list语句
 
Statement_list中可以包含一个或多个SQL语句

　　

例：

　　

流程控制iterate语句

Iterate语句仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环

?
1
2
3
4
5
6
mysql> help iterate
Name: 'ITERATE'
Description:
Syntax:
ITERATE label
Label表示自定义的标签名

　　

流程控制leave语句

Leave语句表明退出指定标签的流程控制语句块 
通常会用在begin…end，以及loop,repeat,while的循环语句中

?
1
2
3
4
5
6
7
mysql> help leave
Name: 'LEAVE'
Description:
Syntax:
LEAVE label
 
Label表明要退出的标签名

　　

流程控制loop语句

Loop语句是存储过程或函数中表达循环执行的一种方式

?
1
2
3
4
5
6
7
mysql> HELP LOOP
Name: 'LOOP'
Description:
Syntax:
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

　　

 

流程控制repeat语句

repeat语句是存储过程或函数中表达循环执行的一种方式

?
1
2
3
4
5
6
7
8
mysql> HELP REPEAT
Name: 'REPEAT LOOP'
Description:
Syntax:
[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

　　

• Repeat语句中statement_list一直重复执行直到search_condition条件满足 
• Statement_list可以包含一个或多个SQL语句

流程控制while语句

?
1
2
3
4
5
6
7
8
9
10
mysql> help while
Name: 'WHILE'
Description:
Syntax:
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]
• 当search_condition返回为true时，则循环执行statement_list中的语句，直到
search_condition的结果返回为false
• Statement_list中可以包含一个或多个SQL语句

　　

流程控制return语句

Return语句用在函数中，用来终结函数的执行并将指定值返回给调用者

?
1
2
3
4
5
6
mysql> HELP RETURN
Name: 'RETURN'
Description:
Syntax:
RETURN expr
• 在函数中必须要有至少一个return语句，当有多个return语句时则表明函数多种退出的方式

　　

cursor游标

Cursor游标用来声明一个数据集 
游标的声明必须在变量和条件声明之后，在handler声明之前

cursor游标close语句

Cursor close语句用来关闭之前打开的游标

?
1
2
3
4
5
6
7
8
mysql> help  close;
Name: 'CLOSE'
Description:
Syntax:
CLOSE cursor_name
 
如果关闭一个未打开的游标，则MySQL会报错
如果在存储过程和函数中未使用此语句关闭已经打开的游标，则游标会在声明的begin…end语句块执行完之后自动关闭

　　

 

cursor游标declare语句

Cursor declare语句用来声明一个游标和指定游标对应的数据集合，通常数据集合是一个select语句

?
1
2
3
4
5
6
7
mysql> help cursor;
Name: 'DECLARE CURSOR'
Description:
Syntax:
DECLARE cursor_name CURSOR FOR select_statement
 
• Select_statement代表一个select语句

　　

cursor游标fetch语句

Cursor fetch语句用来获取游标指定数据集的下一行数据并将各个字段值赋予后面的变量

?
1
2
3
4
5
6
7
8
9
mysql> help fetch;
Name: 'FETCH'
Description:
Syntax:
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
 
数据集中的字段需要和INTO语句中定义的变量一一对应
 
数据集中的数据都fetch完之后，则返回NOT FOUND

　　

cursor游标open语句

Open cursor语句用来打开一个之前已经声明好的游标

?
1
2
3
4
5
mysql> HELP OPEN
Name: 'OPEN'
Description:
Syntax:
OPEN cursor_name

　　

Declare condition语句

Declare condition语句命名特定的错误条件，而该特定错误可以在declare…handler中指定处理方法

?
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> help declare condition
Name: 'DECLARE CONDITION'
Description:
Syntax:
DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
 
Condition_value指定特定的错误条件，可以有以下两种形式
• Mysql_err_code表示MySQL error code的整数
• SQLSTATE sqlstate_value表示MySQL中用5位字符串表达的语句状态
mysql> insert into students (sid) values (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
23000 就是sqlstate_value 主键错误
1062表示mysql_err_code
 
比如在MySQL中1051error code表示的是unknown table的错误，如果要对这
个错误做特殊处理，可以用三种种方法：
 
 
1. DECLARE CONTINUE HANDLER FOR 1051
    BEGIN
    -- body of handler
    END;
 
2. DECLARE no_such_table CONDITION FOR 1051;
   DECLARE CONTINUE HANDLER FOR no_such_table
   BEGIN
   -- body of handler
   END;
 
3. DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
    DECLARE CONTINUE HANDLER FOR no_such_table
    BEGIN
     -- body of handler
    END;

　　

Declare handler语句

Declare handler语句用来声明一个handler来处理一个或多个特殊条件，当其中的某个条件满足时则触发其中的statement语句执行 
Statement可以是一个简单SQL语句，也可以是begin…end组成的多个语句

?
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> help declare handler;
Name: 'DECLARE HANDLER'
Description:
Syntax:
DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
 
handler_action:
    CONTINUE
  | EXIT
  | UNDO
 
condition_value:
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
 
Handler_action子句声明当执行完statement语句之后应该怎么办
    Continue代表继续执行该存储过程或函数
    Exit代表退出声明此handler的begin…end语句块
    Undo参数已经不支持 mysql 5.7
Condition_value的值有以下几种：
     Mysql_err_code表示MySQL error code的整数
     SQLSTATE sqlstate_value表示MySQL中用5位字符串表达的语句状态
     Condition_name表示之前在declare…condition语句中声明的名字
     SQLWARNING表示所有的警告信息，即SQLSTATE中01打头的所有错误
     NOT FOUND表示查完或者查不到数据，即SQLSTATE中02打头的所有错误
     SQLEXCEPTION表示所有的错误信息
 
当condition发生但没有声明handler时，则存储过程和函数依照如
下规则处理
    发生SQLEXCEPTION错误，则执行exit退出
    发生SQLWARNING警告，则执行contine继续执行
    发生NOT FOUND情况，则执行continue继续执行

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
• DECLARE CONTINUE HANDLER FOR 1051
• BEGIN
• -- body of handler
• END;
• •
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
• BEGIN
• -- body of handler
• END;
• •
DECLARE CONTINUE HANDLER FOR SQLWARNING
• BEGIN
• -- body of handler
• END;
• •
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
• BEGIN
• -- body of handler
• END;

　　

例：比如SQLSTATE ‘23000’表示主键冲突错误

　　

游标例：

　　

触发器

触发器是一个特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL

也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动调用。 
触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。 
作用

  1. 触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。

  2. 触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。

  3. 触发器还可以强制执行业务规则

  4. 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

实际应用 
尽管触发器有很多优点，但是在实际的项目开发中，特别是OOP思想的深入，触发器的弊端也逐渐突显，主要：

1、过多的触发器使得数据逻辑变得复杂

2、数据操作比较隐含，不易进行调整修改

3、触发器的功能逐渐在代码逻辑或事务中替代实现，更符合OO思想。

创建触发器

create trigger语句用来创建一个触发器，触发器的作用是当表上有对应SQL语句发生时，则触发执行

触发器创建时需要指定对应的表名tbl_name

?
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> HELP CREATE TRIGGER;
Name: 'CREATE TRIGGER'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body
 
trigger_time: { BEFORE | AFTER }
 
trigger_event: { INSERT | UPDATE | DELETE }
 
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
 
 
Definer关键词用来指定trigger的安全环境
 
Trigger_time指定触发器的执行时间， BEFORE和AFTER指定触发器在表中的每行数据修改前或者后执行
Trigger_event指定触发该触发器的具体事件
    • INSERT当新的一行数据插入表中时触发，比如通过执行insert,loaddata,replace语句插入新数据
    • UPDATE当表的一行数据被修改时触发，比如执行update语句时
    • DELETE当表的一行数据被删除时触发，比如执行delete,replace语句时
 
当执行insert into … on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器 
 
从5.7.2版本开始，可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器，默认情况下按照创建的时间依次执行，通过指定FOLLOWS/PRECEDES改变执行顺序，即FOLLOWS时表示新创建的触发器后执PRECEDES则表示新触发器先执行
 
Trigger_body表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字OLD.col_name表示行数据被修改或删除之前的字段数据， NEW.col_name表示行数据被插入或修改之后的字段数据

　　

例：

　　

查询触发器

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select trigger_schema,trigger_name from information_schema.triggers;
+----------------+----------------------------+
| trigger_schema | trigger_name               |
+----------------+----------------------------+
| course         | simple_trigger             |
| sys            | sys_config_insert_set_user |
| sys            | sys_config_update_set_user |
+----------------+----------------------------+
3 rows in set (0.00 sec)
 
trigger_schema： 哪个库
trigger_name ： 触发器的名字
 
sys 库下面的触发器是默认就有的
 
查看触发器的详细信息
show create trigger course.simple_trigger

　　

删除触发器

?
1
2
3
4
5
6
7
8
mysql> help drop trigger;
Name: 'DROP TRIGGER'
Description:
Syntax:
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
 
• If exists短语用来避免删除不存在的触发器时引发报错
• 当你执行drop table时，表上的触发器也被drop掉了

　　

posted @   keme  阅读(1690)  评论(0)  编辑  收藏  举报
编辑推荐：
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~
# mysql 存储过程，函数，触发器
存储过程和函数创建存储过程和函数详解存储过程优缺点查看存储过程和函数删除存储过程和函数begin … end 语句标签语句declare语句流程控制语句流程控制case语句流程控制IF语句流程控制iterate语句流程控制leave语句流程控制loop语句流程控制repeat语句流程控制while语句流程控制return语句cursor游标cursor游标close语句cursor游标declare语句cursor游标fetch语句cursor游标open语句Declare condition语句Declare handler语句触发器创建触发器查询触发器删除触发器
点击右上角即可分享
微信分享提示
SQL AI 助手