Oracle SQL
  • LICENSE

SQL*Plus tips #3: Iterators and recursions

Posted on April 9, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 8 Comments

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]

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…

SQL*Plus
« SQL*Plus tips. #2
SQL*Plus tips #4: Branching execution »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL