一个简单的存储过程及扩展

一.存储过程

1.创建

drop PROCEDURE if EXISTS test;

create procedure test(in inputParam VARCHAR(32), OUT outParam VARCHAR(32))
begin 
if inputParam=1  then
SET outParam = '2';
  ELSE 
SET outParam = '4';
  END IF;

end ;

2.调用并显示

call test('1',@new);

select @new;

二.存储函数

1.创建存储函数 

drop function if EXISTS f1;
create function f1(inputParam VARCHAR(32)) RETURNS varchar(255)
begin 
DECLARE x VARCHAR(255) DEFAULT '';
if  EXISTS(select userId from web_user where userId = inputParam)  then
set x = 1;
  ELSE 
set x = 2;
  END IF;
RETURN x;
end ; 

2.调用存储函数

select  f1('00304490ec2f48959f98dc2e06783403');

存储过程可以调用存储函数，存储函数不能调用存储过程

-----------------------------------------------------------------根据项目需求写的一个存储过程------------------------------------------------------------

1.需求，有机器人，真人，都有购买记录，机器人和真人是两个表，存储各自的用户信息，购买记录是一个表，机器人和真人都有userId，且不重复，

根据要求查出购买记录，并查出购买用户的相关信息。。

2.存储过程实现：

drop PROCEDURE if EXISTS test0;
create procedure test0(in time varchar(64), timeMin varchar(64), mill INT) 
 BEGIN  

/**变量，注意变量名不能和游标对应的字段名重名！否则给变量名赋值会失败！*/
DECLARE userId1 varchar(32);
DECLARE userName1 varchar(32);   
DECLARE userCode1 varchar(32);   
DECLARE headImg1 varchar(32);  
DECLARE IP1 varchar(32); 
DECLARE location1 varchar(255);     
DECLARE payTime1 varchar(64);     
DECLARE millisecond1 INT;     
DECLARE detailId1 INT; 
DECLARE done INT DEFAULT FALSE;  
                                                                                                                       
/**游标对应的sql*/
DECLARE mycur CURSOR FOR SELECT userId,IP,location, payTime,millisecond,detailIdFROM web_pay_log where UNIX_TIMESTAMP(payTime)*1000+millisecond <= UNIX_TIMESTAMP(time)*1000+mill and payTime > timeMin   order by payTime desc, millisecond desc limit 0 , 50;  

/**将结束标志绑定到游标*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

/**临时表*/
DROP TABLE IF EXISTS temp;
 create temporary table if not exists temp 
         (  
           userId varchar(32) primary key,  
           userName varchar(32),  
           userCode varchar(32),  
           headImg varchar(32), 
           IP varchar(32) ,
           location varchar(255),
           payTime varchar(64),
           millisecond INT,
           detailId INT
         );  
 truncate TABLE temp;

/**打开游标*/
 OPEN mycur;   

/**遍历，给变量赋值，存到临时表*/
emp_loop:LOOP        
        FETCH  NEXT from mycur INTO userId1,IP1,location1,payTime1,millisecond1,detailId1; 
        if done THEN
                leave emp_loop;
        end if;
  
                IF EXISTS(select robot.userId from web_robot robot where robot.userId = userId1) THEN        
                        select robot.userName, robot.userCode, robot.headImg  into userName1, userCode1, headImg1 from web_robot robot where robot.userId = userId1;
                ELSE
                        select us.userName, us.userCode, us.headImg  into userName1, userCode1, headImg1 from web_user us where us.userId = userId1;
                END IF;
                        insert into temp values(userId1 ,userName1 ,userCode1 ,headImg1 ,IP1 ,location1 ,payTime1 ,millisecond1 ,detailId1 ); 
       
 end loop;

/**关闭游标*/
 CLOSE mycur;  

/**输出*/
 select * from temp;

/**删除临时表*/
 DROP TABLE IF EXISTS temp;
END 

3.调用存储过程
call test0("2016-12-12 11:45:57","2016-12-08 17:22:36",966)

4.java代码调用存储过程（mybatis，注解方式调用）

即使存储过程没有查询数据，也必须返回HashMap(键值对的形式)，写void会报错

本例返回多条数据，所以用List<HashMap>

@SuppressWarnings("rawtypes")
@Select("call test0(#{endTime},#{startTime},#{mill})")
@Options(statementType= StatementType.CALLABLE )
public List<HashMap> callTest(@Param("endTime")String endTime, @Param("startTime")String startTime, @Param("mill")int mill);

5.加事务，异常处理。。（待续）

------------------------------------------------------------------以下为转载-------------------------------------------------------

//带OUT返回的  
CREATE PROCEDURE SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT)  
BEGIN 
IF p_name is null or p_name='' THEN 
SELECT * FROM t_user;  
ELSE 
SELECT * FROM t_user WHERE USER_NAME LIKE p_name;  
END IF;  
SELECT FOUND_ROWS() INTO p_int;  
END

//调用并输出结果  CALL SP_SEARCH2( '林%' ,@p_num);  SELECT @p_num;

文章：

http://www.jb51.net/article/70677.htm