存储过程如下:
create or replace
PROCEDURE SP_Exec_Stat(
v_beginTime IN DATE DEFAULT NULL ,
v_endTime IN DATE DEFAULT NULL ,
v_alarmId IN NUMBER DEFAULT NULL ,
v_pageNum IN NUMBER DEFAULT NULL ,--页码
v_pageCount IN NUMBER DEFAULT NULL ,--每页数量
cv_1 OUT SYS_REFCURSOR
)
AS
v_statEndTime----统计结束时间
DATE;
--Not send the effected information
v_dayOfMonth NUMBER(10,0);
v_rptID NUMBER(10,0);
CURSOR Cursor_rpt_id
IS SELECT rpt_id
FROM tt_v_statResult ;BEGIN
--当月天数
v_dayOfMonth := to_number( to_char(last_day(trunc(v_beginTime)),'DD')) ;
--统计历时结束时间
v_statEndTime := to_date(concat(concat(concat(concat(CONCAT(to_char(v_endTime,'yyyy'),'-'),to_char(v_beginTime,'mm')),'-'),to_char(v_dayOfMonth)),' 23:59:59'),'yyyy-mm-dd hh24:mi:ss') ;
INSERT INTO tt_v_stat
( SELECT rpt_id ,
alarm_id ,
(CASE
WHEN v_beginTime > MIN(alarm_time) THEN v_beginTime
ELSE MIN(alarm_time)
END) ,
(CASE
WHEN resume_time IS NULL THEN v_statEndTime
WHEN resume_time > v_statEndTime THEN v_statEndTime
ELSE MAX(resume_time)
END) ,
vendor_id ,
vendor_name
FROM v_alarm_all
WHERE alarm_id = v_alarmId
AND ( ( alarm_time >= v_beginTime
AND alarm_time <= v_statEndTime )
OR ( alarm_time < v_beginTime
AND ( resume_time > v_beginTime
OR resume_time IS NULL ) ) )
GROUP BY rpt_id,alarm_id,resume_time,vendor_id,vendor_name );
INSERT INTO tt_v_statResult
( rpt_id, alarm_id, alarm_time, resume_time, alarmDuration, vendor_id, vendor_name )
( SELECT rpt_id ,
alarm_id ,
MIN(alarm_time) alarmTime ,
MAX(resume_time) resumeTime ,
utils.datediff('MINUTE', MIN(alarm_time), MAX(resume_time)) alarmDuration ,
vendor_id ,
vendor_name
FROM tt_v_stat
GROUP BY rpt_id,alarm_id,vendor_id,vendor_name );
OPEN Cursor_rpt_id;
FETCH Cursor_rpt_id INTO v_rptID;
WHILE ( utils.fetch_status(Cursor_rpt_id%FOUND) = 0 )
LOOP
BEGIN
UPDATE tt_v_statResult
SET create_time = ( SELECT NE_INFO.ctimestamp
FROM NE_INFO
WHERE NE_INFO.rpt_id = v_rptID ),
rpt_name = ( SELECT NE_INFO.rpt_name
FROM NE_INFO
WHERE NE_INFO.rpt_id = v_rptID )
WHERE rpt_id = v_rptID;
FETCH Cursor_rpt_id INTO v_rptID;
END;
END LOOP;
CLOSE Cursor_rpt_id;
IF v_pageNum = -1 THEN
BEGIN
OPEN cv_1 FOR
SELECT COUNT(*) statCount
FROM tt_v_statResult ;
END;
ELSE
IF v_pageNum = -2 THEN
BEGIN
OPEN cv_1 FOR
SELECT stat_id ,
rpt_id ,
alarm_time statTime ,
rpt_name ,
vendor_name ,
alarmDuration ,
utils.datediff('MINUTE', create_time, v_statEndTime) onLineDuration ,
alarmDuration * 1.0 / utils.datediff('MINUTE', create_time, v_statEndTime) outOfServiceRatio
FROM tt_v_statResult ;
END;
ELSE
DECLARE
v_statID NUMBER(10,0);
v_page NUMBER(10,0);
BEGIN
v_page := v_pageCount * (v_pageNum - 1) ;
IF v_page > 0 THEN
BEGIN
SELECT stat_id INTO v_statID
FROM tt_v_statResult WHERE ROWNUM <= v_page;
END;
ELSE
BEGIN
v_statID := 0 ;
END;
END IF;
OPEN cv_1 FOR
SELECT stat_id ,
rpt_id ,
alarm_time statTime ,
rpt_name ,
vendor_name ,
alarmDuration ,
utils.datediff('MINUTE', create_time, v_statEndTime) onLineDuration ,
alarmDuration * 1.0 / utils.datediff('MINUTE', create_time, v_statEndTime) outOfServiceRatio
FROM tt_v_statResult
WHERE stat_id > v_statID AND ROWNUM <= v_pageCount ;--Send the effected information
END;
END IF;
END IF;
END;请问在java中如何调用?
create or replace
PROCEDURE SP_Exec_Stat(
v_beginTime IN DATE DEFAULT NULL ,
v_endTime IN DATE DEFAULT NULL ,
v_alarmId IN NUMBER DEFAULT NULL ,
v_pageNum IN NUMBER DEFAULT NULL ,--页码
v_pageCount IN NUMBER DEFAULT NULL ,--每页数量
cv_1 OUT SYS_REFCURSOR
)
AS
v_statEndTime----统计结束时间
DATE;
--Not send the effected information
v_dayOfMonth NUMBER(10,0);
v_rptID NUMBER(10,0);
CURSOR Cursor_rpt_id
IS SELECT rpt_id
FROM tt_v_statResult ;BEGIN
--当月天数
v_dayOfMonth := to_number( to_char(last_day(trunc(v_beginTime)),'DD')) ;
--统计历时结束时间
v_statEndTime := to_date(concat(concat(concat(concat(CONCAT(to_char(v_endTime,'yyyy'),'-'),to_char(v_beginTime,'mm')),'-'),to_char(v_dayOfMonth)),' 23:59:59'),'yyyy-mm-dd hh24:mi:ss') ;
INSERT INTO tt_v_stat
( SELECT rpt_id ,
alarm_id ,
(CASE
WHEN v_beginTime > MIN(alarm_time) THEN v_beginTime
ELSE MIN(alarm_time)
END) ,
(CASE
WHEN resume_time IS NULL THEN v_statEndTime
WHEN resume_time > v_statEndTime THEN v_statEndTime
ELSE MAX(resume_time)
END) ,
vendor_id ,
vendor_name
FROM v_alarm_all
WHERE alarm_id = v_alarmId
AND ( ( alarm_time >= v_beginTime
AND alarm_time <= v_statEndTime )
OR ( alarm_time < v_beginTime
AND ( resume_time > v_beginTime
OR resume_time IS NULL ) ) )
GROUP BY rpt_id,alarm_id,resume_time,vendor_id,vendor_name );
INSERT INTO tt_v_statResult
( rpt_id, alarm_id, alarm_time, resume_time, alarmDuration, vendor_id, vendor_name )
( SELECT rpt_id ,
alarm_id ,
MIN(alarm_time) alarmTime ,
MAX(resume_time) resumeTime ,
utils.datediff('MINUTE', MIN(alarm_time), MAX(resume_time)) alarmDuration ,
vendor_id ,
vendor_name
FROM tt_v_stat
GROUP BY rpt_id,alarm_id,vendor_id,vendor_name );
OPEN Cursor_rpt_id;
FETCH Cursor_rpt_id INTO v_rptID;
WHILE ( utils.fetch_status(Cursor_rpt_id%FOUND) = 0 )
LOOP
BEGIN
UPDATE tt_v_statResult
SET create_time = ( SELECT NE_INFO.ctimestamp
FROM NE_INFO
WHERE NE_INFO.rpt_id = v_rptID ),
rpt_name = ( SELECT NE_INFO.rpt_name
FROM NE_INFO
WHERE NE_INFO.rpt_id = v_rptID )
WHERE rpt_id = v_rptID;
FETCH Cursor_rpt_id INTO v_rptID;
END;
END LOOP;
CLOSE Cursor_rpt_id;
IF v_pageNum = -1 THEN
BEGIN
OPEN cv_1 FOR
SELECT COUNT(*) statCount
FROM tt_v_statResult ;
END;
ELSE
IF v_pageNum = -2 THEN
BEGIN
OPEN cv_1 FOR
SELECT stat_id ,
rpt_id ,
alarm_time statTime ,
rpt_name ,
vendor_name ,
alarmDuration ,
utils.datediff('MINUTE', create_time, v_statEndTime) onLineDuration ,
alarmDuration * 1.0 / utils.datediff('MINUTE', create_time, v_statEndTime) outOfServiceRatio
FROM tt_v_statResult ;
END;
ELSE
DECLARE
v_statID NUMBER(10,0);
v_page NUMBER(10,0);
BEGIN
v_page := v_pageCount * (v_pageNum - 1) ;
IF v_page > 0 THEN
BEGIN
SELECT stat_id INTO v_statID
FROM tt_v_statResult WHERE ROWNUM <= v_page;
END;
ELSE
BEGIN
v_statID := 0 ;
END;
END IF;
OPEN cv_1 FOR
SELECT stat_id ,
rpt_id ,
alarm_time statTime ,
rpt_name ,
vendor_name ,
alarmDuration ,
utils.datediff('MINUTE', create_time, v_statEndTime) onLineDuration ,
alarmDuration * 1.0 / utils.datediff('MINUTE', create_time, v_statEndTime) outOfServiceRatio
FROM tt_v_statResult
WHERE stat_id > v_statID AND ROWNUM <= v_pageCount ;--Send the effected information
END;
END IF;
END IF;
END;请问在java中如何调用?
2、具体调用要看你底层使用的是什么框架,如果是jdbc就很简单了,直接接受返回的集合进行遍历就可以了
3、以mybites为例吧:<select id="tes" statementType="CALLABLE" parameterType="HashMap">
<![CDATA[
{call SP_Exec_Stat(#{cv_1,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=接受的map的名称})}
]]>
</select>
只需在调用存储过程时,注册返回值类型为OracleTypes.CURSOR.
返回的refcursor实际上与resultSet是同样的结构,只需要通过netx()遍历就可以了。
next遍历进不去 ,可能在get结果集的时候就报错了,或者结果集中的确没记录。
JAVA中去get游标的结果集,你可以使用getObject, 再强转为resultSet应该就可以了
next遍历进不去 ,可能在get结果集的时候就报错了,或者结果集中的确没记录。
JAVA中去get游标的结果集,你可以使用getObject, 再强转为resultSet应该就可以了
还是进不去呢,总是出现execute:无法对 PLSQL 语句执行提取: next
我调用存储过程的方法是这样的:
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
OracleCallableStatement cstmt = null;
CallableStatement cstmtCount = null;// 记录数
try {
Connection conn = HibernateDao.getConnection();
String procedure = "{ call SP_Exec_OutOfService_Stat(?,?,?,?,?,?) }";
cstmt = (OracleCallableStatement) conn.prepareCall(procedure);
if (map != null) {
String endTime = (String) map.get("endTime");
if (endTime != null) { cstmt.setTimestamp(1, getFirstTimeOfMonth());
cstmt.setTimestamp(2, getLastTimeOfMonth());
}
cstmt.setInt(3, 10057);
cstmt.setInt(4, pageNo);
cstmt.setInt(5, pageSize);
cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); }
cstmt.execute();
ResultSet rs = (ResultSet) cstmt.getObject(6);
conn.commit(); while (rs.next()) {
Map<String, Object> newmap = new HashMap<String, Object>();
。
list.add(newmap);
}
cstmtCount = cstmt;
cstmtCount.setInt(4, -1);
ResultSet rsCount = cstmt.executeQuery();// 记录数
while (rsCount.next()) {
recordCount = rsCount.getInt(1);
}
} catch (Exception e) {
logger.info("execute:" + e.getMessage());
} finally {
if (cstmt != null) {
try {
cstmt.close();
cstmtCount.close();
} catch (Exception e) {
} finally {
cstmt = null;
cstmtCount = null;
}
}
}
return list;
CREATE OR REPLACE PROCEDURE testrefcursor (refcur OUT sys_refcursor)
AS
v_sql varchar2(2500 char);
BEGIN
v_sql := ' SELECT *
FROM (WITH r1 AS
(SELECT ''1'' AS d
FROM DUAL
UNION ALL
SELECT ''2'' AS d
FROM DUAL
UNION ALL
SELECT ''3'' AS d
FROM DUAL
UNION ALL
SELECT ''4'' AS d
FROM DUAL
UNION ALL
SELECT ''5'' AS d
FROM DUAL
UNION ALL
SELECT ''6'' AS d
FROM DUAL
UNION ALL
SELECT ''7'' AS d
FROM DUAL
UNION ALL
SELECT ''8'' AS d
FROM DUAL
UNION ALL
SELECT ''9'' AS d
FROM DUAL)
SELECT d
FROM r1)';
open refcur for v_sql;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END testrefcursor;
这是测试使用的PRC,下面是调用的JAVA代码try {
Connection conn = ConUtil.getConn();
CallableStatement cs = null;
ResultSet rs = null;
if (cs == null)
cs = conn.prepareCall("{call testrefcursor(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
rs = (ResultSet) cs.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
输出结果: