Oracle SQL中顶层块用FETCH OFFSET搭配内部CURSOR查询报错的解决办法

当尝试在内部游标外部使用OFFSET x ROWS FETCH y ROWS ONLY语法对顶层结果集做偏移和限制时，执行以下PL/SQL代码会触发错误：

DECLARE
  l_hits SYS_REFCURSOR;
BEGIN 
  OPEN l_hits for
    SELECT CURSOR(SELECT *
                    FROM emp) hits
      FROM dept
     ORDER BY deptno 
    OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
END;

错误信息如下：

ERROR at line 1:
ORA-22902: CURSOR expression not allowed
ORA-06512: at line 4

移除OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY语句可以解决报错，但会丢失顶层结果集的分页功能。需求是对顶层dept结果集而非内部emp游标进行分页，可通过以下方式实现：

解决方案 #

将分页逻辑放到子查询中，先对dept完成分页筛选，再基于分页后的结果集生成对应的内部游标。修改后的代码如下：

DECLARE
  l_hits SYS_REFCURSOR;
BEGIN 
  OPEN l_hits for
    SELECT CURSOR(SELECT * FROM emp) hits
    FROM (
      SELECT deptno, dname, loc
      FROM dept
      ORDER BY deptno 
      OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
    ) dept_page;
END;

原理说明 #

Oracle不允许在包含游标表达式的顶层查询中直接使用OFFSET/FETCH语法。通过将分页逻辑封装到子查询，先得到分页后的部门数据，再为每条部门数据关联对应的员工游标，既满足了顶层结果集的分页需求，又规避了游标表达式的使用限制。

内容的提问来源于stack exchange，提问作者Joao Pereira