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