mysql触发器，存储过程，函数等语句的记录

最新推荐文章于 2022-11-06 15:09:44 发布
最新推荐文章于 2022-11-06 15:09:44 发布
阅读量613 收藏 4
点赞数 1
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42765975/article/details/101036343
版权

触发器：

选课时要求“每门课程选修人数不超过6人”的功能如何实现？
在这里插入图片描述
BEGIN
SELECT COUNT(*) INTO @num FROM score WHERE new.c_id=score.c_id;
if @num >=6 THEN
INSERT INTO score(s_id,c_id) VALUES(null,null);
end if;
END
原理：s_id和c_id共同组成它的主键，把它的主键设置为null，就是插入失败，这就是约束的作用
在这里插入图片描述
向数据表加入一条01课程的记录，出错，不行
在这里插入图片描述

另外一种写法：不执行 insert语句，抛出一个异常，这里要用到一个mysql关键字SIGNAL ,并且写message_text提示信息
declare 关键字用于声明一个变量，sqlstate 状态，“HY000”一种异常状态

BEGIN
DECLARE msg CHAR(20) CHARACTER SET utf8;
SELECT COUNT(*) INTO @num FROM score WHERE new.c_id = score.c_id;
if @num >=6 THEN
set msg =“选课人数不能超过6”;
SIGNAL SQLSTATE “HY000” SET message_text =msg;
end if;
END

业务代码，进行插入的时候加入捕获异常，可以捕获到这个触发器抛出的异常信息

  try {
            scoreMapper.insert(score);
            return "插入成功";
        }catch (Exception e){
            System.out.println(e.toString());
            System.out.println(e.getLocalizedMessage());
            return e.toString();
        }
    }

但是结果带上了很多Java异常的信息，无法得到只有触发器抛出的异常信息，
在这里插入图片描述

存储过程和触发器的区别

触发器是*一种特殊类型的存储过程*，它又不同于存储过程，

触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用
触发器的作用
1.可在写入数据表前，强制检验或转换数据
2.触发器发生错误时，异动的结果会被撤销
触发器格式：
格式
DELIMITER //
Create trigger 触发器名字 触发时机 触发事件 on 表 for each
row
Begin
操作的内容 //如果要插入，或者更新，或者删除 可重新写原来得sql语句，进行成功的操作，失败就抛异常。。

End //
DELIMITER ;

存储过程：一组为了完成特定功能的SQL 语句集，
存储在数据库中，经过第一次编译后再次调用不需要再次编译，
存储过程的格式：
DELIMITER //
CREATE PROCEDURE 储存名([ IN ,OUT ,INOUT ]?参数名?数据类形…)
BEGIN
SQL语句
END //
DELIMITER ;
调用过程:

用call 过程名（ ）

查看所有的存储过程show procedure status;
查看创建的存储过程show create procedure 过程名;
删除过程 drop procedure 过程名
In 表示参数从外部传入到里面使用（过程内部使用）
Out 表示参数从过程里边把数据保存到变量中，交给外部使用，所有传入的必须是变量 如果说传入的out变量本身在外部有数据，那么在进入过程之后，第一件事就是被清空,设为null
Inout 数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返回给外部
我的第一个存储过程：
在这里插入图片描述
CREATE DEFINER=root@localhost PROCEDURE firstProcedure(IN sex varchar(20),IN name varchar(20),IN birth varchar(20))
BEGIN
#Routine body goes here…
INSERT INTO student(s_name,s_birth,s_sex) VALUES(name,birth,sex );
END

一定要加上参数的长度（这个没有自动生成），否则报错，
调用
在这里插入图片描述

函数和存储过程的区别：

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。*函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类*存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

我的第一个函数

CREATE DEFINER=root@localhost FUNCTION firstFunction(name varchar(20),birth varchar(20),sex varchar(20)) RETURNS varchar(200) CHARSET utf8
BEGIN
#Routine body goes here…
INSERT INTO student(s_name,s_birth,s_sex) VALUES(name,birth,sex);
RETURN ‘插入成功’;
END

