day03——存储过程与存储函数

最新推荐文章于 2023-02-26 13:54:28 发布
最新推荐文章于 2023-02-26 13:54:28 发布
阅读量211 收藏 1
点赞数
分类专栏： 数据库 文章标签： 数据库 mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47873769/article/details/113795840
版权

存储过程

基本概念

存储过程和存储函数 类似于 Java 当中的方法, 可以对多组 SQL语句,进行封装操作。从 MySQL5.0 版本开始, 支持存储过程和存储函数。

  • 存储函数和存储过程的区别(了解)
1.存储函数：必须有返回值。
2.存储过程：可以没有返回值,也可以拥有返回值。
  • 优点和缺点(了解)
1.优点：
	A.存储过程可以封装 复杂的SQL语句，简化SQL的编写
	B.存储过程可以回传递增，并接受参数
	C.存储过程无法使用select指定来运行，与查看表数据或定义函数不同
	D.存储过程可以用在数据校验，强制实行商业逻辑等。
	
2.缺点：
	A.存储过程，往往制定在特定的数据库中，因为支持的编程语言不同，当切换其他厂商数据库时候，需要重写原有的存储过程。
	
	B.存储过程性能调校与撰写，受限于种种数据库系统。

准备数据

-- 删除数据库 db7
DROP DATABASE IF EXISTS db7;

-- 创建db7数据库
CREATE DATABASE IF NOT EXISTS db7 CHARACTER SET utf8;

-- 使用数据库
USE  db7;

-- 删除表数据
DROP TABLE IF EXISTS student;

-- 创建学生 student 表
CREATE TABLE IF NOT EXISTS student(
	id INT,  -- 学生id
	name VARCHAR(30),	-- 学生姓名
	age INT, -- 学生年龄
	gender VARCHAR(20),  -- 学生性别
	score INT  -- 考试成绩
);

-- 添加数据
INSERT INTO  student VALUES (1,'张三',23,'男',95),(2,'李四',24,'男',98),(3,'王五',24,'女',100),(4,'赵六',26,'女',90);

-- 查看结果
SELECT * FROM student;

-- 按照性别进行分组,查看每组学生的总成绩. 按照总成绩的升序排列
SELECT gender,SUM(score) '总分' FROM student GROUP BY gender ORDER BY '总分' ASC;

创建存储过程

  • 语法
-- 修改结束分隔符
DELIMITER $

-- 创建存储过程
CREATE PROCEDURE  存储过程名称(参数列表)
BEGIN 
    SQL语句列表;
END $

-- 修改结束分隔符(⚠️警告：一定要打空格)
DELIMITER ;
  • 举例
-- 修改结束分隔符
DELIMITER $

-- 创建存储过程
CREATE PROCEDURE stu_group ()
BEGIN
	SELECT gender,SUM(score) FROM student GROUP BY gender;
END $

-- 修改结束分隔符
DELIMITER ;

调用存储过程

  • 语法
-- 调用存储过程
CALL  存储过程名称(实际参数);
  • 举例
-- 案例代码：调用存储过程
call stu_group();

查看存储过程

  • 语法
-- 查看存储过程（语法）
SELECT * FROM mysql.proc WHERE db = '数据库名称';
  • 举例
-- 案例代码: 查看存储过程
SELECT * FROM mysql.proc WHERE db = 'db7';

删除存储过程

  • 语法
-- 删除存储过程（语法）
DROP PROCEDURE IF EXISTS 存储过程名称;
  • 举例
-- 案例代码: 删除存储过程
DROP PROCEDURE IF EXISTS stu_group;

存储过程 变量使用

  • 创建存储过程，并定义变量
-- 修改结束符
delimiter $

-- 创建存储过程
create procedure stu_getsum()

-- 开始
begin
	-- 定义变量total，默认值10
	declare total int default 10;
	-- 修改变量total的值
	set total=20;
	-- 查询student表的总分，赋值给变量total
	select sum(score) into total from student;
	-- 查询total的结果
	select total;
-- 结束
end $

-- 修改结束符(⚠️警告：一定要打空格)
delimiter ;
  • 调用存储过程
call stu_getsum();

