Implement paging in Oracle stored procedures

When paging is implemented in Oracle stored procedures, stored procedures are used because you do not need to modify the program code in the future. You only need to modify the stored procedure code. But this

When paging is implemented in Oracle stored procedures, stored procedures are used because you do not need to modify the program code in the future. You only need to modify the stored procedure code. But this

Paging is used for almost every WEB application. Therefore, it is very important to make it generic and efficient. Based on your own ideas, a paging storage process is created using the stored procedure, which will be shared with you, we hope to have a better solution through discussion.

Paging is used for almost every WEB application. Therefore, it is very important to make it generic and efficient. Based on your own ideas, a paging storage process is created using the stored procedure, which will be shared with you, we hope to have a better solution through discussion.

The reason for using stored procedure is that you do not need to modify the program code if you need to modify it later. Instead, you only need to modify the stored procedure code. However, this example is a dynamically generated SQL statement in the stored procedure. I don't know if it will lose the features of one-time compilation and fast storage procedure. The Code is as follows:

1. First, create a package. The user creates a cursor type.

Create or replace package pkg_query
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 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;

3. Retrieve the result set from JAVA code

String SQL = "{call prc_query (?,?,?,?,?,?,?,?,?) }";
CallableStatement call = con. prepareCall (SQL );

//...... Intermediate data settings and registration omitted

Call. registerOutParameter (9, OracleTypes. CURSOR );

// Retrieve the result set

(ResultSet) call. getObject (9 );