CREATE OR REPLACE PROCEDURE prc_query (p_tablenameinchVARCHAR2,--Table name P_strwhereinchVARCHAR2,--Query Condition P_ordercolumninchVARCHAR2,--sorted column P_orderstyleinchVARCHAR2,--Sort Method P_curpageinch outNumber,--Current Page P_pagesizeinch outNumber,--number of record bars per page p_totalrecords outNumber,--total number of records p_totalpages outNumber,--Total Pages V_cur outPkg_query.cur_query)--The returned result set is V_sql VARCHAR2 ( +) :="'; --SQL statement V_startrecord number (4); --the number of record bars to start displaying V_endrecord numbers (4); --number of record bars to end the display begin--total number of records in 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 page record size IF p_pagesize<0Then p_pagesize:=0; END IF; --calculate total pages based on page size IF MOD (p_totalrecords,p_pagesize)=0Then p_totalpages:= P_totalrecords/p_pagesize; ELSE p_totalpages:= P_totalrecords/p_pagesize +1; END IF; --Verify page number IF p_curpage<1Then p_curpage:=1; END IF; IF P_curpage>P_totalpages then p_curpage:=p_totalpages; END IF; --Implement paged 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;
3. Call the stored procedure in the data layer, return a DataTable and two parameters, the DataTable stores the data, curpage the current page number (the stored procedure queries the data based on the values passed in, and returns a DataTable.) ), PageCount is the total number of pages, RecordCount is the total data bar
public class Paging {//<summary>///</summary>/<param name= "TNa Me > Table name </param>//<param name= "strSQL" > Query conditions </param>///<param Name= "Sortrow" > Rows Sequential columns </param>//<param name= "SortMethod" > Sort by </param>//<param name= "curpage" > Current page & lt;/param>//<param name= "PageSize" > shows the number of records per page </param>//<param name= "PageCount" > Total Pages & lt;/param>//<param name= "RecordCount" > Total 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=.;D ATA 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 the column cmd. parameters["P_ordercolumn"]. Direction = ParameterDirection.Input; Cmd. parameters["P_ordercolumn"]. Value = Sortrow; Cmd. Parameters.Add ("P_orderstyle", Oracletype.varchar, 3000); Sort by cmd. parameters["P_orderstyle"]. Direction = ParameterDirection.Input; Cmd. parameters["P_orderstyle"]. Value = SortMethod; Cmd. Parameters.Add ("P_curpage", OracleType. number); The 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 record bars per page is displayed in CMD. parameters["P_pagesize"]. Direction = ParameterDirection.Input; Cmd. parameters["P_pagesize"]. Value = pageSize; Cmd. Parameters.Add ("P_totalrecords", Oracletype.number); Total record number of CMD. parameters["P_totalrecords"]. Direction = ParameterDirection.Output; Cmd. parameters["P_totalrecords"]. Value = 0; Cmd. Parameters.Add ("P_totalpages", Oracletype.number); Total page cmd. parameters["P_totalpages"]. Direction = ParameterDirection.Output; Cmd. parameters["P_totalpages"]. Value = 0; Cmd. Parameters.Add ("V_cur", oracletype.cursor); Returns the 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 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]; } }
4. Presentation Layer Invocation Method Quickpage
。。。
Oracle Stored Procedure Paging