1.
create PROCEDURE getRes(IN num int)
BEGIN
select * from t_r where id=num; //储存过程主体 注意入参不要定义与查询条件字段同名 如左不要定义为id
END
call getRes(10949);//调用存储过程
drop PROCEDURE getRes;//删除储存过程
2.
create PROCEDURE getRes(IN num int, //入参
out resName VARCHAR(100),//出参
out resSize int(10)//出参 )
BEGIN
select name into resName from t_resource where id=num;
select size into resSize from t_resource where id=num;
END
call getRes(10949,@resName,@resSize);//调用储存过程
SELECT @resName,@resSize;//返回值
3.
create PROCEDURE incrCount(INOUT count int,IN num int)//INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值
BEGIN
set count=count+num;
end
set @count=1;
call incrCount(@count,1);
SELECT @count;
4. if else 语句
create PROCEDURE incra(IN num int)
BEGIN
DECLARE var int;
set var=num+1;
if var=1 then select '女'; else SELECT '男'; end if;
end
call incra(1)
5. case when 语句
create PROCEDURE incrc(IN num int)
BEGIN
DECLARE var int;
set var=num+1;
case var
when 1 then select '女'; when 2 then SELECT '男'; else SELECT '人妖' ;
end case;
end
call incrc(8)
6.mybatis 调用储存过程
<select id="count" parameterType="DevicePOJO" useCache="false"
statementType="CALLABLE"> //statementType=”CALLABLE” 必须为CALLABLE,告诉MyBatis去执行存储过程
call countDevicesName(
#{devoceName,mode=IN,jdbcType=VARCHAR}, //mode=IN 输入参数 mode=OUT输出参数 jdbcType为数据库定义的字段类型
#{deviceCount,mode=OUT,jdbcType=INTEGER});
</select>