mysql_基础—函数 流程控制

函数

#函数
/**
含义：一组预先编译好的sql语句的集合 理解成批处理语句
类似java类 调用时会检查它是否编译 减少了编译次数

区别
存储过程：可以有0个返回 也可以有多个返回、适合批量插入 批量更新
函数：必须有且1个返回 适合做处理数据后返回一个结果

*/

#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
		函数体
END;

/**
设置变量 局部变量 和成员变量 都可以
注意
参数列表 包含两部分 
参数名 参数类型

函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错 但不建议

return 值
3.函数体中仅有一句话 则可以省略 BEGIN END 
4.使用delimiter 语句设置结束标记
*/

#二、调用语法
#执行完函数体 并且返回值 
SELECT 函数名(参数列表)

#---------------------------案例
#1.无参有返回
#案例 返回公司的员工个数

# 报1418错误 
set global log_bin_trust_function_creators=TRUE

DELIMITER $
CREATE FUNCTION emp_num ( ) RETURNS INT 
BEGIN
DECLARE a INT DEFAULT 0;
SELECT
	COUNT( * ) INTO a 
FROM
	employees;
RETURN a;
END $;


SELECT emp_num();
DECIMAL

#2.有参有返回
#案例1 根据员工名 返回他的工资

DELIMITER $
CREATE FUNCTION sa_lary (empName VARCHAR(20)) RETURNS DECIMAL 
BEGIN 
		
	DECLARE a DECIMAL ;
	SELECT
		salary INTO a 
	FROM
		employees 
	WHERE
		last_name = empName;
		RETURN a;
END $;

SELECT sa_lary('Kochhar');


#案例2 根据部门名 返回该部门的平均工资

DELIMITER $
CREATE FUNCTION avg_sa ( deptName VARCHAR(20) ) RETURNS DOUBLE 
BEGIN
DECLARE
	a DOUBLE;
SELECT
	avg( salary ) INTO a 
FROM
	employees es,
	departments de 
WHERE
	de.department_id = es.department_id 
	AND department_name = deptName;
	
RETURN a;

END $;

SELECT avg_sa('Mar');

#三、 查看函数
SHOW CREATE FUNCTION avg_sa

#四、删除函数
DROP FUNCTION tow_flo

#案例
#一、 创建函数 实现传入两个float 返回两者之和
#用组函数的话 创建不会出问题 调用如果组函数出错则调用失败
DELIMITER $
CREATE FUNCTION tow_flo(f1 FLOAT ,f2 FLOAT ) RETURNS FLOAT 
BEGIN 
		set @a = f1+f2;

		RETURN  @a;

END $;

SELECT tow_flo(1.1,4.2);

#2、创建函数，实现传入工种名，返回该工种的员工人数
DELIMITER $
CREATE FUNCTION job(jobName VARCHAR(20)) RETURNS INT 
BEGIN 
	
	DECLARE a int;
	SELECT
		count( * )  INTO a 
	FROM
		employees es,
		jobs jo 
	WHERE
		es.job_id = jo.job_id 
		AND jo.job_id = jobName;
		
		RETURN a;

END $;

SELECT job('IT_PROG');

# 创建函数，实现传入员工名，返回该员工的领导名

DELIMITER $
CREATE FUNCTION manager ( lastName VARCHAR ( 20 ) ) RETURNS VARCHAR ( 20 )
BEGIN
DECLARE a VARCHAR(20);
SELECT
	last_name INTO a 
FROM
	employees 
WHERE
	employee_id = ( SELECT manager_id FROM employees WHERE last_name = lastName ) ;
	
	RETURN a;
	END $;

SELECT manager('Hunold');

流程控制

# 流程控制结构
/**
顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行 [满足一定条件]
循环结构：程序在满足一定条件基础上 重复执行一段代码 [批量更新]
*/

# 一、分支结构
#1.if函数
/*
功能：实现简单的双分支
语法：
IF(表达式1,表达式2,表达式3)
执行的顺序：
如果表达式1成立,则IF函数返回表达式2的值 否则返回表达式3的值

应用：任何地方
*/

# 2.CASE结构 [也可以放到存储过程中]
情况1 ：类似于Java 中的switch语句 一般用于实现的等值判断
语法：
	CASE 要判断的 变量|表达式|字段
	WHEN 要判断的值1 THEN 返回值1|或者语句1
	WHEN 要判断的值2 THEN 返回值2|或者语句2
	.....
	ELSE 返回值n|或者语句n
END CASE;

