- 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, 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;