MysQL自学笔记9--自定义函数

MySQL自学笔记

使用MySQL 5.5以及MySQL自带命令客户端

自定义函数

在MySQL中，自定义函数（user-define function, UDF）作用就像是MySQL固有的函数一样去创建一个新的函数去扩展MySQL，也就是说UDF是MySQL功能的一个扩展

语法

• 创建

create function 函数名称(参数列表)
returns 返回值类型
函数体

create function fun_Name(parameter define ...)
returns {string/integer/real}
runtime_body

• 创建一个简单的无参数UDF：create function f1() returns varchar(20) return "Hello World!";

  • UDF有两个点要注意：一个是参数，另外一个是返回值
  • UDF可以没有参数，但是必须有而且只能有一个返回值，而且如果有用到begin ... and ... 构造的复合语句，必须将return语句包含在其中

• 删除UDF：drop function fun_Name;

• 调用：select fun_Name(parameter_values);

流程控制语句

MySQL自定义函数中流程控制语句有这几种：if、case、loop、leave、iterate、repeat、while。每一个流程中可能包含单独语句们也可以使用begin ... and ... 构造复合语句，构造可以被嵌套。 这里可以使用delimiter来对结束符进行修改。

• 定义局部变量：

declare var_Name[, var_Name1, ... ] date_Type [default value];
//简单说就是：declare 变量1[变量2, ...] 变量类型 [default 默认值];

• if语句：用来进行条件判断。示例代码如下：

  	if age > 20 then set @count1 = @count1 + 1;
  	elseif age = 20 then set @count2 = @count2 + 1;
  	else set @count3 = @count3 + 1;
  	end if$
  	// 有age和20的大小关系来判断不同SQL语句的执行
  

• case语句：可是用来对条件语句进行判断，可以实现比if更加复杂的条件判断，示例代码如下：

  	case age
  	when 20 then set @count1 = @count1 + 1;
  	else set @count2 = @count2 + 1;
  	end case$
  

  	case
  	when age = 20 then set @count1 = @count1 + 1;
  	else set @count2 = @count2 + 1;
  	end case$
  

• loop语句：可以是某些特定的语句重复执行，实现一个简单的循环，但是loop本身没有停止循环的语句，必须遇到leave语句等才能够停止循环。下面就构造一个loop循环的示例代码，但是没有退出循环的条件，所以是一个死循环！

  	add_num : loop
  	set @count = @count + 1;
  	end loop add_num$
  

• leave语句：用于跳出循环，示例代码如下：

  	add_num : loop
  	set @count = @count + 1;
  	if @count = 100 then
  	leave add_num;
  	end loop add_num$
  	//当@count的值等于100时，退出循环！
  

• iterate语句：用来跳出循环，但是iterate只能跳出本次循环，然后在直接进入下一次循环，示例代码如下：

  	add_num : loop
  	set @count = @count + 1;
  	if @count = 100 then
  	leave add_num;
  	else if mod(@count, 3) = 0 then
  	iterate add_num;
  	select * from employee;
  	end loop add_num$
  	// 在该语句中，@count值为100时跳出循环并结束，在循环过程中，如果@count能够整除3的话，则跳出本次循环，不再执行下面的代码，而是重新开始下一次的循环
  

  • 使用leaver以及iterate跳出循环一定要清楚这两个之间的区别，使用后出现的效果是什么样子的！

• repeat语句：是一个有条件控制的循环语句，当满足条件时就会跳出循环语句。示例代码如下：

  	repeat
  	set @count = @count + 1;
  	until @count = 100;
  	end repeat$
  	// 循环执行到@count到100时结束
  

• while语句：while也是一个有条件控制的循环语句，但是和repeat不一样的是while是满足条件后才开始执行循环语句，而repeat是满足条件后就退出循环语句。示例代码如下：

  	while @count < 100 do
  	set @count = @count + 1;
  	end while$
  	// 在@count值小于100时执行循环语句
  

###函数与存储过程的区别

1. 函数有且只有一个返回值，存储过程不能有返回值
2. 函数只能有输入参数，不能带有in，而存储过程可以带有多个in、out、inout参数
3. 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强
4. 存储过程可以调用存储函数。但函数不能调用存储过程
5. 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用

Reference

http://www.cnblogs.com/caoruiy/p/4485273.html

转载于:https://my.oschina.net/QinghaiZheng/blog/830515