Question: I have a need to do a loop in my SQL without using
PL/SQL. I understand that PL/SQL was create for process logic like IF and
LOOPING, but can Oracle SQL support loop constructs directly? I want to
use Oracle SQL to perform a FOR loop.
Answer: SQL was first
developed as a "Structured query Language", although you could argue that SQL is
not structured, for only for queries, and not a language since SQL is normally
embedded into a host language like C or PL/SQL. However, SQL is expending,
and Oracle SQL now indirectly supports many of the functions of a procedural
language such as branching (see the
CASE operator) and
rudimentary loop constructs.
In this example below, we see Oracle SQL with a FOR loop, embedded into the
xmltable stored procedure. This SQL also materializes a
intermediate result set by using the
SQL WITH clause:
SQL> with t as
(
select date '2008-06-01' d1, date
'2008-06-10' d2 from dual
)
select d1 + i days
from t, xmltable('for $i in 0 to xs:int(D) return $i'
passing xmlelement(d, d2-d1)
columns i integer path '.')
So, while Oracle SQL does not directly support while loops of for loops,
there is extended syntax for looping within some stored procedures that are
embedded into Oracle SQL.
Reader comments:
I always enjoy your site. I saw someone request a loop in SQL so I tried your
SQL loop code. I decided to make my own version of SQL looping (which is a
lot longer than yours, but of course, I can understand it! :)
Set Term &OnOff
Column RunProg1 New_Value Run_Code_Or_End
column RunProg2 New_Value Run_Loop_Or_Stop
Select Case When Counter <=
(Select Length(Ename)
From Emp
Where Rownum <2)
Then 'Code'
Else 'EndLoop'
End
As
RunProg1,
--------------------------------------------------
Case When Counter <= (Select Length(Ename)
From Emp
Where Rownum <2)
Then 'Looper'
Else ' '
End
As
RunProg2
From GTT_CountHolder ;
Set Term On
@&Run_Code_Or_End
@&Run_Loop_Or_Stop
Code is the actual code that we want to run multiple times. It runs the query
and then updates the Counter in the temp table.
code.sql
Column Character Format A9
Select Ename ,
-------------------------------
-- Show position
-------------------------------
(Select Counter
from GTT_CountHolder )
As
Position,
--------------------------------
-- Show character
-- Substr(field,position,length)
--------------------------------
Substr(Ename
/* Field */
, (Select Counter
from GTT_CountHolder ) /* Position */
,1)
/* Length */
As
Character
-------------------------------
From Emp
Where Rownum < 2;
Update GTT_CountHolder
Set Counter = Counter + 1;
EndLoop.SQL
Truncate Table GTT_CountHolder ; Drop Table
GTT_CountHolder ;
Lynn Tobias
