FUNCTION 存储函数

1. 存储函数的理解

1. 函数一定会有返回值
2. FUNCTION里总是默认为IN参数（没有存储过程里的OUT INOUT参数），可以不写
3. FUNCTION 可以放到查询语句中使用，PROCEDURE不行。
　　反之，存储过程的功能更加强大，包括能够执行对表的操作(比如创建表，删除表等)和事务操作，
　　这些功能是存储函数不具备的。

2. 创建和调用存储函数

#方法1 无参数有返回值
DELIMITER //

CREATE FUNCTION fun_employee_tel()
RETURNS VARCHAR(25)
　　#相关特性characteristic，必填（处理方式1）
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN
    RETURN (SELECT telephone FROM employee WHERE NAME = 'frank');
END //

DELIMITER;

#调用
SELECT fun_employee_tel();

#方法2 有参数有返回值

#相关特性characteristic （处理方式2）
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //

CREATE FUNCTION fun_employee_gid(empname VARCHAR(25))
RETURNS VARCHAR(36)

BEGIN
    RETURN (SELECT gid FROM employee WHERE NAME = empname);
END //

DELIMITER;

#调用
SELECT fun_employee_gid('frank');

  SET @empname := 'frank';
  SELECT fun_employee_gid(@empname);

#方法3 项目开发经验（注意方法里的 ; 封号 不能缺少的）
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //

CREATE FUNCTION F_GetMergeObnCartonDtSkuPickLoc(id VARCHAR(100), obnseq INT)
RETURNS NVARCHAR(100)

BEGIN
    DECLARE S NVARCHAR(100);
    
    SELECT GROUP_CONCAT(DISTINCT OP.LOC SEPARATOR ',')  INTO S 
    FROM OBN_PICK op
    WHERE op.OBN_ID=id AND OP.OBN_SEQ = obnseq;
    
    RETURN COALESCE(S, '空'); // s为null的情况下，用空来代替
END //

DELIMITER;

3. 存储过程和存储方法的对比

 4. 存储函数和存储过程的查看、修改、删除

#4.1 查看
#方法1 SHOW CREATE 语句查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE pro_employee_salary;
SHOW CREATE FUNCTION fun_employee_gid;

#方法2 SHOW STATUS 语句查询存储过程和函数的状态信息
SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS LIKE 'pro_employee_salary';

SHOW PROCEDURE STATUS LIKE 'pro%';

SHOW FUNCTION STATUS;

SHOW FUNCTION STATUS LIKE 'fun_employee_gid';

SHOW FUNCTION STATUS LIKE 'fun_employee%';

#方法3 从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines 
WHERE routine_name = 'fun_employee_gid' 
AND routine_type = 'FUNCTION'; #FUNCTION 不能小写

#4.2 修改（不能修改存储过程和函数的内容（函数体），只能修改其相关特性characteristic）
# 用ALTER语句修改
#如果确实需要修改存储过程和函数，则可以删除再重新创建的方式实现

#方法1 
#只能修改的相关特性字段有：
#CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
#SQL SECURITY DEFINER | SQL SECURITY INVOKER)
#COMMENT 'string'

#举例：
ALTER PROCEDURE pro_employee_salary
SQL SECURITY INVOKER
COMMENT '查询员工工资';

#4.3 删除 使用DROP 语句删除存储过程/函数
#举例：
DROP PROCEDURE IF EXISTS pro_employee_salary;
DROP FUNCTION IF EXISTS fun_employee_gid;

5. 存储过程和函数的优缺点

#优点
#1. 存储过程可以一次编译多次使用
#2. 可以减少开发工作量
#3. 存储过程的安全性能
#4. 可以减少网络传输量
#5. 良好的封装性

#缺点
#1. 可移植性差
#2. 调试困难
#3. 存储过程的版本管理很困难
#4. 它不适合高并发的场景