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 row
	begin
		insert into B(bname) values(new.aname);
	end//
	delimiter ;
	
-- 在A表中删除数据时，同时往B表中插入删除的数据
	delimiter //
	create trigger t1 before delete on A for each row
	begin
		insert into B(bname) values(old.aname);
	end//
	delimiter ;
-- new 新创建的数据 new.aname表示将插入A表中的数据的名字插入到B表中
-- old 之前已经存在的数据 （用于删除）
-- 删除触发器
	drop trigger t1;

3、函数

-- 创建函数（不带参数）
	delimiter //
	create function f1() returns int
	begin
		declare total int default 0;
		set total = 1 + 1;
		return (total);
	end //
	delimiter ;
-- 调用f1函数
	select f1();
	
-- 创建函数（带参数）	
	delimiter //
	create function f2(a int, b int) returns int
	begin
		declare 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()
	begin
		select * from A;
		select * from B;
	end //
	delimiter ;
-- 调用p1
	call p1();
	
-- 创建存储过程（带有参数）
-- in（只能用于传入参数）
	delimiter //
	create procedure p2(
		in a int,
		in b int)
	begin
		select * from A where id > a;
		select * from B where id = b;
	end //
	delimiter ;
-- 调用p2
	call p2(1, 3)
	
-- out（只能用于返回值）
	delimiter //
	create procedure p3(
		in a int,
		out b int)
	begin
		select * 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)
	begin
		select * from A where id > a;
		select * from B where id = c;
		set b = 3;
		set c = 5;
	end //
	delimiter ;
-- 调用P4
	set @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
	)
	begin
		declare exit handler for sqlexception
		begin
			set 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()
	begin
		declare 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: loop
			fetch my_cursor into row_id,row_num;
			if done then
				leave f;
			end if;
			set temp = row_id + row_num;
			insert into B(number) values(temp);
		end loop f;
	close my_cursor;
	end //
	delimiter ;

-- 调用p6
	call p6();

4、动态执行SQL

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