存储过程与函数

208

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('值');
  1. 调用OUT模式的参数：
SET @name;CALL sp1(@name);SELECT @name;
  1. 调用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预定义的系统函数一样。

创建存储函数

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

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} 存储过程名或函数名
  1. 使用SHOW STATUS语句查看存储过程和函数的状态信息：
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
  1. 从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. 不适合高并发场景：高并发场景需要减少数据库压力，存储过程会增加数据库压力，不适用。

存储过程与函数

小结

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

© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
其他
# 数据库# ai# mysql# 字符串# sql# red# 并发# 封装# select# 数据类型# sql语句# 全局变量# less# 整型# 封装性# 输出参数# mysql开发
喜欢就支持一下吧
点赞8 分享
站长的头像-小浪学习网
如何使用 Navicat 在达梦数据库中创建表-小浪学习网
如何使用 Navicat 在达梦数据库中创建表
如何使用 Navicat 在达梦数据库中创建表
4个月前 128
2023日主题Ripro9.0升级修正源码下载+美化包和插件-小浪学习网
2023日主题Ripro9.0升级修正源码下载+美化包和插件
2023日主题Ripro9.0升级修正源码下载+美化包和插件
4个月前 93
PHPCMS 多语言版本切换功能如何配置？-小浪学习网
PHPCMS 多语言版本切换功能如何配置？
PHPCMS 多语言版本切换功能如何配置？
2个月前 91
ThinkPHP6中如何同时查询两列数据的总和？-小浪学习网
ThinkPHP6中如何同时查询两列数据的总和？
ThinkPHP6中如何同时查询两列数据的总和？
4个月前 87
layui 表格怎么设置单元格对齐方式-小浪学习网
layui 表格怎么设置单元格对齐方式
layui 表格怎么设置单元格对齐方式
2个月前 73
百度网盘加速补丁版-小浪学习网
百度网盘加速补丁版
百度网盘加速补丁版
4个月前 72
相关推荐
如何使用 Navicat 在达梦数据库中创建表-小浪学习网
如何使用 Navicat 在达梦数据库中创建表
如何使用 Navicat 在达梦数据库中创建表
4个月前 128
PHPCMS 多语言版本切换功能如何配置？-小浪学习网
PHPCMS 多语言版本切换功能如何配置？
PHPCMS 多语言版本切换功能如何配置？
2个月前 91
ThinkPHP6中如何同时查询两列数据的总和？-小浪学习网
ThinkPHP6中如何同时查询两列数据的总和？
ThinkPHP6中如何同时查询两列数据的总和？
4个月前 87
黑帽seo 是什么意思-小浪学习网
黑帽seo 是什么意思
黑帽seo 是什么意思
4个月前 71
忘记数据库密码，能在Navicat中找回吗？-小浪学习网
忘记数据库密码，能在Navicat中找回吗？
忘记数据库密码，能在Navicat中找回吗？
4个月前 68
Flutter能兼容Debian系统吗-小浪学习网
Flutter能兼容Debian系统吗
Flutter能兼容Debian系统吗
3个月前 59
默认头像
标题
标题
鼠标事件黑神话：悟空鸿蒙魔术常量魔兽世界高效开发高德地图高可用架构高可扩展性验证码生成驱动更新驱动安装风格字符串预处理器音频编辑音乐创作面向对象静态定位静态多态性隐藏文件夹
如何使用 Navicat 在达梦数据库中创建表-小浪学习网
128人已阅读
如何使用 Navicat 在达梦数据库中创建表
TOP1
2023日主题Ripro9.0升级修正源码下载+美化包和插件-小浪学习网
2023日主题Ripro9.0升级修正源码下载+美化包和插件
4个月前93人已阅读
TOP2
PHPCMS 多语言版本切换功能如何配置？-小浪学习网
PHPCMS 多语言版本切换功能如何配置？
2个月前91人已阅读
TOP3
ThinkPHP6中如何同时查询两列数据的总和？-小浪学习网
ThinkPHP6中如何同时查询两列数据的总和？
4个月前87人已阅读
TOP4
layui 表格怎么设置单元格对齐方式-小浪学习网
layui 表格怎么设置单元格对齐方式
2个月前73人已阅读
TOP5