java调用oracle存储过程返回含有SYS_REFCURSOR的结果集
存储过程如下：
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中如何调用？
[解决办法]
楼主是要在JAVA中接收一个refcursor,
只需在调用存储过程时，注册返回值类型为OracleTypes.CURSOR.
返回的refcursor实际上与resultSet是同样的结构，只需要通过netx()遍历就可以了。
[解决办法]
next遍历进不去 ，可能在get结果集的时候就报错了，或者结果集中的确没记录。
JAVA中去get游标的结果集，你可以使用getObject， 再强转为resultSet应该就可以了
[解决办法]
我写了个简单的例子，你看一下， 你的代码没注释，我就没细看PRC具体的东西；
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代码