pl/sql loop records select oracle plsql

Question

I have a select statement that I am trying to loop over and increment a variable based on the condition of the select statement, then return the variable as an out so I can do something with it in some front end code. I am using oracle 11g and I am seeing a few ways I can do this... but I am not sure which is the best way. I have some of what I am trying to do below, but again stopped because of confusion.

First I am setting my proc and 'in variable'

PROCEDURE SEEKER (pMonkeyID IN Number, vMarkCounter OUT Number)
AS
BEGIN

CURSOR seeker_cur IS
    Select Mokney_approved, Monkey_vaulted 
    from MonkeyBookApps 
    where MonkeyID = pMonkeyID
    and Monkey_doc_type = 'BANANA' 
    order by docu_approv_timestamp,monkey_doc_type,monkey_doc_approved desc

OPEN seeker_cur;

begin

   OPEN Seeker_cur;
   vMarkCounter := 0;

Here is the part I am not sure about. Should I loop and then exit if the condition is not met or should I do an if statement and somehow determine if there is a record that could be greater than one? If so how would that work? Is there a benefit to doing one way over the other? So... I am going to sudo-code what I am trying to do (below):

FOR (however many records) in Seeker_cur
IF seeker_cur (not found) or (returns no records)

EXIT or (break for loop);
ELSE
LOOP

vMarkCounter := vMarkCounter + 1;

EXIT WHEN seeker_cur is out of records (somehow)
END IF;
END LOOP;

END; 
END SEEKER;

I am sure there are a few ways to do this. What ways would you suggest?

Is there some reason you're not using COUNT() here? Why loop at all? — eaolson
– eaolson, Commented Feb 4, 2012 at 20:50

Alex Peta · Accepted Answer · 2012-02-04 17:19:50Z

7

why dont you use implicit cursor , it will open and close itself:

DECLARE

CURSOR seeker_cur IS
    Select Mokney_approved, Monkey_vaulted 
    from MonkeyBookApps 
    where MonkeyID = pMonkeyID
    and Monkey_doc_type = 'BANANA' 
    order by docu_approv_timestamp,monkey_doc_type,monkey_doc_approved desc;

  vMarkCounter number:=0;

BEGIN

  FOR i IN seeker_cur 
  LOOP
    vMarkCounter := vMarkCounter+1;
  END LOOP;

  dbms_output.put_line(vMarkCounter);
END;

answered Feb 4, 2012 at 17:19

Alex Peta

1,4051 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Szilard Barany · Accepted Answer · 2012-02-06 11:37:09Z

2

It seems to me that the solution your problem might be as simple as this:

SELECT COUNT(*)
INTO   l_some_local_variable
FROM   monkey_book_apps 
WHERE  monkey_id = p_monkey_id
   AND monkey_doc_type = 'BANANA';

RETURN l_some_local_variable;

Avoiding PL/SQL loops and using the simplest SQL possible is (almost always) the most efficient way. Tom Kyte calls the row-by-row execution of LOOPs "slow-by-slow".

answered Feb 6, 2012 at 11:37

Szilard Barany

1,1337 silver badges15 bronze badges

Collectives™ on Stack Overflow

pl/sql loop records select oracle plsql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related