存储过程 if语句

  • 语法
IF  判断条件  THEN  条件满足需要执行的语句;
ELSEIF  判断条件  THEN  条件满足需要执行的语句;
ELSE  其他情况下需要执行的语句;
END IF;
  • 举例
需求：
    1. 定义一个 int 类型的变量,用于存储班级的总成绩
    2. 定义一个 varchar 类型的变量,用于存储分数的描述
    3. 根据总成绩,进行判断
	380分以上:  学习优秀
	320-380分:  学习不错
	320分以下:  学习一般


-- 创建存储过程
DELIMITER $

CREATE PROCEDURE stu_if()
BEGIN
    -- 定义变量,接收结果,查询总成绩赋值。
    DECLARE message VARCHAR(10); 
    DECLARE total INT;
    
    SELECT SUM(score) INTO total FROM student;
    IF total > 380 THEN SET message = '学习优秀';
    ELSEIF total >= 320 AND total <= 380 THEN SET message = '学习不错';
    ELSE SET message = '学习一般';
    END IF;
    -- 查询总成绩和描述信息
    SELECT total,message;
    
END $
DELIMITER ;

-- 第03步：调用存储过程
CALL stu_if();

存储过程 参数传递

  • 语法
CREATE  PROCEDURE  存储过程的名称([IN][OUT][INOUT]  参数名称  参数类型)
BEGIN 
		SQL语句列表;
END $
  • 举例
-- 第01步: 删除存储过程
DROP PROCEDURE IF EXISTS stu_param;

-- 第02步: 创建存储过程,设置为参数传递
DELIMITER $
CREATE PROCEDURE stu_param(IN total INT,INOUT message VARCHAR(20))
BEGIN
	IF total>380 THEN SET message = '学习优秀';
	ELSEIF total>=320 AND total<=380 THEN SET message = '学习不错';
	ELSE SET message = '学习一般';
	END IF;
END $
DELIMITER ;

-- 第03步: 调用存储过程
CALL stu_param(350,@message);

-- 第04步: 查询返回结果
SELECT @message;

存储过程 循环

  • 语法
初始化语句;
WHILE  	条件判断语句 DO
		循环体语句;
		条件控制语句;
END  WHILE;
  • 举例
-- 第01步：删除存储过程
DROP PROCEDURE IF EXISTS stu_loop;

-- 第02步：创建存储过程,计算 1-100 之间的偶数和
DELIMITER $

CREATE PROCEDURE stu_loop()
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE num INT DEFAULT 1;
    WHILE num <= 100 DO 
    IF num % 2 = 0 THEN SET result = result + num;
    END IF;
    SET num = num + 1;
END WHILE;
SELECT result;
END $

DELIMITER ;

-- 第03步: 调用存储过程
CALL stu_loop();

存储函数

存储函数和存储过程理解上是一样的，只是存储函数有返回值，必须写return语句。

无参数的存储函数

  • 创建存储函数格式
DELIMITER $
CREATE  FUNCTION  函数名称(参数列表)  RETURNS 返回值类型
BEGIN
	SQL 语句列表;
	RETURN  结果;
END $
DELIMITER ;
  • 创建存储函数举例
-- 查询学生表当中,成绩大于 95分的学生人数。

-- 第01步: 删除存储函数
DROP FUNCTION IF EXISTS stu_hanshu;

-- 第02步: 创建存储函数
DELIMITER $
CREATE FUNCTION  stu_hanshu()  RETURNS INT
BEGIN 
	-- 定义变量,记录查询结果,返回
	DECLARE total INT;
	-- 获取到查询的结果
	SELECT COUNT(*) INTO total FROM student WHERE score>95;
	-- 返回结果
	RETURN total;
END $
DELIMITER ;

-- 第03步: 调用存储函数
SELECT stu_hanshu();

有参数的存储函数

-- 查询学生表当中,成绩大于 95分的学生人数。

-- 第01步: 删除存储函数
DROP FUNCTION IF EXISTS stu_hanshu;

