Looping thru recordset

Toad for Oracle
1

How can I loop through a recordset? Can someone please provide a simple block of code to illustrate how to do this?

  1. How to open the recordset.
  2. Loop through it.
  3. Assign the current value to a variable.
  4. Close the recordset (if applicable)

thanks in advance!
Mike

2

Is this what you mean

declare

CURSOR c1 IS

SELECT 1 v1 FROM dual

union

select 2 from dual ;

begin

FOR c1_rec IN c1 LOOP

dbms_output.put_line ( c1_rec . v1 );

END LOOP;

end;

/

3

declare

cursor temp

is

select field1 from tablea;

p_localval VARCHAR2 (1);

begin

for tmp in temp

loop

p_localval := tmp.field1;

end loop;

end;

HTH

chris

4

If you’re doing this for homework, make sure to include an exception block
for extra credit J

There are many good PL/SQL code samples on the Knowledge Xpert section of
ToadWorld.com

jeff

5

This is what I usually do (if I’m looping through):

declare
TYPE Archive_data_sqlrec IS RECORD(
Tbl_id_key T_archive_data.Tbl_id%TYPE
, Oper_str T_archive_data.Oper_type%TYPE
, Options_str T_archive_data.Options%TYPE
);

my_var T_archive_data.Oper_type%TYPE;
SQL_cmd VARCHAR2 (200);
Transfer_cur Cv_type;
Transfer_rec Archive_data_sqlrec;
BEGIN

SQL_cmd :=
‘SELECT Tbl_id’
|| ‘, Oper_type’
|| ‘, Options’
|| ’ FROM T_archive_data ’
|| ’ WHERE Tbl_ID > 10’;

OPEN Transfer_cur FOR Transfer_sql;

FETCH Transfer_cur
INTO Transfer_rec;

<>
WHILE Transfer_cur%FOUND
LOOP
my_var := Transfer_rec.Oper_str;

FETCH Transfer_cur
INTO Transfer_rec;

END LOOP Process_each_rec;

CLOSE Transfer_cur;
END;

This gives me a bit more flexibility… I can make the SQL_cmd variable anything
I want. If I have a problem with the statement, then I simply execute that
string as stand-alone SQL. One ORACLE gotcha here: Do NOT make the PL/SQL
variable names the same as the column names. Transient Unpredictable (bad)
results can happen.

HTH,

Steve…

6

Hey, I am usually the grouch who gives people a hard time. I am really upset
that I did not catch that as a possible homework question. I must be getting
old.

7

Isn’t this sort of dynamic SQL less efficient than the previous examples ?
As well as a sight less readable - or is that why you do it :wink: ?
Jason

8

So Chris, your method iterates thru the recordset starting with the first record and then stops looping at the last record, correct? Also, how would I abandon the loop if a condition was met?

Also no, this was not about homework. I’m just new to Toad/Oracle.

Thanks!
Mike

Message was edited by: MThomas

9

Thanks Jeff and Erwin…In the summer it’s harder on all of us to
detect a homework problem.

Everything you need is out there…google it and RTM

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/04_struc.htm

chris

10

Thanks for the resource.

Mike

11

How can I loop through a recordset?

Err… what version of recordset are you talking about?

There’s some differences between how the collections are handled vs the
object collections. The examples others have given you have been collection
based.

Object type collections are handled slightly differently. Since others have
given you samples of non-Object collections, here’s a very basic object
definition and use:

*** Code Start ***

CREATE OR REPLACE TYPE fruit_obj_typ AS OBJECT

(fname VARCHAR2(10), fcolor VARCHAR2(10));

CREATE OR REPLACE TYPE fruit_col_typ AS TABLE OF fruit_obj_typ;

DECLARE

. tab_fruit fruit_col_typ := fruit_col_typ();

BEGIN

. --

. – Fill your fridge…

. --

. tab_fruit.EXTEND;

. --

. -- The object reference is an inherent function, all values must be present...

. --

. tab_fruit(1) := fruit_obj_typ('Apple', 'red');

. tab_fruit.EXTEND;

. --

. -- ... unless the record is initialized to null first

. --

. tab_fruit(2) := fruit_obj_typ(NULL, NULL);

. tab_fruit(2).fname := 'Orange';

. --

. -- Report on the fruit in your fridge

. --

. DBMS_OUTPUT.PUT_LINE('Fruit Name: ' || 'Fruit color:');

. DBMS_OUTPUT.PUT_LINE('----------- ' || '------------');

. FOR num_idx IN tab_fruit.FIRST .. tab_fruit.LAST LOOP

. DBMS_OUTPUT.PUT_LINE(RPAD(tab_fruit(num_idx).fname, 12, ' ') ||
NVL(tab_fruit(num_idx).fcolor, 'Unknown'));

. END LOOP;

. --

. -- Clean out your fridge, it is past expiry date

. --

. tab_fruit.DELETE;

END;

*** Code End ***

Notes:

12

This seems like a good time to post a link here:-

" Everything You Need to Know About Collections, But Were Afraid to Ask"

Mike

13

DECLARE

. tab_fruit fruit

Lovely, this is the first time I’ve actually seen the results of my code
formatting after Yahoo gets hold of it. I didn’t realize they also modify
white space throughout the whole. Guess I’ll have to figure out another
way to produce nicely formatted code without too much work involved in removing
the formatting that can’t compile.

RAS

14

" Everything You Need to Know About Collections, But Were Afraid to Ask"

Excellent presentation with lots of information.

It tends to focus quite heavily on non-object collections though. There’s
only one mention of object collections :wink:

So for someone looking for information on object collections, other sources are
required. There are some differences between the two so you can’t just
transpose all your knowledge about non-object collections to object collections.

I noticed a source in the presentation itself and when I get a bit of time
I’ll have to go browse through Steven’s lesson materials.

RAS

15

Well… A couple of thoughts on this topic…

I will grant you that it is a bit less efficient, although you can use bind
variables and improve on it on the efficiency aspect on it. If I was working
in a heavily used OLTP environment, I wouldn't use this technique.
Most of the code that I write is expected to run in a 7x24 environment.
When choosing between clarity and efficiency, I try to pick clarity if the
loss of efficiency isn't too great. Almost all of the time, there's always a
bigger box
I like using record sets (Yahoo really doesn't do good things to formatting
in e-mail messages). They are flexible and self expanding. A simple
re-compile with properly written code and you're good to go.
I believe that the variable naming problem is present regardless of the
technique being used. I believe that is a "feature" of Oracle (at least back
in 9i).
I do extensive dynamic runtime code instrumentation as part of any code that
I write for several reasons:
Whenever I hear that this will only be used "one time", I cringe. Even if
that is the actual case, many times I wind up using that code as the basis
for another "once and done" piece of work.
When I'm writing the code, putting in instrumentation results in saving
myself time... kind of a oxymoron. I have to pause and state what my
assumptions are at this point in the code. And by documenting these
assumptions it's easier to maintain the code in six months.
If I can't document (and explain) it, then the code is too complicated or
complex.

Steve…