MySQL 存储过程和函数02

MySQL 存储过程
4）case结构
方法一：
    case value
	    when value then sql语句01
		when value then sql语句02
		else sql语句03
	end case
	
方法二：
    case
	    when 条件语句 then sql语句01
		when 条件语句 then sql语句02
		else sql语句03
	end case
	
--给定一个月份，然后计算出所在的季度

create procedure pro_test7(in month int)
begin
    ......
end;
======================================================

create procedure pro_test7(month int)
begin
  declare result varchar(10);
  case
	when month>=1 and month<=3 then
		set result=‘第一季度‘;
	when month>=4 and month<=6 then
		set result=‘第二季度‘;
	when month>=7 and month<=9 then
		set result=‘第三季度‘;
	else
		set result=‘第四季度‘;
  end case;  
  select concat(‘传递的月份为：‘,month,‘计算出的结果为：‘,result) as content;	
end;

call pro_test7(5)


5)while循环    满足条件继续循环

while 条件语句 do
    --SQL语句
	
end while;

--计算从1加到n的值 -- 累加
create procedure pro_test8(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;

call pro_test8(5)

drop procedure pro_test8;   //删除存储过程

6）repeat循环
while 是满足条件才执行循环，repeat是满足条件就退出循环；

repeat
  --SQL语句;
  until --SQL语句     //此处结束无分号 ；
end repeat;

--计算从1加到n的值------->repeat

create procedure pro_test9(n int)
begin
  declare total int default 0;
  
  repeat
  
  end repeat;

end;
================================================


create procedure pro_test9(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;

call pro_test9(10);

7)loop循环  可实现简单的死循环
    退出循环的条件需要使用其他语句定义 如 leave语句
	
8）leave语句

--计算从1加到n的值------->loop....leave语句

create procedure pro_test10(n int)
begin
  declare total int default 0; 
  c:loop                        //loop循环 需要一个别名 c
  
  end loop c;
  select total;
end;

====================================================================

create procedure pro_test10(n int)
begin
  declare total int default 0; 
  c:loop
    set total = total + n;
    set n = n - 1;

    if n <= 0 then
      leave c;
    end if;	  
  end loop c;
  
  select total;
end;

call pro_test10(4);

9)游标|光标

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用
包括光标的声明、OPEN、FETCH 和 CLOSE

cursor---光标 游标
fetch---请来  拿来 抓取

declare cursor_name cursor for --SQL语句；

open sursor_name;
fetch sursor_name into var_name（变量名） ......;
close cursor_name;


create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment ‘姓名‘,
age int(11) comment ‘年龄‘,
salary int(11) comment ‘薪水‘,
primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,‘金毛狮王‘,55,3800),(null,‘白眉鹰
王‘,60,4000),(null,‘青翼蝠王‘,38,2800),(null,‘紫衫龙王‘,42,1800);

----查询emp表中数据，并逐行获取 进行展示
create pro_test11()
begin
  declare emp_result cursor for select * from emp;
  open emp_result;
  fetch emp_result into ......;
  close emp_result;
end;

------------------------------------------------------
create procedure pro_test11()
begin
  declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
  declare emp_result cursor for select * from emp;
  
  open emp_result;
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat(‘id=‘,e_id,‘name=‘,e_name,‘age=‘,e_age,‘薪资为：‘,e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat(‘id=‘,e_id,‘name=‘,e_name,‘age=‘,e_age,‘薪资为：‘,e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat(‘id=‘,e_id,‘name=‘,e_name,‘age=‘,e_age,‘薪资为：‘,e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat(‘id=‘,e_id,‘name=‘,e_name,‘age=‘,e_age,‘薪资为：‘,e_salary);
  
  close emp_result;
end;

call pro_test11();

drop procedure pro_test11();

--------------------------------------------------------------------------------

create procedure pro_test12()
begin
  declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
  declare has_data int default 1;
  
  declare emp_result cursor for select * from emp;
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;     
  
  open emp_result;
  
  repeat
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat(‘id=‘,e_id, ‘name=‘, e_name, ‘age=‘, e_age,‘薪资为‘, e_salary);
	until has_data = 0
  end repeat;
  
  close emp_result;
end;

call pro_test12();

DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;  //mysql 句柄触发退出机制 必须跟在游标声明下面

until has_data = 0  //此处后面结束符  不允许有分号 ； 


10）存储函数   有返回值的过程

create function function_name([参数列表....])
returns type
begin
  ......
end;

------定义一个存储函数，获取满足条件（city表）的总记录数；
create function fun1(countryID int)
RETURNS int
begin
  ......
end;

-------------------------------------------------------------

create function fun1(countryID int)
RETURNS int
begin
  declare cnum int;
  
  select count(*) into cnum from city where country_id = countryID;
  return cnum;
  
end;

select fun1(2);

drop function fun1;