-- 第02步: 创建存储函数
DELIMITER $
CREATE FUNCTION  stu_hanshu()  RETURNS INT
BEGIN 
	-- 定义变量,记录查询结果,返回
	DECLARE total INT;
	-- 获取到查询的结果
	SELECT COUNT(*) INTO total FROM student WHERE score>95;
	-- 返回结果
	RETURN total;
END $
DELIMITER ;

-- 第03步: 调用存储函数
SELECT stu_hanshu();

触发器

触发器就是在表数据发生变化的时候，自动触发的一些 SQL 操作。

触发器分类

触发器类型OLD 触发器之前的效果NEW 触发器之后的效果
INSERT 类型的触发器无（因为插入前，没有之前的数据）NEW 表示将要或者已经新增的数据
UPDATE 类型的触发器OLD 表示修改之前的数据NEW 表示将要或者已经修改后的数据
DELETE 类型的触发器OLD 表示将要或者已经修改的数据无（因为删除后，状态无数据）

准备数据

-- 删除数据库
DROP DATABASE IF EXISTS db8;

-- 创建数据库 db8
CREATE DATABASE IF NOT EXISTS db8 CHARSET utf8;

-- 使用数据库
USE db8;

-- 创建账户表
CREATE TABLE IF NOT EXISTS account(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 账户id
	NAME VARCHAR(20),  	-- 姓名
	money DOUBLE		-- 余额
);

-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);

-- 创建日志表 account_log
CREATE TABLE IF NOT EXISTS account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 日志id
	operation VARCHAR(20),  	-- 操作的类型(insert update delete)
	operation_time DATETIME, 	-- 操作时间
	operation_id INT,		-- 操作表的id
	operation_params VARCHAR(200)	-- 操作参数
);

INSERT 触发器

基础语法

-- 删除触发器
DROP TRIGGER IF EXISTS 触发器的名称;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER  触发器的名称  
AFTER INSERT ON 需要监测的表名称  FOR EACH ROW 
BEGIN
	INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
END $

DELIMITER ;

代码举例

-- 创建 INSERT 类型的触发器
-- 第01步,删除触发器
DROP TRIGGER IF EXISTS account_insert;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER account_insert  
AFTER INSERT ON account FOR EACH ROW 
BEGIN
	INSERT INTO account_log VALUES (
		NULL,
		'INSERT',
		NOW(),     -- 使用NOW函数,获取到当前的系统时间
		new.id,
		CONCAT('插入后{id=',new.id,'name=',new.name,'money=',new.money,'}')
	);
END $

DELIMITER ;

-- 第03步,向 account 表当中添加一条记录
INSERT INTO account VALUES (NULL,'王五',2000);

-- 第04步, 查询 account 表记录
SELECT * FROM account;

-- 第05步, 查询 account_log 表记录
SELECT * FROM account_log;

UPDATE 触发器

基础语法

-- 创建触发器, 创建更新的触发器 
DELIMITER $

CREATE TRIGGER  触发器的名称  
AFTER UPDATE ON  需要监测的表名称 FOR EACH ROW 
BEGIN
	INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
END $

DELIMITER ;

代码实现

-- 创建 UPDATE 类型的触发器

-- 第01步,删除触发器
DROP TRIGGER IF EXISTS account_update;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER account_update  
AFTER UPDATE ON account FOR EACH ROW 
BEGIN
	INSERT INTO account_log VALUES (
		NULL,
		'UPDATE',
		NOW(),     -- 使用NOW函数,获取到当前的系统时间
		new.id,
		CONCAT('更新前{id=',old.id,'name=',old.name,'money=',old.money,'}',
		'更新后{id=',new.id,'name=',new.name,'money=',new.money,'}')
	);
END $

DELIMITER ;

-- 第03步,向 account 表当中更新一条记录
UPDATE account SET money = money+10000 WHERE id = 1;

-- 第04步, 查询 account 表记录
SELECT * FROM account;

-- 第05步, 查询 account_log 表记录
SELECT * FROM account_log;

DELETE 触发器

基础语法

-- 创建触发器, 创建删除的触发器 
DELIMITER $

CREATE TRIGGER 触发器的名称
AFTER DELETE ON 需要监测的表名称 FOR EACH ROW 
BEGIN
	INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
END $

DELIMITER ;

代码实现

