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

发布时间:2023年12月27日

一、什么是自定义函数?

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:记录一下写这个函数时出现的语法错误

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

文章来源:https://blog.csdn.net/ysy1119/article/details/135244579
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。