[[数据库]] MySQL的存储过程、事务、游标、动态执行SQL

Chapter5 MySQL的存储过程、事务、游标、动态执行SQL

一、存储过程

#存储过程的实质是一坨SQL语句，存储在MySQL服务端，客户端只需要知道存储过程的名字
#实现数据库操作的方式有二，其一是MySQL级别储存存储过程+程序级别调用存储过程；其二是MySQL什么也不做+程序级别纯写SQL语句

1.简单
	delimiter//
	create procedure p1()
	BEGIN
		select * from student;
		insert into teacher(tname) values("ct");
	END //
	delimiter;
	使用：call p1()

2.传参数(in,out,inout)
	a.in
	#in是接受传入，传入后，在存储过程中修改值也不会传出
		delimiter //
		create procedure p2(
			in n1 int,
			in n2 int
		)
		BEGIN
			select * from student where sid>n1;
		END //
		delimiter ;
		使用：call p2(12,2)

	b.inout
	#out传的是个引用值，修改后以后可以查看
		delimiter //
		create procedure p3(   #假设在这写上print(n2)
			in n1 int,
			inout n2 int  #初始化全局变量后调用该存储过程，out无打印值，inout有
 		)
		BEGIN
			set n2=123123;
			select * from student where sid>n1;
		END//
		delimiter;
	
		set @v1=10;   #全局变量v1，session级别
		call p2(12,@v1)  #v1的值被修改
		select @v1;   #查看修改后的v1值


	#存储过程的特性：函数有return返回值，而存储过程没有实际的返回值，只有通过out伪造的返回值，为什么需要out伪造一个返回值？
	
	c.out
	#out伪造的返回值用于标识存储过程的执行结果（检查）
		delimiter//
		create procedure p3(
			in n1 int,
			out n2 int  #如果存储过程是一系列insert（无输出值），就不知道插入成功与否，而n2的值可以取1，2，3……，1表示执行成功，2表示局部成功，3表示执行全部失败等。
		)
		BEGIN
			insert into vv(..)
			insert into vv(..)
			insert into vv(..)
			insert into vv(..)
			insert into vv(..)
			insert into vv(..)
			set n2=1
		END//
		delimiter;
		

二、事务

#事务，即错误回滚，成功提交
delimiter//
	create procedure p5(
		out p_return_code tinyint
	)
	
	BEGIN   #如果出现异常
		declare exit handler for sqlexception
		BEGIN
			set p_return_code=1;
			rollback;
	END;

	START TRANSACTION   #开始事务
		delete from tb1;
		insert into tb2(name) values('seven');
	COMMIT;
	
	set p_return_code=2;    #成功标志

END//
delimiter;

三、游标

#游标，用SQL语句实现循环。但能不用游标就不要用，除非要对每一行数据分门别类地操作
#假如想把A表的值导入应用于B表
	(A)	id   num                 (B) id   num
		1     9						  1   9+1
		2     8						  2   8+2

delimiter//
	create procedure p6()
	BEGIN
		declare row_id int;
		declare row_num int;
		declare done int default false;
		declare temp int;	
		declare my_cursor CURSOR for select id,num from A; #创建/声明游标,名称my_cursor 类型CURSOR 关键字for
		declare continue handler for not found set done=true;#当游标内无值时，令done=true
		
		open my_cursor;    #打开游标
			xxoo:LOOP  #随便取名，开始循环
				 fetch mu_cursor into row_id,row_num; #用游标去拿每一行的数据
				 if done then  #如果游标内没有值了，则跳出循环 
				 	leave xxoo;
				 end if;
				 set temp = row_id + row_num;  #让id和num相加
				 insert into B(number) values(temp);  #插入B表
			end loop xxoo;
		close my_cursor;    #关闭游标
END//
delimiter;

四、动态执行SQL

#SQL注入：常见的网络攻击形式。程序没有检测用户输入数据的合法性，攻击者可以在查询语句的末尾添加额外sql语句。
#防SQL注入的工作可以在程序级别做，也可以在数据库级别做

delimiter //
	create procedure p8(
		in nid int
	)

	BEGIN
		set @nid=nid;   # 随便取名
		prepare prod from 'select * from student where sid > ?';
		execute prod using @nid;  #用这种引用型变脸替代上一行中的?
		deallocate prepare prod;  #执行已格式化的SQL语句
	END//
delimiter;