1.创建存储过程
--统计员工人数的存储过程
CREATE OR REPLACE PROCEDURE count_emp(p_deptno IN INTEGER,count_total OUT INTEGER,exec_date OUT DATE)
IS
BEGIN
SELECT COUNT(*) INTO count_total FROM emp WHERE deptno=p_deptno;
SELECT SYSDATE INTO exec_date FROM dual;
dbms_output.put_line('部门'||p_deptno||'总人数:'||count_total||',统计日期:'||exec_date);
END;
2.测试
--测试
DECLARE
v_count INTEGER;
v_date DATE;
BEGIN
count_emp(20,v_count,v_date);
END;
3.测试结果
4.新建对应的POJO
public class ProcedurePOJO {
private int deptno;
private int result;
private Date execDate;
//set、get方法略
}
5.mapper
public interface ProcedureMapper {
public void count(ProcedurePOJO pojo);
}
6.mapper.xml
<select id="count" parameterType="com.yan.po.ProcedurePOJO" statementType="CALLABLE"> call count_emp( #{deptno,mode=IN,jdbcType=INTEGER}, #{result,mode=OUT,jdbcType=INTEGER}, #{execDate,mode=OUT,jdbcType=DATE} ) </select>
7.测试
ProcedureMapper mapper=sqlSession.getMapper(ProcedureMapper.class); ProcedurePOJO pojo=new ProcedurePOJO(); pojo.setDeptno(10); mapper.count(pojo); System.out.println(pojo.getDeptno()+"的总人数:"+pojo.getResult()); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); System.out.println(sdf.format(pojo.getExecDate()));
8.备注
这是根据《深入浅出MyBatis》书上代码稍作修改而来,书上所示mapper.xml格式如下:
<select id="count" parameterType="com.yan.po.ProcedurePOJO" statementType="CALLABLE"> {call count_emp( #{deptno,mode=IN,jdbcType=INTEGER}, #{result,mode=OUT,jdbcType=INTEGER}, #{execDate,mode=OUT,jdbcType=DATE} )} </select>
9. 特别指出:
此处的大括号与call之间不能换行(但是可以有空格),后面的大括号可以换行,否则会抛异常:java.sql.SQLException: 出现不支持的 SQL92 标记: 2: