Oracle Stored Procedure Paging

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