函数的调用前面暂时见到的是一个select 没见过加insert into 或者其他的，所以要想实现insert into 或者update返回一个字符串暂时没办法。。

在这里插入图片描述

用函数实现选课人数不能超过6，这样子就可以自定义返回信息

CREATE DEFINER=`root`@`localhost` FUNCTION `insertScore`(`sId` varchar(10),`cId` varchar(10),`sScore` varchar(10)) RETURNS varchar(200) CHARSET utf8
BEGIN
	#Routine body goes here...
	DECLARE msg CHAR(20) CHARACTER SET utf8;
SELECT COUNT(*) INTO @num  FROM score WHERE cId = score.c_id;
if @num >=6 THEN
set msg ="选课人数不能超过6";
ELSE
INSERT INTO score(s_id,c_id,s_score) VALUES(sId,cId,sScore);
set msg="插入成功";
end if;

	RETURN msg;
END

mybatis 的mapper.xml文件：

<select id="insertScore" parameterType="com.lqh.suanfa_study.entity.Score" resultType="java.lang.String">
		SELECT insertScore(#{sId},#{cId},#{sScore})
	</select>

controller层
try {

        return scoreMapper.insertScore(score);
    }catch (Exception e){
        System.out.println(e.toString());
        System.out.println(e.getLocalizedMessage());
        return e.toString();
    }
}

前端页面：返回信息完全由数据库返回
在这里插入图片描述

存储过程选课

脚本：
CREATE DEFINER=root@localhost PROCEDURE insertScoreProcedure(IN sId varchar(10),IN cId varchar(10),IN sScore varchar(10),OUT msg varchar(200))
BEGIN
#Routine body goes here…
SELECT COUNT(*) INTO @num FROM score WHERE cId = score.c_id;
if @num >=7 THEN
set msg =“选课人数不能超过7”;
ELSE
INSERT INTO score(s_id,c_id,s_score) VALUES(sId,cId,sScore);
set msg=“选课成功”;
end if;

END

这个是一个带有out的存储过程，三个in是传入来的参数，在调用存储过程的时候要定义变量作为out变量传进来，再通过select 这个变量得到输出值，脚本如下：
CALL insertScoreProcedure(“07”, “01”, “90”, @msg);
select @msg

结果：
在这里插入图片描述

mybatis 调用存储过程
传入的参数必须是Map<String,Object> 类型，最后的返回结果也会自动写入这个Map里面，在调用之前还要加一个ParamMap做传参的映射
存储过程的参数和名称无关，只和顺序有关系

2、 存储过程的output参数，只能通过传入的map获取

3、 存储过程返回的结果集可直接用返回的map接收

<parameterMap id="back" type="java.util.Map">
  <parameter property="sId" jdbcType="VARCHAR" mode="IN"></parameter>
  <parameter property="cId" jdbcType="VARCHAR" mode="IN"></parameter>
  <parameter property="sScore" jdbcType="VARCHAR" mode="IN"></parameter>
  <parameter property="msg" jdbcType="VARCHAR" mode="OUT"></parameter>

