create or replace function test111(itemNumber in varchar2) return SYS_REFCURSOR
is
return_cursor SYS_REFCURSOR;
begin
OPEN return_cursor FOR SELECT 'a' FROM dual WHERE 1 = itemNumber;
RETURN return_cursor;
end test111;
使用如下 sql 返回 游标, 在 pl sql developer 可以直接点开查询结果
1 select test111(1) from dual;
适用条件: 在 Sql 语句过长时可以适用, 避免在 java 代码中有过长的 sql 代码!
jdbc 调用结果集
package com.dahuatech.job;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.driver.OracleResultSet;
import oracle.jdbc.driver.OracleTypes;
public class Test {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@10.30.5.106:1521:agile9";
Connection conn = DriverManager.getConnection(url, "agile", "***");
String sql = "{? = call test111(?)}";
CallableStatement cst = conn.prepareCall(sql);
cst.registerOutParameter(1, OracleTypes.CURSOR);
cst.setString(2, "1");
cst.execute();
OracleResultSet rs = (OracleResultSet) cst.getObject(1);
while (rs.next()) {
System.out.println(rs.getString("a"));
}
}
}
SpringMvc 框架的 jdbcTemplete 调用返回为字符串的函数
public String transf(final String inModel) {
return jdbcTemplate.execute("{? = call transfModel(?)}", new CallableStatementCallback<String>() {
@Override
public String doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
cs.registerOutParameter(1, OracleTypes.VARCHAR);
cs.setString(2, inModel);
cs.execute();
return (String) cs.getObject(1);
}
});
}
来源: http://www.bubuko.com/infodetail-2478994.html