oracle paging stored procedure

Oracle is a powerful database management system that supports advanced features such as stored procedures, making it easy for programmers to write complex business logic. In some specific scenarios, it is necessary to perform paging queries on a large amount of data. To achieve this, we can write a paginated stored procedure. This article will introduce how to write Oracle paging stored procedures.

1. Requirements Analysis

In website development, we often encounter situations where the data submitted by users need to be displayed in pages. For example, querying all records in a table will put serious performance pressure on the database and affect the user experience if all records are queried at once. Therefore, displaying data in pages is a better solution. Let's analyze the requirements:

1. Get the total number of records
2. According to the size of each page and the current page number, calculate the positions of the starting record and the ending record
3. Query the required data based on the starting record and ending record

2. Stored procedure design

Based on the above demand analysis, we can design the following Oracle paging stored procedure:

CREATE OR REPLACE PROCEDURE pagination(p_table_name IN VARCHAR2,
                                        p_page_num IN NUMBER,
                                        p_page_size IN NUMBER,
                                        p_total_num OUT NUMBER,
                                        p_cursor OUT SYS_REFCURSOR) IS
  v_start_pos NUMBER;
  v_end_pos NUMBER;
  v_sql_query VARCHAR2(1000);
BEGIN
  SELECT COUNT(*) INTO p_total_num FROM p_table_name;
  v_start_pos := (p_page_num - 1) * p_page_size + 1;
  v_end_pos := v_start_pos + p_page_size - 1;
  v_sql_query := 'SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM ' || p_table_name || ') A WHERE ROWNUM <= ' || v_end_pos || ') WHERE RN >= ' || v_start_pos;
  OPEN p_cursor FOR v_sql_query;
END;
/

The above code will create a stored procedure named pagination, which can receive 4 parameters: table name, page number, size of each page and total number of records. Among them, p_cursor is the output parameter, which is used to return the cursor of query results.

3. Stored procedure description

1. SELECT COUNT(*) INTO p_total_num FROM p_table_name;
   This statement is used to query the total number of records in the table and store the results p_total_num. Through this variable, we can calculate the total number of pages and the record range of the current page.
2. v_start_pos := (p_page_num - 1) * p_page_size 1;
   This statement is used to calculate the position of the starting record, which is calculated from the page number and the size of each page.
3. v_end_pos := v_start_pos p_page_size - 1;
   This statement is used to calculate the position of the end record, which is also calculated from the page number and the size of each page.
4. v_sql_query := 'SELECT FROM (SELECT A., ROWNUM RN FROM (SELECT * FROM ' || p_table_name || ') A WHERE ROWNUM = ' || v_start_pos;
   This statement is a query statement, used to query data within the specified range in the table. ROWNUM is an Oracle-specific pseudo column that represents the row number of each record. We used ROWNUM to limit the query scope, and added the RN column through nested queries to represent the row number of the current record. Finally, the range of query results is limited based on the starting position and ending position.
5. OPEN p_cursor FOR v_sql_query
   This statement is used to execute the query statement and store the results in the cursor p_cursor. This cursor can be used for subsequent data operations and transfers.

4. Test case

In order to verify whether the stored procedure is correct, we can create a test table and insert some data into the table:

CREATE TABLE test(
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(50)
);

DECLARE 
  v_id NUMBER;
BEGIN
  FOR i IN 1..100 LOOP
    v_id := i;
    INSERT INTO test(id,name) VALUES(v_id,'name'||v_id);
  END LOOP;
  COMMIT;
END;
/

Next , we can execute the following code to test our stored procedure:

DECLARE
  v_page_num NUMBER := 1;
  v_page_size NUMBER := 10;
  v_total_num NUMBER;
  v_cursor SYS_REFCURSOR;
  v_id NUMBER;
  v_name VARCHAR2(50);
BEGIN
  pagination('test',v_page_num,v_page_size,v_total_num,v_cursor);
  
  DBMS_OUTPUT.PUT_LINE('Total number of records: ' || v_total_num);
  LOOP
    FETCH v_cursor INTO v_id,v_name;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Id: '||v_id ||', Name: '|| v_name);
  END LOOP;
  CLOSE v_cursor;
END;
/

The above code will output the id and name values ​​of 1~10 records.

It can be seen from the test results that the paging stored procedure we wrote can correctly calculate the record range, and the query results are also correct. This stored procedure can effectively reduce the pressure on the database when querying data, and at the same time avoid performance problems caused by querying too much data at one time.

In addition, we can also adjust the parameters and query statements in the stored procedure according to actual needs to adapt to more complex query scenarios.

5. Summary

In the Oracle database, stored procedures are a very important feature. It can help us write complex business logic and data operation processes, improve the efficiency and efficiency of database operations, and Maintainability. This article introduces how to write Oracle paging stored procedures. By analyzing requirements, designing algorithms and writing code, on the basis of understanding Oracle stored procedures, a simple paging stored procedure is implemented. By studying the cases in this article, readers can better master the methods and techniques of writing Oracle stored procedures.

The above is the detailed content of oracle paging stored procedure. For more information, please follow other related articles on the PHP Chinese website!