第十章:MySQL的自定义函数
MySQL不仅内置了一些常用函数,同时也支持自定义函数,来满足开发者的需求。本文章向大家介绍mysql的自定义函数,主要内容包括如下内容:
本节目标:理解自定义函数的语法结构及函数体的内容
自定义函数:使用myslq的过程中,mysql自带的函数可能不能完成我们的业务需求,这时就需要自定义函数。
函数(存储函数):
1、需要有返回值;
2、可以指定0~n个参数;
创建自定义函数:
语法格式︰
CREATE FUNCTION function_name([func_parameter])
RETURNS type
[characteristics...] routine_bodyCharacteristics指定存储函数的特性,取值举例︰
SQL SECURITY{DEFINER| INVOKER}:指明谁有权限来执行。
DEFINER表示只有定义者才能执行。
INVOKER表示拥有权限的调用者才可以执行,默认情况下,系统指定为DEFINER。
COMMENT 'string':注释信息,可以用来描述存储函数。
函数体:
函数体是由SQL代码构成;
函数体可以是简单的SQL语句,比如:简单的查询语句;
函数体如果为复合结构需要使用BEGIN…END语句
复合结构可以包含声明、流程控制。
示例:
select length('hello'); select date_format(pubdate,'%Y-%m') from bookinfo; -- 创建自定义函数 delimiter // create function ym_date(mydate date) returns varchar(15) begin return date_format(mydate,'%Y-%m'); end// delimiter ; -- 使用(调用)自定义函数 select ym_date(pubdate) from bookinfo;
本节目标:掌握函数的创建、使用及删除的操作。
创建自定义函数:
CREATE FUNCTION function_name([func_parameter])
RETURNS type
[characteristics...] routine_body
创建无参的自定义函数
举例︰
1、创建一个返回系统日期的函数,日期格式为'xxxx年xx月xx日';
create function newdate() returns varchar(20) return date_format(curdate(),'%Y年%m月%d日'); select newdate();
2、创建一个函数,根据图书ID,返回书名和库存的字符串信息。形式是'书名---库存’;
create function show_name_store(bid int) returns varchar(50) return (select concat_ws('----',book_name,store) from bookinfo where book_id = bid); select show_name_store(20150201);
3、删除自定义函数
删除存储过程和函数,可以使用DROP语句,其语法结构如下︰
DROP FUNCTION[IF EXISTS] func_name;
drop function newdate; drop function if exists ym_date;
本节目标:
掌握变量的声明、赋值的操作。
掌握条件判断语句IF语句的使用。
掌握循环语句的使用。
1、变量
可以在存储程序(存储过程和函数)中使用变量。
在存储程序中变量的作用范围在BEGIN...AND之间。
定义变量
语法格式︰
DECLARE var_name[,varname]...date_type[DEFAULT value];
例︰DECLARE num INT DEFAULT 10;
为变量赋值
定义变量之后,通过赋值可以改变变量的默认值。
两种赋值方式︰
SET var_name = expr [,var_name = expr]...;
SELECT col_name[,...]INTO var_name[,...] table_expr;
举例∶
SET num = 100; SELECT store INTO num FROM bookinfo;
案例:
根据图书ID查询,查询书名和库存。形式是'书名---库存’;
create function show_name_store(bid int) returns varchar(5o) return (select concat_ws('----', book_name, store) from bookinfo where book_id = bid);
声明变量
delimiter // create function show_name_store(bid int) returns varchar(50) begin declare result varchar(50); select concat_ws('----', book_name, store) into result from bookinfo where book_id = bid; return result; end // delimiter ; select show_name_store(20150201);
2、流程控制语句
流程控制语句是用来根据条件控制语句的执行。
常用的流程控制语句∶
IF语句
CASE语句
WHILE循环语句
LOOP循环语句
REPEAT循环语句
1)、IF语句
IF语句包含多个条件判断,根据判断的结果为TRUE或FALSE执行相应的语句。
语法格式如下︰
IF condition THEN
……
[ELSEIF condition THEN]
……
[ELSE]
……
END IF;
IF语句举例
查询读者信息表,根据用户的余额判断用户的会员级别。
要求如下∶
1、根据身份证号查询用户的余额
2、当用户的余额大于等于500时为金牌会员
3、当用户的余额大于等于300时为高级会员
4、当用户的余额大于等于200时为普通会员
5、小于200元,不能借书,显示‘非会员,余额不足’
delimiter // create function show_level(cid char(18)) returns varchar(10) begin declare lev varchar(10); declare money decimal(7,3); select balance into money from readerinfo where card_id = cid; if money>=500 then set lev = '金牌会员'; elseif money>=300 then set lev = '高级会员'; elseif money>=200 then set lev = '普通会员'; else set lev = '非会员,余额不足'; end if; return lev; end// delimiter ; select show_level(card_id),balance from readerinfo;
2)、CASE语句
分支语句(另一个条件判断的语句)
该语句有两种语句格式∶
格式1∶
CASE case_expr
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE
delimiter // create function show_level2(cid char(18)) returns varchar(10) begin declare lev varchar(10); declare money decimal(7,3); declare num int; select balance into money from readerinfo where card_id = cid; set num = truncate(money/100,0); case num when 0 then set lev = '非会员,余额不足'; when 1 then set lev = '非会员,余额不足'; when 2 then set lev = '普通会员'; when 3 then set lev = '高级会员'; when 4 then set lev = '高级会员'; else set lev = '金牌会员'; end case; return lev; end// delimiter ;
格式2∶
CASE
WHEN expr_condition THEN statement_list
[WHEN expr_condition THEN statement_list]...
[ELSE statement_list]
END CASE
delimiter // create function show_level3(cid char(18)) returns varchar(10) begin declare lev varchar(10); declare money decimal(7,3); select balance into money from readerinfo where card_id = cid; case when money>=500 then set lev = '金牌会员'; when money>=300 then set lev = '高级会员'; when money>=200 then set lev = '普通会员'; else set lev = '非会员,余额不足'; end case; return lev; end// delimiter ;
3)、WHILE循环语句
判断循环条件,满足条件执行循环体,否则退出。
语法格式∶
[while_label:]WHILE condition Do
…
END WHILE[while_label]
求N以内数的和
▶while循环语句
delimiter // create function testfunc(n int) returns int begin declare num int default 0; declare sum int default 0; while num
4)、LOOP循环语句
该循环没有内置循环条件,但可以通过leave语句退出循环。
语法格式︰
[loop_label:]LOOP
statement_list
END LOOP[loop_label]
leave语句用来跳出循环,语法格式如下∶
Leave label
▶loop循环语句
delimiter // drop function if exists testfunc// create function testfunc(n int) returns int begin declare num int default 0; declare sum int default 0; lab1:loop set num = num + 1; set sum = sum + num; if num>=n then leave lab1; end if; end loop lab1; return sum; end// delimiter ; select testfunc(20);
5)、REPEAT循环语句
该语句执行一次循环体,之后判断condition条件是否为真,为真则退出循环,否则继续执行循环体。
语法格式︰
[repeat_label:]REPEAT
…
UNTIL expr_condition
END REPEAT[repeat_label]
▶repeat循环语句
delimiter // drop function if exists testfunc// create function testfunc(n int) returns int begin declare num int default 0; declare sum int default 0; repeat set num = num +1; set sum = sum + num; until num>=n end repeat; return sum; end// delimiter ; select testfunc(20);