简单实例解释Oracle分页查询

什么是分页查询

在处理大量数据时，一次性查询所有数据显然不是一个好的选择，因为这样的查询请求会占用太多的资源，而且在大多数情况下，用户仅需要查看部分数据。这时候我们可以使用分页查询来使查询更加高效。分页查询即是将数据划分成若干页，每页只显示少量数据，用户可以通过翻页的方式浏览所有数据。

Oracle分页查询示例

在Oracle中，我们可以通过SQL语句的ROWNUM来实现分页查询

假设我们有一个表students，其中的数据如下：

create table students(
  id int primary key,
  name varchar2(10),
  gender varchar2(2),
  age int
);

insert into students values(1, '张三', '男', 18);
insert into students values(2, '李四', '女', 20);
insert into students values(3, '王五', '男', 19);
insert into students values(4, '赵六', '男', 21);
insert into students values(5, '小七', '女', 22);

我们要实现对students表进行分页查询，每页只显示2条记录。我们可以使用以下SQL语句：

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownum, id, name, gender, age
    FROM students
)
WHERE rownum >= 1 AND rownum <= 2;

我们来看一下这个SQL语句的具体流程：

内部查询得到表students所有记录的id、name、gender和age列
通过使用ROW_NUMBER()窗口函数为每行记录设置一个rownum，按照id顺序排序
外部过滤，选取rownum在1和2之间的记录，即第一页的数据

我们可以通过修改WHERE语句的rownum的值，来查询其他页的数据，例如：

查询第二页数据：

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownum, id, name, gender, age
    FROM students
)
WHERE rownum >= 3 AND rownum <= 4;

查询第三页数据：

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownum, id, name, gender, age
    FROM students
)
WHERE rownum >= 5 AND rownum <= 6;

这样就可以实现对students表的分页查询了。

使用PL/SQL实现分页查询

除了SQL语句之外，我们也可以使用PL/SQL来实现分页查询。以下是一个PL/SQL实现分页查询的示例程序：

CREATE OR REPLACE FUNCTION get_students(p_page_num IN INTEGER, p_page_size IN INTEGER)
  RETURN SYS_REFCURSOR
IS
  l_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT *
    FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownum, id, name, gender, age
        FROM students
    )
    WHERE rownum BETWEEN (p_page_num - 1) * p_page_size + 1 AND p_page_num * p_page_size;
  RETURN l_cursor;
END;

这个函数接受两个参数，p_page_num是要查询的页码，p_page_size是每页的记录数。函数返回一个游标变量，可以通过FETCH语句一个一个读取查询结果。

例如，查询第一页的数据：

DECLARE 
  l_cursor SYS_REFCURSOR;
BEGIN 
  l_cursor := get_students(1, 2); 
  LOOP 
    FETCH l_cursor INTO id, name, gender, age; 
    EXIT WHEN l_cursor%NOTFOUND; 
    dbms_output.put_line('ID: ' || id || ' Name: ' || name || ' Gender: ' || gender || ' Age: ' || age); 
  END LOOP; 
  CLOSE l_cursor; 
END;