当前位置: 代码迷 >> J2EE >> Ibatis 调用 orcle 存储过程解决办法
  详细解决方案

Ibatis 调用 orcle 存储过程解决办法

热度:737   发布时间:2016-04-22 03:43:44.0
Ibatis 调用 orcle 存储过程
ibatis配置文件:
<resultMap class="java.util.HashMap" id="delNum">
<result property="delnum" column="vintRetCode" jdbcType="Number"/>  
</resultMap>
<parameterMap class="java.util.HashMap" id="delParamMap">
<parameter property="RIGHT_ID" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/> 
<parameter property="DEL_NUM" jdbcType="NUMBER" javaType="java.lang.Integer" mode="OUT"/> 
</parameterMap>
<procedure id="delArchivBase" parameterMap="delParamMap" resultMap="delNum">
{call PRO_NODES_DEL_ARCHIVES (?,?)}
</procedure>
存储过程如下:
create or replace procedure PRO_NODES_DEL_ARCHIVES
(
  vintSID in number, --传入的节点ID
  vintRetCode out number
) is
  nSID number;
  nRightID number;
  nIsChild number;
  -- 定义一个动态的cursor查询出入的节点的所有下级节点
  cursor selAllChildNodes(nSID number) is
  select t.right_id
  from archives_base t
  where t.base_type = 3
  connect by prior t.right_id =t.parentid
  start with t.right_id = nSID
  order by t.parentid,t.right_id asc;
begin
  vintRetCode := 0;
  begin
  if length(trim(vintSID)) > 0 then
  nSID := vintSID;
  else
  nSID := 0;
  end if;
  -- 首先根据传进来的id值判断该节点是否存在下级节点
  select t.ischild into nIsChild
  from archives_base t
  where t.right_id = nSID;
   
  if nIsChild = 1 then --说明有下级节点
  open selAllChildNodes(nSID);
  loop
  fetch selAllChildNodes into nRightID;
  exit when selAllChildNodes%notfound;
  delete from archives_base t where t.right_id = nRightID;
  end loop;
  close selAllChildNodes;
  else
  delete from archives_base t where t.right_id = nSID;
  end if;
  commit;
  exception
  when others then
  rollback;
  vintRetCode := -1;
  end;
end PRO_NODES_DEL_ARCHIVES;

java代码调用:
public List delArchivBase(HashMap map) throws Exception {
  map.put("RIGHT_ID", Integer.valueOf(rightid));
map.put("DEL_NUM", 0);
List delArchives = getSqlMapClientTemplate().queryForList("syssetting.delArchivBase", map);
return delArchives;
}

程序调用存储过程时出错:
--- The error occurred in com/sinosoft/platform/archives/conf/syssetting.xml.  
--- The error occurred while executing query procedure.  
--- Check the {call PRO_NODES_DEL_ARCHIVES (?,?)}.  
--- Check the output parameters (register output parameters failed).  
--- Cause: java.sql.SQLException: 无效的列类型; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:  
--- The error occurred in com/sinosoft/platform/archives/conf/syssetting.xml.  
--- The error occurred while executing query procedure.  
--- Check the {call PRO_NODES_DEL_ARCHIVES (?,?)}.  
--- Check the output parameters (register output parameters failed).  
--- Cause: java.sql.SQLException: 无效的列类型


------解决方案--------------------
我想,既然是用ibatis至少应该是这样吧
{call PRO_NODES_DEL_ARCHIVES (#RIGHT_ID#,#DEL_NUM#)}
------解决方案--------------------
  相关解决方案