从pl/sql块返回数据行 [英] Return data rows from a pl/sql block

问题描述

我想编写使用游标和批量收集来检索我的数据的pl/sql代码.我的数据库中的行数以百万计，有时我必须查询它以根据客户的请求获取几乎所有记录.我分批进行查询和后续处理，以免使服务器拥塞并向客户端显示增量进度.我已经看到挖掘后续批次所花费的时间要多得多，这就是为什么我要尝试使用游标的方式.

I want to write pl/sql code which utilizes a Cursor and Bulk Collect to retrieve my data. My database has rows in the order of millions, and sometimes I have to query it to fetch nearly all records on client's request. I do the querying and subsequent processing in batches, so as to not congest the server and show incremental progress to the client. I have seen that digging down for later batches takes considerably more time, which is why I am trying to do it by way of cursor.

这是我的主要sql查询周围应该是简单的pl/sql的内容:

Here is what should be simple pl/sql around my main sql query:

declare
  cursor device_row_cur
    is
      select /my_query_details/;

  type  l_device_rows is table of device_row_cur%rowtype;
  out_entries l_device_rows := l_device_rows();

begin
    open device_row_cur;    
      fetch device_row_cur
      bulk collect into out_entries
      limit 100;        
  close device_row_cur; 
end;

我要批量处理100次，并将它们提取到out_entries中.问题在于此块可以编译并执行得很好，但不会返回其获取的数据行.我希望它像选择一样返回那些行.如何做到这一点?有什么想法吗?

I am doing batches of 100, and fetching them into out_entries. The problem is that this block compiles and executes just fine, but doesn't return the data rows it fetched. I would like it to return those rows just the way a select would. How can this be achieved? Any ideas?

推荐答案

我研究了有关优化分页的出色论文: http://www.inf.unideb.hu/~gabora/pagination /article/Gabor_Andras_pagination_article.pdf

I studied this excellent paper on optimizing pagination: http://www.inf.unideb.hu/~gabora/pagination/article/Gabor_Andras_pagination_article.pdf

我主要使用了技巧6.它描述了如何限制查询以获取第x页及更高版本.为了获得更多的改进，您可以将其进一步限制为仅获取第x页.如果使用得当，它可以将性能提高1000倍.

I used technique 6 mainly. It describes how to limit query to fetch page x and onward. For added improvement, you can limit it further to fetch page x alone. If used right, it can bring a performance improvement by a factor of 1000.

我没有返回自定义表行(这很难，即使不是不可能与Java接口)，我还是在我的pl/sql中打开了一个sys_refcursor，它可以被接口，例如:

Instead of returning custom table rows (which is very hard, if not impossible to interface with Java), I eneded up opening a sys_refcursor in my pl/sql which can be interfaced such as:

OracleCallableStatement stmt = (OracleCallableStatement) connection.prepareCall(sql); stmt.registerOutParameter(someIndex, OracleTypes.CURSOR); stmt.execute(); resultSet = stmt.getCursor(idx);

OracleCallableStatement stmt = (OracleCallableStatement) connection.prepareCall(sql); stmt.registerOutParameter(someIndex, OracleTypes.CURSOR); stmt.execute(); resultSet = stmt.getCursor(idx);

这篇关于从pl/sql块返回数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！