1,创建表:
oracle 建表
表名:student 列名:id,sname;
2,创建简单存储过程:
create or replace procedure sql_parametric(sid in varchar2 ,out_return out sys_refcursor) is
begin
open out_return for select * from student where id= sid ;
end sql_parametric;
3,使用jdbctemplate调用存储过程
public List quaryParametricResult(String id) {
final String sid = id;//全局使用变量需要被final修饰
List resultList = (List) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call sql_parametric(?,?)}";// 调用的sql
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1, sid);// 设置输入参数的值
cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
List resultsMap = new ArrayList();
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值
while (rs.next()) {// 转换每行的返回值到Map中
Map rowMap = new HashMap();
rowMap.put("id", rs.getString("id"));
rowMap.put("sname", rs.getString("sname"));
resultsMap.add(rowMap);
}
rs.close();
return resultsMap;
}
});
return resultList;
}
简单的存储过程学习