mysql存储过程存储函数

注：存储过程和函数的区别：存储过程没有返回值。
1、存储过程：
格式：create procedure sp_name([proc_parameter[,...]])[characteristic...] routine_body
注：1、sp_name参数是存储过程的名称；
  2、proc_parameter表示存储过程的参数列表，此参数由3部分组成：输入输出类型、参数名称、参数类型。形式如：[in|out|inout]param_name type。其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；
  3、characteristic参数指定存储过程的特性，该参数有多个取值如：
	a、language sql：说明routine_body部分是由sql预约的语句组成，这也是数据库系统默认的语言；
	b、[not]deterministic:指明存储过程的执行结果是否是确定的。表示相同的输入是否会得到相同的输出，deterministic表示能得到，默认是不确定的。
	c、{contains sql|no sql|reads sql data|modifies sql data}:指明子程序使用sql语句的限制。contains sql表示子程序包含sql语句，但不包含读或写数据的语句；no sql表示子程序中不包含sql语句；reads sql data表示子程序中包含读数据的语句；modifies sql data表示子程序中包含写数据的语句。默认情况下，系统指定为contains sql。
	d、sql security{definer|invoker}：指明谁有权限来执行。definer表示只有定义者自己才可以执行；invoker表示调用者可以执行。默认情况下，系统指定的权限是definer。
	e、comment 'string':注释信息。
  4、routine_body参数是sql代码的内容，可以用begin...end来标志sql代码的开始和结束。
例：
mysql>delimiter &&
mysql>create procedure num_from_employee(int emp_id int,out count_num int)
	reads sql data
	begin
		select count(*) into count_num
		from employee
		where d_id=emp_id;
	end &&
mysql>delimiter ;

2、存储函数：
create function sp_name([func_parameter[,...]])
	returns type
	[characteristic...]routine_body
注：sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；returns type指定返回值的类型；characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的；routine_body参数是sql代码的内容。
例：create function name_from_employee(emp_id int)
	returns varchar(20)
	begin
		return (select name from employee where num=emp_id);
	end;

注：返回值是varchar类型，该函数和mysql内部函数的使用方法一样

3、变量的使用
格式：declare var_name[,...] type [default value]例：declare my_sql int default 10;
注：可以定义多个变量；type参数是用来指定变量的类型；default value子句将变量默认值设为value，没有使用default子句时，默认值为null。declare使用范围只能在begin...end中使用，定义在复合语句的开头。
1)、为变量赋值：set var_name=expr[,var_name=expr]...
2）、还可以用select...into语句为变量赋值。格式：
select col_name[,...] into var_name[,...] from table_name where condition
注：var_name参数是变量名

4、光标的使用
查询语句可以查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。光标使用包括生命光标、打开光标、使用光标和关闭光标。光标要声明在处理程序之前并且声明在变量和条件之后。光标使用后一定要关闭。
1）、声明光标格式：declare cursor_name CURSOR FOR select_statement;注：select_statement参数表示select语句的内容。
2）、打开光标 格式：OPEN cusor_name;
3)、使用光标 格式：FETCH cusor_name INTO var_name[,var_name...];注：var_name必须在声明光标前就定义好。
4）、关闭光标：CLOSE cursor_name; 注：关闭后就不能用fetch来使用光标了。

5、流程控制的使用
if语句、case语句、loop语句、leave语句、iterate语句、repeat语句、while语句
1）、if语句格式：
if search_condition then statement_list
	[elseif search_condition then statement_list]...
	[else statement_list]
end if
注：search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。
例：if age>20 then set @count1=@count1+1;
	elseif age=20 then @count2=@count2+1;
	else @count3=@count3+1;
    end if;
2)、case格式：
格式一、
case case_value
	when when_value then statement_list
	[when when_value then statement_list]...
	[else statement_list]
end case
格式二、
case
	when search_condition then statement_list
	[when search_condition then statement_list]...
	[else statement_list]
end case
3)、loop语句
loop语句可以使某些特点的语句重复执行实现一个简单的循环。但是loop语句本身没有停止循环的语句必须是遇到leave语句等才能停止循环。
格式：[begin_label:]LOOP
	statement_list
       end LOOP [end_label]
