How to implement pagination in Oracle stored procedures

Almost every Web application will use pagination, so it becomes very important to make it universal and efficient, and to do a paging stored procedure with your own ideas, to share with you, and hopefully get a better solution through discussion.

The reason why you use stored procedures is that you do not need to modify the program code, just modify the code of the stored procedure. But this example is a dynamically generated SQL statement in a stored procedure, and does not know that it will not be able to lose a compile and fast feature of the stored procedure. The code is as follows:

1. First build a package, the user creates a cursor type

Create or replace package pkg_query as

Type cur_query is REF CURSOR;

End Pkg_query;

2. Create a stored procedure

CREATE OR REPLACE PROCEDURE prc_query

(P_tablename in Varchar2,--table name

P_strwhere in Varchar2,--Query criteria

P_ordercolumn in Varchar2,--sorted columns

P_orderstyle in Varchar2,--sorting method

P_curpage in Out number--current page

P_pagesize in Out number--Display record bars per page

P_totalrecords out number,--Total records

P_totalpages out number,--Total pages

V_cur out Pkg_query.cur_query)--returned result set

Is

V_sql VARCHAR2 (1000): = '; --sql statement

V_startrecord number (4); --Number of record bars to begin displaying

V_endrecord number (4); --end displayed number of record bars

BEGIN

--The total number of records in the 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;

--Verifying page record size

IF P_pagesize < 0 THEN

P_pagesize: = 0;

End IF;

--Calculate Total pages based on 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 page number

IF P_curpage < 1 THEN

P_curpage: = 1;

End IF;

IF p_curpage > P_totalpages THEN

P_curpage: = p_totalpages;

End IF;

--Paging query implementation

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, in the Java code to take out the result set

String sql= "{call Prc_query (?,?,?,?,?,?,?,?,?)}";

CallableStatement call = Con.preparecall (SQL);

...... Intermediate data setup and registration omitted

Call.registeroutparameter (9, oracletypes.cursor);

To remove a result set

(ResultSet) Call.getobject (9);