函数与存储过程有什么区别
函数： 一组预先编译号的sql语句的集合，理解成批处理语句
- 提高代码重用性
- 简化操作
- 减少编译次数和数据库服务器的连接次数，提高效率
区别：
存储过程：可以有0个返回，也可以有多个返回 ，（批量插入，批量更新）
函数：有且仅有一个返回 （适合做处理数据后返回一个结果）
- 创建语法：
- create function 函数名（参数列表） returns 返回类型
- begin
- 函数体
- end
- 函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不会报错，但不建议，建议return 值放到最后
- 函数体中仅有一句话的时候，则可以省略begin end
- 使用delimiter 语句设置结束标记
二 调用语法
select 函数名（参数列表）
代码演示
-
delimiter $$
create function myf2()
returns int
no sql
begin
declare c int default 0;
select count(*) into c from employees;
return c;
end $$
delimiter ;
select myf2()$ -
drop function myf3; 删除函数
- 查看函数 show create function 函数名
show create function myf3;
-
delimiter $$
create function myf3(empName varchar(20))
returns double
reads sql data
begin
set @sal = 0; # 定义一个用户变量
select salary into @sal
from employees
where last_name = empName;
return @sal;
end $$
delimiter ;
select myf3('Kochhar')$;
- 8.0以上mysql 创建函数错误
- [Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
- [Err] 1418-此函数中没有声明DETERMINISTIC，NO SQL或READS SQL DATA，只用声明才会启用二进制日志记录（您可能想使用不太安全的log_bin_trust_function_creators变量）
- 解决办法
- 第一种办法，需要声明DETERMINISTIC，NO SQL或READS SQL DATA的一种
- 第二种办法，信任子程序的创建者，设置变量log_bin_trust_function_creators值为1
- -- 查看该参数，默认为0
- select @@log_bin_trust_function_creators;
- -- 设置为1
- set GLOBAL log_bin_trust_function_creators=1;
- 使用样例
- 函数声明NO SQL
- -- mysql中创建一个自定义函数
- delimiter $$
- create function fun_addnum()
- -- 注意是returns而不是return
- returns int
- NO SQL
- begin
- set @i=0;
- add_num:LOOP
- set @i=@i+1;
- -- leave用于跳出循环
- if @i=10 then leave add_num;
- -- iterate跳过后面语句进入下一循环
- elseif mod(@i,2)=0 then iterate add_num;
- end if;
- -- 结束循环
- end loop add_num;
- return @i;
- end $$
- delimiter ;
- select fun_addnum();
- 函数声明DETERMINISTIC，表示该函数在每次为其参数调用相同值时都返回相同的结果值
- delimiter $$
- create function fun_hello(x int)
- returns int
- DETERMINISTIC
- comment '这是注释'
- begin
- declare i int default 1;
- declare j int default 10;
- case x
- when i then set x=i;
- when j then set x=j;
- else set x=i+j;
- end CASE;
- return x;
- end $$
- delimiter ;
- select fun_hello(2);
- 设置变量log_bin_trust_function_creators的方式
- -- 设置为1，此时才可以使用 CONTAINS SQL，MODIFIES SQL DATA
- set GLOBAL log_bin_trust_function_creators=1;
- delimiter $$
- create function fun_temp()
- returns int
- -- READS SQL DATA
- MODIFIES SQL DATA
- begin
- declare cnt int default 0;
- repeat
- set cnt=cnt+1;
- until cnt=10 end repeat;
- return cnt;
- end $$
- delimiter ;
流程控制结构
顺序结构：程序从上往下依次执行
分支结构: 程序从两条或多条路径中选择一条去执行
循环结构：满足一定条件基础上。重复执行一段代码
分支结构：
if函数： 实现简单的双分支
select if（B1，B2，B3）
如果B1成立，则if函数返回B2的值，否则返回B3的值 任何地方
- case 结构： 相当于switch 语句 一般实现等值判断
-
语法:
case 变量|表达式|字段
when 判断的值 then 返回值 |语句
when 判断的值 then 返回值 |语句
when 判断的值 then 返回值 |语句
else 要返会的值 n |语句
end
- 类似于java中多重if，区间判断
-
语法:
case
when 条件 then 返回值 |语句
when 条件 then 返回值 |语句
when 条件 then 返回值 |语句
else 要返会的值 n |语句
end
- 特点：
- 可以作为表达式嵌套再其他语句中使用，也可以放在任何地方，begin end中或外面
- 也可以作为独立的语句去使用，只能放在begin end中
- 实例：
-
delimiter $
create procedure testcase(in sal int)
begin
case
when sal>=90 then select 'A';
when sal>=80 then select 'B';
when sal>=70 then select 'C';
else select 'D';
end case;
end $
call testcase(82)$
-
- if结构，实现多重分支
- 语法：
- if 条件1 then 语句1
- else if 条件 then 语句2
- else 语句 n
- end if
- i应用在begin end中
- 实例：
-
delimiter $$
create function testif(socre int)
returns char
no sql
begin
if socre>=90 and socre <=100 then return 'A';
elseif socre>=80 then return 'B';
elseif socre>=70 then return 'C';
else return 'E';
end if;
end $$
delimiter ;
select testif(89)$;
-
- 语法：
- 循环结构
- 分类
- while
- loop
- repeat
- 循环控制
- iterate 类似于continue ，继续，结束本次循环，继续下一次
- leave 类似于break，跳出，结束循环
- while语法：
- while 条件 do
- 循环体；
- end while 标签；
- 实例：
-
# 批量插入
delimiter $
create procedure pro_while1(in insertCount int)
begin
declare i int default 1;
a:while i<=insertCount do
insert into db01.tab1(id, age) values(i,i*10);
set i = i + 1;
end while a ;
end $
call pro_while1(7)$;
select * from tab1; -
delimiter $
create procedure pro_while2(in insertCount int)
begin
declare i int default 1;
a:while i<insertCount do
insert into tab1(id, age) values(i,i*10);
if i>=20 then leave a;
end if;
set i = i+1;
end while a;
end $
call pro_while2(21);
select * from tab1; -
delimiter $
create procedure pro_while3(in insertCount int)
begin
declare i int default 0;
a:while i<insertCount do
set i = i+1;
if mod(i,2)!=0 then iterate a;
end if;
insert into tab1(id, age) values(i,i*10);
end while a;
end $
call pro_while3(30);
select * from tab1;
-
- loop语法
- loop
- 循环体
- end loop 标签； 用来模拟死循环
- repeat语法：
- 标签 repeat
- 循环体
- until 结束循环条件
- end repeat 标签；
- 分类
- 总结：
-
-