一.存储过程
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;
二.存储函数
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