MySQL_自定义函数

1. 自定义函数

  用户自定义函数是一种对 MySQL 扩展的途径，其用法与内置函数相同。

• 自定义函数和内置函数一样，具有两个必备条件：

参数：不一定要有；
返回值：一定要有。

  函数可以返回任意类型的值，同样可以接收这些类型的参数，且函数的参数和返回值之间没有必然的内在联系。

创建自定义函数SQL：

delimiter //　　--如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略
　　create function 函数名(形参列表) returns 返回类型　　--注意是retruns
　　begin
　　　　函数体　　　　--函数内定义的变量如：set @x = 1; 变量x为全局变量，在函数外面也可以使用
　　　　返回值
　　end
　　//
delimiter ;

关于函数体:

• 函数体由合法的 SQL 语句构成；
• 函数体可以是简单的 SELECT 或 INSERT 语句；
• 函数体如果为复合结构则使用 begin…end 语句；
• 复合结构可以包含声明，循环，控制结构。

自定义无参函数：

函数中只需含一句查询 SQL ，因此省略了 delimiter 和 begin…end

create function f1() returns varchar(30)
return date_format(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

select f1();

自定义带参函数：

delimiter //
CREATE FUNCTION f2(a int, b int) RETURNS int
begin
    return a + b;
end
//
delimiter ;

select f2(1,2);

删除自定义函数：

drop function [if exists] function_name;

---

2. 自定义函数中定义变量

定义全局变量

 赋值的操作符：=或:=。

 作用域：针对于当前会话（连接）有效，同于会话变量的作用域，应用在任何地方，也就是begin...end里面或begin...end外边。

• 声明变量并初始化

SET @用户变量名 = 值;	

定义局部变量

 作用域：仅仅在定义它的begin...end中有效。

• 声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

• 赋值

SET 局部变量名 = 值;

---

3、流程控制

  MySQL 中可以使用IF语句、CASE语句、和WHILE语句等来进行流程控制：

• IF语句

  IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list
   [ELSEIF search_condition THEN statement_list] ...
   [ELSE statement_list]
END IF;

• WHILE语句

  WHILE语句是当满足条件时，执行循环内的语句。WHILE语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]

  begin_label参数和end_label参数分别表示循环开始和结束的标志，两个标志必须相同，而且都可以省略；statement_list参数表示需要循环执行的语句。

---

4. 编程实战

4.1 求三个数中的最大值

• 定义一个函数，输出三个数中的最大值，函数形式为：
• fn_three_max(param_1 int, param_2 int, param_3 int)

delimiter //
create function fn_three_max(param_1 int,param_2 int,param_3 int) RETURNS int
BEGIN
        DECLARE max_val int DEFAULT 0;
        if param_1 > param_2 then
            set max_val=param_1;
        else 
            set max_val=param_2;
        end if;
        if param_3 > max_val then
            set max_val=param_3;
        end if;
        return max_val;
END
//

---

4.2 第N高的薪水

题目内容:

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。 Employee 表的字段: id, salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

注意点: limit 里面不能做运算,要对输入的变量进行处理

limit 2,1: 跳过 2 条取出 1 条数据，即读取第 3 条数据

 

limit 2 offset 1: 跳过1条取两条，即读取第2,3条

所以，读取第 N 条数据，则跳过前 N - 1 条记录，再读取一条记录。第二高薪水则可以：limit 1 offset 1（记录已去重）

---