MySQL存储过程总结

存储过程

• 概念：事先经过编译并存储在数据库的一段SQL语句集合

• 创建存储过程

  delimiter $	#声明分隔符为$,因为sql语句中可能会有分隔符;，而；出现会导致SQL认为编辑结束，就会执行
  create procedure procedure_name([proc_parameter[...]])
  begin
  	sql语句
  end$
  

• 调用储存过程

  call procedure_name()
  

• 查看存储过程

  #第一种方式
  select 'name' from mysql.proc where db='db_name';
  
  #第二种方式
  show procedure status\G;
  
  #查看存储过程的定义
  show procedure procedure_name;
  
  

• 删除存储过程

  drop procedure procedure_name;
  

语法

• 存储过程是可以编程的，意味着可以使用变量，表达式，控制结构来完成比较复杂的功能

• 变量declare var_name type [default value]，该变量只能作用在begin…end块中

• 赋值

  #第一种
    set var_name=xxx
   #第二种
   select xxx into var_name from tb_name;#从表中选择xxx赋值给var_name
  

• if 条件判断

  if serch_condition then
  	statement_list
  [elseif serch_condition then
  	statement_list]
  [else serch_condition then
  	statement_list]
  
  #举例
  delimiter $
  create procedure procedure_test01()
  begin
  	declare height int default 175;
  	declare description varchar(50) default '';
  	if height >=180 then
  		set description = '身材高挑';
  	elseif height >= 170 and height <180 then
  		set description = '身材良好';
  	else 
  		set description = '一般身材';
  	select concat('身高',height,'对应的身材类型'，description);
  	end if；
  end$
  
  #调用
  call procedure_test01()$
  

• 传递参数create procedure procedure_name([in/out/inout] 参数名 参数类型)

  #in
  delimiter $
  create procedure procedure_test02(in height int)
  begin
  	#declare height int default 175;
  	declare description varchar(50) default '';
  	if height >=180 then
  		set description = '身材高挑';
  	elseif height >= 170 and height <180 then
  		set description = '身材良好';
  	else 
  		set description = '一般身材';
  	select concat('身高',height,'对应的身材类型'，description);
  	end if；
  end$
  
  #调用
  call procedure_test02(190)$
  

  out

  delimiter $
  create procedure procedure_test03(in height int,out description varchar[(20)])#[]代表可选项，可写可不写
  begin
  	#declare height int default 175;
  	#declare description varchar(50) default '';
  	if height >=180 then
  		set description = '身材高挑';
  	elseif height >= 170 and height <180 then
  		set description = '身材良好';
  	else 
  		set description = '一般身材';
  	#select concat('身高',height,'对应的身材类型'，description);
  end if；
  end$
  
  #调用
  call procedure_test03(171,@description)$ #@代表是用户会话变量，类似于全局变量
  select @description
  

  case

  create procedure procedure_test04(in mon int)
  begin
  	declare resault varchar(10);
  	case
  		when mon>=1 and mon<=3 then
  			set resault = '第一季度';
  		when mon>=4 and mon<=6 then
  			set resault = '第二季度';
  		when mon>=7 and mon<=9 then
  			set resault = '第三季度';
  		when mon>=10 and mon<=12 then
  			set resault = '第四季度';
  	end case;
  	select concat('查询结果为：',resault) as context; #起个别名contexxxt
  end$
  
  #调用
  call procedure_test04(4)
  

• while循环

  #计算从1加到n的值--累加
  create procedure procedure_test05(n int)
  begin
  	declare total int default 0;
  	declare num int default 1;
  	while num <= n do
  		set total = total +num;
  		set num = num+1;
  	end while;
  	select total;
  end$
  

• repeat结构

  repeat
  	statement
  	until serch_condition
  end repeat;
  
  
  示例
  create procedure procedure_test06(n int)
  begin
  	declare total int default 0;
  	
  	repeat 
  		set total = total +n;
  		set n = n-1;
  		until n=0
  		
  	end repeat;
  	select total;
  end$
  

• loop 与 leave 结构

  create procedure procedure_test07(n int)
  begin
  	declare total int default 0;
  	
  	c:loop #c为给loop起的别名
  		set total = total +n;
  		set n = n-1;
  		if n<=0 then
  			leave c;
  		end if;
  		
  	end loop c;
  	select total;
  end$
  

• 游标

  #声明光标
  declare cursor_name cursor for select_statement;
  
  #open 光标
  open cursor_name;
  
  fetch 光标
  fetch cursor_name into var_name[,var_char,..];
  
  close 光标
  close cursor_name;
  
  
  #示例
  create procedure procedure_test08()
  	begin
  		declare e_id int(10),
  		declare e_name varchar(30),
  		declare e_age int(10),
  		declare e_salary int(8),
  		declare cursor_test01 cursor from select * from emp; #emp为表
  		open cursor_test;
  		fetch cursor_test into e_id,e_name,e_age,e_salary; #注意：有多少条记录就fetch多少次
  		#如果fetch的条数小于查询到的记录数，那么在下面的select中只会先择相应的fetch条数
  		#如果fetch条数大于查询到的记录数，那么显示完相应的fetch条数后，在下面的select中会报错
  		close cursor_test;
  		select concat('id'=e_id,'name'=e_name,'age'=e_age,'salary'=e_salary);
  	end$
  #上面列子由于不知道查询的结果数，所以改进一下
  create procedure procedure_test08()
  	begin
  		declare e_id int(10),
  		declare e_name varchar(30),
  		declare e_age int(10),
  		declare e_salary int(8),
  		declare has_data int default 1
  		
  		declare cursor_test01 cursor from select * from emp; #emp为表
  		DECLARE EXIT HANDLE FOR NOT FOUND set has_data = 0;#必须写在声明游标之后
  		
  		
  		
  		open cursor_test01;
  		
  		repeat
  			fetch cursor_test01 into e_id,e_name,e_age,e_salary; 
  			select concat('id'=e_id,'name'=e_name,'age'=e_age,'salary'=e_salary);
  			until has_data = 0
  		end repeat;
  		
  		close cursor_test01;
  		
  	end$
  

存储函数

• 语法结构

  create function function_name([param type...]);
  RETURNS type
  begin
  	...
  end$
  

• 实例：定义一个函数，满足查询（city表）的总记录条数

  create function function_test01(countryId int_);
  RETURNS	int
  begin
  	declare cnum int;
  	select count(*) into cnum from city where country_id=countryId;
  	return cnum
  end$
  
  #调用
  select function_test01(3)
  

• 删除函数

  drop function function_name