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_beginTim