mysql学习之旅（十）——mysql的自定义函数

mysql的自定义函数

自定义函数的介绍

使用mysql的过程中，mysql自带的函数可能不能完成我们的业务需求，这时就需要自定义函数。

函数（存储函数）：
1、需要有返回值
2、可以指定0~n个参数

创建自定义函数

语法格式：
CREATE FUNCTION fuction_name([func_parameter])
RETURNS type
[characteristics…] routine_body
characteristics指定存储函数的特性，取值举例：
SQL SECURITY{DEFINER|INVOKER}:指明谁有权限来执行。
DEFINER表示只有定义者才能执行
INVOKER表示拥有权限的调用者才能执行，默认情况下啊，系统指定为DEFINER。
COMMENT’string’:注释信息，可以用来描述存储函数。

函数体

函数体是由SQL代码构成；函数体可以是简单的SQL语句比如：简单的查询语句
函数体如果为复合结构需要使用BEGIN…END语句（单条语句可以省略，多条语句必须加上begin…end）
复合结构可以包含声明、流程控制。
eg：
CREATE FUNCTION ym_date(mydate DATE)
RETURNS VARCHAR(15)
DETERMINISTIC
RETURN DATE_FORMAT(mydate,’%Y-%m’);

注：
{在例子中如果没有声明DETERMINISTIC时会出现错误1418：
MySQL 出现错误1418 的原因分析及解决方法
使用mysql创建、调用存储过程，函数以及触发器的时候会有错误符号为1418错误。
因为CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,ALTER FUNCTION,CALL, DROP PROCEDURE, DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况。

为了解决这个问题，MySQL强制要求：

在主服务器上，除非子程序被声明为确定性的或者不更改数据，否则创建或者替换子程序将被拒绝。这意味着当创建一个子程序的时候，必须要么声明它是确定性的，要么它不改变数据。

解决方案一：
声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个，
DETERMINISTIC声明一个子程序是确定性的。
CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL用来指出子程序是读还是写数据的。

解决方案二：
信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:
1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
2.MySQL启动时，加上–log-bin-trust-function-creators选贤，参数设置为1
3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1
我采用了第一种方案。}

如果函数体中语句多的话，需用begin…end ，格式如下：
DELIMITER //
CREATE FUNCTION ym_date(mydate DATE)
RETURNS VARCHAR(15)
DETERMINISTIC
BEGIN
RETURN DATE_FORMAT(mydate,’%Y-%m’);
END//
DELIMITER;（能建立成功，但又格式错误，暂时没找到原因，delimiter的错误）

创建和使用自定义函数

创建自定义函数

一、
创建无参的自定义函数
举例：创建一个返回系统日期的函数，日期格式为‘xxxx年xx月xx日’；
CREATE FUNCTION newdate()
RETURNS VARCHAR(20)
DETERMINISTIC
RETURN DATE_FORMAT(CURDATE(),’%Y年%m月%d日’);

SELECT newdate();

二、
创建有参的自定义函数：
CREATE FUNCTION show_name_store(bid INT)
RETURNS VARCHAR(50)
DETERMINISTIC
RETURN (SELECT CONCAT_WS(’----’,book_name,store)FROM bookinfo WHERE book_id=bid);

SELECT show_name_store(20190207);

三、
删除自定义函数：
删除存储过程和函数，可以使用DROP语句，其语法结构如下：
DROP FUNCTION [IF EXISTS] func_name;
eg:
DROP FUNCTION newdate;
DROP FUNCTION IF EXISTS newdate;

流程控制的使用

掌握变量的声明、赋值的操作

变量：
可以在存储程序（存储过程和函数）中使用变量。
在存储程序中变量的作用范围在BEGIN…AND之间。
掌握条件判断语句if语句的使用。
1、定义变量：DECLARE var_name[,varname]…date_type[DEFAULT value];
例：
DECLARE num INT DEFAULT 10;
2、为变量赋值
定义变量之后，通过赋值可以改变变量的默认值。
两种赋值方式：
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查询，查询书名和库存。形式是‘书名–库存’；
DELIMITER //
CREATE FUNCTION show_name_store(bid INT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE result VARCHAR(50);
SELECT CONCAT_WS(’----’,book_name,store) INTO result FROM bookinfo WHERE book_id=bid;
RETURN result;
END //
DELIMITER;

流程控制语句

流程控制语句是用来根据条件控制的执行。常用的流程控制语句：
IF 语句
CASE 语句
WHILE语句
LOOP 语句
REPEAT 循环语句

一、IF 语句
IF 语句包含多个条件判断，根据判断的结果为TURE或FLASE执行相应的语句。
语法格式如下：
IF condition THEN
…
[ELSEIF condition THEN]
…
[ELSE]
…
END IF;
IF 语句举例：
DELIMITER //
CREATE FUNCTION show_level(cid CHAR(18))
RETURNS VARCHAR(10)
DETERMINISTIC
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;
二、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)
DETERMINISTIC
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)
DETERMINISTIC
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;
三、WHILE循环语句
判断循环条件，满足条件执行循环体，否则退出。
语法格式：
[while_label:]WHILE condition DO
…
END WHILE[while_label]
例：
求n以内的和
DELIMITER //
CREATE FUNCTION testfunc(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE num INT DEFAULT 0;
DECLARE SUM INT DEFAULT 0;
WHILE num<n DO
SET num=num+1;
SET SUM=SUM+num;
END WHILE;
RETURN SUM;
END //
DELIMITER;

四、LOOP循环语句
该循环没有内置循环条件，但可以通过leave语句退出循环。
语法格式：
[loop_label:]LOOP
statement_list
END LOOP[loop_label]

leave语句用来跳出循环，语法格式如下：
Leave lable

例：
求n以内的和
DELIMITER //
DROP FUNCTION IF EXISTS testfunc//
CREATE FUNCTION testfunc(n INT)
RETURNS INT
DETERMINISTIC
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;

五、REPEAT循环语句
该语句执行一次循环体，之后判断condition条件是否为真，为真则退出循环，否则继续执行循环体。
语法格式：
[repeat_label:]REPEAT
…
UMTIL expr_condition
END REPAET [repeat_label]

例子：
求n以内的和
DELIMITER //
DROP FUNCTION IF EXISTS testfunc//
CREATE FUNCTION testfunc(n INT)
RETURNS INT
DETERMINISTIC
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;