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

Question

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

Answer 1

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

Answer 2

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;改成这样再试试

Answer 3

第一,错误提示是什么?
第二,传递参数: 
     USING mcuid_value, selectYear_value, selectMonth_value,begindate,enddate;               p1,         p2,                   p3,             p4,    p5
如上对应! 

Answer 4

、
我没看明白。。
你这里的p1,p2,p3，p4，p5 是什么？
是字段还是变量？
用法都不对。

Answer 5

mcuid_value       := mcuid;
  selectYear_value  := selectYear;
  selectMonth_value := selectMonth  
这些你说明你已经用到你传递的参数了撒,你将mcuid 这个值赋给了 mcuid_value ;
不知道你是不是想问,你调用这个存储过程的时候怎样传递?
declare
outVal varchar2(100);
begin过程名(p1,p2,....,p_out) ;end;

Answer 6

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 hh24:mi:ss');
  enddate           := to_date(to_char(selectYear) || '-' ||
                               to_char(selectMonth) || '-' ||
                               to_char(endday) || ' 00:00:00',
                               'yyyy-mm-dd hh24:mi:ss');
  strSql            := 'select t.MCUID,
       p1 as syear,
       p2 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 = :p3
   and t.LOCATESTATE!=0
   and t.RECEIVETIME between :p4 and :p5
 GROUP BY t.MCUID,
          extract(YEAR FROM t.RECEIVETIME),
          extract(Month FROM t.RECEIVETIME),
          extract(DAY FROM t.RECEIVETIME);';
  OPEN curCursor FOR strSql
    USING selectYear_value, selectMonth_value, mcuid_value, begindate, enddate;end SP_GIS_Get_VehicleSpeedOmeter1;
这个总是报错 无效字符。