这句SQL语句拿错了，相当纠结！！

Question

create or replace procedure SP_GIS_Get_Vehicle_SpeedOmeter
(
       mcuid INTEGER,
       selectYear Integer,
       selectMonth   Integer,
       curCursor out SYS_REFCURSOR
)
as
       strSql varchar2(2000);
       mcuid_value INTEGER;
       selectYear_value INTEGER;
       selectMonth_value INTEGER;begin
       mcuid_value := mcuid;
       selectYear_value := selectYear;
       selectMonth_value := selectMonth;
       
        strSql:='select t.MCUID,t.DEVICENO,
        extract(YEAR FROM t.RECEIVETIME) as syear,
        extract(MONTH  FROM t.RECEIVETIME) as smonth,
        extract(DAY FROM t.RECEIVETIME) as sday,
        (MAX(SPEEDOMETER)-MIN(SPEEDOMETER)) as DayOfSpeedOmeter 
        from bmps_his_receive_gpsinfo t
        GROUP BY t.MCUID,t.DEVICENO,extract(YEAR FROM t.RECEIVETIME),extract(Month FROM t.RECEIVETIME),extract(DAY FROM t.RECEIVETIME)
        HAVING t.MCUID=:p1
        AND extract(YEAR FROM t.RECEIVETIME)=:p2 
        AND extract(MONTH FROM t.RECEIVETIME)=:p3 
        t.MCUID ASC,t.DEVICENO ASC,extract(YEAR FROM t.RECEIVETIME) ASC,
        extract(MONTH  FROM t.RECEIVETIME)ASC ,extract(DAY FROM t.RECEIVETIME) ASC';
        open curCursor for strSql USING mcuid_value,selectYear_value,selectMonth_value;
  
end SP_GIS_Get_Vehicle_SpeedOmeter;

Answer 1

缺少Order Byselect t.MCUID,t.DEVICENO,
        extract(YEAR FROM t.RECEIVETIME) as syear,
        extract(MONTH  FROM t.RECEIVETIME) as smonth,
        extract(DAY FROM t.RECEIVETIME) as sday,
        (MAX(SPEEDOMETER)-MIN(SPEEDOMETER)) as DayOfSpeedOmeter 
        from bmps_his_receive_gpsinfo t
        GROUP BY t.MCUID,t.DEVICENO,extract(YEAR FROM t.RECEIVETIME),extract(Month FROM t.RECEIVETIME),extract(DAY FROM t.RECEIVETIME)
        HAVING t.MCUID=:p1
        AND extract(YEAR FROM t.RECEIVETIME)=:p2 
        AND extract(MONTH FROM t.RECEIVETIME)=:p3 
        Order BY t.MCUID ASC,t.DEVICENO ASC,extract(YEAR FROM t.RECEIVETIME) ASC,
        extract(MONTH  FROM t.RECEIVETIME)ASC ,extract(DAY FROM t.RECEIVETIME) ASC

Answer 2

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_GIS_GET_VEHICLE_SPEEDOMETER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Answer 3

LZ的strSql有相当大的错误啊，我看了都晕，我给你整理整理了一下，你看看吧select t.MCUID,t.DEVICENO,
        extract(YEAR FROM t.RECEIVETIME) as syear,
        extract(MONTH  FROM t.RECEIVETIME) as smonth,
        extract(DAY FROM t.RECEIVETIME) as sday,
        (MAX(SPEEDOMETER)-MIN(SPEEDOMETER)) as DayOfSpeedOmeter 
from bmps_his_receive_gpsinfo t
GROUP BY t.MCUID,
         t.DEVICENO,
         extract(YEAR FROM t.RECEIVETIME),
         extract(Month FROM t.RECEIVETIME),
         extract(DAY FROM t.RECEIVETIME)
HAVING t.MCUID=:p1 AND 
       extract(YEAR FROM t.RECEIVETIME)=:p2  AND
       extract(MONTH FROM t.RECEIVETIME)=:p3 
       t.MCUID ASC,t.DEVICENO ASC,
       extract(YEAR FROM t.RECEIVETIME) ASC,
       extract(MONTH  FROM t.RECEIVETIME)ASC ,
       extract(DAY FROM t.RECEIVETIME) ASC
/*
HAVING t.MCUID=:p1 AND 
       extract(YEAR FROM t.RECEIVETIME)=:p2  AND
       extract(MONTH FROM t.RECEIVETIME)=:p3 
       从这里往下，我就看不懂了
       t.MCUID ASC,
       t.DEVICENO ASC,
       extract(YEAR FROM t.RECEIVETIME) ASC,
       extract(MONTH  FROM t.RECEIVETIME)ASC ,
       extract(DAY FROM t.RECEIVETIME) ASC
*/

Answer 4

就是ＳＱＬ中缺少ORDER BY了

Answer 5

抱歉复制过来正在修改丢了Order by ，原句是杨白劳那样的，报的错误是：ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_GIS_GET_VEHICLE_SPEEDOMETER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Answer 6

select t.MCUID,t.DEVICENO,
        extract(YEAR FROM t.RECEIVETIME) as syear,
        extract(MONTH  FROM t.RECEIVETIME) as smonth,
        extract(DAY FROM t.RECEIVETIME) as sday,
        (MAX(SPEEDOMETER)-MIN(SPEEDOMETER)) as DayOfSpeedOmeter 
from bmps_his_receive_gpsinfo t
group by t.MCUID,
         t.DEVICENO,
         extract(YEAR FROM t.RECEIVETIME),
         extract(Month FROM t.RECEIVETIME),
         extract(DAY FROM t.RECEIVETIME)
having t.MCUID=:p1 and 
       extract(YEAR FROM t.RECEIVETIME)=:p2  and
       extract(MONTH FROM t.RECEIVETIME)=:p3 
order by
       t.MCUID ASC,t.DEVICENO ASC,
       extract(YEAR FROM t.RECEIVETIME) ASC,
       extract(MONTH  FROM t.RECEIVETIME)ASC ,
       extract(DAY FROM t.RECEIVETIME) ASC

Answer 7

原来 我在C#代码中有误，SQL没错 哈哈！