日期:2014-05-16 浏览次数:20890 次
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;
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,