游标中的记录数:从Oracle存储过程到Java [英] Number of records in cursor: from Oracle stored procedure to Java
问题描述
我们从Java代码调用PL/SQL存储过程来处理Oracle 10g数据库.当我们需要检索一些数据时,我们使用SYS_REFCURSOR OUT参数.问题是:如何在没有重复的SELECT的情况下获取游标中的记录数?
(%ROWCOUNT游标属性不会在第一次获取之前返回查询的总行数.如果在存储过程中进行获取,则会丢失数据.)
我们使用以下内容:
We call PL/SQL stored procedures from Java code to process Oracle 10g database. When we need to retrieve some data, we use SYS_REFCURSOR OUT parameter. The question is: how we can get number of records in the cursor without duplicated SELECT?
(The %ROWCOUNT cursor attribute does not return the total number of rows for a query prior to the first fetch. If we fetch in stored procedure, we lose data.)
We use the following:
CREATE OR REPLACE PROCEDURE GET_DATA(p_cursor OUT SYS_REFCURSOR,
nRecs OUT INTEGER)
IS
BEGIN
OPEN p_cursor FOR SELECT * FROM SOME_TABLE WHERE ...;
SELECT COUNT(0) INTO nRecs FROM SOME_TABLE WHERE ...;
...
END GET_DATA;
那么我们可以避免第二个SELECT返回记录数吗?还是我们可以以某种方式从Java的SYS_REFCURSOR中获取它?
So can we avoid the second SELECT to return number of records? Or may be we can somehow get it from SYS_REFCURSOR in Java?
推荐答案
一个快速的google,答案在这里24.8.11.在Java中使用REF CURSOR的示例 [ ^ ]
A quick google and the answer is here in 24.8.11. Example Using REF CURSOR in Java[^]
这篇关于游标中的记录数:从Oracle存储过程到Java的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!