asked on

Count number of rows in a cursor

Hi,

I am new to Procedures and cursors, so would appreciate some advice on this.

In the following Procedure, the Select of the Cursor can return one or more rows.

CREATE OR REPLACE PROCEDURE COMPUTE_DELAY
( P_STEP_ID IN BPM_DEF_STEPS.ID%TYPE)
AS
xPRED_ID BPM_DEF_STEPS.ID%TYPE;
xSUCC_ID BPM_DEF_STEPS.ID%TYPE;

CURSOR c_get_parent(pSTEP_ID BPM_DEF_STEPS.ID%TYPE) IS
SELECT
PREDSTEP.ID PRED_ID,
SUCCSTEP.ID SUCC_ID
FROM
BPM_DEF_STEPS PREDSTEP,
BPM_DEF_STEPS SUCCSTEP,
BPM_DEF_STEP_CONDITIONS COND,
BPM_DEF_STEP_TRANSITIONS TRANS
WHERE
PREDSTEP.ID = COND.STEP_ID
AND COND.ID = TRANS.STEP_CONDITION_ID
AND TRANS.NEXT_STEP_ID = SUCCSTEP.ID
AND SUCCSTEP.ID = pSTEP_ID
ORDER BY
PRED_ID, SUCC_ID;

BEGIN
OPEN c_get_parent(P_STEP_ID);
LOOP
FETCH c_get_parent INTO
xPRED_ID, -- xPred_ID = ID of PRED
xSUCC_ID; -- xSucc_ID = ID of SUCC

&

END LOOP;
CLOSE c_get_parent;
END COMPUTE_DELAY;

Within the main Loop, I need to know how many rows were returned by the Select. I know that c_get_parent%ROWCOUNT after Fetch, can tell how many rows have been retrieved so far, but I need to know how to determine the total number of rows in c_get_parent before I proceed with the next statements.

schwertner

To do this you have to execute

v_count integer;

.....
SELECT count(*) INTO v_count
FROM (
SELECT
PREDSTEP.ID PRED_ID,
SUCCSTEP.ID SUCC_ID
FROM
BPM_DEF_STEPS PREDSTEP,
BPM_DEF_STEPS SUCCSTEP,
BPM_DEF_STEP_CONDITIONS COND,
BPM_DEF_STEP_TRANSITIONS TRANS
WHERE
PREDSTEP.ID = COND.STEP_ID
AND COND.ID = TRANS.STEP_CONDITION_ID
AND TRANS.NEXT_STEP_ID = SUCCSTEP.ID
AND SUCCSTEP.ID = pSTEP_ID
ORDER BY
PRED_ID, SUCC_ID);

Jinesh Kamdar

... or you can use the FOR LOOP with the same cursor as below : -

...
v_count := 0;
...
BEGIN
FOR i IN c_get_parent LOOP
v_count := v_count + 1;
END LOOP;

OPEN ...

ASKER CERTIFIED SOLUTION

Jinesh Kamdar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cgosney42

ASKER

I was hoping for an easier solution to be honest.

Is it possible to fetch the values from the cursor twice? Something like:

counter := 0;
FOR c IN c_get_parent
LOOP
FETCH c_get_parent INTO c ;
EXIT WHEN c_get_parent%NOTFOUND ;
counter := counter + 1 ;
END LOOP ;

Then to continue with ..

LOOP
FETCH c_get_parent INTO
xPRED_ID, -- xPred_ID = ID of PRED
xSUCC_ID; -- xSucc_ID = ID of SUCC

?

Jinesh Kamdar

Yes, you can :) but you need not fetch it again in the LOOP, FOR LOOP does an implicit fetch. Use it the way i showed.

cgosney42

ASKER

You were too quick for me ... you posted your response while I was typing mine LOL

Anyway, with a slight change of syntax, this works :) Thanks

xPredCount := 0;

FOR i IN c_get_parent(p_step_id) LOOP
xPredCount := xPredCount + 1;
END LOOP;

Jinesh Kamdar

:) ... I corrected that syntax in my next post as well -
https://www.experts-exchange.com/questions/22874960/Count-number-of-rows-in-a-cursor.html?cid=238#20022628

Jinesh Kamdar

Oh sorry ... You were right ... Got it :)