MySQL的存储函数与存储过程相关概念与具体实例详解

目录
  • MySQL存储过程与存储函数的相关概念
  • 存储过程
  • 存储函数
  • 存储函数与存储过程的对比
  • 存储过程和函数的查看修改删除

MySQL存储过程与存储函数的相关概念

存储函数和存储过程的主要区别:

  • 存储函数一定会有返回值的
  • 存储过程不一定有返回值

存储过程和函数能后将复杂的SQL逻辑封装在一起,应用程序无需关注存储过程和函数内部复杂的SQL逻辑,而只需要简单地调用存储过程和函数即可

存储过程

一组预先编译的SQL语句的封装

执行过程:执行过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要向服务器发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行

  • 简化操作,提高了SQL语句的重用性,减少了开发程序员的压力
  • 减少操作过程中的失误,提高效率
  • 减少网路传输量,客户端不需要将所有的SQL语句通过网络发给服务器
  • 减少SQL语句暴露在网上的风险,提高数据查询的安全性

与视图,函数的对比:

  • 视图:是虚拟表,通常不对底层数据表直接操作
  • 存储过程:程序化的SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理
  • 相较于函数,存储过程没有返回值

分类

  • 没有参数(无参数无返回)
  • 仅仅带有IN 类型 (有参数无返回)
  • 仅仅带OUT类型(无参数有返回)
  • 即带IN又带OUT(有参数有返回)
  • 带INOUT(有参数有返回)

创建存储过程

DELIMITER $

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

DELIMITER ;

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

调用存储过程

CALL select_all_data();

无参数无返回值

DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
	SELECT AVG(salary) FROM emp;
END //
DELIMITER ;
CALL avg_employee_salary();

无参数有返回值

