知识库

Appearance

sql
CREATE FUNCTION 函数名(参数名 参数类型, ...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
  # 函数体中肯定有 RETURN 语句
  函数体 
END

1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参 数。 2、RETURNS type 语句表示函数返回数据的类型； RETURNS子句只能对FUNCTION做指定，对函数而言这是 强制 的。它用来指定函数的返回类型，而且函 数体必须包含一个 RETURN value 语句。 3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。 4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END。

sql
# 创建存储函数 email_by_name，参数定义为空，查询 Abel 的 email 并返回，数据类型为
# 字符串。
DELIMITER $
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(20)
BEGIN
  RETURN (
    SELECT email
    FROM emps
    WHERE last_name = 'Abel'
  );
END $
DELIMITER ;

# 报错
ERROR 1418 (HY000): 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)

# 解决方式1：加上必要的函数特性 “[NOT] DETERMINISTIC” 和 
“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
DELIMITER $
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(20)
        DETERMINISTIC
        CONTAINS SQL
        READS SQL DATA
BEGIN
  RETURN (
    SELECT email
    FROM emps
    WHERE last_name = 'Abel'
  );
END $
DELIMITER ;

## 解决方式2
SET GLOBAL log_bin_trust_function_creators = 1;

SELECT email_by_name();

# 创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回
# 数据类型为字符串。
DELIMITER $
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(20)
BEGIN
  RETURN (
    SELECT email
    FROM emps
    WHERE employee_id = emp_id
  );
END $
DELIMITER ;

SELECT email_by_id(100);
# 或
SET @emp_id = 102
SELECT email_by_id(@emp_id);
    关键字      调用语法          返回值              应用场景

存储过程 PROCEDURE CALL 存储过程() 理解为有0个或多个 一般用于更新 存储函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时

存储函数可以放在查询语句中使用，存储过程不行。存储过程的功能更加强大，包括能够 执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

查看存储过程和存储函数 ​

  • SHOW CREATE 查看创建信息

    SHOW CRREATE FUNCTION 存储函数名; SHOW CRREATE PROCEDURE 存储过程名;

  • SHOW STATUS 查看状态信息

    SHOW FUNCTION STATUS; SHOW PROCEDURE STATUS;

    SHOW FUNCTION STATUS LIKE 'show_mgr_name'; SHOW PROCEDURE STATUS LIKE 'show_mgr_name';

  • information_schema.Routines 表中查看

    sql
    SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = 存储函数名或存储过程名
# 可选，当存储过程和存储函数重名时使用
AND ROUTINE_TYPE = PROCEDURE 或 FUNCTION

修改存储过程和存储函数 ​

修改存储过程或存储函数，不影响功能，只是修改相关特性。

sql
ALTER PROCEDURE 存储过程或函数的名 
# 四个选一个
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
# 两个选一个
SQL SECURITY DEFINER | SQL SECURITY INVOKER
COMMENT 'string'

CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。 NO SQL ，表示子程序中不包含SQL语句。 READS SQL DATA ，表示子程序中包含读数据的语句。 MODIFIES SQL DATA ，表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。 DEFINER ，表示只有定义者自己才能够执行。 INVOKER ，表示调用者可以执行。

COMMENT 'string' ，表示注释信息

sql
ALTER PROCEDURE show_max_sal
SQL SECURITY INVOKER
COMMENT '查询最高工资'

删除存储过程和存储函数 ​

sql
DROP PROCEDURE [IF EXISTS] 存储过程名或存储函数名

存储过程/存储函数的优点 ​

1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译， 这就提升了 SQL 的执行效率。 2、可以减少开发工作量。将代码 封装 成模块，实际上是编程的核心思想之一，这样可以把复杂的问题 拆解成不同的模块，然后模块之间可以 重复使用 ，在减少开发工作量的同时，还能保证代码的结构清 晰。 3、存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限 ，这样就和视图一样具 有较强的安全性。 4、可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减 少了网络传输量。 5、良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接 多次数据库才能完成的操作，现在变成了一次存储过程，只需要 连接一次即可 。

存储过程/存储函数的确定 ​

1、可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过 程，在换成其他数据库时都需要重新编写。 2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容 易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。 3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发 软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。 4、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方 式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就 不适用了。