当前位置：首页 → 问答吧 → 这个SQL错误不少，怎么解决？？

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

时间：2011-09-23

来源：互联网

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 。另外知道年月日怎么转化为时间，我的很笨的方式转的。
谢谢前辈们帮我纠错。。

作者: xx_mm 发布时间: 2011-09-23

nobody response！！！！

作者: xx_mm 发布时间: 2011-09-23

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

作者: zxf261 发布时间: 2011-09-23

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,
          extract(YEAR FROM t.RECEIVETIME),
          extract(Month FROM t.RECEIVETIME),
          extract(DAY FROM t.RECEIVETIME)';
  OPEN curCursor FOR strSql
    USING mcuid_value,begindate,enddate;

end SP_GIS_Get_VehicleSpeedOmeter1;

改成这样再试试

作者: zxf261 发布时间: 2011-09-23

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

热门阅读

热门下载