mysql存储过程,触发器

存储过程：
　　是在大型数据库系统中，
　　一组为了完成特定功能的SQL 语句集，
　　存储在数据库中，经过第一次编译后再次调用不需要再次编译，
　　用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
　　存储过程是数据库中的一个重要对象
优点：
　　1允许模块化程序设计（创建一次多次使用）
　　2允许更快执行
　　3减少网络流量
　　4更好的安全机制
格式：
DELIMITER //
CREATE PROCEDURE 储存名([ IN ,OUT ,INOUT ]?参数名?数据类形...)
BEGIN
SQL语句
END //
DELIMITER ;

调用过程:
用call 过程名（ ）

查看所有的存储过程show procedure status;
查看创建的存储过程show create procedure 过程名;
删除过程 drop procedure 过程名

In 表示参数从外部传入到里面使用（过程内部使用）
Out 表示参数从过程里边把数据保存到变量中，交给外部使用，所有传入的必须是变量 如果说传入的out变量本身在外部有数据，那么在进入过程之后，第一件事就是被清空,设为null
Inout 数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返回给外部

-- 声明结束符
-- 创建存储过程
DELIMITER $ -- 声明存储过程的结束符
CREATE PROCEDURE pro_test() --存储过程名称(参数列表)
BEGIN
-- 可以写多个sql语句; -- sql语句+流程控制
SELECT * FROM employee;
END $ -- 结束 结束符

-- 执行存储过程
CALL pro_test(); -- CALL 存储过程名称(参数);

-- 删除存储过程
DROP PROCEDURE pro_test;

参数：
IN： 表示输入参数，可以携带数据带存储过程中
OUT： 表示输出参数，可以从存储过程中返回结果
INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

测试：
1、带输入参数
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT) -- IN: 输入参数
BEGIN
SELECT * FROM test01 WHERE id=eid;
END $

2、带输出参数
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) -- OUT：输出参数
BEGIN
-- 给参数赋值
SET str='hellojava';
END $

3、带输入输出的存储过程
DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT： 输入输出参数
BEGIN
-- 查看变量
SELECT n;
SET n =500 + n;
END $

4、带有条件判断的存储过程
DELIMITER $
CREATE PROCEDURE pro_testIf(in nu INT,out str VARCHAR(20)) -- if 输入输出参数
BEGIN
IF nu = 1 then
set str = "周一";
ELSEIF nu = 2 THEN
set str = "周二";
ELSE
set str = "错误";
END IF;
END $

触发器：
触发器是一种特殊类型的存储过程，它又不同于存储过程，
触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用
作用：
1.可在写入数据表前，强制检验或转换数据
2.触发器发生错误时，异动的结果会被撤销
格式
DELIMITER //
Create trigger 触发器名字 触发时机 触发事件 on 表 for each
row
Begin
操作的内容
End //
DELIMITER ;
触发对象 ：on 表 for each row 触发器绑定实质是表中的所有行，因此当每一行发生改变的时候，就会触发触发器
触发时机：每张表中对应的行都会有不同的状态，当SQL 指令发生的时候，
都会令行中的数据发生改变，每一行总会有两个状态。操作数据之前(before)，操作数据(after)之后
触发事件:
Mysql中触发器针对的目标是数据发生改变，对应的操作只有（增，删，改）查询不发生数据的改变，
所以查询没有触发事件
注意事项:
一张表中，每一个触发器时机绑定的触发事件对应的触发器类型只能有一个；
一张表中只能有一个after insert 触发器 因此，一张表中最多的触发器只能有六个

案列
-------------------------------创建存储过程---------------------------

DELIMITER //
CREATE PROCEDURE addUser
(IN uCode VARCHAR(50),IN uName VARCHAR(20),IN uRole INT,IN sex INT,IN tel VARCHAR(30))
BEGIN
INSERT INTO smbms_user (userCode,userName,userRole,gender,phone)
VALUES(uCode,uName,uRole,sex,tel);
END//
DELIMITER //

查看存储过程 show procedure status;

java代码：

