Oracle Research on paging method
- 1、Oracle Three paging methods in
- 1.1、 By analyzing function paging
- 1.2、 adopt ROWNUM Pagination
- 1.3、 adopt ROWID Pagination
- 2、Oracle Paging solution analysis
- 2.1、 Pure back-end code completes paging
- 2.2、 Paging through stored procedures
- 2.3、 Two common paging stored procedures
- 3、 summary
1、Oracle Three paging methods in
I recently summed up Oracle Pagination in , From pure SQL In writing , Paging is nested subquery , It’s just that different paging methods have different levels of nested subqueries .Oracle There are three kinds of pagination in , Namely : Nested analysis function of a layer of sub queries 、 Nested two layers of subqueries ROWNUM Paging and nesting three levels of sub query ROWID Pagination .
1.1、 By analyzing function paging
Sort by employee age , Each page shows 3 Employees , Take the first place 1 Pages of data . Nested only one level of subqueries , The writing is concise , Easy to understand , But generally no one uses this method . The sorting function can be realized by sorting within the analysis function of the subquery .
SELECT t2.staff_name,t2.birthday FROM( SELECT t1.staff_name,t1.birthday,ROW_NUMBER() OVER(ORDER BY t1.birthday) rn FROM demo.t_staff t1 ) t2 WHERE t2.rn >= ((1-1)*3+1) AND t2.rn <= (1*3);
1.2、 adopt ROWNUM Pagination
Sort by employee age , Each page shows 3 Employees , Take the first place 1 Pages of data . Nested two levels of subqueries , The writing is flexible , It’s usually this way . You only need to sort inside the subquery to realize the sorting function .
SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday ) t2 WHERE ROWNUM <= (1*3) ) t3 WHERE t3.rn >= ((1-1)*3+1);
adopt ROWNUM An alternative to pagination ( It’s relatively better to understand ):
SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday ) t2 ) t3 WHERE t3.rn >= ((1-1)*3+1) AND t3.rn <= (1*3);
1.3、 adopt ROWID Pagination
Sort by employee age , Each page shows 3 Employees , Take the first place 1 Pages of data . The writing is complicated , Not very flexible , Not easy to understand , Very few people use this method . You must sort in both the innermost sub query and the outermost query to realize the sorting function .
SELECT t4.staff_name,t4.birthday FROM demo.t_staff t4 WHERE t4.ROWID IN( SELECT t3.rid FROM( SELECT t2.rid,ROWNUM rn FROM( SELECT t1.ROWID rid FROM demo.t_staff t1 ORDER BY t1.birthday ) t2 WHERE ROWNUM <= (1*3) ) t3 WHERE t3.rn >= ((1-1)*3+1) ) ORDER BY t4.birthday;
2、Oracle Paging solution analysis
Oracle The second is the most widely used of the three paging methods in , That is based on ROWNUM The pagination method of . Because the syntax for pagination is fixed , Therefore, a common paging template method will be provided in general projects , Then other business methods that need paging call this method to complete the paging function .
The implementation process of paging is splicing SQL Statement procedure , But there’s also a choice to complete it in that place . Generally speaking, splicing on the server side is a better choice , The main advantage of this scheme is flexibility 、 Simple 、 Easy maintenance . Another common method is paging through stored procedures , Then call the stored procedure on the server side , In theory, this scheme has high paging efficiency , But the implementation process is relatively complex , It’s not as easy to maintain as pure server-side code .
2.1、 Pure back-end code completes paging
Pure back-end code completes paging in the definition 、 call 、 performance 、 understand 、 There are a lot of small skills in maintenance, etc . A few days ago, I combined my paging experience over the years with a technical expert who has worked in the company for more than ten years to exchange this problem , In the end, we agreed that the best way is to pass the whole inner layer sub query ( It can avoid a lot of small pits ). The splicing format is as follows :
SELECT t3.* FROM( SELECT t2.*,ROWNUM rn FROM( :subquery ) t2 WHERE ROWNUM <= (:pageIndex*:pageSize) ) t3 WHERE t3.rn >= ((:pageIndex-1)*:pageSize+1)
We’ve all tried to split subqueries into parts before , And then the way they are delivered separately , But once the project gets deeper, there are more problems than you think . For example, too many parameters make calling more difficult , In order to achieve pagination, we have to split the whole statement into several parts, which is a waste of time , When something goes wrong, the complexity of debugging also increases , Multi table paging is also relatively difficult to handle , Inexperienced programmers often have no patience to understand the existing code, and then fabricate a so-called improved version ( In fact, there are still many such situations )……
But even if the whole subquery comes in , There will still be different ways of dealing with it . For example, the expert I mentioned above said that they had tried to split the incoming subquery into multiple parts and then recombine it , But later found that complex subqueries are extremely difficult to write right , It just increases the frustration of the new people in the team ……
The asterisk in the outer query is the key point , Although we all know that asterisks in queries are often bad , But if you still stick to this point when pagination , It’s bound to lead to complex splicing . Complex stitching is often hard to write , It’s also error prone when calling , From time to time, I have to look back at the internal implementation and deduce how to call it , This process is obviously a waste of time .
2.2、 Paging through stored procedures
Most of the time, I use stored procedures to achieve paging , However, it is difficult for many people to write or even call stored procedures , I think the main reason is that I am not familiar with the relevant knowledge 、 Write less . The following is a list of related reference connections for writing paging stored procedures and calling stored procedures :
- 《.Net Programmers learn to use Oracle series (7): View 、 function 、 stored procedure 、 package 》: stored procedure
- 《.Net Programmers learn to use Oracle series (26):PLSQL The types of 、 Variables and structures 》: Variable
- 《.Net Programmers learn to use Oracle series (27):PLSQL The cursor of 、 Exceptions and transactions 》: The cursor
- 《.Net Programmers learn to use Oracle series (16): Access database (ODP.NET)》: Oracle provides the drive
Here is a call Oracle Of paged stored procedures C# Method :
/// <summary> /// Calling stored procedure , Perform paging /// </summary> /// <param name="tableName"> Table name </param> /// <param name="queryFields"> Inquire about ( Field ) list </param> /// <param name="queryWhere"> Query criteria </param> /// <param name="orderBy"> Sort clause </param> /// <param name="pageIndex"> Page index ( Page number )</param> /// <param name="pageSize"> Page size ( Number of data bars per page )</param> /// <param name="pageCount"> Total number of pages </param> /// <param name="rowCount"> Total number of lines </param> /// <param name="resultSet"> Result set </param> public void ExecutePaging( string tableName, string queryFields, string queryWhere, string orderBy, int pageIndex, int pageSize, ref int pageCount, ref int rowCount, ref DataTable resultSet) { OracleParameter[] ps = { new OracleParameter(":tableName", OracleDbType.Varchar2, 1000), new OracleParameter(":queryFields", OracleDbType.Varchar2, 1000), new OracleParameter(":queryWhere", OracleDbType.Varchar2, 2000), new OracleParameter(":orderBy", OracleDbType.Varchar2, 200), new OracleParameter(":pageIndex", OracleDbType.Int32), new OracleParameter(":pageSize", OracleDbType.Int32), new OracleParameter(":pageCount", OracleDbType.Int32), new OracleParameter(":rowCount", OracleDbType.Int32), new OracleParameter(":resultSet", OracleDbType.RefCursor) }; ps[0].Value = tableName; ps[1].Value = queryFields; ps[2].Value = queryWhere; ps[3].Value = orderBy; ps[4].Value = pageIndex; ps[5].Value = pageSize; ps[6].Direction = ParameterDirection.Output; ps[7].Direction = ParameterDirection.Output; ps[8].Direction = ParameterDirection.Output; resultSet = OracleHelper.ProcQuery("sp_dynamic_paging", ps); // Calling stored procedure pageCount = Verifier.VerifyInt(ps[6].Value); rowCount = Verifier.VerifyInt(ps[7].Value); }
2.3、 Two common paging stored procedures
The following stored procedure is extracted from a project I was responsible for , It’s also my first attempt to write stored procedure paging ,100% original , There have been several revisions in the middle , For the convenience of reading, the notes have been removed by me , Now in this version i_queryFields Parameters don’t accept asterisks :
CREATE OR REPLACE PROCEDURE sp_paging( i_tableName VARCHAR2, -- Table name i_queryFields VARCHAR2, -- Inquire about ( Field ) list i_queryWhere VARCHAR2, -- Query criteria i_orderBy VARCHAR2, -- Sort clause i_pageIndex NUMBER, -- Current page index i_pageSize NUMBER, -- Page size o_rowCount OUT NUMBER, -- Total number of lines o_pageCount OUT NUMBER, -- Total number of pages o_resultSet OUT SYS_REFCURSOR -- Result set ) IS v_count_sql VARCHAR2(2000); v_select_sql VARCHAR2(4000); BEGIN -- Splice the statements to query the total number of rows v_count_sql := 'SELECT COUNT(1) FROM '||i_tableName; -- Splicing query criteria IF i_queryWhere IS NOT NULL THEN v_count_sql := v_count_sql||' WHERE 1=1 '||i_queryWhere; END IF; -- Calculate the total number EXECUTE IMMEDIATE v_count_sql INTO o_rowCount; --DBMS_OUTPUT.PUT_LINE(v_count_sql||';'); -- Calculate the total number of pages (CEIL Rounding up ) o_pageCount := CEIL(o_rowCount / i_pageSize); -- If there's a record , And the current page index is legal , Then continue to query IF o_rowCount >= 1 AND i_pageIndex >= 1 AND i_pageIndex <= o_pageCount THEN -- When the total number of records is less than or equal to the page size , Check all records IF o_rowCount <= i_pageSize THEN v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN v_select_sql := v_select_sql||' order by '||i_orderBy; END IF; -- Check the first page ELSIF i_pageIndex = 1 THEN v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN v_select_sql := v_select_sql||' order by '||i_orderBy; END IF; v_select_sql := 'SELECT '||i_queryFields||' FROM('||v_select_sql||') WHERE ROWNUM<='||i_pageSize; -- Query the specified page ELSE v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN v_select_sql := v_select_sql||' order by '||i_orderBy; END IF; v_select_sql := 'SELECT '||i_queryFields||' FROM(SELECT ROWNUM rn,'||i_queryFields||' FROM('||v_select_sql ||')) WHERE rn>'||((i_pageIndex-1)*i_pageSize)||' AND rn<='||(i_pageIndex*i_pageSize); END IF; --DBMS_OUTPUT.PUT_LINE(v_select_sql||';'); OPEN o_resultSet FOR v_select_sql; ELSE OPEN o_resultSet FOR 'SELECT * FROM '||i_tableName||' WHERE 1!=1'; END IF; END;
The following stored procedure is taken from 《 The sword breaks the iceberg ——Oracle Developing art 》 A Book , There is a deletion :
OR REPLACE PROCEDURE sp_dynamic_paging( i_tableName VARCHAR2, -- Table name i_queryFields VARCHAR2, -- Query list i_queryWhere VARCHAR2, -- Query criteria i_orderBy VARCHAR2, -- Sort i_pageSize NUMBER, -- Page size i_pageIndex NUMBER, -- Page index o_rowCount OUT NUMBER, -- Return the total number o_pageCount OUT NUMBER, -- Returns the total number of pages o_resultSet OUT SYS_REFCURSOR -- Returns the paged result set ) IS v_startRows INT; -- Go ahead v_endRows INT; -- End line v_pageSize INT; v_pageIndex INT; v_queryFields VARCHAR2(2000); v_queryWhere VARCHAR2(2000); v_orderBy VARCHAR2(200); v_count_sql VARCHAR2(1000); -- Receiving the number of statistics SQL sentence v_select_sql VARCHAR2(4000); -- Receive query paging data SQL sentence BEGIN -- If there is no table name , The exception message is returned directly -- If there are no fields , It means to query all fields IF i_queryFields IS NOT NULL THEN v_queryFields:=i_queryFields; ELSE v_queryFields:=' * '; END IF; -- There can be no query conditions IF i_queryWhere IS NOT NULL THEN v_queryWhere := ' WHERE 1=1 AND'||i_queryWhere||' '; ELSE v_queryWhere := ' WHERE 1=1 '; END IF; -- There can be no sort conditions IF i_orderBy IS NULL THEN v_orderBy:=' '; ELSE v_orderBy:='ORDER BY '||i_orderBy; END IF; -- If no query page is specified , The default is home page IF i_pageIndex IS NULL OR i_pageIndex<1 THEN v_pageIndex:=1; ELSE v_pageIndex:=i_pageIndex; END IF; -- If the number of records per page is not specified , The default is 10 strip IF i_pageSize IS NULL THEN v_pageSize:=10; ELSE v_pageSize:=i_pageSize; END IF; -- Construct the total number of queries v_count_sql:='SELECT COUNT(1) FROM '||i_tableName||v_queryWhere; --DBMS_OUTPUT.PUT_LINE(v_count_sql||';'); -- Construct statements to query data v_select_sql:='(SELECT '||v_queryFields||' FROM '||i_tableName||v_queryWhere||v_orderBy||') t2'; -- Total number of queries EXECUTE IMMEDIATE v_count_sql INTO o_rowCount; -- Get the total number of pages IF MOD(o_rowCount,i_pageSize)=0 THEN o_pageCount:=o_rowCount/i_pageSize; ELSE o_pageCount:=FLOOR(o_rowCount/i_pageSize)+1; END IF; -- If the current page is greater than the maximum number of pages , Take the maximum number of pages IF i_pageIndex>o_pageCount THEN v_pageIndex:=o_pageCount; END IF; -- Set the number of records that start and end v_startRows:=(v_pageIndex-1)*v_pageSize+1; v_endRows:=v_pageIndex*v_pageSize; -- The dynamics of completion SQL Statement splicing v_select_sql:='SELECT t3.* FROM'||'(SELECT t2.*,ROWNUM rn FROM'||v_select_sql ||' WHERE ROWNUM<='||v_endRows||') t3 WHERE t3.rn>='||v_startRows; --DBMS_OUTPUT.PUT_LINE(v_select_sql||';'); OPEN o_resultSet FOR v_select_sql; END;
The following paragraph PL/SQL The code is used to test the above two stored procedures :
DECLARE v_tableName VARCHAR2(1000); v_queryFields VARCHAR2(1000); v_queryWhere VARCHAR2(1000); v_orderBy VARCHAR2(200); v_pageSize INT := 3; v_pageIndex INT; v_rowCount INT := 0; v_pageCount INT := 0; v_resultSet SYS_REFCURSOR; BEGIN v_tableName:='t_staff'; v_queryFields:='staff_name,birthday'; v_orderBy:='birthday'; v_pageIndex:=1; sp_dynamic_paging( i_tableName => v_tableName, i_queryFields => v_queryFields, i_queryWhere => v_queryWhere, i_orderBy => v_orderBy, i_pageSize => v_pageSize, i_pageIndex => v_pageIndex, o_rowCount => v_rowCount, o_pageCount => v_pageCount, o_resultSet => v_resultSet ); END;
3、 summary
This article mainly describes Oracle Three paging methods in and two common paging solutions , And gives two general paging stored procedure source code . It’s mainly about what I have personally Oracle This is a comprehensive review of paging methods and techniques .