数据库之MySQL自定义函数

本篇文章介绍MySQL中的自定义函数,文章部分内容由笔者看其他大牛博客总结而来,仅做笔记。

本篇文章举的所有例子都对应于city关系,city关系部分数据如下:·

city关系

MySQL除了数据库内置的函数之外还可以自定义函数,自定义的函数包含一系列SQL语句,除了SQL语句还可以在函数体中使用类似通用程序语言中的某些关键字的关键字来声明变量或流程控制。

自定义函数语法如下:

DELIMITER 自定义语句结束符
CREATE FUNCTION 函数名[(参数列表)]
RETURNS 数据类型
BEGIN
    [DECLARE 参数列表;]
    [SQL语句;]
    RETURN 返回值;
END 自定义语句结束符
DELIMITER ;

其中delimiter用来定义结束标识符,在MySQL中默认是以分号作为语句结束符,但在函数内部也会用到分号,因此会冲突,这样定义一个语句结束符,在END后使用就表示整个函数是一体。在函数结束后再使用delimiter重新定义分号为结束标识符。

自定义函数可以无参,也可以定义多个参数,参数的格式与在定义关系时属性的格式类似,例如:

CREATE FUNCTION findProvinceIdByCity(cityId INT(10),province_id VARCHAR(20))

函数体必须放在bigin和end关键字之间。

declare是定义变量的关键字,定义变量语句必须在函数体的第一行,作用域是整个函数体,其基本格式为:

DECLARE 变量1[,变量2,...变量n] 变量类型 [DEFAULT 默认值];

return用来返回一个返回值,这里的一个一般是指某个值或者关系中某一行数据中的某个属性,且类型必须与returns定义的一致。我们可以把函数类比成一个有返回值且类型为基本类型的方法,在这个方法中,return语句必须存在且返回一个与返回值类型相同的值(null除外)。

例如我们要定义一个城市id作为参数,返回值为此城市所在省份的id的函数,其语句如下:

DELIMITER **
CREATE FUNCTION findProvinceIdByCityId(cityId INT(10))
RETURNS VARCHAR(20)
BEGIN
	DECLARE provinceId VARCHAR(20);
	SELECT province_id
	INTO provinceId 
	FROM city
	WHERE city_id = cityId;
	RETURN provinceId;
END **
DELIMITER;

定义函数的目的是使用它,使用自定义函数的形式与数据库内置函数类似:

SELECT 函数名[(参数列表)];

例如调用上面定义的findProcinceIdByCityId函数查询城市id为130201的城市所在的省份id:

SELECT findProvinceIdByCityId(130201) AS 省份id;

以上只是定义了一个简单的函数,有时在函数中我们可能需要做一些稍微复杂的事情,就需要用到变量赋值和流程控制等语句。

变量赋值

在自定义函数中有两种方式可以给变量赋值,分别使用set和select into关键词,其使用形式如下:

DECLARE num INT(10);
SET num = 10;

SELECT count(*) FROM city INTO num;

if语句

函数中也可以使用if语句,作用与通用程序语言相同,格式略有差别。

IF 条件判断语句 THEN 执行语句;
[ELSEIF 条件判断语句 THEN 执行语句;]
[ELSE 执行语句;]
END IF;

例如根据city_id判断该城市是否为直辖市,是则返回'是',否则返回'否',使用if语句判断的函数体可以这样写:

DECLARE flag VARCHAR(10);
DECLARE provinceId VARCHAR(20);
SELECT province_id
FROM city
WHERE city_id = cityId
INTO provinceId;
IF(provinceId = 110000) THEN SET flag = '是';
ELSEIF(provinceId = 120000) THEN SET flag = '是';
ELSEIF(provinceId = 310000) THEN SET flag = '是';
ELSEIF(provinceId = 500000) THEN SET flag = '是';
ELSE SET flag = '否';
END IF;
RETURN flag;

case语句

函数中的case语句与通用程序语言中的switch case语句类似,但有两种形式,第一种形式如下:

CASE 变量
WHEN 值1 THEN 执行语句;
[WHEN 值2 THEN 执行语句;]
[ELSE 执行语句;]
END CASE;

用case替代上面的If语句如下:

CASE provinceId 
WHEN 110000 THEN SET flag = '是';
WHEN 120000 THEN SET flag = '是';
WHEN 310000 THEN SET flag = '是';
WHEN 500000 THEN SET flag = '是';
ELSE SET flag = '否';
END CASE;

case语句的第二种形式如下:

CASE
WHEN 条件判断语句 THEN 执行语句;
[WHEN 条件判断语句 THEN 执行语句;]
[ELSE 执行语句;]
END CASE;

也可以使用这种形式的case语句达到前面判断的效果,语句如下:

CASE
WHEN provinceId = 110000 THEN SET flag = '是';
WHEN provinceId = 120000 THEN SET flag = '是';
WHEN provinceId = 310000 THEN SET flag = '是';
WHEN provinceId = 500000 THEN SET flag = '是';
ELSE SET flag = '否';
END CASE;

while语句

while语句是条件控制循环语句,判断满足条件时继续执行循环语句,其基本形式如下:

[lable:]WHILE 条件判断语句 DO
循环语句
END WHILE [lable];

其中lable是循环开始和结束的标志,前后必须相同,可以省略。以下使用while语句实现的一个没什么实际意义的函数:

DELIMITER //
CREATE FUNCTION whileTest(num INT(10))
RETURNS INT(10)
BEGIN
    DECLARE count INT(10) DEFAULT 0;
    WHILE(count < num) DO
        SET count = count + 1;
    END WHILE;
    RETURN count;
END //

repeat语句

repeat语句也是条件控制循环语句,但与while语句不同的是,repeat语句在满足条件时循环结束。其基本形式如下:

[lable:]REPEAT
循环语句
UNTIL 条件判断语句
END REPEAT [lable];

以下是使用repeat语句实现的与上面while语句作用相同的函数:

DELIMITER //
CREATE FUNCTION repeatTest(num INT(10))
RETURNS INT(10)
BEGIN
    DECLARE count INT(10) DEFAULT 0;
    REPEAT
    SET count = count + 1;
    UNTIL count = num
    END REPEAT;
    RETURN count;
END //

loop语句

loop语句是循环语句,与while语句和repeat语句最大的不同是没有条件控制,换句话说就是没有结束循环的功能。其基本形式如下:

[lable:]LOOP
循环语句
END LOOP [lable];

以下是一个使用loop语句的死循环:

flag:LOOP
SET count = count + 1;
END LOOP flag;

leave语句

leave语句用来跳出循环语句,类似于Java中的break关键字。其基本形式如下:

LEAVE lable;

leave语句可以和loop语句、while语句与repeat语句一起使用,在介绍loop语句时没有结束循环的作用,我们现在使用leave语句完成与repeat语句例子中的函数同样的功能:

DELIMITER //
CREATE FUNCTION loopAndLeaveTest(num INT(10))
RETURNS INT(10)
BEGIN
    DECLARE count INT(10) DEFAULT 0;
    flag:LOOP
    SET count = count + 1;
    IF count = num THEN LEAVE flag;
    END IF;
    END LOOP flag;
    RETURN count;
END //

需要注意if语句、case语句和这几个循环语句都需要用end来结束,笔者在写上面这个函数的时候忘了写“END IF”导致报错。

iterate语句

iterate语句用来跳出本次循环,进入下一次循环,类似于Java语言中的continue关键字。其基本使用形式如下:

ITERATE lable;

使用iterate语句实现以下函数,此函数功能是返回入参的一半,如果是奇数就返回其-1之后的一半:

DELIMITER //
CREATE FUNCTION iterateTest(num INT(10))
RETURNS INT(10)
BEGIN
    DECLARE count INT(10) DEFAULT 0;
    WHILE num > 0 DO
    IF MOD(num,2) = 0 THEN SET count = count + 1;
    END IF;
    SET num = num - 1;
    END WHILE;
    RETURN count;
END //

查看函数列表

查看函数列表的SQL语句如下:

SHOW FUNCTION STATUS;

查看函数定义

查看函数定义的SQL语句如下:

SHOW CREATE FUNCTION 函数名;

删除函数

删除函数的SQL语句如下:

DROP FUNCTION 函数名;

 

  • 6
    点赞
  • 41
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值