<insert id="saveUser">
CALL addUser(#{userCode},#{userName},#{userRole},#{gender},#{phone})
</insert>

public int saveUser(
@Param("userCode") String userCode,
@Param("userName") String userName,
@Param("userRole") Integer userRole,
@Param("gender") Integer gender,
@Param("phone") String phone);

public List<User> findUserListPage(String queryUserName, 
Integer queryUserRole, 
Integer currentPageNo, Integer pageSzie);


public boolean saveUser(String userCode, String userName, Integer userRole,
Integer gender, String phone) {
SqlSession sqlSession = null;
int row = 0; // 受影响的行数
try {
sqlSession = MyBatisUtil.createSqlSession();
row = sqlSession.getMapper(UserMapper.class).saveUser(userCode, userName, userRole, gender, phone);
// 提交事务
sqlSession.commit();
} catch (Exception e) {
if (sqlSession != null) {
sqlSession.rollback();
}
row = 0;
e.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
if (row > 0) {
return true;
}
return false;
}
userService.saveUser("zhangcuishan", "亚索", 1, 2, "15645678941");

---------------------------------------end--------------------------------------------

创建触发器

创建两张表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null,
inv int
)

create table my_orders(
id int primary key auto_increment,
goods_id int not null,
goods_num int not null)

insert into my_goods values(null,'手机',1000),(null,'电脑',500),(null,'游戏机',100);

DELIMITER //
CREATE TRIGGER a_i_o_t AFTER INSERT ON my_orders FOR EACH ROW
BEGIN
UPDATE my_goods SET inv =inv -new.goods_num WHERE id=new.goods_id;
END
//
DELIMITER ;


DELIMITER //
CREATE TRIGGER b_i_o_t BEFORE INSERT ON my_orders FOR EACH ROW 
BEGIN 
SELECT inv FROM my_goods WHERE id=new.goods_id INTO @inv;
IF @inv <new.goods_num THEN 
INSERT INTO xxx VALUES('xx');
END IF;
END 
//
DELIMITER //

测试 insert into my_orders values(null,3,5);

面试相关：
1.存储过程和函数

存储过程重在处理数据，函数可以返回值。
（1）存储过程是procedure用户定义的一系列sql语句的集合，涉及特定表或其他对象的任务，用户可以调用存储过程。
（2）函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。
（3）可以理解函数是存储过程的一种，都是预编译的（块语句每次运行都会编译 存储过程块 一次编译多次运行 效率更高）
plsql块语句：
Begin
End
存储过程块
Create procedure prg_add()
As
Begin
End；
（4）函数可以没有参数，但是一定要有一个返回值，存储过程可以没有参数，不需要返回值。
（5）函数return返回值没有返回参数模式，存储过程通过out参数返回值，如果需要返回多个参数则建议使用存储过程（函数oracle 在函数可以使用in和out mysql不能使用out）
（6）在sql数据操纵（DML）语句中只能调用函数而不能调用存储过程

2.存储过程的概念，优点（或特点），写一个简单的存储过程

存储过程：是一组为了完成特定功能的SQL语句集,利用SQL Server所提供的T-SQL语言所编写的程序,经编译后存储在数据库中。
优点：
（1）执行速度快，存储过程只在创建时进行编译，以后每次执行不需要再重新编译，一般sql语句每执行一次就编译一次
（2）存储过程可重复使用
（3）安全性高（可设定只有某些用户才具有对指定存储过程的使用权）
（4）当对数据库进行复杂操作时，可完成复杂的判断和比较复杂的运算，可用存储过程封装起来
（5）易于维护和集中控制，当企业规则变化时在服务器中改变存储过程即可。无需修改 应用程序。
简单的存储过程：
create proc select_query @year int
as
select * from tmp where year=@year

3.触发器

（1）触发器：触发器可以看成是一个特殊的存储过程，存储过程是要显示调用去完成，而触发器可以自动完成。比如：当数据库中的表发生增删改操作时，对应的触发器就可以执行对应的PL/SQL语句块
（2）作用：维护表的完整性，记录表的修改来审计表的相关信息
分为：
DML触发器：当数据库服务器中发生数据操作语言事件时执行的存储过程，分为：After触发器和instead of触发器
DDL触发器：特殊的触发器，在响应数据定义语言（DDL）语句时触发，一般用于数据库中执行管理任务。DDL触发器是响应create、after、或drop开头的语句而激活
触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高

存储函数：
什么是存储函数: 封装一段sql代码，完成一种特定的功能，返回结果。
存储函数的语法:
　　create function 函数([函数参数[,….]]) Returns 返回类型
　　Begin
　　　　If(
　　　　　　Return (返回的数据)
　　　　Else
　　　　　　Return (返回的数据)
　　　　end if;
　　end;
例如: create function count_news(hits int) returns int
　　与存储过程返回参数不同的是存储函数在定义时没用直接声明哪个变量是返回参数，而只是使用了returns声明了返回参数所属的数据类型，返回参数是在函数体中使用return返回要返回的数据变量的形式来表示的。这就需要注意的是：
存储函数只支持输入参数，并且输入参数前没有IN或INOUT.
存储函数中的限制
流控制（Flow-of-control）语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.
变量声明(DECLARE)以及指派(SET)是合法的.
允许条件声明.
异常处理声明也是允许的.
但是在这里要记住函数有受限条件:不能在函数中访问表.因此在函数中使用以下语句是非法的。
ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE

存储函数与存储过程的区别
一、 存储函数有且只有一个返回值，而存储过程不能有返回值。
二、 函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
三、 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工 作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
四、 存储过程可以调用存储函数。但函数不能调用存储过程。
五、 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。