本帖最后由 gyhgood 于 2011-11-16 11:30 编辑
CREATE OR REPLACE PROCEDURE "P_RPT_PUMP_HOURS_DD"
(
P_MCSTA_SN IN VARCHAR2
,P_DATE_TIME IN DATE
,P_RS OUT NUMBER
,P_RS_VAL OUT sys_refcursor
)AS
v_errorcode number;
v_errortext varchar2(1000);
BEGIN
P_RS := 0;
OPEN P_RS_VAL FOR
select decode(rn, 1, MCSTA_NAME, null) MCSTA_NAME,
AREA_USER_NAME,
NO_ONE,
NO_TWO,
NO_THREE,
NO_FOUR
from (selectMCSTA_NAME,
AREA_USER_NAME,
sum(case
when substr(equi_use_id, 1, 1) = '1' then
continued
end) NO_ONE,
sum(case
when substr(equi_use_id, 1, 1) = '2' then
continued
end) NO_TWO,
sum(case
when substr(equi_use_id, 1, 1) = '3' then
continued
end) NO_THREE,
sum(case
when substr(equi_use_id, 1, 1) = '4' then
continued
end) NO_FOUR,
row_number() over(partition by MCSTA_NAME order by MCSTA_NAME,case
when instr(AREA_USER_NAME,
'特高') > 0 then
0
when instr(AREA_USER_NAME,
'高') > 0 then
1
when instr(AREA_USER_NAME,
'中') > 0 then
2
when instr(AREA_USER_NAME,
'低') > 0 then
3
else
4
end) rn
from (select MCSTA_NAME,
AREA_USER_NAME,
EQUI_USE_ID,
sum(continued) continued
from (select AREA_USER_NAME,
EQUI_USE_ID,
MCSTA_NAME,
ROUND((MAX(GETDATE) - MIN(GETDATE)) * 24, 1) continued
from (select t.MCSTA_NAME,
t.DATA_CHANNEL_SN,
t.EQUI_USE_ID,
t.MC_EQUI_SN,
t.AREA_USER_NAME,
t.AREA_USER_SN,
t.DATA_KIND_NAME,
t.SW_0_STAT,
t.SW_1_STAT,
c.mdata_val,
c.getdate,
row_number() over(partition by t.MC_EQUI_SN, t.AREA_USER_SN order by t.DATA_CHANNEL_SN ASC, t.AREA_USER_SN ASC, c.GETDATE ASC) rn
from V_STATION_CHANNEL t
join CD_MONI_H_DATA c
on t.DATA_CHANNEL_SN = c.data_channel_sn
where t.MCSTA_SN in
(SELECT REGEXP_SUBSTR(P_MCSTA_SN,
'([^|]+)',
1,
ROWNUM) str
FROM DUAL
CONNECT BY ROWNUM =
TO_DATE(P_DATE_TIME, 'YYYY-MM-DD')
and c.getdate <
TO_DATE(P_DATE_TIME, 'YYYY-MM-DD') + 1)
WHERE MDATA_VAL = 1
GROUP BY MCSTA_NAME,
AREA_USER_NAME,
AREA_USER_SN,
EQUI_USE_ID,
rn - rownum)
group by MCSTA_NAME, AREA_USER_NAME, EQUI_USE_ID)
group by MCSTA_NAME, AREA_USER_NAME);
EXCEPTION
WHEN OTHERS THEN
P_RS := -1;
v_errorcode:=sqlcode;
v_errortext:=substr
(
sqlerrm,1,1000
);
INSERT INTO BS_SYS_EVENT
(
EVENT_SENDER,
EVENT_TYPE,
EVENT_MSG
)
VALUES
(
'P_RPT_PUMP_HOURS',
'ERROR',
'sqlcode:'
||v_errorcode
||' sqlerrm:'
||v_errortext
);
RETURN;
END P_RPT_PUMP_HOURS_DD;
SQL单拿出来运行肯定没有问题
我把它写成存储过程调用就报“ORA-01002: 提取违反顺序”
实在想不通到底错误出在哪里
|
