sql之视图、触发器、函数、存储过程、事务
视图
# 视图也是一张表，但在data文件里只有表结构，没有表数据
# 不建议使用，扩展性差，程序需改变时，依赖的视图也要改变
# 视图牵涉到多张表时，视图中的记录不能修改。
create view course2teacher as select * from course inner join teacher on course.teacher_id = teacher.tid
alter view
select * from course2teacher
触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
create trigger tri_before_insert_tb1 before insert on tb1 for each row
上图中，delimiter //将原本 ；号的结束符改为//，语句写完后，又换成 ；结束符。
NEW 表示即将插入的数据行，OLD表示即将删除的数据行。
使用触发器：无法由用户直接调用，而是由于对表的增删改操作被动引发。
删除触发器：drop triggeer tri_after_insert_cmd。
触发器的逻辑尽量在应用程序里写，否则还要找数据库人员修改触发器。
函数
内置函数，写在select后面，有char_length()，date_format()等
自定义函数，
create function f1(
t1 int,
t2 int)
return int
BEGIN
declare num int;
set num = t1+t2;
return num;
END //
delimiter ;
存储过程
# 无参存储过程
delimiter //
create procedure p1()
BEGIN
select * from db1.teacher;
END //
delimiter ;
show create procedure p1; # 查看存储过程
# 在MySQL中调用
call p1();
# 在Python中调用
cursor.call_proc('p1')
# 有参存储过程
delimiter //
create procedure p2(
in n1 int,
in n2 int,
out res int , # 用于标识执行结果
inout n3 int)
BEGIN
select * from db1.teacher where tid > n1 and tid < n2;
set res = 1; # res是out伪造的返回值，如果要取select语句返回的表格，需从@_p2_0和@_P2_1中取
# 为什么有结果集又有out伪造的返回值？
END //
delimiter ;
# 在MySQL中调用
set @x=0 # 给session级别的变量设值
call p2(2,4,@x);
select @x;
# 在Python中调用
cursor.call_proc('p2',(2,4,0)) #@_p2_0 = 2,@_P2_1=4,
拿返回值需再进行一次查询
cursor.execute('select @_p2_0,@_P2_1,@_p2_2')
cursor.fetchone()
有了存储过程后，真正实现了应用程序和数据库的集成开发。
应用程序和数据库结合的三种方式：
方式一：只需通过网络发存储过程名
Python调用存储过程
MySQL编写存储过程
方式二：
Python编写原生SQL语句
方式三：
Python用ORM框架生成SQL语句
事务
事务常被用于存储过程中
事务用于将某些操作的多个sql作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
delimiter //
create procedure p4(
out status int
)
BIGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END
start transaction;
update user set balance = 900 where name='wdb'; # 购买支付100元
user set balance = 1010 where name='egon' # 商家增加10元
commit;
--SUCCESS
set p_return_code = 2;
END //
delimiter ;
通过游标实现循环的存储过程
delimiter //
create procedure p6()
begin
declare row_id int;
declare row_num varchar(50);
declare done int default false;
declare my_cursor cursor for select id,num from A;
declare continue handler for not found set done = true;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
end if;
set temp = row_id + row_num
insert into B(num) values(temp);
end loop xxoo;
close my_cursor;
end //
delimiter ;
动态执行sql，（防sql注入）
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1、预检测sql语句合法性
2、sql = 格式化 tpl + arg
3、执行sql语句
set @xo = arg;
prepare xxx from 'select * from student where sid >?';
execute xxx using @xo;
deallocate prepare prod;
end //
delimiter ;
