We all used to iterators, recursions and branching in programming, but sql*plus does not have such commands. Today I will show how to do iterators/recusions.
Suppose we want to call a script 3 times. So it would be convenient if we can do it like:
@iterate 3 @some_script
It is very easy: We can recursively call the script by reducing variable until it is greater than zero. and if a variable is zero, then call an empty “NULL” script.
iterate.sql:
-- initializing input parameters as i showed in previous tips:
@inc/input_params_init.sql;
-- for example max number of input parameters = 14:
def _INIT_PARAMS = '"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
def _ITER = "&1"
prompt ------ iteration &_ITER start: "@&2 -----------;
set termout off
col _SCRIPT new_val _SCRIPT noprint;
col _CONTINUE new_val _CONTINUE noprint;
col _PARAMS new_val _PARAMS noprint;
select
replace('"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
,'${ITER}'
,'&_ITER'
) as "_PARAMS"
,case
when &1 > 0 then 'iterate '||(&1 - 1)
else 'inc/null'
end as "_CONTINUE"
,case
when &1 > 0 then '&2'
else 'inc/null'
end as "_SCRIPT"
from dual;
set termout on
@&_script &_PARAMS
@&_CONTINUE &_SCRIPT &_INIT_PARAMS
As you see i also added option to pass iteration_number as input parameter, so we can call it like: @iterate 3 script.sql ${ITER}
And null.sql in “inc” directory is just empty file.
Lets test it:
SQL> $cat test.sql
select &1 a,'&2' b,'&3' c from dual;
SQL> @iterate 3 test.sql 1 2 3
------ iteration 3 start: "@test.sql -----------
A B C
---------- - -
1 2 3
------ iteration 2 start: "@test.sql -----------
A B C
---------- - -
1 2 3
------ iteration 1 start: "@test.sql -----------
A B C
---------- - -
1 2 3
------ iteration 0 start: "@test.sql -----------
SQL> @iterate 2 test.sql 1 2 ${ITER}
------ iteration 2 start: "@test.sql -----------
A B C
---------- - -
1 2 2
------ iteration 1 start: "@test.sql -----------
A B C
---------- - -
1 2 1
------ iteration 0 start: "@test.sql -----------
Note that last string “iteration 0 start” just means that it is last empty call. I made this only for showing how we can do postprocessing after all iterations.
Ok, it works good, but with a little modification we can also add option to call such script with list of values too:
@inc/input_params_init.sql;
set termout off
def _INIT_PARAMS = '"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
col _ITER_CURR new_val _ITER_CURR noprint
col _ITER_NEXT new_val _ITER_NEXT noprint
select
case
-- simple N iterations:
when translate('&1','x0123456789','x') is null
then '&1'
-- list iteration:
when substr('&1',1,1)='('
then decode( instr('&1',',')
,0,substr('&1',2,length('&1')-2)
,substr('&1',2,instr('&1',',')-2)
)
end "_ITER_CURR",
case
when translate('&1','x0123456789','x') is null
then
case
when '&1'>1 then 'iterate '||('&1' - 1)
else 'inc/null'
end
when substr('&1',1,1)='('
then
case
when instr('&1',',')=0 or '&1'='()' then 'inc/null'
else 'iterate '
||'('
||decode( instr('&1',',')
,0,')'
,substr('&1',instr('&1',',')+1)
)
end
end "_ITER_NEXT"
from dual;
set termout on
prompt ****** iteration &_ITER_CURR start: "@&2 ***********;
set termout off
col _SCRIPT new_val _SCRIPT noprint;
col _PARAMS new_val _PARAMS noprint;
select
replace('"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
,'${ITER}'
,'&_ITER_CURR'
) as "_PARAMS"
,case
when nvl('&_ITER_CURR',0) in ('0','()') then 'inc/null'
else '&2'
end as "_SCRIPT"
from dual;
set termout on
@&_script &_PARAMS
@&_ITER_NEXT &_SCRIPT &_INIT_PARAMS
See how it works:
-- iterate by list of values (7,3,10):
SQL> @iterate (7,3,10) test.sql 1 2 ${ITER}
****** iteration 7 start: "@test.sql ***********
A B C
---------- - -
1 2 7
****** iteration 3 start: "@test.sql ***********
A B C
---------- - -
1 2 3
****** iteration 10 start: "@test.sql ***********
A B C
---------- - --
1 2 10
-- now with simple 2 iterations:
SQL> @iterate 2 test.sql 1 2 ${ITER}
****** iteration 2 start: "@test.sql ***********
A B C
---------- - -
1 2 2
****** iteration 1 start: "@test.sql ***********
A B C
---------- - -
1 2 1
So if you call script with “iterate N …” it will iterate N times, and if you specify “iterate (X,Y,…,Z)” it will iterate by this list.
PS. About a branching i will wrote later…