Implement paging operations accurately in Oracle stored procedures

It can be said that the actual application of each WEB will use paging. Therefore, we need to make it more generic and efficient in actual operations. The following article is based on my personal thoughts, I used the Oracle stored procedure to create a paging Oracle stored procedure and shared it with you. I hope we can get a better solution through discussion.

The reason for using stored procedures is that you do not need to modify the program code in the future, but you only need to modify the Oracle 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 as  
  
  
type cur_query is ref cursor;  
  
  
end pkg_query; 
 
 

2. Create an Oracle 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 11=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 < 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; 
 
 

Verification 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 11=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 result set

 
 

  
  
(ResultSet) call.getObject(9); 
 
 

The above content is an introduction to how to implement paging in the Oracle stored procedure. I hope you will have some gains.