数据库-自定义函数

1、分隔符的修改

分隔符（定界符）-DELIMITER

• 告诉MySQL解释器语句结束
• DELIMITER可以设置MySQL的结束符
• MySQL默认的语句结束符是；，

为了避免与自定义函数或者存储过程总SQL结束符冲突，使用DELIMITER改变MySQL的结束符，如修改为//    DELIMITER//

定义完毕，需要将结束符恢复  DELIMITER;

---

2、永远的Hello-World

函数

• 内置函数

极大的方便了开发和数据操作，如聚合函数等

• 自定义函数

解决个性化需求

自定义函数

• UDF（User-Defind Function）
• 自定义函数是对MySQL功能的扩展

创建自定义函数

• CREATE [AGGERGATE]  FUNCTION

function_name(parameter_name type,[parameter_name type,...])

        RETURNS (STRING | INTRGER |REAL)

       runtime_body

即：CREATE FUNCTION 函数名称（参数列表）

RETURENS   返回值类型

 

函数体

• 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成
• 多条语句应该使用begin  end语句块包含

1）CREATE FUNCTION f_say_hello()
RETURNS VARCHAR(20)
BEGIN
      RETURN 'Hello World!';
END; 

 

删除自定义函数

DROP  FUNCTION  function_name;

2）delimiter //
DROP FUNCTION IF EXISTS f_say_hello;
CREATE FUNCTION f_say_hello()
RETURNS VARCHAR(20)
BEGIN
   RETURN 'Hello World!';
END//
delimiter ;
 

 

调用自定义函数

SELECT  function_name(parameter_value)

SELECT f_say_hello();

---

3、带参数的自定义函数

1）一个参数

delimiter //
DROP FUNCTION IF EXISTS f_say_a_word;
CREATE FUNCTION f_say_a_word(word VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
   RETURN 'Hello World!';
END//
delimiter ;

SELECT f_say_a_word('Hello World!!!');

 

2）两个参数

delimiter //
DROP FUNCTION IF EXISTS f_add_num;
CREATE FUNCTION f_add_num(a INT,b INT)
RETURNS INT
BEGIN
   RETURN a+b;
END//
delimiter ;

SELECT f_add_num(5,4);

---

4、变量

变量

• 变量是用来存储数据的
• 记忆功能

 

变量的定义

DECLARE var_name[，var_name]...data_type [DEFAULT VALUE];

• 即DECLARE  变量1（，变量2，...） 变量类型 [DEFAULT  默认值]
• var_name 是局部变量的名称，data_type是变量的数据类型
• DEFALT VALUE字句可以为变量提供默认值

 

变量的作用范围

• 变量需要定义在BEGIN...END间的第一行
• 可以理解成全部变量

 

变量的赋值

• 为变量赋值可以改变变量的值，使用SET为变量赋值

SET  parameter_name = value[,parameter_name = value...]

SELECT  value  INTO  parameter_name

 

自定义函数实战

DECLARE _investTimes INT DEFAULT 0;

-- 为变量赋值
-- SET _sumAmount = 100.00; 

--   求出该用户的投资总额
SELECT SUM(Amount) INTO _sumAmount
FROM invest
WHERE MemberID = userId;

-- 求出该用户的投资次数
SELECT COUNT(Amount) INTO _investTimes
FROM invest
WHERE MemberID = userId;
RETURN _sumAmount / _investTimes;
END//
delimiter ;

 

SELECT f_get_avg_amount(1003);