MySQL基础三：函数和存储过程

6. 内置函数和自定义函数

6.1内置函数

1. 字符函数
2. 数值运算符和函数
3. 比较运算符和函数
4. 日期时间函数
5. 信息函数
6. 聚合函数
7. 加密函数

6.2 自定义函数简介

1. 自定义函数语法格式

CREATE FUNCTION 函数名(参数名 参数类型) # mysql中参数数量不能超过1024个（一般没问题）
RETURNS {返回值类型}
函数体 #（函数体中如果为复合结构要用BEGIN...END语句；复合结构可以包含声明、循环、流程控制等；）

2. 创建不带参数的自定义函数

# 创建无参函数
CREATE FUNCTION f1() 
RETURNS VARCHAR(40)
DETERMINISTIC	# 确定性声明（不然会报错：> 1418-This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in...）
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');  # RETURN表示有返回值

# 函数调用
SELECT f1();

3. 创建带有参数的自定义函数

# 创建带参的函数
CREATE FUNCTION f2(num1 INT,num2 INT) # 两个参数
RETURNS FLOAT(10,2)			# 返回值类型为float
DETERMINISTIC
RETURN (num1+num2)/3;		# 一个返回值

SELECT f2(3,2);

4. 创建具有复合结构函数体的自定义函数
   当其中执行的语句不仅仅是一条的时候需要由begin开始、end结束。begin和end中间放入多条执行语句。返回值如果是查询语句，必须带上括号。

# 创建带参的函数
delimiter //
CREATE FUNCTION fAddUser2(username11 VARCHAR(20),unit11 VARCHAR(20)) 
RETURNS INT
DETERMINISTIC
BEGIN
INSERT test_user(username) VALUES (username11);
INSERT test_unit(unit) VALUES (unit11);
RETURN (SELECT COUNT(id) FROM test_unit);# 注意返回值这里要带括号
END
//
delimiter ;

SELECT fAddUser2('马超','蜀汉');

7. 存储过程

7.1 存储过程简介

优点：
二次及多次调用时的执行速度快（不再需要语法分析和编译）
减少网络流量
不能创建数据表和数据库
不带参数的存储过程小括号可以省略
存储过程中参数的名字不能和数据表字段的名字一样

7.2 创建不带参数的存储过程

略

7.3 创建带有IN类型参数的存储过程

略

7.4 创建带有IN和OUT类型参数的存储过程

# 修改结束符号
delimiter //
# 创建存储过程
CREATE PROCEDURE delUserAndReturnUserNums(IN p_id INT, OUT userNums INT)
BEGIN
DELETE FROM test_user WHERE id = p_id;
SELECT count(id) FROM test_user INTO userNums;# 通过into将数值放到变量中
END //
# 将结束符号修改回来
delimiter ;


# 存储过程的调用与返回值的查看
CALL delUserAndReturnUserNums(10,@nums);	# 用@来定义变量
SELECT @nums;

7.5 创建带有多个OUT类型参数的存储过程

delimiter //	# 修改结束符号 ";" 为 "//"
CREATE PROCEDURE removeUserByAgeAndReturnInfos(	# 创建名为removeUserByAgeAndReturnInfos的存储过程
 IN p_age INT,	# IN输入变量，
 OUT deleteUsers INT,# OUT输出变量1
 OUT userRemain INT# OUT输出变量2
 )
BEGIN	# 过程体开始
DELETE FROM test_user WHERE age = p_age;# 按照年龄删除用户
SELECT ROW_COUNT() INTO deleteUsers;# 相当于php中mysql_affected_rows指的是被影响的行数，利用INTO放入OUT类型的变量1中
SELECT COUNT(id) FROM test_user INTO userRemain;# 将剩余用户的个数利用into放入OUT类型的变量2中
END 	# 过程体结束
//	# 整个存储过程结束
delimiter ;	# 将结束符号修改回来
CALL removeUserByAgeAndReturnInfos(20,@deleteUsers,@userRemain);	# 使用CALL调用存储过程，并用@符号定义变量
SELECT @deleteUsers,@userRemain;# 查看变量的值

总结：存储过程和函数的区别:

1. 存储过程实现的功能要更复杂，而函数的针对性更强
2. 存错过程可以返回多个值，而函数只能有一个值（或者用表或json字符串变相输出多个值的信息）
3. 存储过程一般独立执行，而函数可以作为其它sql语句的组成部分来出现（用法和count()等内置函数用法相同）