一:
存储过程的优点:
1.允许模块化程序设计,也就是说只需要创建一次,以后程序就可以调用无数次。
2.执行速度更快
3.当执行的sql语句上百行时,使用存储过程只需一条代码,减少网络流量。
4.有更安全的机制
什么是事务:
原来的sql语句是执行并提交的,当我们希望多个数据库操作作为一个整体,一起执行一起提交时,就可以使用事务。事务下的sql语句执行了并不提交(只针对增删改操作)。
存储过程和事务结合的例子代码(模拟银行转账)
(在这里写事务的原因:银行转账可能存在数据安全漏洞,转账后背后操作其实就是对数据的更改,当同时更改两条账户数据时,一条执行成功,而一条失败,那么就会造成数据的失实。这里以bank数据库为例子)
drop procedure if exists proc_transfer;
create procedure proc_transfer
(
in from_account varchar(32),
in to_account varchar(32),
in amount int
)
begin
declare errCount int default 0;
#创建一个异常处理器,并规定当发生异常时执行的代码
declare continue handler for SQLEXCEPTION
set errCount = errCount+1;
#事务部分(4步走)
set autocommit=0; #1.取消自动提交功能
update bank #2.执行sql语句
set balance=balance-amount
where account=from_account;
update bank
set balance=balance-amount
where account=to_account;
if errCount > 0 then #3.commit/rollback
select 'have a error in your sql, rollback!' as 结果;
rollback;
else
select 'the order is successful' as 结果;
commit;
end if;
set autocommit=1; #4.恢复自动提交功能
end;
#调用
call proc_transfer('159357','159358',100);
#注:存储过程放的参数可以是输入参数也可以是输出参数(in / out)
输入参数在调用的时候放入实参,而输出参数在调用的时候接收结果。同常用@开头。
#上面讲了if选择结构,这里穿插进循环while的结构代码
declare i int default 0;
while i<500 do
insert into bank (id, `name`, balance)
values(null,concat('GB',i),100);
set i=i+1; #迭代部分
end while;
(通常也是在存储过程使用)
二:
触发器:原先设定好的,当某个事件被执行的时候触发调用的一段代码。事件指增删改。
mysql中没有check约束,而触发器的功能就是相当于SQL server的check约束。
drop trigger if exists trig_bank_before_insert;
create trigger trig_bank_before_insert(触发器名) before insert(触发操作)
on bank(表名)
for each row
begin
#触发器执行代码
if new.balance<1 then
signal sqlstate 'TX000'
set message_text='余额不能小于1元';
end if ;
if new.balance>10000000 then
signal sqlstate 'TX000'
set message_text='余额不能大于10000000 元';
end if ;
end;