SQL yes or no to repeat function

Question

goto1:

           --- update tabellen
    update dgdtw_lockedinfo set ciuserid = couserid where locknr = &lock;
    update dgdtw_topografie set locknr = '' where locknr = &lock;
    update dgdtw_topografie set verval=sysdate where id= &lock;
    commit;

    accept var prompt ' yes or no '

    if (var = yes) then 
    goto1

    and if (var = no)
       exit
    end if

need to repeat a function if user inputs yes

is there a way to repeat command by input?

sql is a query language, not a programming language. If you want functionality you should use a programming language to make the queries — Anton, Dec 23 '15 at 10:16

score 1 · Accepted Answer · answered Jan 11 '16 at 22:31

If you really must do this, use recursion via the start command to loop.

Create a 'end of recursion' file stop.sql with content:

prompt OK, end of repeat
exit

Create a file repeat.sql with the following content:

set echo off verify off

select 'Your SQL statements go here' from dual;

accept var prompt 'Repeat the SQL statement [yes or no] ? '

define doit = 'stop.sql'
column doit new_value doit noprint
set termout off
select 'repeat.sql' doit from dual where upper('&var') like 'Y%';
set termout on
start &doit.

This is an example run:

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 11 23:30:24 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


'YOURSQLSTATEMENTSGOHERE'
---------------------------
Your SQL statements go here

Repeat the SQL statement [yes or no] ? yes

'YOURSQLSTATEMENTSGOHERE'
---------------------------
Your SQL statements go here

Repeat the SQL statement [yes or no] ? y

'YOURSQLSTATEMENTSGOHERE'
---------------------------
Your SQL statements go here

Repeat the SQL statement [yes or no] ? n
OK, no further repetitions.

can you help me with [this question](http://stackoverflow.com/questions/34926569/oracle-plsql-if-not-found-repeat?noredirect=1#comment57587868_34926569)? — Rafael, Jan 22 '16 at 07:30

SQL yes or no to repeat function

1 Answers1