存储过程:
create or replace procedure pro_add(p1 in number,p2 in number,p3 out number)
as
begin
p3:=p1+p2;
end;
函数:
create or replace function fun_add(p1 in number,p2 in number)
return number
as
begin
return p1+p2;
Mapper.xml:
<!-- jdbc调用存储过程语法
{call 存储过程名(?,?)}
函数语法
{?=call 函数名(?,?)} -->
<mapper namespace="proc">
<!-- 存储过程 -->
<select id="call_pro_add" resultType="java.util.Map" statementType="CALLABLE">
{call pro_add(
#{p1,mode=IN,jdbcType=NUMERIC},
#{p2,mode=IN,jdbcType=NUMERIC},
#{result,mode=OUT,jdbcType=NUMERIC})}
</select>
<!-- 函数-->
<select id="call_fun_add" resultType="java.util.Map" statementType="CALLABLE">
{#{result,mode=OUT,jdbcType=NUMERIC}=call fun_add(
#{p1,mode=IN,jdbcType=NUMERIC},
#{p2,mode=IN,jdbcType=NUMERIC})
}
</select>
</mapper>
测试方法:
private SqlSession getSession() throws IOException {
// mybatis核心配置文件路径
String resource = "cn/et/lesson2/preduce/mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 工厂类
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
// session操作的是指向sql语句的一个唯一标识符
SqlSession openSession = sqlSessionFactory.openSession();
return openSession;
}
@Test
public void testProcInterface() throws IOException{
SqlSession session =getSession();
Map map=new HashMap();
map.put("p1", 1111);
map.put("p2", 1212);
map.put("result", 0);
session.selectOne("proc.call_pro_add",map);
System.out.println(map.get("result"));
}
@Test
public void testFunInterface() throws IOException{
SqlSession session =getSession();
Map map=new HashMap<String, Integer>();
map.put("p1", 1111);
map.put("p2", 1212);
map.put("result", 0);
session.selectOne("proc.call_fun_add",map);
System.out.println(map.get("result"));
}
create or replace procedure pro_add(p1 in number,p2 in number,p3 out number)
as
begin
p3:=p1+p2;
end;
函数:
create or replace function fun_add(p1 in number,p2 in number)
return number
as
begin
return p1+p2;
end;
Mapper.xml:
<!-- jdbc调用存储过程语法
{call 存储过程名(?,?)}
函数语法
{?=call 函数名(?,?)} -->
<mapper namespace="proc">
<!-- 存储过程 -->
<select id="call_pro_add" resultType="java.util.Map" statementType="CALLABLE">
{call pro_add(
#{p1,mode=IN,jdbcType=NUMERIC},
#{p2,mode=IN,jdbcType=NUMERIC},
#{result,mode=OUT,jdbcType=NUMERIC})}
</select>
<!-- 函数-->
<select id="call_fun_add" resultType="java.util.Map" statementType="CALLABLE">
{#{result,mode=OUT,jdbcType=NUMERIC}=call fun_add(
#{p1,mode=IN,jdbcType=NUMERIC},
#{p2,mode=IN,jdbcType=NUMERIC})
}
</select>
</mapper>
测试方法:
private SqlSession getSession() throws IOException {
// mybatis核心配置文件路径
String resource = "cn/et/lesson2/preduce/mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 工厂类
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
// session操作的是指向sql语句的一个唯一标识符
SqlSession openSession = sqlSessionFactory.openSession();
return openSession;
}
@Test
public void testProcInterface() throws IOException{
SqlSession session =getSession();
Map map=new HashMap();
map.put("p1", 1111);
map.put("p2", 1212);
map.put("result", 0);
session.selectOne("proc.call_pro_add",map);
System.out.println(map.get("result"));
}
@Test
public void testFunInterface() throws IOException{
SqlSession session =getSession();
Map map=new HashMap<String, Integer>();
map.put("p1", 1111);
map.put("p2", 1212);
map.put("result", 0);
session.selectOne("proc.call_fun_add",map);
System.out.println(map.get("result"));
}