Oracle存储过程分页示例

CREATE OR REPLACE PROCEDURE P_QUERY_RESULT_BY_ROWNUM(ISTARTROWNUM     NUMBER, --起始记录号
                                                     IENDROWNUM       NUMBER, --结束记录号
                                                     P_SQLSELECT      VARCHAR2, --查询语句,含排序部分,可以多表关联
                                                     P_OUTRECORDCOUNT OUT NUMBER, --返回总记录数
                                                     P_OUTCURSOR      OUT SYS_REFCURSOR) AS
  V_SQL   VARCHAR2(3000);
  V_COUNT NUMBER;
BEGIN
  ----取记录总数
  V_SQL := 'SELECT COUNT(*) FROM (' || P_SQLSELECT || ')';
  EXECUTE IMMEDIATE V_SQL
    INTO V_COUNT;
  P_OUTRECORDCOUNT := V_COUNT;

  V_SQL := 'SELECT *
            FROM (
                  SELECT A.*, ROWNUM RN
                  FROM  (' || P_SQLSELECT || ') A
                  WHERE ROWNUM <= ' || TO_CHAR(IENDROWNUM) || '
                 ) B
            WHERE RN > ' || TO_CHAR(ISTARTROWNUM);
  --注意对ROWNUM别名的使用,第一次直接用ROWNUM,第二次一定要用别名RN
  OPEN P_OUTCURSOR FOR V_SQL;
END P_QUERY_RESULT_BY_ROWNUM;

 

使用示例：

	/**
	 * 历史记录查询统计
	 * @param unitid 车台ID
	 * @param startDate 开始时间
	 * @param endDate 结束时间
	 * @param start 开始行数
	 * @param limit 结束行数
	 * @return
	 */
	public HashMap countHistory(long userid,String unitid,String startDate,String endDate,int startRowNu,int endRowNu){
		HashMap hm  = new HashMap();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT G.TEAM AS GROUPNAME, ");
		sql.append("        U.NUMBERPLATE, ");
		sql.append("        H.CALL_LETTER, ");
		sql.append("        H.LON, ");
		sql.append("        H.LAT, ");
		sql.append("        H.SPEED, ");
		sql.append("        H.COURSE, ");
		sql.append("        H.LOC_STATE, ");
		sql.append("        H.GPSTIME, ");
		sql.append("        H.STAMP, ");
		sql.append("        H.STATE, ");
		sql.append("        H.ISALARM, ");
		sql.append("        H.REFERENCEPOSITION ");
		sql.append("   FROM T_HISTORY H, T_USER_CAR U, T_TEAM G ");
		sql.append("  WHERE H.CALL_LETTER = U.CALL_LETTER ");
		sql.append("    AND U.TEAMID = G.TEAMID ");
		sql.append("    AND U.UNITID IN ("+unitid+") ");
		sql.append("    AND U.USERID = "+userid+" ");
		sql.append("    AND H.STAMP BETWEEN ");
		sql.append("        TO_DATE('"+startDate+"', 'YYYY-MM-DD HH24:MI:SS') AND ");
		sql.append("        TO_DATE('"+endDate+"', 'YYYY-MM-DD HH24:MI:SS') ");
		sql.append("    ORDER BY U.NUMBERPLATE,H.GPSTIME DESC ");
		//System.out.println("sql.toString():"+sql.toString());
		Connection conn = con.getConnection();
		CallableStatement cstm = null;
		ResultSet rs = null;
		List list = new ArrayList();
		int rowcount=0;
		try{
			conn = 	con.getConnection();
			cstm = conn.prepareCall ("CALL P_QUERY_RESULT_BY_ROWNUM(?,?,?,?,?)");   
			cstm.setInt(1, startRowNu);
			cstm.setInt(2, endRowNu);
			cstm.setString(3, sql.toString());
            cstm.registerOutParameter(4, OracleTypes.INTEGER);
            cstm.registerOutParameter(5, OracleTypes.CURSOR);
            cstm.execute();
            rowcount = cstm.getInt(4);
            rs = (ResultSet)cstm.getObject(5);
			HashMap hmp = null;
			int  j=0;
			ResponseStatus databean = new ResponseStatus();
			while(rs.next()){
				hmp = new HashMap();
		        int collength = rs.getMetaData().getColumnCount();
		        String[] coldata = new String[collength];
		        //System.out.println(collength);
		        for (int i = 1; i <= collength; i++) {
		        	//if(j==0){
		        	//	System.out.println("rs.getMetaData().getColumnName("+i+"):"+rs.getMetaData().getColumnName(i)+"="+rs.getString(i));
		        	//}
		        	hmp.put(rs.getMetaData().getColumnName(i).toLowerCase(), rs.getString(i));
		        }
		        //j++;
		        
		        hmp.put("state",databean.getCnState(rs.getString("STATE")));
		        hmp.put("loc_state",databean.getLocState(rs.getInt("LOC_STATE")));
		        hmp.put("isalarm",databean.getIsAlarm(rs.getInt("ISALARM")));
		        hmp.put("course",databean.ChangCourseCH(rs.getDouble("COURSE")));
				list.add(hmp);
			}
		}catch(Exception ex){
			System.out.println("CountDao.countHistory error");
			ex.printStackTrace();
		}finally{
			con.CloseCstam(rs, cstm, Param.DB_CONN, conn);
		}
		hm.put("list", list);
		hm.put("rowcount", rowcount);
		return hm;
	}

 

前台页面：

