Oracle Cursor - Return result and rowcount-CodePudding

I'm trying to create a procedure in Oracle that would return the result of the query and at the same time count the number of returned rows. Is it possible to do that in one procedure? This one returns the result of the query (this is an example query, our production query is more complicated):

CREATE OR REPLACE PROCEDURE GETCUR(PARAM1 VARCHAR2)
AS
cur SYS_REFCURSOR;
cSql NUMBER;
cnt INTEGER;

BEGIN
   OPEN CUR FOR
   SELECT T1.F3, T2.F3 FROM T1 JOIN T2 ON T1.F1 = T2.F2 WHERE T2.F9 = PARAM1;
   
   DBMS_SQL.RETURN_RESULT(CUR);
END;

And this one can count the number of returned rows. Here I print it using PUT_LINE but want to be able to assign it to some variable and insert that into other table - something like a logging mechanism.

CREATE OR REPLACE PROCEDURE GETCUR(PARAM1 VARCHAR2)
AS
cur SYS_REFCURSOR;
cSql NUMBER;
cnt INTEGER;
       
BEGIN
  OPEN CUR FOR
    SELECT T1.F3, T2.F3 FROM T1 JOIN T2 ON T1.F1 = T2.F2 WHERE T2.F9 = PARAM1;
           
--DBMS_SQL.RETURN_RESULT(CUR);

cSql := DBMS_SQL.TO_CURSOR_NUMBER(CUR);
cnt := 0;
LOOP
  EXIT WHEN DBMS_SQL.FETCH_ROWS(cSql) = 0;
  cnt := cnt   1;
END LOOP;
    
DBMS_SQL.CLOSE_CURSOR(cSql);
DBMS_OUTPUT.PUT_LINE(cnt||' rows returned');
    
END;

But I can't marry these two solutions into one procedure. Is it possible?

CodePudding user response：

If you want to pass back two outputs - a cursor and a count, there is no way to do that without running two queries. That's inefficient and possibly inaccurate for reasons Justin Cave pointed out.

One thing you might do is something like this:

CREATE OR REPLACE FUNCTION GETCUR(PARAM1 VARCHAR2) RETURN SYS_REFCURSOR
AS
cur SYS_REFCURSOR;

BEGIN
   OPEN CUR FOR
   SELECT T1.F3, 
          T2.F3,
          COUNT(*) OVER ( PARTITION BY NULL ) ROW_COUNT
   FROM T1 JOIN T2 ON T1.F1 = T2.F2
   WHERE T2.F9 = PARAM1;
   
   RETURN cur;
END;

Your consuming application will know the total row count after fetching the 1st record. And, thanks to statement-level read consistency in Oracle, it does not require you to set the isolation level to guarantee accurate results. It's also more efficient than running two separate queries.

CodePudding user response：

You could do something like

CREATE OR REPLACE PROCEDURE GETCUR(p_PARAM1 OUT VARCHAR2,
                                   p_rc     OUT sys_refcursor,
                                   p_cnt    OUT integer )
AS
BEGIN
  OPEN p_rc
   FOR SELECT T1.F3, T2.F3 
         FROM T1 JOIN T2 ON T1.F1 = T2.F2 
        WHERE T2.F9 = p_PARAM1;  

  select count(*)
    into p_cnt
    from (SELECT T1.F3, T2.F3 
           FROM T1 JOIN T2 ON T1.F1 = T2.F2 
          WHERE T2.F9 = p_PARAM1);
end;

However,

This is going to be twice as expensive because you are running the query twice.
If the underlying tables are changing, it is possible that the count you get in the second step won't match the count you got from fetching from the cursor you opened in the first step. You could potentially address that by using a serializable transaction isolation level but that is going to have a number of impacts on your application so it's not something that should be done lightly.