How to implement paging query in Oracle stored procedure

In Oracle database, a stored procedure is a reusable block of SQL code that can be used to implement many complex data operations. Among them, paging query is a common requirement, such as displaying a paging data list in a web application, or displaying results in paging in a report.

In this article, we will introduce how to implement paging queries in Oracle stored procedures, and provide a simple sample code to help readers better understand and apply this technology.

1. Basic principles of paging query

In general SQL queries, we can use the syntax of "SELECT * FROM table_name WHERE condition" to retrieve all rows that meet the conditions. In order to implement paging query, we need to cut the query results according to the specified number of pages and rows per page, and then only return the data of the specified number of pages. For example, in page 1, we can retrieve the first 10 rows of data, in page 2, we can retrieve rows 11 to 20, and so on.

Based on this principle, we can use Oracle stored procedures to implement paging queries. First, we need to calculate the retrieval starting row and retrieval ending row, then use the "ROWNUM" function to limit the number of rows in the retrieval results, and finally return the query results. The following is a simple implementation step:

1. Calculate the starting line and ending line.

It should be noted that Oracle's ROWNUM function sorts the query results before they are returned, not before the query. Therefore, if we use the ROWNUM function in the main query statement, the results may be inaccurate or unpredictable. To solve this problem, we can use a subquery statement to enable the ROWNUM function. For example, in the following statement, we can calculate the start row and end row:

SELECT start_row, end_row
FROM (
SELECT ROWNUM AS rnum, ((page_no - 1) page_size 1) AS start_row, (page_no page_size) AS end_row
FROM (

SELECT 1 AS page_no, 10 AS page_size FROM DUAL

)
)
WHERE rnum = 1;

In this example , we first defined the number of rows and pages per page, and then calculated the starting row and ending row through the subquery statement. This statement will return a row of data, including the starting and ending row values.

2. Retrieve data.

After calculating the starting row and ending row, we need to query the data that meets the conditions. Using a subquery statement, we can select all rows that meet the criteria and limit the number of rows using the ROWNUM function. For example, in the following statement, we can query the data of the specified page number:

SELECT *
FROM (
SELECT ROWNUM as rnum, t.*
FROM (

SELECT *
FROM table_name
WHERE condition
ORDER BY order_by

) t
)
WHERE rnum >= start_row AND rnum <= end_row;

In this example, we first sort the data that meets the conditions, and then use the ROWNUM function to The results are limited. Finally, we select only the data between the specified start row and the end row from all rows that meet the conditions, and finally return the query results.

2. Implementation example of paging query

The following is a complete Oracle stored procedure example for implementing paging query:

CREATE OR REPLACE PROCEDURE PAGING_PROC(
i_page_no IN INTEGER,
i_page_size IN INTEGER,
o_records OUT SYS_REFCURSOR,
o_page_count OUT INTEGER,
i_table_name IN VARCHAR2,
i_condition IN VARCHAR2,
i_order_by IN VARCHAR2
)
IS
v_start_row INTEGER;
v_end_row INTEGER;
BEGIN
-- Step 1: Calculate start and end row
SELECT (i_page_no - 1) i_page_size 1, i_page_no i_page_size
INTO v_start_row, v_end_row
FROM DUAL;

-- Step 2: Fetch data
OPEN o_records FOR
SELECT *
FROM (

SELECT ROWNUM AS rnum, t.*
FROM (
  SELECT *
  FROM i_table_name
  WHERE i_condition
  ORDER BY i_order_by
) t

)
WHERE rnum >= v_start_row AND rnum <= v_end_row;

-- Step 3: Calculate page count
SELECT CEIL(COUNT(*)/i_page_size)
INTO o_page_count
FROM i_table_name
WHERE i_condition;
END PAGING_PROC;

In this example, we pass in the page number, page size, output record, page number and table name, conditions and sorting and other parameters. Based on the input parameters, we first calculate the starting row and ending row, and then use the OPEN statement to open a REFCURSOR output data.

Finally, we count the number of pages and output the results. Please note that the way we calculate the number of pages is to use the COUNT aggregate function to divide the number of all rows that meet the condition by the number of rows per page and round up.

3. Conclusion

In Oracle database, stored procedure is an important data operation technology. By using stored procedures, we can implement complex data operations, such as paging queries, batch updates, data import and export, etc. Especially in terms of paging queries, Oracle stored procedures can provide higher performance and better data security, and can also easily interact with other program interfaces.

In this article, we introduce how to implement paging queries in Oracle stored procedures and provide a simple sample code. We encourage readers to try this technique in real applications and optimize and extend it according to their own needs.

The above is the detailed content of How to implement paging query in Oracle stored procedure. For more information, please follow other related articles on the PHP Chinese website!