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:
[sourcecode language=”sql”]
@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
[/sourcecode]
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…