</parameterMap>

  <select id="insertScoreProcedure" parameterMap="back" statementType="CALLABLE">
    CALL insertScoreProcedure(#{sId,mode=IN,jdbcType=VARCHAR},
    #{cId,mode=IN,jdbcType=VARCHAR},
    #{sScore,mode=IN,jdbcType=VARCHAR},
    #{msg,mode=OUT,jdbcType=VARCHAR})
    ;
  </select>


//调用Mysql存储过程
    String insertScoreProcedure(Map<String,Object> map);

调用：

 Map<String,Object> map=new LinkedHashMap<>();

        map.put("sId","07");
        map.put("cId","01");
        map.put("sScore","98");
        scoreMapper.insertScoreProcedure(map);
        System.out.println(map);

结果：
{sId=07, cId=01, sScore=98, msg=选课成功}

调用存储过程返回一个表格（List集合）

问题：
在这里插入图片描述

存储过程的脚本：给返回的集合加一个字段，存储过程可以实现，要新建一个临时表，注意这个表不会实际创建，这里用到游标，游标的实现会很慢，特别是mysql,Oracle就稍微好一点，没那么慢

CREATE DEFINER=`root`@`localhost` PROCEDURE `backSelectFirst`()
BEGIN
		#创建一个临时表
        DROP TABLE if  exists score_temporary;
		create temporary table if not exists score_temporary
		(
				s_id	VARCHAR(64) ,#学号
				s_name VARCHAR(20), #姓名
				c_id VARCHAR(20),#课程号
				c_name VARCHAR(20),#课程名字
				s_score VARCHAR(20),#分数
				score_level VARCHAR(20)#等级
					 
				) ;
         begin
                   
                   
           #定义 变量  接收id和姓名
         DECLARE sId	VARCHAR(64) ;#学号
				 DECLARE	sName VARCHAR(20); #姓名
				 DECLARE	cId VARCHAR(20);#课程号
			   DECLARE	cName VARCHAR(20);#课程名字
		   	 DECLARE	sScore INT(11);#分数
		  	 DECLARE	scoreLevel VARCHAR(20);#等级
             
           #这个用于处理游标到达最后一行的情况  
  
           DECLARE s int default 0;  
             
           #声明游标cursor_name（cursor_name是个多行结果集）  
  
           DECLARE cursor_name CURSOR FOR SELECT student.s_id,student.s_name,course.c_id,course.c_name,score.s_score FROM 
					 student,score,course WHERE score.s_id=student.s_id AND score.c_id=course.c_id ORDER BY student.s_id;  
             
           #设置一个终止标记   
    
           DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;  
            
                #打开游标  
  
                OPEN cursor_name;  
                      
                    #获取游标当前指针的记录，读取一行数据并传给变量  
  
                    fetch  cursor_name into sId,sName,cId,cName,sScore; 
                    #开始循环，判断是否游标已经到达了最后作为循环条件   
  
                    while s <> 1 do  
                            
														IF sScore>=90 THEN
														SET scoreLevel="优秀";
														ELSEIF sScore>=80 AND sScore<90 THEN
														SET scoreLevel="良好";
														ELSEIF sScore>=70 AND sScore<80 THEN
														SET scoreLevel="中等";
														ELSEIF sScore>=60 AND sScore<70 THEN
														SET scoreLevel="较差";
														ELSE 
														SET scoreLevel="不及格";
														end IF;
													 
														
                            insert into score_temporary(s_id,s_name,c_id,c_name,s_score,score_level) values(sId,sName,cId,
														cName,sScore,scoreLevel);
                            #读取下一行的数据  
  
                            fetch  cursor_name into sId,sName,cId,cName,sScore; 
                      
                    end while;  
                   
                 #关闭游标  
  
                 CLOSE cursor_name ;  
              
         #从临时表中拿到结果集 
				SELECT  * from score_temporary;  
               
    #语句执行结束  
                   
         end;
         
END

注意 DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET s=1; 的作用
在这里插入图片描述
结果：
在这里插入图片描述

mybatis调用存储过程返回一个表格：

如果存储过程的传参必须是map集合，写一个parameterMap映射来传入，这个Map里面可以是存储过程的in,或者out,但是如果这个存储过程要返回一个表格，一定要List类型来接受，传参可以是无，存储过程里面没有in 或者out,也就不用传参，
Mapper接口
//调用存储过程返回集合

  List<Map> backSelect();

mapper的xml文件

<!--调用存储过程返回一个集合-->

  <resultMap id="procedureBack" type="java.util.Map">
    <result column="s_id" property="sId"></result>
    <result column="s_name" property="sName"></result>
    <result column="c_id" property="cId"></result>
    <result column="c_name" property="cName"></result>
    <result column="s_score" property="sScore"></result>
    <result column="score_level" property="scoreLevel"></result>
  </resultMap>
  <!--parameterType可以不要-->
  <select id="backSelect" statementType="CALLABLE" resultMap="procedureBack">
   call backSelectFirst();
  </select>

测试：

@Test
    public void t02(){
       // Map result =new HashMap();
    List<Map> result01= scoreMapper.backSelect();
        System.out.println(result01);
        for (int i=0;i<result01.size();i++){
            System.out.println(result01.get(i));
        }
        //System.out.println(result);
    }

结果：
在这里插入图片描述

头发不会掉
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql存储过程 函数 触发器_MySQL 存储过程、函数、触发器
weixin_29625757的博客
01-19 129
1.触发器mysql> delimiter |mysql> create trigger beforeinsertuserinfo-> before insert on userinfo-> for each row begin-> insert into userinfolog values(now(),CONCAT(new.userid,new.usernam...
Mysql【存储过程和存储函数，触发器，事务】
weixin_41431242的博客
10-11 566
一、存储过程和存储函数 概念： 存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合 好处： 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处 存储过程和存储函数的区别：【存储过程和函数效果基本相同】 .
oracle报00942错误,也说ORACLE 数据库的 pls-00942 错误
weixin_34138014的博客
04-04 1670
今天在学用navicat链接ORACLE数据库，数据库中有表testlong创建新存储过程如下：CREATE OR REPLACEPROCEDURE white_user_list ASV_PROD_ID number;BEGINselect number1 into V_PROD_ID from testlong;-- routine body goes here, e.g.-- DBMS_OU...
数据库之—用触发器实现每个学生最多只能选择3门课程
12-22
用触发器实现每个学生最多只能选择3门课程的代码如下： use 学生档案 go create trigger elective_restrict on 选课表 for insert, update as begin if update (课程号) begin declare @num varchar(10) declare @count int select @num=课程号 from inserted select @count = count(*) from 选课表 where 课程号 = @num if(@num>3)
【数据库系统概论】实验六 完整性控制
杨明金的博客
11-06 2982
2．掌握SQL Server数据库系统完整性控制方法。– 执行插入语句，重新插入数据后，进行查看。1．写出你操作并正确执行的代码及结果。– 更新成绩 (变化幅度超过10%)– 查询T.SC_U看是否有记录。1．掌握数据库完整性控制原理。– 删除T.SC的所有数据。– 删除T.SC的所有数据。– 重新插入数据后进行查看。– 查询更改成绩后的的数据。– 查询更改成绩前的数据。– 创建表T.SC_U。– 删除触发器S_1。– 创建触发器S_3。
数据库课程设计题
热门推荐
qq_45457669的博客
08-09 2万+
实验二 单表查询 一、实验目的和要求: 掌握SELECT语句的基本语法和查询条件表示方法； 掌握查询条件表达式和使用方法； 掌握GROUP BY 子句的作用和使用方法； 掌握HAVING子句的作用和使用方法； 掌握`ORDER BY子句的作用和使用方法。 二、实验内容: 使用SQL语句创建和删除索引。 三、实验步骤: （1） 查询全体教师的工号姓名电话 SELECT Tno,Tname,Tphone from Teacher; （2）查询副教授的基本信息 SELECT * from Teacher w
MySQL 的记录存储结构、存储引擎与 Buffer Pool
sekever的博客
08-20 700
但是utf8mb3没有考虑到生僻字以及繁体字，而随着人类的语言越来越抽象，emoji 类型字符也成了常用的字符，因此，我们一般在 mysql 中使用 utf8mb4，它是UTF-8 的完整实现，使用1到4个字节来表示字符。我们知道innodb是用页结构来存放数据的，每一页一般占16kb，而行中的记录被一个个的存放在页中，一般情况下，MySQL一次读取磁盘中一页的数据量放到内存中，那页中的行结构又是怎么存储的呢。计算机只能存储二进制的数据，所谓字符集就是字符的集合，它设定了字符和二进制的数据的对应关系。...
MySql视图触发器存储过程详解
09-08
与视图不同，存储过程更像是可执行的函数，可以包含控制流语句，如IF-ELSE、WHILE等。存储过程提高了代码复用性，降低了网络通信量，并提供了更高的安全性。 创建存储过程的语法如下： ```sql DELIMITER // CREATE ...
MySQL数据库函数，触发器，存储过程
09-15
通过深入学习和掌握MySQL的函数、触发器和存储过程，开发者能够更加高效地管理和操作数据库，解决复杂的数据处理问题，提升应用程序的性能。在实际项目中，熟练运用这些特性是数据库设计和优化的关键。通过"mysql...
MySQL触发器、存储过程、自定义函数、视图示例
09-01
### MySQL触发器、存储过程、自定义函数、视图示例 #### 一、MySQL触发器 触发器是一种特殊类型的存储过程，它被设计为在特定事件（如INSERT、UPDATE或DELETE操作）发生时自动执行。触发器可以用来强制业务规则、...
MYSQL触发器与存储过程.ppt
09-15
1. 存储过程和函数的修改可能影响依赖于它们的其他对象，如视图、触发器等。 2. 触发器可能导致数据更新的隐式行为，需要谨慎设计以避免逻辑复杂性和潜在的问题。 总之，MySQL的存储过程和触发器是强大的工具，用于...
MySQL-存储过程-函数-触发器-游标
码赛客1024的博客
09-27 1058
本文讲解了MySQL中的存储过程，函数，触发器，游标 。
SQL练习题目，看这篇文章就够了！
Husky
10-15 937
该文章出自：知乎 首先，创建表咯。一共有4张表。分别是学生表，课程表，教师表，成绩表 。 学生表Student 4个字段，SId（学生ID）,Sname(学生姓名)，Sage(学生年龄)，Ssex(学生性别) create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10),PRIMARY k...
MySQL - 循环与游标
sugar516的博客
08-17 1644
MySQL - 循环与游标MySQL的控制流1、if语句2、case语句3、循环语句MySQL的游标机制定义处理程序 MySQL的控制流 if | case | leave(类似break) | iterate(类似continue) | repeat | while | loop 后三个可以实现循环 1、if语句 if 条件 then 语句; end if if 条件 then 语句; else 语句; end if if 条件 then 语句; elseif
实验六、完整性控制（包含触发器的使用方法）——SQLServer触发器
最新发布
GHZ2443063059的博客
11-06 2716
掌握SQL Server数据库系统完整性控制方法 1. 创建一个触发器，“数据库”课程的选课人数不能超过5人。 2. 创建一个触发器，规定每位同学选课门数不能超过3门。 3. 创建一个触发器，当修改成绩表中成绩时，如果变化幅度超过10%，将操作记录在下表SC_U（Sno,Cno,Oldgrade,Newgrade）中。
【数据库作业13】SQL练习8 - CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION
weixin_45871977的博客
04-05 1717
数据的完整性：防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据。 防范对象：不合语义的、不正确的数据。 数据的安全性：保护数据库 防止恶意的破坏和非法的存取。 防范对象：非法用户和非法操作。 实体完整性：主码唯一，不能为空 关系模型的实体完整性： CREATE TABLE中用PRIMARY KEY定义 主属性只有一个时可以使用列级完整性，将条件直接写在数据类型后面，也可以使用表...
《数据库系统概论》课程之实验四：数据控制(完整性部分)
Yuhan2001的CSDN博客
10-11 1836
《数据库系统概论》课程之实验四：数据控制(完整性部分)，熟悉通过SQL对数据进行完整性控制。
[XJTUSE DATABASE]——第九章 数据库完整性
weixin_47692652的博客
02-08 1216
完结撒花
mysql触发器个数限制_MySQL通过触发器解决数据库中表的行数限制的需求
weixin_31688273的博客
02-19 458
最近项目一个需求是对操作日志的数量限制为10万条，超过十万条便删除最旧的那一条，保存数据库中日志数量不超过10万。当时我的第一想法是通过触发器来做，便在数据库中执行了如下的SQL:delimiter $create trigger limitLogbeforeinserton OperationLogfor each rowbeginif (select count(*) from Operati...
MySQL数据库函数存储过程触发器实践
"该文档是针对大二学生的一份MySQL数据库开发技术的题库，主要涵盖函数、存储过程和触发器的理论与实践操作。实验旨在帮助学生理解这些概念，掌握其语法和应用，以及如何在实际系统中利用它们处理业务逻辑。实验环境...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值