DELIMITER //
CREATE PROCEDURE show_min_salart(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO ms
	FROM emp;
END //
DELIMITER ;
CALL show_min_salart(@ms);
SELECT @ms;

有参数无返回值

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
	SELECT salary
	FROM emp
	WHERE last_name=empname;
END //
DELIMITER ;
CALL show_someone_salary('Abel');
SET @empname='Abel';
CALL show_someone_salary(@empname)

有参数有返回值

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
	SELECT salary INTO empsalary
	FROM emp
	WHERE last_name=empname;
END //
DELIMITER ;
SET @empname='Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;

带INOUT

DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
SELECT last_name
FROM emp
Where employee_id=
(
	SELECT manager_id
	FROM emp
	WHERE last_name=empname
);
END //
DELIMITER ;
SET @empname='Abel';
CALL show_mgr_name(@empname);
SELECT @empname;

如何调试

通过SELECT语句,把程序执行的中间结果查询出来,从而调试一个SQL语句的正确性。调试成功之后,把SELECT语句后移到下一个SQL语句,逐步推进查询下一个 SQL语句

存储函数

MySQL允许用户自定义函数,自定义好了之后,调用方式与调用MySQL预定义的系统函数一样

创建存储函数

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

  • 参数类型,FUNCTION 中总是默认为IN参数
  • RETURNS type 表示函数返回数据的类型,对于函数而言是强制的
  • characteristics 表示创建函数时指定的对函数的约束
  • 函数题可以用BEGIN … END表示SQL代码的开始和结束。如果函数体只有一条语句,则可以省略BEGIN … END

调用存储函数

SELECT 函数名(实参列表)

练习一

DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
BEGIN
RETURN
(
	SELECT email
	FROM emp
	WHERE last_name='Abel'
);
END //
DELIMITER ;
SELECT email_by_name();

练习2

DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
RETURN
(
	SELECT email
	FROM emp
	WHERE employee_id=emp_id
);
END //
DELIMITER ;
SELECT email_by_id(101);
SET @emp_id=102;
SELECT email_by_id(@emp_id);

存储函数与存储过程的对比

  • 存储过程 PEOCEDURE 存储函数 FUNCTION
  • 调用语法 CALL 存储过程 SELECT 存储函数
  • 存储过程返回值可以有0个或对各 存储函数返回值只有一个
  • 存储过程一般用于更新操作 存储函数一般用于查询结果为一个值并返回
  • 存储函数可以放在查询语句中使用,存储过程则不行
  • 存储过程功能更为强大,包括能够执行对表的操作(创建表,删除表)和事务操作,这些功能是存储函数并不具备的

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

查看

使用SHOW CREATE 语句 查看创建信息

SHOW CREATE PROCEDURE show_mgr_name\G;
SHOW  CREATE FUNCTION email_by_id\G;

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

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

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

SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME='email_by_id' AND ROUTINE_TYPE='FUNCTION';
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME='show_min_salart' AND ROUTINE_TYPE='PROCEDURE';

修改存储过程与函数

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

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

删除存储过程或函数

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

到此这篇关于MySQL的存储函数与存储过程相关概念与具体实例详解的文章就介绍到这了,更多相关MySQL的存储函数与存储过程内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL系列之五 视图、存储函数、存储过程、触发器

    目录 一.视图 1.视图的创建 2.查看视图定义 3.删除视图 二.存储函数 1.系统函数 2.自定义函数(user-defined function:UDF) 三.存储过程 四.触发器 总结 一.视图 视图:VIEW,虚表,保存有实表的查询结果,实际数据不保存在磁盘 物化视图:实际数据在磁盘中有保存,加快访问,MySQL不支持物化视图 基表:视图依赖的表 视图中的数据事实上存储于"基表"中,因此,其修改操作也会针对基表实现.其修改操作受基表限制. 注意:修改视图时是修改的原表 1.视

  • MySQL的存储函数与存储过程的区别解析

    MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数. 存储函数和存储过程一样,都是在数据库中定义一些 SQL 语句的集合. 存储函数与存储过程的区别 1.存储函数有且只有一个返回值,而存储过程可以有多个返回值,也可以没有返回值. 2.存储函数只能有输入参数,而且不能带in, 而存储过程可以有多个in,out,inout参数. 3.存储过程中的语句功能更强大,存储过程可以实现很复杂的业务逻辑,而函数有很多限制,如不能在函数中使用insert,

  • 细谈Mysql的存储过程和存储函数

    1 存储过程 1.1 什么是存储过程 存储过程是一组为了完成某项特定功能的sql语句集,其实质上就是一段存储在数据库中的代码,他可以由声明式的sql语句(如CREATE,UPDATE,SELECT等语句)和过程式sql语句(如IF...THEN...ELSE控制结构语句)组成.存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用. 1.2 存储过程的优缺点 优点: 1.可增强sql语言的功能和灵活性 存储过程可以用流程控制语言编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算.

  • MySQL的存储函数与存储过程相关概念与具体实例详解

    目录 MySQL存储过程与存储函数的相关概念 存储过程 存储函数 存储函数与存储过程的对比 存储过程和函数的查看修改删除 MySQL存储过程与存储函数的相关概念 存储函数和存储过程的主要区别: 存储函数一定会有返回值的 存储过程不一定有返回值 存储过程和函数能后将复杂的SQL逻辑封装在一起,应用程序无需关注存储过程和函数内部复杂的SQL逻辑,而只需要简单地调用存储过程和函数即可 存储过程 一组预先编译的SQL语句的封装 执行过程:执行过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要

  • mysql(5.6及以下)解析json的方法实例详解

    mysql(5.6及以下)解析json #json解析函数 DELIMITER $$ DROP FUNCTION IF EXISTS `json_extract_c`$$ CREATE FUNCTION `json_extract_c`( details TEXT, required_field VARCHAR (255) ) RETURNS TEXT CHARSET latin1 BEGIN SET details = SUBSTRING_INDEX(details, "{", -1

  • mysql存储过程之错误处理实例详解

    本文实例讲述了mysql存储过程之错误处理.分享给大家供大家参考,具体如下: 当存储过程中发生错误时,重要的是适当处理它,例如:继续或退出当前代码块的执行,并发出有意义的错误消息.其中mysql提供了一种简单的方法来定义处理从一般条件(如警告或异常)到特定条件(例如特定错误代码)的处理程序.完事我们来使用DECLARE HANDLER语句来尝试声明一个处理程序,先来看语法: DECLARE action HANDLER FOR condition_value statement; 上述sql中,

  • mysql学习笔记之完整的select语句用法实例详解

    本文实例讲述了mysql学习笔记之完整的select语句用法.分享给大家供大家参考,具体如下: 本文内容: 完整语法 去重选项 字段别名 数据源 where group by having order by limit 首发日期:2018-04-11 完整语法: 先给一下完整的语法,后面将逐一来讲解. 基础语法:select 字段列表 from 数据源; 完整语法:select 去重选项 字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [havin

  • python基于mysql实现的简单队列以及跨进程锁实例详解

    通常在我们进行多进程应用开发的过程中,不可避免的会遇到多个进程访问同一个资源(临界资源)的状况,这时候必须通过加一个全局性的锁,来实现资源的同步访问(即:同一时间里只能有一个进程访问资源). 举个例子如下: 假设我们用mysql来实现一个任务队列,实现的过程如下: 1. 在Mysql中创建Job表,用于储存队列任务,如下: create table jobs( id auto_increment not null primary key, message text not null, job_s

  • 将DataTable作为存储过程参数的用法实例详解

    最近工作中写了几个存储过程,需要向存储过程中传递字符串,因为SQL Server 2000中没有内置类似于 split 的函数,只好自己处理,将前台数据集中的一列用逗号拆分存到一个List<string>中,再转化为字符串传给存储过程,很是麻烦.今天看了下SQL Server 2008的新特性,发现有表变量的使用,及其将DataTable作为参数的用法,就尝试了一下,简单谈谈心得. 示例代码下载 一.测试环境 1.Windows Server 2008 R2 DataCenter 2.Visu

  • Python函数装饰器常见使用方法实例详解

    本文实例讲述了Python函数装饰器常见使用方法.分享给大家供大家参考,具体如下: 一.装饰器 首先,我们要了解到什么是开放封闭式原则? 软件一旦上线后,对修改源代码是封闭的,对功能的扩张是开放的,所以我们应该遵循开放封闭的原则. 也就是说:我们必须找到一种解决方案,能够在不修改一个功能源代码以及调用方式的前提下,为其加上新功能. 总结:原则如下: 1.不修改源代码 2.不修改调用方式 目的:在遵循1和2原则的基础上扩展新功能. 二.什么是装饰器? 器:指的是工具, 装饰:指的是为被装饰对象添加

  • mysql实现事务的提交与回滚的实例详解

    最近要对数据库的数据进行一个定时迁移,为了防止在执行过程sql语句因为某些原因报错而导致数据转移混乱,因此要对我们的脚本加以事务进行控制. 首先我们建一张tran_test表 CREATE TABLE tran_test( f1 VARCHAR(10) NOT NULL, f2 INT(1) DEFAULT NULL, PRIMARY KEY (f1) )ENGINE=INNODB CHARSET=utf8 我想对tran_test插入两条数据,但是为了防止插入中报错,因此我要把插入语句控制在一

随机推荐