这个SQL异常不少，如何解决？-Oracle教程-爱易网页

这个SQL异常不少，如何解决？

日期：2014-05-16　浏览次数：20840 次

这个SQL错误不少，怎么解决？？

SQL code

create or replace procedure SP_GIS_Get_VehicleSpeedOmeter1(mcuid       IN INTEGER,
                                                           selectYear  IN INTEGER,
                                                           selectMonth IN INTEGER,
                                                           startDay    in Integer,
                                                           endday      in integer,
                                                           curCursor   OUT SYS_REFCURSOR) as
  strSql            varchar2(2000);
  mcuid_value       INTEGER;
  selectYear_value  INTEGER;
  selectMonth_value INTEGER;
  begindate         date;
  enddate           date;
begin
  mcuid_value       := mcuid;
  selectYear_value  := selectYear;
  selectMonth_value := selectMonth;
  begindate         := to_date(to_char(selectYear) || '-' ||
                               to_char(selectMonth) || '-' ||
                               to_char(startDay)||' 00:00:00',
                               'yyyy-MM-dd HH:mm:ss');
  enddate           := to_date(to_char(selectYear) || '-' ||
                               to_char(selectMonth) || '-' ||
                               to_char(endday)||' 00:00:00',
                               'yyyy-MM-dd HH:mm:ss');
  strSql            := 'select t.MCUID,
       p2 as syear,
       p3 as smonth,
       extract(DAY FROM t.RECEIVETIME) as sday,
       (MAX(SPEEDOMETER) - MIN(SPEEDOMETER)) / 1000.0 as DayOfSpeedOmeter
  from bmps_his_receive_gpsinfo t
 where t.speedometer > 0
   and t.MCUID = :p1
   and t.RECEIVETIME  between p4 andp5
 GROUP BY t.MCUID,
          extract(YEAR FROM t.RECEIVETIME),
          extract(Month FROM t.RECEIVETIME),
          extract(DAY FROM t.RECEIVETIME)';
  OPEN curCursor FOR strSql
    USING mcuid_value, selectYear_value, selectMonth_value,begindate,enddate;

end SP_GIS_Get_VehicleSpeedOmeter1;

不知道怎么利用传递的参数，p1,p2,p3，p4，p5 。另外知道年月日怎么转化为时间，我的很笨的方式转的。
谢谢前辈们帮我纠错。。

------解决方案--------------------
:p1就像这样用就对了，按顺序传递；
另外你的开始时间结束时间为什么不设计为date类型呢？
------解决方案--------------------

SQL code


create or replace procedure SP_GIS_Get_VehicleSpeedOmeter1(mcuid       IN INTEGER,
                                                           selectYear  IN INTEGER,
                                                           selectMonth IN INTEGER,
                                                           startDay    in Integer,
                                                           endday      in integer,
                                                           curCursor   OUT SYS_REFCURSOR) as
  strSql            varchar2(2000);
  mcuid_value       INTEGER;
  selectYear_value  INTEGER;
  selectMonth_value INTEGER;
  begindate         date;
  enddate           date;
begin
  mcuid_value       := mcuid;
  selectYear_value  := selectYear;
  selectMonth_value := selectMonth;
  begindate         := to_date(to_char(selectYear) || '-' ||
                               to_char(selectMonth) || '-' ||
                               to_char(startDay)||' 00:00:00',
                               'yyyy-MM-dd HH:mm:ss');
  enddate           := to_date(to_char(selectYear) || '-' ||
                               to_char(selectMonth) || '-' ||
                               to_char(endday)||' 00:00:00',
                               'yyyy-MM-dd HH:mm:ss');
  strSql            := 'select t.MCUID,
       p2 as syear,
       p3 as smonth,
       extract(DAY FROM t.RECEIVETIME) as sday,
       (MAX(SPEEDOMETER) - MIN(SPEEDOMETER)) / 1000.0 as DayOfSpeedOmeter
  from bmps_his_receive_gpsinfo t
 where t.speedometer > 0
   and t.MCUID = :p1
   and t.RECEIVETIME  between :p2 and :p3
 GROUP BY t.MCUID,

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

这个SQL异常不少，如何解决？

相关资料更多>

推荐阅读更多>