七、调用存储过程

1.查询某个年级的学生总数

输入：年级

输出：学生总数

create or replace procedure queryCountByGradeWithProcedure(gName in varchar, sCount out number)

as

begin

select count(*) into sCount from student where gname = graName;

end;

<!--
通过调用存储过程实现查询 		
存储过程的输入参数，在MyBatis用Map来传递（HashMap）	
Oracle需要ojdbc7.jar或更高版本，才可以支持回车、空格
-->
<select id="queryCountByGradeWithProcedure" statementType="CALLABLE" parameterType="HashMap">
 		{
 			CALL queryCountByGradeWithProcedure
 			(
 			#{gName, jdbcType=VARCHAR, mode=IN},
 			#{sCount, jdbcType=INTEGER, mode=OUT}
 			)
 		}	
    
</select>

void queryCountByGradeWithProcedure(Map<String, Object> params);

public static void queryCountByGradeWithProcedure() throws IOException {
		Reader reader = Resources.getResourceAsReader("conf.xml");
		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
		SqlSession session = sessionFactory.openSession();
		studentMapper studentmapper = session.getMapper(studentMapper.class);	
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("gName", "hzb");
		studentmapper.queryCountByGradeWithProcedure(params);
		int count = (int) params.get("sCount");
		System.out.println(count);
		session.close();
}

2.根据学号删除学生

create or replace procedure deleteStuByNoWithProcedure(sno in number)
as
begin
delete from student where stuno = sno  ;
end;

<delete id="deleteStuByNoWithProcedure" statementType="CALLABLE" parameterType="HashMap">
 		{
 			CALL deleteStuByNoWithProcedure
 			(
 			#{sno, jdbcType=INTEGER, mode=IN}
 			)
 		}
</delete>

void deleteStuByNoWithProcedure(Map<String, Object> sno);

public static void deleteStuByNoWithProcedure() throws IOException {
		Reader reader = Resources.getResourceAsReader("conf.xml");
		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
		SqlSession session = sessionFactory.openSession();
		studentMapper studentmapper = session.getMapper(studentMapper.class);	
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("sno", 1);
		studentmapper.deleteStuByNoWithProcedure(params);
		session.commit();
		session.close();
}

2.注意

其中通过statementType="CALLABLE"设置SQL的执行方式是存储过程

存储过程的输入参数gName需要通过HashMap来指定

在使用时，通过HashMap的put方法传入输入参数的值；通过HashMap的Get方法获取输出参数的值

要注意Jar问题：ojdbc6.jar不能在调存储过程时打回车、tab，但是ojdbc7.jar可以

No enum constant org.apache.ibatis.type.JdbcType.xxx：则说明mybatis不支持xxx类型，需要查表

存储过程 无论输入参数是什么值，语法上都需要用map来传递该值；

只要是，则增删改都需要手工commit;

mapper.xml->mapper接口->测试方法