Ibatis中怎么调用oracle存储的函数

一.下载最新ibatis包.
二.最新的连接oracle 10g的驱动ojdbc(我这里用的是oracle 10g)
三.下载最新的common包.
示例如下:
函数定义:
Sql代码
CREATE OR REPLACE FUNCTION GetCurTest(

param1 in varchar2;

param2 in varchar2;

)return SYS_REFCURSOR

IS

outCursor SYS_REFCURSOR; --输出的游标

BEGIN

open outCursor for

select ORGANCODE from testTable where col1 = GetCurTest.param1 and col2 = GetCurTest.param2;

return outCursor;

END;-- 注意这里用的是SYS_REFCURSOR,在oracle10g里面提供的.
CREATE OR REPLACE FUNCTION GetCurTest(

param1 in varchar2;

param2 in varchar2;

)return SYS_REFCURSOR

IS

outCursor SYS_REFCURSOR; --输出的游标

BEGIN

open outCursor for

select ORGANCODE from testTable where col1 = GetCurTest.param1 and col2 = GetCurTest.param2;

return outCursor;

END;-- 注意这里用的是SYS_REFCURSOR,在oracle10g里面提供的.

ibatis中调用如下:

Xml代码




















{? = call GetCurTest(?, ?)}






















{? = call GetCurTest(?, ?)}



java中调用如下:

Java代码
String resource = "SqlMapConfig.xml";

SqlMapClient client = null;

try...{

Reader reader = Resources.getResourceAsReader(resource);

client = SqlMapClientBuilder.buildSqlMapClient(reader);

}catch (Throwable e)...{}

Map p = new HashMap();

p.put("param1","a");

p.put("param2","b");

client.queryForObject(mapid, p);

List list = (List) p.get("result");

if (list == null || list.size() == 0)

list = new ArrayList();

return list;
String resource = "SqlMapConfig.xml";

SqlMapClient client = null;

try...{

Reader reader = Resources.getResourceAsReader(resource);

client = SqlMapClientBuilder.buildSqlMapClient(reader);

}catch (Throwable e)...{}

Map p = new HashMap();

p.put("param1","a");

p.put("param2","b");

client.queryForObject(mapid, p);

List list = (List) p.get("result");

if (list == null || list.size() == 0)

list = new ArrayList();

return list;

list存放的就是我们要的结果集.

4.Procedure
procedure 元素支持存储过程。大部分存储过程要一些类似IN, INOUT 或者 OUT 的参数。所以，创建一个 元素并列出你要向存储过程传递的参数。只有当参数类型为OUT或者INOUT时parameterMap 对象才会被改变。

Xml代码












{call swap_contact_name (?, ?,?)}














{call swap_contact_name (?, ?,?)}



你的代码一开始就要创建一个你要传递给存储过程的参数的 HashMap ，然后把它和你要执行的查询的名称一同传递给 sqlMap 。

Java代码
HashMap paramMap = new HashMap();

paramMap.put("contactId", new Integer(1));

paramMap.put("firstName", "Sunil");

paramMap.put("lastName", "Patil");

sqlMap.queryForObject("swapCustomerName", paramMap);
HashMap paramMap = new HashMap();

paramMap.put("contactId", new Integer(1));

paramMap.put("firstName", "Sunil");

paramMap.put("lastName", "Patil");

sqlMap.queryForObject("swapCustomerName", paramMap);

5.Calling an Oracle procedure function using Ibatis

The ibatis documentation provides a sample on how to call a procedure, but how do you call a function in Oracle? The code sample shows it.
Xml代码
















{? = call FNC_EXECUTE_SOME_FUNCTION(?, ?, ?, ?)}

希望能帮到你。