Oracle 存储过程返回分页结果集

发布于 2024-09-11 20:26:17 字数 358 浏览 11 评论 0原文

Oracle 存储过程是否有办法返回分页结果集（作为引用游标）？例如，我想将要返回的记录数和感兴趣的页码传递给存储过程。然后我只想将这些数量的记录返回给我的客户。 Oracle 这边是如何完成的？

即

var v_numrecords number := 30;
var v_pagenum number: = 5;
var v_rows refcursor;
exec my_stored_proc(:v_rows, :v_numrecords, :v_pagenum);
print v_rows;  -- expecting only 30 records of page number 5 back

非常感谢。

原文

Is there a way for an Oracle stored procedure to return paged resultset (as refcursor) back? For example, I would like to pass to the stored procedure the number of records to return and the page number interested. Then I only want to see those number of records back to my client. How is that done on the Oracle side?

i.e.

var v_numrecords number := 30;
var v_pagenum number: = 5;
var v_rows refcursor;
exec my_stored_proc(:v_rows, :v_numrecords, :v_pagenum);
print v_rows;  -- expecting only 30 records of page number 5 back

thanks much.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

维持三分热 2024-09-18 20:26:17

您可以使用ROWNUM。
您需要在结果排序后应用它。

下面是一个示例：我们需要第 6 行到第 10 行。

   SELECT *
    FROM (SELECT e.*, ROWNUM rnum
          FROM (SELECT *
                FROM employee
                ORDER BY employee_id) e
          WHERE ROWNUM <= 10)
    WHERE rnum >= 6;

要获取第 121 到 150 行记录，您应该将内部 SELECT 中的 10 替换为 150，然后将 WHERE 子句中的 6 替换为 121。

在存储过程中，您需要用变量替换这些硬编码值。

You can use ROWNUM.
You need to apply that after the results have been sorted.

Here's an example: we want rows 6 through 10.

   SELECT *
    FROM (SELECT e.*, ROWNUM rnum
          FROM (SELECT *
                FROM employee
                ORDER BY employee_id) e
          WHERE ROWNUM <= 10)
    WHERE rnum >= 6;

To get records 121 through 150, you should replace the 10 in the inner SELECT with 150, and then replace the 6 in the WHERE clause with 121.

In your stored procedure, you will want to replace these hard-coded values with variables.

回复收藏 0 原文

绮筵 2024-09-18 20:26:17

如果您希望 Oracle 返回结果集的 N 到 M 行，您有几种选择，具体取决于您真正想要的。

如果您希望整个结果集是静态的 - 例如，第 5 页上的行永远不会出现在任何其他页面上，您必须：

为整个结果集打开游标，然后执行在应用程序中分页。
让存储过程将整个结果集保存到全局临时表中并对其进行分页。这使得您的存储过程更加复杂，因为当应用程序要求它时，它必须从头开始构建结果集。

如果您不关心第 6 页上的行可能已在第 5 页上（结果集是动态的），则让游标执行惯用的 Oracle 分页：

select [columns] 
  from (select [columns], rownum as rn
          from [some such tables]
         where [some such conditions]
           and rownum < (:v_pagenum + 1) * :v_numrecs
         order by [some expression])
 where rn >= :v_pagenum * :v_numrecs;

If you want Oracle to return rows N through M of a result set, you've got a few choices, depending on what you really want.

If you want the entire result set to be static - the rows on page 5, for example, will never possibly appear on any other page, you'll have to either:

Open a cursor for the entire result set, and perform pagination in the application.
Have the the stored procedure persist the entire result set into a global temporary table and paginate off of that. That makes your stored procedure more complicated, as it will have to build the result set from scratch when the application tells it to.

If you don't care that the rows on page 6 might have been on page 5 (the result set is dynamic), then have the cursor perform the customary Oracle pagination:

select [columns] 
  from (select [columns], rownum as rn
          from [some such tables]
         where [some such conditions]
           and rownum < (:v_pagenum + 1) * :v_numrecs
         order by [some expression])
 where rn >= :v_pagenum * :v_numrecs;

回复收藏 0 原文

~没有更多了~