Mybatis oracle存储过程返回纪录集

zyl258 发布于 2011/10/15 11:29
阅读 2K+
收藏 1

各位大侠下面问题困扰了我好长时间,至今未解决,希望各位能够给予帮助!

开发环境:struts2+spring3+mybatis3.0.2 

报错:eclipse 控制台显示信息:DEBUG - ooo Connection Opened
DEBUG - ==>  Executing: {? = call pkg_gettesterbycodeandpwd.proc_gettesterbycodeandpwd(?, ?)}
DEBUG - ==> Parameters: 10001000568(String), 000000(String)
INFO - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
INFO - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
DEBUG - xxx Connection Closed

IE中显示信息:

Struts has detected an unhandled exception:

Messages:
  1. ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'PROC_GETTESTERBYCODEANDPWD' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
  2. SqlSession operation; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'PROC_GETTESTERBYCODEANDPWD' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
File: oracle/jdbc/driver/DatabaseError.java
Line number: 111

 

我的存储过程:

CREATE OR REPLACE PACKAGE pkg_getTesterByCodeAndPwd
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE proc_getTesterByCodeAndPwd(u_cursor OUT myrctype, TesterCode IN VARCHAR,Pwd IN VARCHAR);
END pkg_getTesterByCodeAndPwd;

CREATE OR REPLACE PACKAGE BODY pkg_getTesterByCodeAndPwd
  AS
  PROCEDURE proc_getTesterByCodeAndPwd(u_cursor OUT myrctype,TesterCode IN VARCHAR,Pwd IN VARCHAR)
  IS
  sqlString VARCHAR2 (500);
  BEGIN
      sqlString :='SELECT studentCode,testerName,sex,iDCard,age,birthDay,e_Mail,passWord,
                    schoolOutName,motherMobile,fatherMobile,operId,operDate,stopFlag
                   FROM EXAMTESTER t WHERE StopFlag=1001 AND TesterCode=:TesterCode AND PassWord=:Pwd';
      OPEN u_cursor FOR sqlString USING TesterCode,Pwd;
  END proc_getTesterByCodeAndPwd;
END pkg_getTesterByCodeAndPwd;

mapper:

<mapper namespace="com.blas.login.entity.User"> 
 
   <cache />
   
    <resultMap type="User" id="userResultMap"> 
        <id property="testerCode" column="TesterCode"/>
       
  <result property="studentCode" column="StudentCode"/>      <!-- 学号 -->
     <result property="testerName" column="TesterName"/>       <!-- 学生姓名 -->
     <result property="sex" column="Sex"/>              <!-- 性别 -->
     <result property="iDCard" column="IDCard"/>           <!-- 身份证号 -->
     <result property="age" column="Age"/>              <!-- 年龄 -->
     <result property="birthDay" column="BirthDay"/>         <!-- 出生日期 -->
     <result property="e_Mail" column="E_Mail"/>           <!-- E_mail -->
     <result property="passWord" column="PassWord"/>         <!-- 登录密码 -->
     <result property="schoolOutName" column="SchoolOutName"/>    <!-- 公立学校名称 -->
     <result property="motherMobile" column="MotherMobile"/>     <!-- 母亲手机 -->
     <result property="fatherMobile" column="FatherMobile"/>     <!-- 父亲手机 -->
     <result property="operId" column="OperId"/>           <!-- 录入人员 -->
     <result property="operDate" column="OperDate"/>         <!-- 录入日期 -->
     <result property="stopFlag" column="StopFlag"/>         <!-- 停用标志 -->
    </resultMap>  
       
     <select id="selectUserByIdAndPwd" parameterType="User" resultType="User" resultMap="userResultMap"> 
     SELECT *
     FROM EXAMTESTER WHERE StopFlag=1001 AND TesterCode=#{testerCode} AND PassWord=#{passWord}
 </select>
 
 <!-- call procedure parameter -->
    <parameterMap id="parameterMap" type="java.util.HashMap">
     <parameter property="result" jdbcType="CURSOR" javaType="java.sql.ResultSet" mode="OUT" />
        <parameter property="TesterCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
        <parameter property="Pwd" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    </parameterMap>
    
 <select id="getTesterByCodeAndPwd"  parameterMap="parameterMap" statementType="CALLABLE" resultMap="userResultMap">
        {? = call pkg_gettesterbycodeandpwd.proc_gettesterbycodeandpwd(?, ?)}
    </select>
</mapper>

java code:

  List userList=null;
  
  Map parameterMap  = new HashMap();
  parameterMap.put("result",null);
  parameterMap.put("TesterCode","10001000568");
  parameterMap.put("Pwd","000000");
     userServices.getTesterByCodeAndPwd(parameterMap);
     userList = (ArrayList) parameterMap.get("result");//取值

问题困扰了好久,存储过程应该没有问题,我在pl/sql中测试可以通过,得到想要的结果.网上查了好久,也没有好的解决办法.希望各位大侠帮助,非常感谢!!!!

加载中
返回顶部
顶部