触发器：

选课时要求“每门课程选修人数不超过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);
    }

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

Logo
一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

cover

2024年最新【Python学习教程】Python异常处理机制_avoid division byzero，2024年最新一次违反常规的Python大厂面试经历

avatar Byzer 白泽

byzer 笔记总结

本章将详细介绍 Byzer Notebook 工作区相关操作。您可以在页面顶栏点击工作区进入工作区页面，创建或编辑笔记本。

avatar Byzer 白泽
cover

byzer python error:RuntimeError: (‘Exception thrown when converting pandas.Series (object) to Arrow

avatar Byzer 白泽