06-mysql自定义函数

mysql自定义函数

1.查看是否打开创建函数功能

show variables like '%func%';
-------------------------------------------
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------

若Value=OFF则需要开启，命令如下

SET GLOBAL log_bin_trust_function_creators = 1;

自定义函数的概念

1.概念：自定义函数是对sql功能的拓展其语法与函数相同

2.必备条件：参数不一定要有，返回值必须要有

3.包含 :sql语句、声明、循环、结构控制等

自定义函数的语法

创建函数

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

​ 还可以有一些特别的选项，特别的选项写在return 之后，begin之前，如：

• comment:一个关于函数的描述

• 还有一些比如sql security等选项，有兴趣可以自行百度。这里不讲解，仅一提有此知识点。

举例

复制代码-- 最简单的仅有一条sql的函数
create function myselect2() returns int return 666;
select myselect2(); -- 调用函数

--
create function myselect3() returns int
begin 
    declare c int;
    select id from class where cname="python" into c;
    return c;
end;
select myselect3();
-- 带传参的函数
create function myselect5(name varchar(15)) returns int
begin 
    declare c int;
    select id from class where cname=name into c;
    return c;
end;
select myselect5("python");

函数的调用

• 直接使用函数名()就可以调用【虽然这么说，但返回的是一个结果，sql中不使用select的话任何结果都无法显示出来（所以单纯调用会报错），】
• 如果想要传入参数可以使用函数名(参数)
• 调用方式【下面调用的函数都是上面中创建的。】：

Select function_name(参数列表);
-- 无参调用
select myselect3();
-- 传参调用
select myselect5("python");
select * from class where id=myselect5("python");

函数的查看

查看函数创建语句：
show create function 函数名;
查看所有函数：
show function status [like 'pattern'];

函数的修改

函数的修改只能修改一些如comment的选项，不能修改内部的sql语句和参数列表。
alter function 函数名 选项；

函数的删除

drop function 函数名;
drop function if exists function_name参数列表);  ——推荐，删除时判断是否存在

变量的使用

局部变量的声明：
	DECLARE 变量1[,变量2,... ] 变量类型  [DEFAULT 默认值]
为变量赋值的两种方式：
	1）、SET parameter_name = value；
	2）、SELECT INTO parameter_name；——可以跟sql的select复用。
例如：
	将a表的值查出来给变量param——SELECT a.value INTO param FROM table a WHERE a.xx  = xx。	
	用户变量定义（可理解为全局变量,作用域为当前用户的客户端）:SET @param_name = value 

结构控制语法

1、IF 条件语句

IF cj > 90 THEN 
    SET pf = '优秀' 
ELSEIF cj > 60 THEN 
    SET pf = '良好' THEN
ELSE SET pf = '不及格'
END IF

2、CASE 条件语句

CASE 
WHEN cj >= 60 THEN SET pf = '及格'; 
ELSE SET pf = '不及格'; 
END CASE ; 

3、LEAVE 终止语句：LEAVE label ——跳出循环 类似java中的break

​ ITERATE 终止语句：ITERATE label ——结束此次循环，开始下次循环 类似java中的continue

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
END LOOP add_num ; 

4、WHILE 循环语句：

WHILE sum<100 DO
SET sum = sum + 1;
END WHILE

if else

现在我们来使用 定义一个阶乘的 mysql 函数

使用到 if else，以及 while函数

，此处需要注意，set 值的后面得加 结尾号，end if ，以及 end while 后面都要加结尾符号，这里得特别注意，不然各种报错，

我刚才写的时候就一直报错

CREATE FUNCTION jic(a INT) RETURNS INT
BEGIN
	SET @i = 1;
	SET @result = 1;
	IF a = 0 THEN
		RETURN 0;
	ELSEIF a = 1 THEN
		RETURN 1;
	ELSE
		WHILE @i <= a DO
			SET @result = @result * @i;
			SET @i = @i +1;
		END WHILE;
		RETURN @result;
	END IF;
END

if 里面的多条件 and or

CREATE FUNCTION ifelse(a int) RETURNS VARCHAR(20)
BEGIN
	if a>10 and a < 20 THEN
		return "大于10小于20";
	ELSEIF a<10 or a>20 THEN
		return "小于10或者大于20";
	END IF;
END

四、虚拟表的创建

建立虚拟表的优点：

省空间——客户端退出时，虚拟表（临时表）自动drop

私密性——客户端建立临时表只为特定的事务服务，这个表具有专用和私有性，不需要共享给其他人

高效性——客户端建立的临时表具有单独操作和读写性能，因此速度上和处理效率上更高

DROP TEMPORARY TABLE IF EXISTS temp_virtualTable;
CREATE TEMPORARY TABLE temp_virtualTable (
	id int 
);