存储过程与函数

mysql从5.0版本开始支持存储过程和函数。这些功能可以将复杂的sql逻辑封装起来，使得应用程序无需关注存储过程和函数内部的复杂sql逻辑，只需简单地调用它们即可。

存储过程概述

存储过程的英文是Stored Procedure。其核心思想是将一组预先编译的SQL语句封装起来。执行存储过程时，客户端只需向服务器端发送调用命令，服务器端便会执行这些预先存储的SQL语句。

存储过程的好处

1. 简化操作：提高了SQL语句的重用性，减轻了开发人员的压力。
2. 减少操作失误：提高了操作效率。
3. 减少网络传输量：客户端无需通过网络发送所有SQL语句。
4. 提高数据查询的安全性：减少了SQL语句在网络上的暴露风险。

与视图、函数的对比

存储过程与视图一样，具有清晰、安全和减少网络传输量的优点。但与视图不同的是，视图是虚拟表，通常不直接操作底层数据表，而存储过程是程序化的SQL，可以直接操作底层数据表，适合更复杂的数据处理。存储过程的使用类似于函数，但存储过程没有返回值。

存储过程的分类

根据参数类型，存储过程可以分为以下几类：

1. 无参数无返回
2. 有参数无返回（仅IN类型）
3. 无参数有返回（仅OUT类型）
4. 有参数有返回（IN和OUT类型）
5. 有参数有返回（INOUT类型）

注意：一个存储过程中可以包含多个IN、OUT、INOUT类型的参数。

创建存储过程

创建存储过程的基本语法如下：

CREATE PROCEDURE 存储过程名 ( IN | OUT | INOUT 参数名 参数类型 , . . . )[ characteristics . . . ]BEGIN 存储过程体END

说明：

1. 参数类型：
   • IN：输入参数，存储过程读取其值，默认类型。
   • OUT：输出参数，执行后客户端或应用程序可以读取其返回值。
   • INOUT：既可以作为输入参数，也可以作为输出参数。
2. 参数类型：可以是MySQL数据库中的任意类型。
3. characteristics：创建存储过程时的约束条件，包括：
   • LANGUAGE SQL：表示存储过程执行体由SQL语句组成。
   • [NOT] DETERMINISTIC：指明存储过程执行结果是否确定。
   • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。
   • SQL SECURITY { DEFINER | INVOKER }：指明执行当前存储过程的权限。

存储过程体

存储过程体可以包含多条SQL语句。如果只有一条SQL语句，可以省略BEGIN和END。存储过程体中常用的语句包括：

1. BEGIN...END：包含多个语句，每个语句以分号（;）结束。
2. DECLARE：声明变量，需在BEGIN...END语句中使用，且在其他语句之前声明。
3. SET：对变量进行赋值。
4. SELECT... INTO：将查询结果存放到变量中。

设置新的结束标记

由于MySQL默认的语句结束符号为分号（;），为了避免与存储过程中的SQL语句结束符冲突，需要使用DELIMITER命令改变存储过程的结束符。例如：

DELIMITER 新的结束标记

例如，“DELIMITER //”将MySQL的结束符设置为//，并以“END //”结束存储过程。定义完存储过程后，使用“DELIMITER ;”恢复默认结束符。应避免使用反斜杠（\）作为结束符，因为它是MySQL的转义字符。

代码举例

举例1：创建存储过程select_all_data()，查看emps表的所有数据。

DELIMITER $CREATE PROCEDURE select_all_data ( )BEGIN SELECT * FROM emps ; END $DELIMITER ;

举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资。

DELIMITER //CREATE PROCEDURE avg_employee_salary ( )BEGIN SELECT AVG ( salary) AS avg_salary FROM emps ;END //DELIMITER ;

举例3：创建存储过程show_max_salary()，查看emps表的最高薪资值。

