创建存储过程：
格式：create or replace procedure procedure_name(参数 参数类型)
Is/as
变量1 变量1的类型；
begin
----------业务逻辑----------
end；
-- plsql里面调用存储过程
begin
proc(10);
end; --创建存储过程
create procedure proc(dno number)
is
cursor c is select * from emp where deptno = dno;
begin
--使用游标循环集合（list）
for r in c loop
dbms_output.put_line(r.ename || ' : ' || r.sal);
end loop;
end;
Java代码里调用存储过程
public List<Map<String, Object>> queryAllData(final String curragency,
final String systype, final String userid,
final String pisoffsetcheck, final String whereSql) {
final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
getJdbcTemplate().execute(new ConnectionCallback() {
public Object doInConnection(Connection conn) throws SQLException,
DataAccessException {
ResultSet rs = null;
String sql = "{call PKG_GCFR_OFFSET.queryAllData(?,?,?,?,?,?)}";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.setString(1, curragency);
cstmt.setString(2, systype);
cstmt.setString(3, userid);
cstmt.setString(4, pisoffsetcheck);
cstmt.setString(5, whereSql);
cstmt.registerOutParameter(6, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(6);
if (rs != null) {
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int r = 1; r <= numberOfColumns; r++) {
map.put(rsmd.getColumnName(r).toLowerCase(),
rs.getObject(r));
}
list.add(map);
}
}
if (cstmt != null) {
cstmt.close();
}
if (rs != null) {
rs.close();
}
if (conn != null) {
conn.close();
}
return list;
}
});
return list;
}