HistoryCountGrid = function() {
	var cm = new Ext.grid.ColumnModel([new Ext.grid.RowNumberer(), {
				header : '单位名称',
				dataIndex : 'groupname',
				width : 80
			}, {
				header : '车牌号码',
				dataIndex : 'numberplate',
				width : 80
			}, {
				header : '车载电话',
				dataIndex : 'call_letter',
				width : 90
			}, {
				header : '经度',
				dataIndex : 'lon',
				width : 70
			}, {
				header : '纬度',
				dataIndex : 'lat',
				width : 70
			}, {
				header : '速度(KM/H)',
				dataIndex : 'speed',
				width : 60
			}, {
				header : '方向',
				dataIndex : 'course',
				width : 50/*,
				renderer : parseCourse*/
			}, {
				header : '是否定位',
				dataIndex : 'loc_state',
				width : 60
			}, {
				header : '定位时间',
				dataIndex : 'gpstime',
				width : 120
			}, {
				header : '接收时间',
				dataIndex : 'stamp',
				width : 120
			}, {
				header : '状态',
				dataIndex : 'state',
				width : 100
			}, {
				header : '是否警情',
				dataIndex : 'isalarm',
				width : 55/*,
				renderer : function(v) {
					if (v == 0) {
						return "否";
					}
					return "是";
				}*/
			}, {
				header : '参考位置',
				dataIndex : 'referenceposition',
				width : 100
			}]);

	var store = new Ext.data.Store({
				proxy : new Ext.data.HttpProxy({
							url : 'servlet/CountServlet'
						}),
				reader : new Ext.data.JsonReader({
							root : 'list',
							totalProperty : 'rowcount',
							fields : [{
								name : 'groupname'
							}, {
								name : 'numberplate'
							}, {
								name : 'call_letter'
							}, {
								name : 'lon'
							}, {
								name : 'lat'
							}, {
								name : 'speed'
							}, {
								name : 'course'
							}, {
								name : 'loc_state'
							}, {
								name : 'gpstime'
							}, {
								name : 'stamp'
							}, {
								name : 'state'
							}, {
								name : 'isalarm'
							}, {
								name : 'referenceposition'
							}]
						}),
				listeners : {
					'beforeload' : function(store, options) {
						var beginTime = view.findById("startDate").getValue();
						// alert("beginTime:"+beginTime);
						var endTime = view.findById("endDate").getValue();
						// alert("endtime:"+endtime);
						var typeid = -1;// panel.type.getValue();
						if (beginTime == '' || endTime == '') {
							alert('查询时间不能为空');
							return;
						}
						beginTime = beginTime.format('Y-m-d H:i:s');
						endTime = endTime.format('Y-m-d H:i:s');
						options.params.type = 'HistoryCountGrid';
						options.params.startDate = beginTime;
						options.params.endDate = endTime;
						options.params.unitid = curSelUnitId;
						options.params.typeid = typeid;
					},
					'load' : function(store, record, options) {
						if (record.length > 0) {
							countwindows['alarmPanel'].setPosition(
									document.body.clientWidth - 300,
									document.body.clientHeight - 205);
							countwindows['alarmPanel'].show();
							var map = document.getElementById("mapdiv");
							if (!maplet) {
								maplet = new Map(map, 23.134624, 113.256069, 10);
							}
						}
					}
				}
			});

	this.grid = new Ext.grid.GridPanel({
				store : store,
				border : false,
				loadMask : {
					msg : "正在加载数据，请稍侯……"
				},
				cm : cm,
				viewConfig : {
					forceFit : true,
					enableRowBody : true
				},
				bbar : new Ext.PagingToolbar({
							pageSize : pageSize,
							store : store,
							displayInfo : true,
							displayMsg : '本页显示 {0} - {1} 条 共 {2} 条',
							emptyMsg : "未查到记录"
						}),
				tbar : [{
							text : '历史轨迹统计'
						}, '->', {
							text : '导出Excel',
							handler : function() {
								var beginTime = view.findById("startDate")
										.getValue();
								// alert("beginTime:"+beginTime);
								var endTime = view.findById("endDate")
										.getValue();
								// alert("endTime:"+endTime);
								var typeid = -1;// panel.type.getValue();
								if (beginTime == '' || endTime == '') {
									alert('查询时间不能为空');
									return;
								}
								beginTime = beginTime.format('Y-m-d H:i:s');
								endTime = endTime.format('Y-m-d H:i:s');
								if (curSelUnitId) {
									window.location.href = 'servlet/ExportFile?unitid='
											+ curSelUnitId
											+ '&startDate='
											+ beginTime
											+ '&endDate='
											+ endTime
											+ '&type=history&typeid=-1';
								} else {
									alert("请选择要查询的车辆");
								}
							}
						}],
				listeners : {
					'rowclick' : function(grid, row, e) {
						if (maplet) {
							if (!countwindows['alarmPanel'].isVisible()) {
								countwindows['alarmPanel'].show();
							}
							countwindows['alarmPanel'].toFront();
							var mark = new Marker(parseFloat(grid.getStore()
											.getAt(row).get('lat')),
									parseFloat(grid.getStore().getAt(row)
											.get('lon')), grid.getStore()
											.getAt(row).get('numberplate'));
							maplet.clearMarkers();
							maplet.addMarker(mark);
							maplet.setCenter(parseFloat(grid.getStore()
											.getAt(row).get('lat')),
									parseFloat(grid.getStore().getAt(row)
											.get('lon')));
						}
					}
				}
			});
}
Ext.extend(HistoryCountGrid, Ext.grid);