1. 函数
-
函数与存储过程一样，也是一组预先编译好的SQL语句的集合，理解为批处理语句。将实现某个功能的一段代码封装起来，只暴露名字，隐藏具体实现过程。
-
函数与存储过程的区别
- 存储过程：可以有0个返回值，也可以有多个返回值，适合做批量插入、批量更新
- 函数：有且仅有1个返回值，适合做处理数据后返回一个结果
-
下面例子中可能使用的数据表
-
employees 员工表
-
departments 部门表
-
jobs 工种表
-
locations 地点表
员工表分别通过部门id、工种id与部门表、工种表关联，部门表通过地点id与地点表关联。
-
1.1. 函数的创建与调用
-
创建语法
create function 函数名(参数列表) returns 返回类型 begin 函数体 end注意：
-
参数列表包含两部分：参数名 参数类型
-
函数体：肯定会有return语句，如果没有会报错；如果return语句没有放在函数体最后也不报错，但不建议。
-
函数体中仅有一句话，则可以省略begin end
-
使用delimiter语句设置结束标记
-
-
调用语法
select 函数名(参数列表);
1.2. 示例
-
无参数有返回
#创建函数，返回员工个数 delimiter $ #设置结束标记为$ create function myf1() returns int begin declare c int default 0; #定义局部变量 select count(*) into c #赋值 from employees; return c; end $ #调用函数 select myf1()$ -
有参数有返回
#根据员工名，返回工资 delimiter $ create function myf2(name varchar(20)) returns double begin set @sal=0; #定义用户变量 select salary into @sal from employees where last_name=name; return @sal; end $ #调用 select myf2('kochhar')$
1.3. 函数的查看与删除
-
查看函数的语法
show create function 函数名; -
删除函数的语法
drop function 函数名;
2. 流程控制结构
- 顺序结构：程序从上往下依次执行
- 分支结构：程序从两条或多条路径中选择一条去执行
- 循环结构：程序在满足一定条件的基础上，重复执行一段代码
2.1. 分支结构
-
if函数（可以实现简单的双分支）
if(表达式1,表达式2,表达式3)如果表达式1成立，则返回表达式2的值，否则返回表达式3的值。可以应用在任何地方。
-
case结构
-
情况1：一般用于实现等值判断
case 变量|表达式|字段 when 要判断的值 then 返回值1或语句1; when 要判断的值 then 返回值2或语句2; ... else 返回值n或语句n; #此句可省略 end case; -
情况2：一般用于实现区间判断
case when 要判断的条件1 then 返回值1或语句1; when 要判断的条件2 then 返回值2或语句2; ... else 返回值n或语句n; #此句可省略 end case; -
可以作为表达式，嵌套在其他语句中使用（此时只能返回值，且不加分号，end后也不加case和分号），可以放在任何地方，begin end中或begin end外面；也可以作为独立的语句去使用（此时返回语句且加分号，end后需加case和分号），只能放在begin end中。
-
如果when中的值或条件成立，则执行对应的then后面的语句，并且结束case；如果都不满足，则执行else中的语句或值；若else省略，并且所有when条件都不满足，则返回null。
-
示例
#创建存储过程，根据传入的成绩，显示等级，如成绩90-100为A，80-90为B，60-80为C，否则为D delimiter $ create procedure test_case(in score int) begin case when score>90 and score<=100 then select 'A'; when score>80 then select 'B'; when score>60 then select 'C'; else select 'D'; end case; end $ #调用存储过程 call test_case(75)$
-
-
if结构（可以实现多重分支）
-
语法
if 条件1 then 语句1; elseif 条件2 then 语句2; ... else 语句n; #此句可省略 end if;应用在begin end中
-
示例
#创建函数，根据传入的成绩，返回等级，如成绩90-100为A，80-90为B，60-80为C，否则为D create function test_if(score int) returns varchar(20) begin declare grade varchar(20); if score>90 and score<=100 then select 'A' into grade; elseif score>80 then select 'B' into grade; elseif score>60 then select 'C' into grade; else select 'D' into grade; end if; return grade; end $ #调用函数 select test_if(75)$
-
2.2. 循环结构
- 分类
- while
- loop
- repeat
- 循环控制
- iterate：类似于continue，继续，结束本次循环，继续下一次
- leave：类似于break，跳出，结束当前所在循环
- 放在begin end中
2.2.1. while循环
-
语法
【标签:】while 循环条件 do 循环体; end while 【标签】;中括号【】中内容是可选的，若要加循环控制语句，需要加标签，给循环起名字。
-
示例
#批量插入，根据次数插入到admin表中多条记录，admin表包含username和password两个字段 create procedure test_while1(in insertcount int) begin declare i int default 1; while i<=insertcount do insert into admin(username,password) values(concat('rose',i),'666'); set i=i+1; end while; end $ #调用 call test_while1(100)$#添加leave语句。批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止 create procedure test_while2(in insertcount int) begin declare i int default 1; a:while i<=insertcount do insert into admin(username,password) values(concat('rose',i),'666'); if i>=20 then leave a; end if; set i=i+1; end while a; end $ #调用 call test_while2(100)$#添加iterate语句。批量插入，根据次数插入到admin表中多条记录，只插入偶数次 create procedure test_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 admin(username,password) values(concat('rose',i),'666'); end while a; end $ #调用 call test_while3(100)$
2.2.2. loop循环
-
语法
【标签:】loop 循环体; end loop 【标签】;可以用来模拟简单的死循环，若要结束循环，必须搭配leave使用。
2.2.3. repeat循环
-
语法
【标签:】repeat 循环体; until 结束循环的条件 end repeat 【标签】;