MySQL的视图、函数、存储过程、动态执行SQL

视图、函数、存储过程、动态执行SQL"/>

MySQL的视图、函数、存储过程、动态执行SQL

1、视图

-- 创建视图crate view v1 as select * from A where id > 1;
-- 删除视图drop view v1;
-- 修改视图alter view v1 as select * from A where id > 2;
-- 通过调用视图来查询select * from v1;
-- 说明当在A表中更新了数据时，视图也会跟着更新数据，反之亦然。

2、触发器

-- 创建触发器
--往A表中添加数据时，同时也会在B表插入对应数据delimiter //create trigger t1 before insert on A for each rowbegininsert into B(bname) values(new.aname);end//delimiter ;-- 在A表中删除数据时，同时往B表中插入删除的数据delimiter //create trigger t1 before delete on A for each rowbegininsert into B(bname) values(old.aname);end//delimiter ;
-- new 新创建的数据 new.aname表示将插入A表中的数据的名字插入到B表中
-- old 之前已经存在的数据 （用于删除）
-- 删除触发器drop trigger t1;

3、函数

-- 创建函数（不带参数）delimiter //create function f1() returns intbegindeclare total int default 0;set total = 1 + 1;return (total);end //delimiter ;
-- 调用f1函数select f1();-- 创建函数（带参数）	delimiter //create function f2(a int, b int) returns intbegindeclare total int default 0;set total = a + b;return (total);end //delimiter ;
-- 调用f2函数select f2(1,3)
-- 删除函数drop function f1;

3、存储过程

-- 创建存储过程(不带参数）delimiter //create procedure p1()beginselect * from A;select * from B;end //delimiter ;
-- 调用p1call p1();-- 创建存储过程（带有参数）
-- in（只能用于传入参数）delimiter //create procedure p2(in a int,in b int)beginselect * from A where id > a;select * from B where id = b;end //delimiter ;
-- 调用p2call p2(1, 3)-- out（只能用于返回值）delimiter //create procedure p3(in a int,out b int)beginselect * from A where id > a;set b = 3;end //delimiter ;
-- 调用p3-- 设置变量（会话级别）set @v1 = 1;call p3(5, @v1);-- 查询@v1的值select @v1;-- inout（既能用于传入参数，又能返回值）delimiter //create procedure p4(in a int,out b int,inout c int)beginselect * from A where id > a;select * from B where id = c;set b = 3;set c = 5;end //delimiter ;
-- 调用P4set @v1=5, @v2=7;call p4(8, @v1, @v2);查看更改后@v1和@v2的值select @v1,@v2;-- 事务delimiter //create procedure p5(in num int,out return_code tinyint)begindeclare exit handler for sqlexceptionbeginset return_code = 1;rollback;end;start transaction;delete from b where id = num;insert into b(number) values(num);commit;set return_code = 0;end//delimiter ;
-- 调用set @v1=0;call p5(15,@v1);select @v1;
-- 基于位置传参set @_p5_0=32,@_p5_1=0;call p5(@_p5_0,@_p5_1);select @_p5_1;-- 游标delimiter //create procedure p6()begindeclare row_id int;declare row_num int;declare temp int;declare done int default 0;declare my_cursor cursor for select sid,class_id from student order by sid;declare continue handler for not found set done = 1;open my_cursor;f: loopfetch my_cursor into row_id,row_num;if done thenleave f;end if;set temp = row_id + row_num;insert into B(number) values(temp);end loop f;close my_cursor;end //delimiter ;-- 调用p6call p6();

4、动态执行SQL

-- 动态执行SQL（防SQL注入）delimiter //create procedure p7(in num int)beginset @num = num;prepare s from 'select * from student where sid > ?';execute s using @num;deallocate prepare s;end//delimiter ;
-- 调用p7call p7(5);