Database之三 存储过程

1、存储过程之HelloWorld

delimiter //
drop procedure if exists pro;
create procedure pro(in inParam int(10), out outParam varchar(64))
begin		
	select uname into outParam from abc where uid=inParam;
end
//
delimiter ;

--调用存储过程pro
set @in=1;
set @out='';
call pro(@in, @out);
select @out;

2、变量

--声明变量
declare a int(10) ;
declare a,b int(10) ;

--为变量赋值
declare a int(10) default 0;
set a =0;
select count(*) into a from abc;

3、参数 IN OUT INOUT

--in
mysql > delimiter //  
mysql > create procedure pro_in(in p_in int)  
-> begin   
-> select p_in;   
-> set p_in=2;   
-> select p_in;   
-> end   
-> //  
mysql > delimiter ;

--调用存储过程pro_in
set @in=1;
call pro(@in);


--out
mysql > delimiter //  
mysql > create procedure pro_out(out p_out int)  
-> begin 
-> select p_out;  
-> set p_out=2;  
-> select p_out;  
-> end  
-> //  
mysql > delimiter ; 

--调用存储过程pro_out
set @out=0;
call pro(@out);
select @out;


--inout
mysql > delimiter //   
mysql > create procedure pro_inout(inout p_inout int)   
-> begin 
-> select p_inout;  
-> set p_inout=2;  
-> select p_inout;   
-> end
-> //   
mysql > delimiter ; 

--调用存储过程pro_out
set @inout=0;
call pro(@inout);
select @inout;

4、条件判断

--if...then...else
mysql > delimiter //   
mysql > create procedure pro_inout(in p_in int)   
-> begin 
-> if p_in =0 then 
-> 
-> else p_in=1 then
-> 
-> else 
-> 
-> end if   
-> end  
-> //   
mysql > delimiter ; 
 
--case
mysql > delimiter //   
mysql > create procedure pro_inout(in val int)   
-> begin 
-> case val
-> when 0 then
-> when 1 then
-> else
-> end case;
-> end  

5、循环语句

--while
-> set val=0;
-> while val <60 do
-> ...
-> set val = val+1;
-> end while;

--repeat
-> set v=0;
-> repeat
-> ...
-> set v=v+1;
-> until v>=5;
-> end repeat;

--loop、leave、iterate
set x = 1;
set str =  '';
loop_label:  loop
  if  x > 10 then
   leave  loop_label;
  end  if;
  set  x = x + 1;
  if  (x mod 2) then
   iterate  loop_label;
  else
   set  str = concat(str,x,',');
  end  if;

end loop;
select str;

6、游标

--首先用DECLARE语句声明一个游标 declare cur cursor for select uname from abc;

--其次需要使用OPEN语句来打开游标 open cur;

--用FETCH语句来获得下一行数据，并且游标也将移动到对应的记录上 fetch cur into _uname;

--关闭游标 close cur;

delimiter //
drop procedure if exists pro;
create procedure pro()
begin
	declare _uname varchar(64);
	declare flag int default 0;
	declare cur cursor for select uname from abc;
	declare continue handler for not found set flag = 1;
		
	--从游标中读取数据
	open cur;
	repeat
		fetch cur into _uname;
			if flag=0 then
				select _uname;
			end if;
			until flag = 1
	end repeat;
	close cur;
end
//
delimiter ;

7、异常处理

--DECLARE....HANDLER语句

--DECLARE...CONDITION语句

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR duplicate_key
  BEGIN
    -- body of handler
  END;