注：begin_label参数和end_label参数分别表示循环开始和结束的标志，两个标志必须相同都可以省略。
例：add_num:LOOP
	set @count=@count+1;
    end LOOP add_num;
4）、leave语句
格式：LEAVE label 该语句用于跳出循环控制。注：label表示循环的标志。
例：add_num:LOOP
	set @count=@count+1;
	if @count=100 then
		leave add_num;
    end LOOP add_num;
5)、iterate语句
格式：iterate label 跳出本次循环的语句。注：label表示循环的标志。
6）、repeat语句
格式：[begin_label:]REPEAT
	statement_list
	UNTIL search_condition
      end REPEAT [end_label]
注：statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件。
例：repeat
	set @count=@count+1;
	UNTIL @count=100;
    end repeat;
7)、while语句
格式：[begin_label:]WHILE search_condition DO
	statement_list
      end while [end_label]

6、调用存储过程
存储过程是通过call语句来调用的。而存储函数和mysql内部函数的使用方法是一样的。执行存储过程和存储函数都需要有execute权限。execute权限的信息存储在information_schema数据库下的user_privileges表中。
格式：call sp_name([parameter[,...]]);
例：mysql>delimiter &&
mysql>create procedure num_from_employee(IN emp_id int,OUT conut_num int)
->reads sql data
->begin
->select count(*) into count_num from employee where d_id=emp_id;
->end &&
mysql>delimiter ;
mysql>call num_from_employee(1120,@n);
//查询返回结果
mysql>select @n;
7、查看存储过程和函数
show {procedure|function}status[like 'pattern'];//pattern是存储过程名
show create {procedure|function}sp_name;
1）、存储过程和函数的信息存储在information_schema数据库下的routines表中。可以通过该表的记录查询存储过程和函数的信息。
例：select * from information_schema.Routines where routine_name='sp_name';
注：routine_name字段中存储的是存储过程和函数的名称；sp_name参数表示存储过程或函数的名称。
8、修改存储过程和函数
格式：alter {procedure|function} sp_name [characteristic...]
characteristic:
	{contains sql|no sql|reads sql data|modifies sql data}
	|sql security{definer|invoker}
	|comment 'string'
注：characteristic参数知道存储函数的特性。
例：将存储过程读写权限改为modifies sql data，并指明调用者可以执行。
alter procedure num_from_employee
	modifies sql data
	sql security invoker;
//查询修改后num_from_employee表的信息
mysql>select specific_name,sql_data_access,security_type from information_schema.routines where routine_name='num_from_employee';
+----------------------------+-----------------+---------------+
| specific_name              | sql_data_access | security_type |
+----------------------------+-----------------+---------------+
| num_from_employee          |modifies sql data| invoker       |
+----------------------------+-----------------+---------------+
6 rows in set (0.02 sec)
例：alter function name_from_employee 
	reads sql data
	comment 'find name';
mysql>select routine_comment from information_schema.routines where routine_name='num_from_employee';
+----------------------------+
| routine_comment            |
+----------------------------+
| find name                  |
+----------------------------+
1 rows in set (0.02 sec)
9、删除存储过程和函数
drop {procedure|function} sp_name;

实例：
delimiter &&
create procedure food_price_count(in price_info float,in price_info2 float,out count int)
	reads sql data
	begin
		declare temp float;//定义变量
		declare match_price cursor for select price from food;//定义光标
		declare exit handler for not found close match_price;//定义条件处理。如果没有遇到关闭光标，就退出存储过程。
		set @sum=0;//为临时变量赋值
		select count(*) into count from food where price>price_info1 and price<price_info2;//用select...into语句为输出变量count赋值
		open match_price;//打开光标
		repeat//执行循环
			fetch match_price into temp;//使用光标
			if temp>price_info1 and temp<price_info2
				then set @sum=@sum+temp;
			end if;
		until 0 end repeat;//结束循环
		close match_price;//关闭光标
	end &&
delimtier ;