IBatis调用ORACLE的存储过程、函数的返回结果集例子

数据库方面:定义程序包,该程序包中声名了存储过程和函数

--定义包声明
create or replace package pkg_stu
as
type list_stu is ref cursor;--定义游标，通过游标将数据模型结果集返回给关系模型
procedure proc_findStuList(stulist out list_stu,s_id number);
function fun_findStuList(s_id number) return list_stu;
end pkg_stu;

--包主体声明
create or replace package body pkg_stu
  as
  procedure proc_findStuList(stulist out list_stu,s_id number)--实现查找所有学生的存储过程
    is
            sqlString varchar2(500);
    begin
            if s_id = 0 then
               open stulist for select sid,sname,major,birth,socre from student order by sid;
            else
               sqlString := 'select sid,sname,major,birth,socre from student where sid=:sid';
               open stulist for sqlString using s_id;
            end if;
    end proc_findStuList;
  function fun_findStuList(s_id number)   --实现查找所有学生的函数
  return list_stu
  is
         stulist list_stu;
         sqlString varchar2(500);
  begin
         if s_id = 0 then
            open stulist for select sid,sname,major,birth,socre from student order by sid;
         else
            sqlString := 'select sid,sname,major,birth,socre from student where sid=:sid';
            open stulist for sqlString using s_id;
         end if;
         return stulist;
  end fun_findStuList;
end pkg_stu;

Ibatis配置文件中的配置

<!-- 声明结果集类型参数 -->
	<resultMap class="Student" id="stuAccount">
		<result property="sid" column="SID" columnIndex="1"/>
		<result property="sname" column="SNAME" columnIndex="2"/>
		<result property="major" column="MAJOR" columnIndex="3"/>
		<result property="birth" column="BIRTH" columnIndex="4"/>
		<result property="socre" column="SOCRE" columnIndex="4"/>
	</resultMap>
	
	<!--  -->
	<parameterMap  id="parameterMap" class="java.util.HashMap">
        <parameter property="stulist" jdbcType="ORACLECURSOR" 
         javaType="java.sql.ResultSet" mode="OUT" />
        <parameter property="s_id" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/>
    </parameterMap>
    
    <!-- 存储过程的调用方式 -->
    <procedure id="pkgPro_stu_cursor" parameterMap="parameterMap" resultMap="stuAccount">
        {call pkg_stu.proc_findStuList(?,?)}
    </procedure>
    
    <!-- 函数的调用方式 -->
    <procedure id="fun_stu_cursor" parameterMap="parameterMap" resultMap="stuAccount">
    	{? = call pkg_stu.fun_findStuList(?) }
    </procedure>

测试执行类

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {
//存储过程的调用
		
//		IStudentDAO dao = new IStudentDAOImpl();
//		
//		Map parameterMap = new HashMap();
//		parameterMap.put("s_id", 0);
//		List<Student> accoutList = dao.queryPkgProStudentList(parameterMap);
//		if(accoutList.size()>0){
//			for(Student temp:accoutList){
//				if(temp!=null){
//					System.out.println(temp.getBirth().toLocaleString()+"---"+temp.getMajor());
//				}
//			}
//		}
//		System.out.println("-------------------------");
//		System.out.println("size:"+accoutList.size());

//函数的调用		
		IStudentDAO dao = new IStudentDAOImpl();
		
		Map parameterMap = new HashMap();
		parameterMap.put("s_id", 0);
		List<Student> accoutList = dao.queryFunStudentList(parameterMap);
		if(accoutList.size()>0){
			for(Student temp:accoutList){
				if(temp!=null){
					System.out.println(temp.getBirth().toLocaleString()+"---"+temp.getMajor());
				}
			}
		}
		System.out.println("-------------------------");
		System.out.println("size:"+accoutList.size());
	}
	
	@Override
	public List queryFunStudentList(Map parameterMap) {
		List result = null;
		try {
			result = sqlMapClient.queryForList("fun_stu_cursor",parameterMap);
			System.out.println("size:"+result.size());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
@Override
	public List queryPkgProStudentList(Map parameterMap) {
		// TODO Auto-generated method stub
		List result = null;
		try {
			result = sqlMapClient.queryForList("pkgPro_stu_cursor",parameterMap);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}