mysql自定义函数和存储过程_MySQL自定义函数和存储过程

自定义函数：

概念：用户自定义函数(user-defined function，UDF)是一种对 MySQL 扩展的途径，其用法与内置函数相同。

创建自定义函数语法结构：

CREATE FUNCTION function_name

RETURNS

{ STRING | INTEGER | REAL | DECIMAL}

routine_body

函数体(routine_body)：

⑴ 函数体由合法的 SQL 语句构成

⑵ 函数体可以是简单的 SELECT 或 INSERT 语句

⑶ 函数体如果为复合结构则使用 BEGIN ... END 语句

⑷ 复合结构可以包含声明，循环，控制结构

删除自定义函数语法结构：

DROP FUNCTION [ IF EXISTS ] function_name

案例：

无参函数：

将系统默认返回的日期格式化为年月月时分表

把这个返回格式封装成一个自定义函数，返回类型为 VARCHAR，返回的函数体为 DATE_FORMAT()：

带参函数：

创建一个函数 f2，传入 num1 和 num2 返回它们的平均值

调用该函数：

带有复合结构函数体的自定义函数：

首先查看下表 tb_user5 的记录：

如果每次插入记录都需要写 INSERT 语句，会有点麻烦。现在把 INSERT 语句封装成一个函数，并返回当前插入的记录的 ID：

分号 “；” 和系统默认的结束符冲突，所以通过 DELIMITER $$ 将分隔符修改为 “$$”。由于有多个语句要执行，所以需要用 BEGIN ... END 构成一个聚合体。

调用函数 adduser：

返回该条记录的 ID，再查询一下表 tb_user5 的记录：

定义局部变量：

DECLARE var_name [，varname] ... date_type [DEFAULT VALUE]

局部变量的作用范围是在 BEGIN...END 语句中，而且定义局部变量语句必须在 BEGIN...END语句中的第一行定义

为变量赋值：

SET parameter_name = value [，parameter_name = value...]

SELECT ... INTO parameter_name

用户变量语法(可以理解为全局变量)：

SET @param_name = value

流程控制语句：

⑴ IF 语句

语法结构：

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

其中，search_condition 表示条件判断语句，statement_list 表示执行语句。

案例：

传入一个参数，根据数值判断返回指定的数值。

⑵ CASE 语句

语法结构：

①

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

其中，case_value 表示条件判断的变量，when_value 表示变量的取值，

statement_list 表示执行语句

案例：

②

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] ...

[ELSE statement_list]

END CASE

其中，search_condition 表示条件判断，statement_list 表示执行语句

案例：

⑶ LOOP 语句

概念：

实现一个简单的循环，没有结束循环语句，需要通过 LEAVE 来停止循环

语法结构：

[begin_label:] LOOP

statement_list

END LOOP [end_label]

其中，begin_label 和 end_label 表示开始和结束的标志可以随意命名，

statement_list 执行语句。

⑷ LEAVE 语句

概念：

结束本次循环

语法结构：

LEAVE label

案例：

声明局部变量 a 循环累加至 10 打印出来。

⑸ ITERATE 语句

概念：

跳出本次循环，重新执行循环，只能出现在 LOOP，REPEATE，WHILE 循环中。

语法结构：

ITERATE label

案例：

执行结果：

由图，声明两个局部变量 x，y，循环 x+1 当 x>10 时结束本次循环；当 x=5 时，y = x*2，

所以 y = 10 ，接着 ITERATE lp 重新执行 LOOP 循环，直到循环结束。

⑹ REPEAT 语句

概念：

含有条件控制的循环语句，满足条件跳出循环

语法结构：

[begin_label:] REPEAT

statement_list

UNTIL search_conditionEND REPEAT [end_label]

案例：

由图，声明局部变量 x 循环加1，大于10时结束循环。

⑺ WHILE 语句

概念：

含有条件控制的循环语句，满足条件进入循环

语法结构：

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label]

案例：

由图，当 x < 10 时，循环加1，x >= 10 时跳出循环。

存储过程：

MySQL命令的执行流程：

如果把流程里的语法分析和编译的环节省略掉，那么 MySQL 的执行效率就会提高。所以这就需要存储过程来完成。

概念：

存储过程是 SQL 语句和控制语句的预编译集合，以一个名词存储并作为一个单元处理，它存储在数据库里面，可以由应用程序调用执行，允许用户声明变量和进行流程控制，而且可以接收参数，不仅可以接收输入类型的参数，也可以接收输出类型的参数，同时可以存在多个返回值。

存储过程的效率比单一的 SQL 执行效率要高，假设有两个 SQL 语句，MySQL 引擎会对这个两个 SQL 语句进行逐一的语法分析，再编译，再执行，而有了存储过程后，只有第一次才进行语法分析和编译流程，后续调用只需调用结果即可。

优点：

⑴ 增强 SQL 语句的功能和灵活性

⑵ 实现较快的执行速度

如果某个操作包含大量 SQL 语句，那么这些语句都将被 MySQL 引擎进行逐一的语法分析，编译和执行的过程，所以效率相对较低，而存储过程是预编译的，当客户端第一次调用存储过程的时候，MySQL 引擎将对它语法分析和编译等操作，然后把编译的结果存储到内存中，后续调用直接从内存里面调用结果。所以效率大量提高。

⑶ 减少网络流量

语法结构：

CREATE

[ DEFINER = { user | CURRENT_USER } ]

PROCEDURE sp_name ( [ proc_parameter [，...] ] )

[ characteristic ...] routine_body

proc_parameter：

[ IN | OUT | INOUT ] param_name_type

其中，

IN 表示该参数的值必须在调用存储过程时指定

OUT 表示该参数的值可以被存储过程改变，并且可以返回

INOUT 表示该参数的调用时指定，并且可以被改变和返回

修改存储过程：

注：只能修改简单的特性，不能修改过程体，若要修改，只能先把存储过程删了，重新创建

语法结构：

ALTER PROCEDURE sp_name [characteristic ...]

COMMENT 'string'

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

COMMENT：注释

CONTAINS SQL：包含 SQL 语句，但不包含读或写数据的语句

NO SQL：不包含 SQL 语句

READS SQL DATA：包含读数据的语句

MODIFIES SQL DATA：包含写数据的语句

SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行

过程体：

⑴ 过程体由合法的 SQL 语句构成

⑵ 过程体可以是任意的(增、删、改、查、多表连接)SQL 语句

⑶ 过程体如果为复合结构则使用 BEGIN ... END 语句

⑷ 复合结构可以包含声明，循环，控制结构

调用存储过程：

⑴ CALL sp_name ([ parameter [，...]])

⑵ CALL sp_name [()]

删除存储过程：

DROP PROCEDURE [IF EXISTS] sp_name

案例：

创建不带参数的存储过程：

存储体 sp1() 不带参数，过程体获取当前版本号，调用该存储体：

创建带有 IN 类型参数的存储过程：

查询下表 tb_goods_cate 的记录：

再创建一个根据 cate_id 删除记录的存储过程：

注：参数的变量名不能和表里的字段名一样

调用：

删除 cate_id = 2 的记录，再查询下记录：

cate_id = 2 已删除。

创建带有 IN 和 OUT 类型参数的存储类型：

由图，创建存储过程 test，传入类目 id，然后查询对应的类目名称。调用的时候，传入 id 等于1，并把返回的名称赋给全局变量 @name，再通过 SELECT 查询该变量的值。

创建带有多个 OUT 类型参数的存储类型：

由图，创建一个存储过程 test，传入类型 id，输出类型该类型的名称和表里的记录总数。

总结：

⑴ 存储过程实现的功能比较复杂，而函数针对性更强，一般情况下，存储过程主要针对表做操作

⑵ 存储过程可以返回多个值，而函数只能有一个返回值

⑶ 存储过程一般独立的来执行，而函数可以作为 SQL 语句中的组成部分

以上为本人的一些学习笔记，如有出错欢迎指正，陆续更新！！！