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,
p.Position3
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
union
select
players.Position2 as pos from dual
union
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.
Tags: PLSQL