Bunch's Blog

One day I'll have a catchy subtitle, one day
posts - 87 , comments - 120 , trackbacks - 0

Tuesday, July 17, 2012

Oracle and Looping Through Columns

I recently had to write a stored procedure that validated some data in a staging table. Looping through the records was pretty straight forward in Oracle using a loop (way easier than the SQL Server version). However there was set of columns in the table that were all the same type but different values that needed to be checked. To clarify I had a table setup like this:

ID  Name  Number  Position1  Position2  Position3

The ID is just an integer PK value, Name would be a players name, Number their jersey number and then Position1 - 3 the different positions the player knows how to play (like shortstop, second base, left field, etc.).

To start with I create my outer loop that I would use to check their Name and Number along with getting the various Position values.

for players in (select p.ID, p.Name, p.Number, p.Position1, p.Position2, 
                    from stage_players p) loop

After checking the Name and Number I needed now to loop through the three possible Positions for the player's record or loop through that record's columns. To do this I setup an inner loop like this.

for positions in (select players.Position1 as pos from dual
                       select players.Position2 as pos from dual
                       select players.Position3 as pos from dual) loop

Now I can use this loop to check each positions.pos to make sure they are valid.


Posted On Tuesday, July 17, 2012 3:24 PM | Comments (0) |

Powered by: