14 MySQL 从入门到精通——存储过程与存储函数

实例库：
DROP TABLE IF EXISTS `studentinfo`;
CREATE TABLE `studentinfo` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` int(1) DEFAULT '1',
  `tel` varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `studentinfo` VALUES ('1', '小明', '18', '1', '');
INSERT INTO `studentinfo` VALUES ('2', '小蓝', '18', '1', '');

存储过程与存储函数：是指在数据库中定义一些SQL语句的集合，然后可以直接调用这些存储过程和存储函数来执行SQL语句，类似于ptyhon函数、shell函数

创建存储过程和存储函数
	创建存储过程
		Usage：
			CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
				#sp_name：存储过程名称，不区分大小写
				#proc_parameter（存储过程的参数列表）:由三部分组成
					Usage：[IN|OUT|INOUT]param_name type
					[IN|OUT|INOUT]:输入参数|输出参数|即可以输入也可以输出
					param_name：参数名称
					type：参数类型，可以为MySQL数据库中任意类型
				#characteristic：指定存储过程的特性
				#routine_body：SQL代码的内容，可以用BEGIN和END来标识SQL代码的开始和结束。
			补充：
				由于存储过程内部语句要以分号结束，所以在定义存储过程前，应将语句结束标志“；”更改为其他字符
			  		示例：delimiter //
			  	声明变量：关键字declare
			  		示例：declare vari)
		示例1（创建存储过程）：
			delimiter //
			create procedure proc_name(in parameter int)
			begin
			declare variable varchar(20);
			if parameter = 1 then
			set variable = 'MySQL';
			else
			set variable = 'PHP';
			end if;
			insert into tb(name) values(variable);
			end;
			//
		示例2：建一个名称为count_of_student的存储过程，统计studentinfo数据表中的记录数
			delimiter //
			create procedure count_of_student(OUT count_num INT)
			reads sql data	#读取sql语句
			begin
			select count(*) into count_num from studentinfo; #从studentinfo查看行数并赋值给count_num
			end	
			//	
			call count_of_student(@total)//	#调用存储过程count_of_student，并赋值给变量total
			select @total//	#查看变量total 	 		
						
	创建存储函数
		Usage：
			CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
				#RETURNS type  返回函数的类型
		补充	：log_bin_trust_function_creators：MySQL中的此参数控制是否可以信任存储函数创建者，如果设置为0（默认值），用户不得创建或修改存储函数；如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。所以创建函数时需要进行设置
			SET global log_bin_trust_function_creators = 1 
		示例：
			create function name_of_student(std_id INT)
			returns varchar(50)	#定义返回参数类型
			begin
			return(select name from studentinfo where sid=std_id);
			end
			//
							
	变量的应用
		局部变量：BEGIN…END范围内有效，关键字 DECLARE
			Usage：
				DECLARE var_name[,…] type [DEFAULT value]
			示例：
				DROP PROCEDURE IF EXISTS `p1`;
				DELIMITER ;;
				CREATE DEFINER=`root`@`%` PROCEDURE `p1`()
				begin
				declare x char(10) default 'outer ';
				begin
				declare x char(10) default 'inner ';
				select x;
				end;
				select x;
				end
				;;
				DELIMITER ;
				call p1();
		全局变量：整个存储过程范围内有效,关键字set 
			Usage：
				SET var_name=expr[,var_name=expr]…					
			示例：
				DROP PROCEDURE IF EXISTS `p2`;
				DELIMITER ;;
				CREATE DEFINER=`root`@`%` PROCEDURE `p2`()
				begin
				set @t=1;
				begin
				set @t=2;
				select @t;
				end;
				select @t;
				end
				;;
				DELIMITER ;	
				call p2();	#生效的是最后一个全局变量
			注意：在存储过程或存储函数做变量运算时，局部变量可以直接运算，例如set t1 = t1	+1 ,全局变量需要带上@，例如 set @t2=@t2+1
			
			为变量赋值
				Usage：
					SELECT col_name[,…] INTO var_name[,…] FROM table_name WHERE conditionvar_name=expr[,var_name=expr]…
				示例：
					SELECT price INTO book_price FROM tb_bookinfo WHERE barcode= '9787115418425'; #把查询到的price赋值给变量book_price
						
	光标（游标）的运用
		作用：可以逐条读取结果集中的记录，并且不能单独执行，只能在函数中使用
		声明光标
			Usage：
				DECLARE cursor_name CURSOR FOR select_statement
			示例：
				DECLARE cursor_book CURSOR FOR SELECT
				barcode,bookname,price 
				FROM tb_bookinfo 
				WHERE typeid=4;			
		打开光标
			Usage：
				OPEN cursor_name
		使用光标
			Usage：
				FETCH  cursor_name INTO var_name[,var_name]…
			示例：
				FETCH cursor_book INTO tmp_barcode,tmp_bookname,tmp_price;
		关闭光标
			Usage：
				CLOSE  cursor_name
		存储过程示例：
			select * from studentinfo;	#执行前测试
			delimiter //
			create procedure proc_cur() begin declare user_id int;
			declare user_name varchar(200);
			declare done int;
			declare cur_test cursor for select sid as user_id,name as user_name from studentinfo; #创建游标并存储数据
			declare continue handler for not found set done=1;	#游标中的内容执行完后将done设置为1
			open cur_test;	#打开游标
			posLoop:LOOP	#执行循环
			IF done=1 THEN
			LEAVE posLoop;
			END IF;
			FETCH cur_test into user_id,user_name;	#取游标中的值并赋值给user_id,user_name
			update studentinfo set name = CONCAT(user_id,"-",user_name) where sid=user_id;	#执行更新操作
			END LOOP posLoop;	
			CLOSE cur_test;	#关闭游标
			END
			//
			delimiter ;
			call proc_cur();
			select * from studentinfo;	#执行后验证										
			
存储过程和存储函数的调用
	调用存储过程：
		Usage：
			CALL sp_name([parameter[,…]]);
	调用存储函数
		Usage：
			SELECT function_name([parameter[,…]]);
查看存储过程和函数
	SHOW STATUS语句：只能查看储过程或函数的名称、类型、定义者、修改时间等信息
		Usage：
			SHOW {PROCEDURE | FUNCTION}STATUS[LIKE 'pattern']
		示例：
			show procedure status like 'proc_cur%';
	SHOW CREATE语句：查看详细定义
		Usage：
			SHOW CREATE{PROCEDURE | FUNCTION } sp_name;
		示例：
			show create procedure  proc_cur;
			
修改存储过程和函数
	Usage：
		ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
			characteristic:
				{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
				| SQL SECURITY { DEFINER | INVOKER }
				| COMMENT 'string'	
				#CONTAINS SQL:表示子程序包含SQL语句，但不包含读写数据的语句
				#NO SQL：表示子程序不包含SQL语句
				#READS SQL DATA：表示子程序包含读数据的语句
				#MODIFIES SQL DATA：表示子程序包含写数据的语句
				#SQL SECURITY { DEFINER | INVOKER}：指明权限执行，DEFINER表示只有定义者才能够执行；INVOKER表示调用者可以执行
	示例	：
		alter procedure count_of_student modifies sql data sql security invoker;
删除存储过程和函数
	Usage：
		DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name