Oracle stored procedure page

1. First, create a stored PROCEDURE in oracle. The stored PROCEDURE name library PROCEDURE prc_query

Create or replace procedure prc_query
(P_tableName in varchar2, -- table name
P_strWhere in varchar2, -- Query Condition
P_orderColumn in varchar2, -- Sort Columns
P_orderStyle in varchar2, -- sorting method
P_curPage in out Number, -- current page
P_pageSize in out Number, -- Number of records displayed per page
P_totalRecords out Number, -- total Number of records
P_totalPages out Number, -- total Number of pages
V_cur out pkg_query.cur_query) -- returned result set
IS
V_ SQL VARCHAR2 (1000): = ''; -- SQL statement
V_startRecord Number (4); -- Number of records that start to be displayed
V_endRecord Number (4); -- Number of records displayed after the end
BEGIN
-- Total number of records in a record
V_ SQL: = 'select TO_NUMBER (COUNT (*) FROM '| p_tableName | 'where 1 = 1 ';
IF p_strWhere is not null or p_strWhere <> ''then
V_ SQL: = v_ SQL | p_strWhere;
End if;
Execute immediate v_ SQL INTO p_totalRecords;

-- Verify the page record size
IF p_pageSize <0 THEN
P_pageSize: = 0;
End if;

-- Calculate the total number of pages based on the page size
If mod (p_totalRecords, p_pageSize) = 0 THEN
P_totalPages: = p_totalRecords/p_pageSize;
ELSE
P_totalPages: = p_totalRecords/p_pageSize + 1;
End if;

-- Verify the page number
IF p_curPage <1 THEN
P_curPage: = 1;
End if;
IF p_curPage> p_totalPages THEN
P_curPage: = p_totalPages;
End if;

-- Implement paging Query
V_startRecord: = (p_curPage-1) * p_pageSize + 1;
V_endRecord: = p_curPage * p_pageSize;
V_ SQL: = 'select * FROM (SELECT A. *, rownum r from' |
'(SELECT * FROM' | p_tableName;
IF p_strWhere is not null or p_strWhere <> ''then
V_ SQL: = v_ SQL | 'where 1 = 1' | p_strWhere;
End if;
IF p_orderColumn is not null or p_orderColumn <> ''then
V_ SQL: = v_ SQL | 'ORDER BY' | p_orderColumn | ''| p_orderStyle;
End if;
V_ SQL: = v_ SQL | ') a where rownum <=' | v_endRecord | ') B WHERE r> ='
| V_startRecord;
DBMS_OUTPUT.put_line (v_ SQL );
OPEN v_cur FOR v_ SQL;
END prc_query;

 

 

2. when a stored procedure is called in the data layer, a able and two parameters are returned. The datatable stores the data and the curPage is the current page number (the Stored Procedure queries the data based on the input value and returns the datatable .), PageCount indicates the total number of pages, and RecordCount indicates the total number of data items.

Public class Paging
{
/// <Summary>
///
/// </Summary>
/// <Param name = "tName"> table name </param>
/// <Param name = "strSql"> query condition </param>
/// <Param name = "sortRow"> sort columns </param>
/// <Param name = "sortMethod"> sorting method </param>
/// <Param name = "curPage"> current page </param>
/// <Param name = "pageSize"> Number of records per page </param>
/// <Param name = "pageCount"> total number of pages </param>
/// <Param name = "RecordCount"> total number of records </param>
/// <Returns> </returns>
Public DataTable QuickPage (string tName, string strSql, string sortRow, string sortMethod, int curPage, int pageSize, ref int pageCount, ref int RecordCount)
{
OracleConnection conn = new OracleConnection ("server =.; Data Source = db; User ID = zsxl1; Password = zsxl ;");
OracleCommand cmd = new OracleCommand ();
Cmd. Connection = conn;
Cmd. CommandText = "prc_query ";
Cmd. CommandType = CommandType. StoredProcedure;

Cmd. Parameters. Add ("p_tableName", OracleType. VarChar, 50); // table name
Cmd. Parameters ["p_tableName"]. Direction = ParameterDirection. Input;
Cmd. Parameters ["p_tableName"]. Value = tName;

Cmd. Parameters. Add ("p_strWhere", OracleType. VarChar, 3000); // query Condition
Cmd. Parameters ["p_strWhere"]. Direction = ParameterDirection. Input;
Cmd. Parameters ["p_strWhere"]. Value = strSql;

Cmd. Parameters. Add ("p_orderColumn", OracleType. VarChar, 3000); // sort Columns
Cmd. Parameters ["p_orderColumn"]. Direction = ParameterDirection. Input;
Cmd. Parameters ["p_orderColumn"]. Value = sortRow;

Cmd. Parameters. Add ("p_orderStyle", OracleType. VarChar, 3000); // sorting method
Cmd. Parameters ["p_orderStyle"]. Direction = ParameterDirection. Input;
Cmd. Parameters ["p_orderStyle"]. Value = sortMethod;

Cmd. Parameters. Add ("p_curPage", OracleType. Number); // current page
Cmd. Parameters ["p_curPage"]. Direction = ParameterDirection. Input;
Cmd. Parameters ["p_curPage"]. Value = curPage;

Cmd. Parameters. Add ("p_pageSize", OracleType. Number); // The Number of records per page.
Cmd. Parameters ["p_pageSize"]. Direction = ParameterDirection. Input;
Cmd. Parameters ["p_pageSize"]. Value = pageSize;

Cmd. Parameters. Add ("p_totalRecords", OracleType. Number); // The total Number of records
Cmd. Parameters ["p_totalRecords"]. Direction = ParameterDirection. Output;
Cmd. Parameters ["p_totalRecords"]. Value = 0;

Cmd. Parameters. Add ("p_totalPages", OracleType. Number); // the total Number of pages.
Cmd. Parameters ["p_totalPages"]. Direction = ParameterDirection. Output;
Cmd. Parameters ["p_totalPages"]. Value = 0;

Cmd. Parameters. Add ("v_cur", OracleType. Cursor); // The returned Cursor.
Cmd. Parameters ["v_cur"]. Direction = ParameterDirection. Output;

DataSet Ds = new DataSet ();
OracleDataAdapter adapter = new OracleDataAdapter (cmd );
Adapter. Fill (Ds );
Conn. Close ();

// Total number of records
RecordCount = int. Parse (cmd. Parameters ["p_totalRecords"]. Value. ToString ());
// Total number of pages
PageCount = Convert. ToInt32 (Math. Ceiling (Convert. ToDouble (cmd. Parameters ["p_totalPages"]. Value. ToString ())));
// Current page number
// CurPage = int. Parse (cmd. Parameters ["p_curPage"]. Value. ToString ());
// TotalPages = int. Parse (cmd. Parameters ["p_totalPages"]. Value. ToString ());
Return Ds. Tables [0];
}

}

3. Presentation Layer call method QuickPage