Oracle SQL - 如何使用分页循环访问记录

Question

>我正在使用一个 Oracle 数据库，其中有一个包含 20 条记录的表

CREATE TABLE MyTable (
    MY_RECORD_ID NUMBER PRIMARY KEY, 
    SOME_DATA_1 VARCHAR2(200), 
    SOME_DATA_2 VARCHAR2(200)
);
INSERT INTO MyTable VALUES (1, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (2, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (3, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (4, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (5, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (6, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (7, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (8, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (9, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (10, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (11, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (12, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (13, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (14, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (15, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (16, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (17, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (18, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (19, 'BLAH1', 'BLAH2');
INSERT INTO MyTable VALUES (20, 'BLAH1', 'BLAH2');

我想做的是根据MY_RECORD_ID列一次循环访问此表，选择 5 条记录。 OFFSET和FETCH命令似乎允许我这样做：

SELECT * 
FROM MyTable 
ORDER BY MY_RECORD_ID 
OFFSET 0 ROWS -- start at the first row
FETCH NEXT 5 ROWS ONLY;

我想将上述语句插入到一个循环中，以便我可以对五行中每一行的数据执行操作（因此总共将执行 4 次）。 有没有办法使用存储过程来做到这一点？

Answer 1

您可以将分析函数用于此类请求

SELECT my_record_id, some_data_1, some_data_2
  FROM (
        SELECT CEIL(ROW_NUMBER() OVER(ORDER BY mt.my_record_id) / 5/*set the page size*/) page_id
             ,mt.* 
          FROM MyTable mt
       ) t
 WHERE page_id = 1 /* set the desired page*/
ORDER BY my_record_id

您可以查看此文档

分析函数

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174

问汤姆

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137577300346084930

其他例子

https://blogs.oracle.com/oraclemagazine/on-top-n-and-pagination-queries

Answer 2

您可以通过使用批量收集来执行此操作。

DECLARE
  TYPE t_MyTable_tab IS TABLE OF MyTable%ROWTYPE;
  l_tab    t_MyTable_tab ; 
  CURSOR c_data IS
    SELECT *
    FROM   MyTable;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 5; --- Here 5 is your limiting number
    -- Process contents of collection here.
    DBMS_OUTPUT.put_line(l_tab.count || ‘ rows’);
    EXIT WHEN c_data%NOTFOUND;
  END LOOP
  CLOSE c_data;
END;
/