-- 创建 DELETE 类型的触发器
 
-- 第01步,删除触发器
DROP TRIGGER IF EXISTS account_delete;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE ON account FOR EACH ROW 
BEGIN
	INSERT INTO account_log VALUES (
		NULL,
		'DELETE',
		NOW(),     -- 使用NOW函数,获取到当前的系统时间
		old.id,
		CONCAT('删除前{id=',old.id,'name=',old.name,'money=',old.money,'}')
	);
END $

DELIMITER ;

-- 第03步,向 account 表当中删除一条记录
DELETE FROM account WHERE id = 2;

-- 第04步, 查询 account 表记录
SELECT * FROM account;

-- 第05步, 查询 account_log 表记录
SELECT * FROM account_log;

查看和删除触发器

-- 1. 查看触发器
SHOW  TRIGGERS;

-- 2. 删除触发器
DROP  TRIGGER  触发器的名称;
道也自由
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
存储过程和存储函数
小枫的博客
05-03 1569
一、存储过程和存储函数区别： 1、本质相同，没有区别 2、函数的存在是让过程调用，存储过程内部又是调用存储函数 3、存储函数可以在sql语句中直接调用 如 select 函数 from 表名 4、存储过程能实现的，存储函数也都可以实现，存储函数可以实现的，存储过程也都可以实现 5、存储过程中可以没有返回值，也可以有多个，但存储函数中强制返回一个return 二、存储过程和存...
mysql 题带答案
热门推荐
Sorananger的博客
11-12 1万+
注意：主观题的答案，仅供参考，主观题是除了选择和填空题都是主观题，请各位老师在讲解时如有偏差可根据情况修 订。 第一部分：选择题 1、SQL语言通常称为( A ) A、结构化查询语言 B、结构化控制语言 C、结构化定义语言 D、结构化操纵语言 2、关系模型的基本结构是( A )。 A、二维表 B、树形结构 C、无向图 D、有向图 3、下列四项中，不属于数据库特点的是( D )。 A、数据共享 B、数据完整性 C、数据冗余很高 D、数据独立性高 4、DBMS是什么（ B ） A、操作系统 B、数据库管理系统
数据库原理_ 存储过程与触发器
qq_45915957的博客
05-28 5797
七、存储过程与触发器 1、要求 1、执行存储过程时，实参数等于存储过程中形参数；实参数少于存储过程中形参数； 2、执行存储过程；通过对表操作触发激发触发器执行并查看结果。 实验环境：MySQL 实验步骤 1． 定义存储过程 ① 创建一个能向学生表Student中插入一条记录的存储过程Insert_student，该存储过程需要五个参数，分别用来传递学号、姓名、班级号、性别、出生日期5个值。 插入一条记录的存储过程Insert_student: ②执行存储过程Insert_student，向Student表中
MySQL---存储过程与存储函数的相关概念
sxycylq的博客
02-26 432
MySQL---存储过程与存储函数的相关概念，及创建，删除，修改，查看
oracle中函数和存储过程的区别和联系
时光荏苒心依旧的博客
04-11 1630
oracle中函数和存储过程的区别和联系 　　在oracle中，函数和存储过程是经常使用到的，他们的语法中有很多相似的地方，但也有自己的特点。刚学完函数和存储过程，下面来和大家分享一下自己总结的关于函数和存储过程的区别。 一、存储过程 1.定义 　　存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为procedure。 2.创建存储过程 　　create [o
每天30分钟 一起来学习爬虫——day12（数据存储 之 json &csv，实例：爬取All IT eBook）
01-21
文章目录json什么是jsonpython处理json格式用到的函数CSV什么是CSV：操作json文件转csv文件实例：爬取 All IT eBooks json 什么是json JSON是一种取代XML的数据结构,和xml相比,它更小巧但描述能力却不差,由于它的...
C#全能速查宝典
04-26
1.1.4 变量——存储特定类型的数据 4 1.1.5 Console类——控制台中的输入流、输出流和错误流 6 1.1.6 Convert类——类型转换 8 1.1.7 常量——值不改变的量 9 1.1.8 Dispose方法——释放资源 10 1.1.9 迭代器——...
数据库课程设计设计指导书-最新.pdf
01-08
重点掌握如下环节—— 概念结构设计与逻辑结构设计；熟练的使用 SQL 语言实现数据库的建立、应用和维护。 要求如下 1．要充分认识课程设计对培养自己的重要性，认真做好设计前的各项准备工作。 2．既要虚心接受老师...
关于数据库存储过程和函数的区别，优缺点以及各家之言
weixin_34389926的博客
05-24 172
摘抄观点： 存储过程(Stored Procedure)：一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM...
MySQL存储过程和函数的区别与优缺点
飞跃
07-02 5433
为什么要使用存储过程和函数 数据库对象存储过程和函数，是用来实现一组关于表操作的SQL语句代码当做一个整体来执行。一个完整的操作会包含多条SQL语句，在执行过程中需要根据前面的SQL语句来执行结果有选择的执行后面的SQL语句。 存储过程和函数可以简单的理解为一条或多条SQL语句的集合。存储过程和函数就是事先经过编译并存储在数据库中的一段SQL语句集合。存储过程和函数执行不是由程序调用，也不是手动启动
mysql-存储过程
z123ljx的博客
01-10 606
目录 存储过程 存储过程干嘛的？ 存储过程的创建 删除存储过程 存储过程 存储过程干嘛的？ 存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程和函数的区别： 存储过程是没有返回值的，而函数是带有返回值的 存储过程的创建 定义分隔符： 为什么要定义分隔符，因为mysql中默认的分隔符是 ；然后使用回车就会执行这个命令，但是我
oracle存储过程没有返回值,Oracle 无返回值的存储过程
weixin_36488954的博客
04-08 1308
PL/SQL分页分页技术的实现：分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。无返回值的存储过程古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：案例：现有一张表book，表结构如下：看图： 书号书名出版社请写一个过程，可以向b...
存储过程返回值
think
02-23 719
sql server 2000存储过程的返回值 1. 存储过程没有返回值的情况(即存储过程语句中没有return之类的语句) 用方法 int count = ExecuteNonQuery(..)执行存储过程其返回值只有两种情况 (1)如果通过查询分析器执行该存储过程，在显示栏中如果有影响的行数，则影响几行count就是几 (2)如果通过查询分析器执行该存储过程，在显示栏中如果显...
存储过程的创建和使用，以及存储过程和存储函数的区别
有所为的博客
06-20 4306
简单存储过程的创建和使用 --简单存储过程的创建和使用 create or replace procedure test(ids account.id%type) as begin update account set money = money + 100 where id = ids; commit; end; --查询一下 select * from account where id = 3; --调用一下 declare begin test(3); end; 带有输出变量的存储过
SQLServer -＞ 存储过程返回值总结
caoyanchao1的博客
01-24 9855
1.存储过程没有返回值的情况(即存储过程语句中没有return之类的语句) 用方法intcount = ExecuteNonQuery(..)执行存储过程其返回值只有两种情况 (1)假如通过查询分析器执行该存储过程，在显示栏中假如有影响的行数，则影响几行count就是几 (2)假如通过查询分析器执行该存储过程，在显示栏中假如显示'命令已成功完成。'则count = -1;在显示栏中假如有查询结果，则count = -1 总结：A.ExecuteNonQuery()该方法只返回影响的行数，假如没有..
关于存储赛程返回值的问题
邱纪伟的专栏
04-09 649
 有一个表，其主键是int并且为自动增长的。当我向这个表中插入一项并且不提供主键的时候，数据库会自动添加一个唯一的主键。问题是：怎么得到刚刚插入的那一项的主键值？1、存储过程有两种方式返回值:第一:通过output 参数第二:通过return来实现而在你的存储过程已经声明了一个output参数,只要你在你的存储过程已经给这个值赋值了,它就能返回回去.建议一般用o
MySQL的存储函数characteristic使用
最新发布
06-01
MySQL是一个关系型数据库管理系统，支持多种存储函数，包括数学函数、日期函数、字符串函数等，它们都有各自的特点和用法。 使用MySQL的存储函数可以帮助我们更加方便、高效地处理数据，例如可以使用数学函数计算...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值