MYSQL存储过程和函数

最新推荐文章于 2024-02-25 12:06:08 发布

时也-K

最新推荐文章于 2024-02-25 12:06:08 发布

阅读量971

点赞数 14

分类专栏： mysql数据库文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/qq_40210639/article/details/135196109

版权

mysql数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MYSQL存储过程和函数

一、存储过程和函数概述

1.1、什么是存储过程和函数

针对表的一个完整操作往往不是单条SQL语句就能实现的，而是需要一组SQL语句来实现，存储过程和函数可以简单地理解为一组经过编译并保存在数据库中的SQL语句的集合，可以随时被调用。

1.2、优点

允许标准组件式编程：存储过程和函数在创建后可以在程序中被多次调用。
较快的执行速度：如果某一操作包含大量的事务处理代码，并且被多次执行，那么存储过程要比批处理的执行速度快很多。
减少网络流量：对于大量的SQL语句，将其组织成存储过程，会比一条一条的调用SQL语句要大大节省网络流量，降低网络负载。
安全：数据库管理员通过设置执行某一存储过程的权限，从而限制相应数据的访问权限，避免非授权用户对数据的访问，保证数据的安全。

二、创建存储过程

2.1、存储过程语法

-- 创建语法
DELIMITER $$
CREATE PROCEDURE 存储过程名 ( [ IN | OUT | INOUT ] 参数名 参数类型 )
[characteristics ...]
BEGIN
	存储过程体
END $$
DELIMITER;

-- 调用语法
CALL 存储过程名();

