Oracle paging stored procedures - making paging searches more efficient

With the development of the Internet, the application of databases has become more and more popular, and a large amount of data needs to be queried and processed. In this process, paging search is one of the most common requirements. Although the Oracle database provides ROWNUM as a means to implement paging queries, it faces many limitations and deficiencies in the actual application process. Therefore, developing Oracle paging stored procedures has become a more efficient solution.

This article will combine actual cases to introduce how to design efficient paging stored procedures based on Oracle database.

1. Overall design

The design architecture of Oracle paging stored procedure can be divided into four core parts: parameter transfer, data query, data paging calculation and return results.

1. Passing parameters: Pass the query statement, current page number, number of records per page, etc. as input parameters into the stored procedure.
2. Data query: Query based on the incoming query statement to obtain data that meets the conditions.
3. Data paging calculation: perform paging processing on the query results and calculate the data of the specified page number.
4. Return results: Return the data of the specified page number and the total number of records.

2. Specific implementation

In the process of implementing Oracle paging stored procedures, some Oracle-specific syntax and functions need to be used. So, there are some basics that need to be understood first.

1. ROW_NUMBER function

ROW_NUMBER function is a keyword in Oracle that is used to count the number of rows in the result set. Its return result is an integer value, arranged in ascending row order. The following is an example:

SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
FROM emp;

The ROW_NUMBER function in the above example will sort by employee number and assign an increasing serial number. This is very important for paginated queries.

2. Query of the first N records

How to query the first N records? Oracle provides two methods: ROWNUM and ROW_NUMBER() functions. As shown below:

SELECT *
FROM (
    SELECT emp.*, ROWNUM rn
    FROM emp
) t
WHERE t.rn <= N;

or

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno <= N;

Just replace N with the number of records to be queried.

3. Paging query

Paging query is a typical scenario. It is usually necessary to specify the page number to be displayed and the number of records per page. Among them, OFFSET specifies the starting index of the data displayed on each page, and LIMIT specifies the maximum number of records displayed on each page. As shown below:

SELECT emp.*
FROM (
    SELECT emp.*, ROW_NUMBER() OVER(ORDER BY empno) as rowno
    FROM emp
) t
WHERE t.rowno > OFFSET AND t.rowno <= OFFSET+LIMIT;

Among them, OFFSET and LIMIT can be adjusted according to specific needs to achieve flexible paging queries.

3. Code implementation

The following is an example of a complete Oracle paging stored procedure:

CREATE OR REPLACE PROCEDURE paginating_demo (
    p_sql       IN    VARCHAR2,    --带有占位符(:P1,:P2...)的查询语句
    p_curPage   IN    NUMBER,      --当前页码
    p_pageSize  IN    NUMBER,      --每页的记录数量
    p_recordset OUT   SYS_REFCURSOR,--查询结果集
    p_total     OUT   NUMBER       --记录的总数
)
AS
  v_sql VARCHAR2(4000); 
  v_fromIndex NUMBER; 
  v_toIndex NUMBER;

BEGIN
  SELECT COUNT(*) INTO p_total FROM ( p_sql );

  IF (p_total > 0) THEN
    -- 计算 limit 和offset 边界值
    v_fromIndex := ((p_curPage - 1) * p_pageSize);
    v_toIndex   := (p_curPage * p_pageSize);

    v_sql := 'SELECT * FROM ( SELECT t.*, ROWNUM RN FROM ( ' || p_sql || ' ) t WHERE ROWNUM <= &#39; || v_toIndex || &#39; ) WHERE RN > ' || v_fromIndex;

    OPEN p_recordset FOR v_sql;
  END IF;

END paginating_demo;

This code combines the query statement, current page number, number of records per page, The query result set and the total number of records are taken as input and output parameters. Among them, the query result set and the total number of records will be returned as output parameters.

4. Summary

In actual applications, Oracle paging stored procedures greatly improve the efficiency and stability of paging queries. By mastering the above knowledge and skills, we can flexibly use Oracle paging stored procedures in practice to significantly improve system performance and user experience.

The above is the detailed content of Oracle paging stored procedures - making paging searches more efficient. For more information, please follow other related articles on the PHP Chinese website!