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:



final iterate.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 [collapse]

-- 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

See how it works:

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…