触发器和存储过程是为了提高SQL的运行效率。
SQL语句先编译、后执行,而触发器与存储过程都会提前预编译完成,且只编译一次,供反复调用。
随着时代的进步,硬件与带宽的提升,触发器和存储过程提升效率并不明显,所以在传统或并发量低的项目中已经很少用到了。
1.触发器
简单的作用示意:用于增删改的时候,表关联的连贯操作(具体根据需求的表设计定义)。以前会有人说可以用来做日志统计,现实开发中,其实很少有人用库表做日志。
创建:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW exesql
注释:
trigger_name 触发器名称
trigger_time 触发时间,值:BEFORE、AFTER
trigger_event 触发事件,值:INSERT、DELETE、UPDATE
tb_name 表名称,触发器建立在哪个表的基础上
exesql 触发器条件满足时,执行的sql或函数
列子:
DELIMITER $$drop trigger if existsms.trigger_test;create trigger ms.trigger_test after insert
on ms.master foreach rowbegin
INSERT INTO slave (s_id, s_content) VALUES(new.m_id,new.m_content);end$$
注:
DELIMITER $$ 定义结束符$$,意味SQL语句以$$结束,mysql默认;分号结束。
new表示记录更新后的值,更新前的值用old
该脚本不可以在视图工具Navicat或dbvisual里执行。
必须如图执行
2.存储过程
简单描述:存储过程是一个特定的SQL语句集,在数据库中只需编译一次,重复使用。一般项目不建议使用存储过程,维护麻烦。
优点:重复使用、减少网络流量、防止SQL注入(关键)
缺点:应用层程序员调试麻烦、可移植性低、可扩展性差
CREATE PROCEDURE PROCEDURE_NAME (PROCEDURE_MODE PARAM_NAME PARAM_TYPE) EXE_SQL
注释:
PROCEDURE_NAME 存储过程名字
PROCEDURE_MODE 存储过程类型,有IN、OUT、INOUT
PARAM_NAME 参数引用名称
PARAM_TYPE 参数类型(int、varchar、datetime等等)
EXE_SQL 执行sql语句
IN例子:
DELIMITER $$DROP PROCEDURE IF EXISTSms.in_procedure;CREATE PROCEDURE ms.in_procedure(IN param int)BEGIN
SELECTparam;SET param=3;SELECTparam;END$$SET @param=1;
CALL ms.in_procedure(@param);SELECT @param;
$$
注:
DELIMITER $$ 定义结束符$$,意味SQL语句以$$结束,mysql默认;分号结束。
该脚本不可以在视图工具Navicat或dbvisual里执行。
必须如图执行
OUT例子:
DELIMITER $$DROP PROCEDURE IF EXISTSms.out_procedure;CREATE PROCEDURE ms.out_procedure(OUT param int)BEGIN
SELECTparam;SET param=3;SELECTparam;END$$SET @param=1;
CALL ms.out_procedure(@param);SELECT @param;
$$
注:
DELIMITER $$ 定义结束符$$,意味SQL语句以$$结束,mysql默认;分号结束。
该脚本不可以在视图工具Navicat或dbvisual里执行。
必须如图执行
INOUT例子
DELIMITER $$DROP PROCEDURE IF EXISTSms.inout_procedure;CREATE PROCEDURE ms.inout_procedure(INOUT param int)BEGIN
SELECTparam;SET param=3;SELECTparam;END$$SET @param=1;
CALL ms.inout_procedure(@param);SELECT @param;
$$
注:
DELIMITER $$ 定义结束符$$,意味SQL语句以$$结束,mysql默认;分号结束。
该脚本不可以在视图工具Navicat或dbvisual里执行。
必须如图执行
3.java调用存储过程
packagecom.nginx.session.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.SQLException;importorg.junit.Test;importorg.junit.runner.RunWith;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importorg.springframework.jdbc.core.CallableStatementCallback;importorg.springframework.jdbc.core.CallableStatementCreator;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTestpublic classJDBCTest {
@AutowiredprivateJdbcTemplate jdbcTemplate;
@Testpublic voidtest(){
Integer value= jdbcTemplate.execute(newCallableStatementCreator() {
@Overridepublic CallableStatement createCallableStatement(Connection con) throwsSQLException {
String storedProc= "{call ms.inout_procedure (?)}";
CallableStatement cs=con.prepareCall(storedProc);
cs.setInt(1,1);returncs;
}
},new CallableStatementCallback() {
@Overridepublic Integer doInCallableStatement(CallableStatement cs) throwsSQLException {
cs.execute();return cs.getInt(1);
}
});
System.out.println(value);
}
}