“DELIMITER $” 的作用是将语句的结束符 “; ” 修改为 “$ ”，这样存储过程中的SQL语句结束符“;”就不会被MySQL解释成语句的结束而提示错误。在存储过程创建完成后，应使用“DELIMITER ;”语句将结束符修改为默认结束符。
**IN ：**当前参数为输入参数，存储过程只是读取这个参数的值。
**OUT ：**当前参数为输出参数，执行完成之后，调用这个存储过程的应用程序就可以读取这个参数返回的值了。
**INOUT ：**当前参数既可以为输入参数，也可以为输出参数。
**参数类型：**参数类型可以是 MySQL数据库中的任意类型。
characteristics：表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
- LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
- [NOT]DETERMINISTIC：DETERMINISTIC表示存储过程的执行结果是确定的；默认为NOT DETERMINISTIC，表示执行结果不确定。
- {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。
- SQL SECURITY { DEFINER | INVOKER }：指定可执行存储过程的用户，DEFINER表示只有创建者才能执行，INVOKER表示拥有权限的调用者可以执行。
- COMMENT ‘string’：表示存储过程或者函数的注释信息。

2.2、示例

准备数据库和表

-- 创建数据库 
crate database kdftest;
-- 选择数据库
user kdftest;
-- ----------------------------
-- 创建商品表
-- ----------------------------
drop table if exists goods;
create table goods (
	id int(11) not null primary key  auto_increment comment'商品编号',
	type varchar(30) not null comment '商品类型',
	name varchar(20) unique comment '商品名称',
	price decimal(7,2) comment '商品价格',
	num int(11) default 0 comment'商品库存',
	add_time datetime comment '添加时间'
);
-- 添加商品表数据
INSERT INTO goods(id,type,name,price,num,add_time)
VALUES(1,'书籍','西游记',50.4,20,'2018-01-01 13:40:40'),
(2,'糖类','牛奶糖',7.5,200,'2018-02-02 13:40:40'),
(3,'糖类','水果糖',2.5,100,null),
(4,'服饰','休闲西服',800,null,'2018-04-04 13:40:40'),
(5,'饮品','果汁',3,70,'2018-05-05 13:40:40'),
(6,'书籍','东游记',50.4,200,'2018-09-01 14:40:40'),
(7,'书籍','水浒传',50.4,200,'2018-09-01 14:40:40');

2.2.1、示例1:无参数

创建存储过程，统计商品信息表中有多少条记录

DELIMITER $$
CREATE PROCEDURE proc_count ( )
BEGIN
	SELECT COUNT(*)  FROM goods;
END $$
DELIMITER;

创建一个存储过程，输出商品信息表中的平均价格

DELIMITER $$
CREATE PROCEDURE proc_avg ( )
BEGIN
	SELECT avg(*)  FROM goods;
END $$
DELIMITER;

2.2.2、示例2:IN参数

创建存储过程show_somegoods()，查看“goods”表的某个商品的价格，并用IN参数goodname输入商品名称。

DELIMITER $$
CREATE PROCEDURE show_somegoods(in goodname varchar(20))
BEGIN
	SELECT price  FROM goods where name = goodname;
END $$
DELIMITER;

2.2.3、示例3：OUT参数

创建存储过程show_max_price()，查看“goods”表的最高价格。并将最高价格通过OUT参数“maxprice”输出

DELIMITER $$
CREATE PROCEDURE show_max_price(OUT maxprice float)
BEGIN	
	#将查询出来的结果赋值给 maxprice
	select max(price) into maxprice from goods ;
END $$
DELIMITER;

2.2.4、示例4：IN、OUT参数

创建存储过程show_name_price()，查看表中某个商品的价格，并使用IN参数goodname输入商品信息，使用OUT参数goodprice输出商品价格

DELIMITER $$
CREATE PROCEDURE show_name_price(INT goodname varchar(20),OUT goodprice float)
BEGIN
	#将查询出来的参数赋值给goodprice，将输入参数goodname当作条件
	select price into goodprice from goods where name = goodname;
END $$
DELIMITER;

2.2.5、示例5：INOUT参数

创建存储过程show_good_type()，查询某个商品类型，并用INOUT参数“typename”输入商品姓名，输出商品的类型。

DELIMITER $$
CREATE PROCEDURE show_name_price(INOUT typename varchar(20))
BEGIN
	select type into typename from goods where name = typename;
END $$
DELIMITER;

2.3、调用存储过程

使用 CALL 关键字调用存储过程。

CALL 存储过程名([参数[,…]]);

2.3.1、无参存储过程调用

CALL proc_count();
CALL proc_avg();

2.3.2、IN参数调用

# 方式一
CALL 存储过程名('值');

# 方式二
# 定义变量并赋值, := 为变量赋值
SET @变量名 := '值';
# 将变量传递给存储过程的IN参数
CALL 存储过程名(@变量名);

CALL show_somegoods("西游记");

set @goodName :="东游记"
CALL show_somegoods(@goodName);

2.3.3、OUT参数调用

SET @good_name = '西游记';
# @good_name 传入参数
# @good_price 接收查询结果
CALL show_name_price(@good_name, @good_price);
SELECT @good_price;

三、创建存储函数

3.1、函数语法

-- 创建语法
DELIMITER $$
CREATE FUNCTION 函数名 ( [ 参数 [,…] )
	RETURNS TYPE  #返回值类型
	[characteristics ...]
	BEGIN
		函数体 #函数体中肯定有 RETURN 语句
	END$$
DELIMITER;

-- 调用语法：
select 函数名([参数])

RETURNS TYPE子句对于存储函数而言是必须存在的，如果RETURN子句返回值的数据类型与RETURNS TYPE子句指定的数据类型不同，MySQL会将返回值强制转换为RETURNS TYPE子句指定的类型。

3.2、示例

3.2.1、示例1

创建一个名为func的简单存储函数，用于获取goods表中的记录数。

DELIMITER $$
CREATE FUNCTION func()
  RETURNS INT(11)
  # 该函数中包含读数据的SQL语句
  READS SQL DATA
  BEGIN
  RETURN 
  	(SELECT COUNT(*) FROM goods);
  END $$
DELIMITER;

-- 调用函数
SELECT func()

3.2.2、示例2

创建存储函数，名称为name_by_id()，参数传入good_id，该函数查询id对应的商品名name，并返回，数据类型为字符串型。

# 创建存储函数，名称为 name_by_id()，参数传入good_id，
# 该函数查询id对应的商品名name，并返回，数据类型为字符串型。
# 存储函数中的参数都为IN类型，可以不用写参数的类别DELIMITER $$
CREATE FUNCTION name_by_id( good_id INT)
  RETURNS VARCHAR(25)
  # 该函数中包含读数据的SQL语句
  READS SQL DATA
  BEGIN
  RETURN 
  	(SELECT name FROM goods WHERE id = good_id );
  END;
  
-- 函数调用
SELECT name_by_id(1);

四、存储过程、函数查看和删除

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

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

ALTER关键字可以修改存储过程和函数

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

characteristic表示存储过程和函数的特性，其可取值有CONTAINS SQL，NO SQL，READS SQL DATA，MODIFIES SQL DATA，SQL SECURITY { DEFINER | INVOKER }，各值的意义与创建存储过程和函数时相同

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

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

五、存储过程和函数的区别

以下是一个简化的表格，展示存储过程和函数之间的主要区别：

	存储过程procedure	函数 function
关键字	procedure	function
调用语法	CALL 存储过程名（参数）	SELECT 函数名(参数)
返回值	可以有多个返回值，例如输出参数或记录集	只能有一个返回值，通常是单个值或单行结果集
应用场景	适用于执行复杂的数据库操作，如批量数据处理、业务逻辑处理等	适用于计算单个值或单行结果集，如数学计算、字符串处理等