创建存储过程:
格式: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; }