《数据库开发实践》之自定义函数【知识点罗列+例题演练】

一、什么是自定义函数？

1.函数：

在数据库中，函数是指完成特定功能的SQL语句集。函数分为内部函数和自定义函数。

内部函数

内部函数是MySQL开发者定义的，比如我们接触过的那些常用函数： 聚合函数 功能 COUNT 统计符合条件的数据条数 AVG 计算指定列的平均值 SUM 计算指定列的总和 MAX 找出指定列中的最大值 MIN 找出指定列中的最小值 日期和时间函数 功能 NOW 返回当前日期时间 CURDATE 返回当前日期 CURTIME 返回当前时间 DATE 提取日期部分 TIME 提取时间部分 TIMESTAMPADD 增加指定时间间隔到日期时间值 TIMESTAMPDIFF 计算两个日期时间之间的差值 字符串函数 功能 CONCAT 连接两个或多个字符串 SUBSTRING 返回字符串的子串 LENGTH 返回字符串的长度 REPLACE 替换字符串中的指定子串 UPPER 将字符串转换为大写 LOWER 将字符串转换为小写 格式化函数 功能 DATE_FORMAT 格式化日期时间字符串 FORMAT 将数字格式化为指定的格式 LPAD 在字符串的左侧填充指定的字符 RPAD 在字符串的右侧填充指定的字符 TRIM 去除字符串两端的空格或指定字符 控制流函数 功能 IF 条件判断函数，返回满足条件的值或表达式 CASE 多条件判断函数，根据条件返回不同的值或表达式 COALESCE 返回参数列表中第一个非空的值 NULLIF 如果两个参数相等则返回null，否则返回第一个参数 加密函数 功能 MD5 计算字符串的MD5哈希值 SHA1 计算字符串的SHA1哈希值 ENCRYPT 使用UNIX加密算法对字符串进行加密 AES_ENCRYPT 使用AES算法对字符串进行加密 AES_DECRYPT 使用AES算法对字符串进行解密 系统信息函数 功能 VERSION 返回数据库版本信息 DATABASE 返回当前数据库名称 USER 返回当前用户名称 CONNECTION_ID 返回当前数据库连接的ID CURRENT_DATE 返回当前日期 类型转换函数 功能 CAST 转换数据类型 CONVERT 转换数据类型 STR_TO_DATE 将字符串转换为日期 UNIX_TIMESTAMP 将日期时间转换为UNIX时间戳 FROM_UNIXTIME 将UNIX时间戳转换为日期时间

2.自定义函数：

（1）自定义函数是由一条或多条SQL语句组成的程序，可用于封装代码以便进行重用

（2）用户可以根据需求自己去编写函数的逻辑和实现方式，并将其存储在数据库中，以便在需要的时候调用。这些自定义函数可以提高数据库的灵活性和可扩展性，使得用户可以根据自己的需求实现定制化的功能

3.自定义函数的两个必要条件：

（1）参数：可以有零个或者多个；

（2）返回值：必须有且只有一个；

二、自定义函数和存储过程的区别

	自定义函数	存储过程
定义	一种可重复使用的数据库对象，接受参数在执行一系列操作后返回一个值	一种可重复使用的数据库对象，接受参数并执行一系列操作
功能	用于执行特定的操作并返回结果，功能的针对性较强	用于执行一系列操作，可以包含条件判断、循环等控制语句，功能较复杂
参数	只能有输入参数	有IN、OUT、INOUT三个参数
返回值	可以返回一个单一的值，并且需要描述返回值的类型，一个函数体中必须要有一个有效的return语句	通常不返回值，返回的是数据集。 但可以使用OUT或INOUT参数传递结果
调用方式	作为查询语句（SELECT）的一个部分来调用	作为一个独立的部分来执行（call语句），可以通过存储过程名称进行调用
事务支持	可以包含在事务中，可以回滚	可以包含在事务中，可以回滚
数据库操作	可以读取和修改数据库中的数据
执行速度	通常比存储过程慢，因为每次调用都需要连接数据库	通常比函数快，因为可以在同一连接中多次调用
适用场景	适用于需要计算、转换或处理数据并返回结果的场景	适用于需要执行多个步骤或复杂逻辑的场景

三、Mysql语句创建、执行和删除自定义函数

1.创建函数

?MySQL中创建自定义函数的语法如下：

CREATE FUNCTION function_name(parameter_name type, [parameter_name type,...])
　　　　RETURNS type
　　　　[characteristic ...] fun_body

a. function_name——函数名
b.parameter_name type——输入参数名及其类型，可以0个也可以多个
c.RETURNS type——返回参数的类型
d.characteristic——指定函数的特征，和存储过程一样
e.fun_body——函数体，Begin开始标志、end结束标志

2.执行调用自定义函数

?MySQL调用自定义函数和内置函数用法一样，语法如下：

select function_name[(传参)]; 

其中，function_name为所需调用的函数名称，
传参表示根据函数定义时的参数进行传参。

3.查看自定义函数

用户可以查看指定的自定义函数的详细信息，语法如下：

show create function func_name;

其中，func_name表示需要查看的自定义函数的名称。

4.删除自定义函数

?删除自定义函数语法如下：

DROP FUNCTION function_name;

其中，function_name为需删除的自定义函数的名称。

四、例题演练

1.创建一函数f_jysl，基于读者表reader：

根据传入参数读者姓名查询出其借阅书籍的数量，如果借阅书籍数量为空，则返回0。写出该自定义函数的代码，调用该函数分别查询出读者姓名为“陈晨”“叶子”的读者借阅书籍的数量。

delimiter //
create function f_jysl(rname varchar(30))
returns int
begin
? ? ? ? declare lendnum int;
? ? ? ? select num into lendnum from reader where readername=rname;
? ? ? ? if? lendnum is null?then
? ? ? ? ? ? set? lendnum=0;
? ? ? ? end if;
? ? ? ? return lendnum;
end //
delimiter;

创建函数成功：

调用函数：

---

ps:记录一下写这个函数时出现的语法错误

• 第一：在第三行也就是returns int 这里，少写了s
• 第二：declare单词打错，打成了daclare
• 第三：if语句 写完条件后没有写then
• 第四：return lendnum；这里没有加分号

2.创建一函数f_sjzt1，基于书籍表bookinfo：

根据传入参数书籍名称和出版社查询出书籍的状态，如果书籍状态为空，则返回“该书籍不存在”。写出该自定义函数的代码，及调用该函数分别查询出“人民邮电出版社”出版的《数据结构》书籍的状态及“电子工业出版社”出版的《数据结构》书籍的状态。

delimiter //
create function f_sjzt1(bname varchar(30),pub?varchar(30))
returns varchar(10)
begin
     declare state varchar(10);
     select bookstatus into state from bookinfo where bookname=bname and Publisher=pub;
? ? ?if state is null then
? ? ????set state='该书籍不存在';
?????end if;
?????return state;
end //
delimiter;

创建成功：

调用函数：

ps:记录一下写这个函数时出现的语法错误

出现的所有问题都是中英文输入分号问题：分号使用不正确，应该是英文分号;而不是中文分号；