oracle的存储过程里只能select into 而不能直接select ,
有些select 语句比较长且复杂,
那么如果要在前端页面显示结果集的话,
程序代码(java,c#..)里不能调用存储过程了,
难道把这种'比较长且复杂的select 语句'直接写在程序代码(java,c#..)里?
有更好的 办法吗(如调用过程一样方便的)?
有些select 语句比较长且复杂,
那么如果要在前端页面显示结果集的话,
程序代码(java,c#..)里不能调用存储过程了,
难道把这种'比较长且复杂的select 语句'直接写在程序代码(java,c#..)里?
有更好的 办法吗(如调用过程一样方便的)?
结果集,用游标啊。
p_user_id_i in varchar2,
o_cur out sys_refcursor
)
as
begin
open o_cur for select * from test where t1 = p_user_id_i;
end aaaaa1;
/然后java代码
cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); rs = (ResultSet) cs.getObject(1); while (rs.next()) {
SMSProcessMonitorObj pmo = new SMSProcessMonitorObj(); pmo.setMachineIp(rs.getString(DBI.PROC_MON.MACHINE_IP));
pmo.setNormalAmount(rs.getInt(DBI.PROC_MON.NORMAL_AMOUNT));
pmo.setProcessId(rs.getString(DBI.PROC_MON.PROCESS_ID));
out_cr_monitor OUT SYS_REFCURSOR
)
AS
begin
open out_cr_monitor for
select server_id,server_name,server_ip,to_char(last_response_time,'YYYY-MM-DD HH24:MI:SS') last_response_time
from dat_ct_monitor
order by server_id; Exception when others then
raise;
end proc_sel_monitor;
/java:
private static final String proc_sel_monitor = "{ call proc_sel_monitor(?)}";
public ArrayList<MonitorObj> getMonitorList() throws ComicException {
ArrayList<MonitorObj> lst = new ArrayList<MonitorObj>();
ICallableStatement cs = null;
ResultSet rs = null;
try {
cs = DbFactory.getInstance().getCallableStatement(
DBI.SERVER_DB_NAME, proc_sel_monitor); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); rs = (ResultSet) cs.getObject(1); while (rs.next()) {
MonitorObj obj = new MonitorObj();
obj.setServerId(rs.getString(DBI.MONITOR.SERVER_ID));
obj.setServerName(rs.getString(DBI.MONITOR.SERVER_NAME));
obj.setServerIp(rs.getString(DBI.MONITOR.SERVER_IP));
obj.setLastResponseTime(rs
.getString(DBI.MONITOR.LAST_RESPONSE_TIME));
lst.add(obj);
}
} catch (Exception e) {
throw new ComicException(e);
} finally {
try {
if (rs != null) {
rs.close();
}
if (cs != null) {
cs.close();
}
} catch (SQLException e) {
throw new ComicException(e);
}
}
return lst;
}
知道你会这么问。看下面的。
另外,我的db是封装过的,你用jdbc的preparedStatement就可以