CREATE PROCEDURE show_max_salary ( ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '查看最高薪资 ' BEGIN SELECT MAX ( salary) FROM emps ; END //DELIMITER ;

举例4：创建存储过程show_min_salary()，查看emps表的最低薪资值，并通过OUT参数“ms”输出。

DELIMITER //CREATE PROCEDURE show_min_salary ( OUT ms DOUBLE ) BEGIN SELECT MIN ( salary) INTO ms FROM emps ; END //DELIMITER ;

举例5：创建存储过程show_someone_salary()，查看emps表的某个员工的薪资，并用IN参数empname输入员工姓名。

DELIMITER //CREATE PROCEDURE show_someone_salary ( IN empname VARCHAR ( 20 ) ) BEGIN SELECT salary FROM emps WHERE ename = empname ; END //DELIMITER ;

举例6：创建存储过程show_someone_salary2()，查看emps表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER //CREATE PROCEDURE show_someone_salary2 ( IN empname VARCHAR ( 20 ), OUT empsalary DOUBLE ) BEGIN SELECT salary INTO empsalary FROM emps WHERE ename = empname ; END //DELIMITER ;

调用存储过程

存储过程的调用使用CALL语句，并且存储过程与数据库相关。如果要执行其他数据库中的存储过程，需要指定数据库名称。例如：

CALL 存储过程名(实参列表)

调用方式：

1. 调用IN模式的参数：

CALL sp1('值');

2. 调用OUT模式的参数：

SET @name;CALL sp1(@name);SELECT @name;

3. 调用INOUT模式的参数：

SET @name=值;CALL sp1(@name);SELECT @name;

代码举例

DELIMITER //CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)BEGINSELECT COUNT(*) INTO num FROM fruitsWHERE s_id = sid;END //DELIMITER 

调试存储过程

在MySQL中，存储过程没有专门的集成开发环境。可以通过SELECT语句查询程序执行的中间结果来调试SQL语句的正确性。调试成功后，将SELECT语句移到下一个SQL语句之后，逐步推进，完成对存储过程中所有操作的调试。也可以将存储过程中的SQL语句复制出来，逐段单独调试。

存储函数

MySQL支持自定义函数，定义好后，调用方式与调用MySQL预定义的系统函数一样。

知我AI

一款多端AI知识助理，通过一键生成播客/视频/文档/网页文章摘要、思维导图，提高个人知识获取效率；自动存储知识，通过与知识库聊天，提高知识利用效率。

26

查看详情

创建存储函数

创建存储函数的基本语法如下：

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

说明：

1. 参数列表：在函数中，参数总是默认为IN类型。
2. RETURNS type：表示函数返回数据的类型，函数体必须包含一个RETURN value语句。
3. characteristic：创建函数时的约束条件，与创建存储过程时相同。
4. 函数体：可以用BEGIN...END表示SQL代码的开始和结束。如果函数体只有一条语句，可以省略BEGIN...END。

注意：若在创建存储函数时报错“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}”。
2. 设置全局变量：

SET GLOBAL log_bin_trust_function_creators = 1

调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数相同。存储函数是用户自己定义的，而内部函数是MySQL开发者定义的。

SELECT 函数名(实参列表)

代码举例

举例1：创建存储函数email_by_name()，查询Abel的email并返回，数据类型为字符串型。

SET GLOBAL log_bin_trust_function_creators = 1DELIMITER //CREATE FUNCTION email_by_name()RETURNS VARCHAR(25)BEGIN RETURN (SELECT email FROM employees WHERE last_name='Abel');END //DELIMITER ;

举例2：创建存储函数email_by_id()，参数传入emp_id，查询emp_id的email并返回，数据类型为字符串型。

DELIMITER //CREATE FUNCTION email_by_id(emp_id INT)RETURNS VARCHAR(25)BEGINRETURN (SELECT email FROM employees WHERE employee_id=emp_id);END//DELIMITER ;

举例3：创建存储函数count_by_id()，参数传入dept_id，查询dept_id部门的员工人数并返回，数据类型为整型。

DELIMITER //CREATE FUNCTION count_by_id(dept_id INT)RETURNS INTBEGINRETURN (SELECT COUNT(*) FROM employees WHERE department_id=dept_id);END //

对比存储函数和存储过程

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

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

查看

MySQL存储了存储过程和函数的状态信息，可以使用SHOW STATUS语句、SHOW CREATE语句或从information_schema数据库中查询。

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息：

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

2. 使用SHOW STATUS语句查看存储过程和函数的状态信息：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

3. 从information_schema.Routines表中查看存储过程和函数的信息：

SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。

修改

修改存储过程或函数不影响其功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性。

举例1：修改存储过程CountProc的定义，将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。

ALTER PROCEDURE CountProcMODIFIES SQL DATASQL SECURITY INVOKER 

删除

删除存储过程和函数可以使用DROP语句。

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

关于存储过程使用的争议

尽管存储过程有诸多优点，但对于其使用一直存在争议。不同公司对存储过程的需求差异很大。

优点

1. 一次编译多次使用：存储过程只在创建时编译，提高了SQL的执行效率。
2. 减少开发工作量：将代码封装成模块，提高了代码的重用性和结构清晰度。
3. 安全性强：可以设置对用户的使用权限，具有较强的安全性。
4. 减少网络传输量：调用存储过程只需一次网络传输。
5. 良好的封装性：复杂的数据库操作可以一次完成，减少了连接数据库的次数。

缺点

1. 可移植性差：存储过程不能跨数据库移植。
2. 调试困难：只有少数DBMS支持存储过程的调试，开发和维护不易。
3. 版本管理困难：存储过程没有版本控制，版本迭代更新时麻烦。
4. 不适合高并发场景：高并发场景需要减少数据库压力，存储过程会增加数据库压力，不适用。

小结

存储过程既方便又有局限性。不同公司对存储过程的态度不一，但掌握存储过程是开发人员必备的技能之一。

以上就是存储过程与函数的详细内容，更多请关注php中文网其它相关文章！