情况2 ：类似于java中的多重IF语句 一般用于实现区间判断
语法：
CASE 
WHEN 条件1 then 要显示的值1或语句1
WHEN 条件2 then 要显示的值2或语句2
...
ELSE 要显示的值n或语句n
END  CASE ;

特点：
① 可以作为表达式 嵌套在其他语句中使用 可以放在任何地方 BEGIN END 中或 BEGIN END 的外面 
可以作为独立的语句去使用 只能放在 BEGIN END中
② 如果 WHEN 中的值满足条件或成立 则执行对应的 THEN后面的语句 并且结束CASE
如果都不满足 则执行ELSE 中的语句或值

③ ELSE 可以省略 如果else 省略了 并且所有WHEN 条件不满足 则返回null	

#案例
# 创建存储过程 根据传入的成绩 来显示等级 比如传入的成绩：90-100 显示A 80-90 显示B 60-80 显示C 否则 显示D 


	
	DELIMITER $
	CREATE PROCEDURE garder ( IN score INT ) BEGIN
		CASE
				
				WHEN score >= 90 AND score <= 100 THEN SELECT 'A ' ;
				WHEN score >= 80 AND score <= 90 THEN SELECT 'B' ;
				WHEN score >= 60 AND score <= 80 THEN SELECT 'C' ;
				ELSE SELECT 'D' ;
				END CASE;
					
				END $;

CALL garder(90);

# 3.if结构

/**
功能：实现多重分支

语法 ：
if 条件1 then 语句1 

ELSEIF 条件2 then 语句2 
.....
【ELSE  语句n;】 
END IF;

应用在begin END 中
*/

# 案例1 根据传入的成绩 来显示等级 比如传入的成绩：90-100 返回A 80-90 返回B 60-80 返回C 否则 返回D 


CREATE FUNCTION re_garden(score INT  ) RETURNS CHAR
BEGIN
	
	IF score<=90 and  score >=100 THEN RETURN 'A';
	ELSEIF score<=80 and  score >=90 THEN RETURN 'B' ;
	ELSEIF score<=60 and  score >=80 THEN RETURN 'C' ;
	ELSE RETURN 'D' ;
END IF;
END ;

SELECT re_garden(40);

# 二、循环结构
/**
WHILE 、 loop 、repeat 

循环控制

ITERATE 类似于 CONTINUE 继续 结束本次循环 继续下一次
LEAVE 类似于 break 跳出 结束当前所在的循环

*/

#1.WHILE 
/*
语法：
[标签：] while 循环条件 do 
			循环体;
END WHILE[标签];			
*/

#loop 
/*
语法：
[标签：] LOOP 
			循环体;
END LOOP[标签];	

用来模拟简单死循环
*/

#REPEAT 
/*
语法：类似do--WHILE 至少执行一次 
[标签：] REPEAT 
			循环体;
UNTIL 结束循环的条件
END REPEAT [标签];	
*/


# 案例 批量插入 根据次数插入到admin表中多条记录 
DESC admin;
CREATE PROCEDURE WHILE_1(IN count int)
BEGIN 
	DECLARE i INT DEFAULT 1;
	WHILE i<=count DO
			INSERT INTO admin (username,password) VALUES (SUBSTR(RAND(50),9),SUBSTR(RAND(50),10));
			SET i=i+1;
END WHILE;
END;

CALL WHILE_1(5);

#  批量插入 根据次数插入到admin表中多条记录 次数>20 停止

CREATE PROCEDURE WHILE_2(in count INT )
BEGIN 

	DECLARE i INT DEFAULT 1;
	a: WHILE i<=count DO
			INSERT INTO admin (username,password) VALUES (SUBSTR(RAND(50),9),SUBSTR(RAND(50),10));
			
			IF i>=20 THEN LEAVE a ;
			END if;
			SET i=i+1;
END WHILE a ;
END;


CALL WHILE_2(100);

# 批量插入 根据次数插入到admin表中多条记录 只插入偶数次数
DROP PROCEDURE WHILE_3

CREATE PROCEDURE WHILE_3(in count INT )
BEGIN 

	DECLARE i INT DEFAULT -1;
	a: WHILE i<=count DO
			SET i=i+1; 
			IF i%2 !=0 THEN ITERATE a ;
			END if;
		
			INSERT INTO admin (username,password) VALUES (CONCAT('小花',i),SUBSTR(RAND(50),10));
			
			
END WHILE a ;
END;

CALL WHILE_3(100);

TRUNCATE admin;

**mysql基础结束了 哈哈哈哈 **