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
);