Simulating Control Flow Statements in SQL*Plus

SQL*Plus does not have any control flow structures like IF-THEN-ELSE, SWITCH-CASE or a kind of LOOPS. Sometimes we need to have such control flow structures, e.g. using different SQL-Statements in response to command line parameters. Fortunately, it is possible to simulate loops and conditional execution by using

  • binding of user-variables to SQL columns
  • switch-case in a SQL statement
  • calling „sub“-scripts for each branch for IF-THEN-ELSE like structures
  • calling the script itself for loop like structures

IF-THEN-ELSE

Here, we give the user the choice to enter some text which is echoed or just print some standard text and exit. For this, we need two files start.sql and echo.sql.

start.sql

-- activate substitution variables
SET define ON
-- suppress echo for substitution variables
SET verify OFF
-- activate stdout
SET serveroutput ON

-- create menu structure
PROMPT
PROMPT Would you like to echo some text? Press y

-- read the input to the substitution variable CHOICE
ACCEPT CHOICE
PROMPT

-- bind a substitution variable SELECTION to a SQL column
col SELECTED new_val SELECTION
-- do if then else with SQL
-- the selected value is the name of the script to be executed

-- suppress terminal output
set term off

SELECT
 CASE
 WHEN 'y'='&CHOICE' THEN 'echo.sql'
 ELSE 'standard.sql'
 END AS SELECTED
FROM dual;

-- activate terminal output
set term on

-- execute the selected script
@ &SELECTION

EXIT

echo.sql

PROMPT Enter some text:
ACCEPT userinput

PROMPT &userinput

standard.sql

PROMPT This is some standard output.

LOOP

Here, we want to simulate a FOR loop. The main idea is to call the script itself again and again passing the current iteration count as command line parameter.

for.sql

-- activate substitution variables
SET define ON
-- suppress echo for substitution variables
SET verify OFF
-- activate stdout
SET serveroutput ON

-- bind a substitution variable SELECTION to a SQL column
col SELECTED new_val SELECTION

-- active terminal output
set term on

PROMPT iteration counter: &1

-- suppres terminal output
set term off

SELECT
 CASE
 WHEN &1 < &2 - &3 THEN 'for.sql ' || to_char(&1 + &3) || ' &2 &3'
 ELSE ''
 END AS SELECTED
FROM dual;

@ &SELECTION
EXIT

You can call it with the pattern

[ ~]$ sqlplus -S for.sql <counter start value> <max value> <increment by>

For example, iterate 10 times and increment by one.

[kabelplus@vsrv10891 ~]$ sqlplus -S <user>/<password> @for.sql 0 10 1
iteration counter: 0
iteration counter: 1
iteration counter: 2
iteration counter: 3
iteration counter: 4
iteration counter: 5
iteration counter: 6
iteration counter: 7
iteration counter: 8
iteration counter: 9

Iterate 5 times and increment by 2

[kabelplus@vsrv10891 ~]$ sqlplus -S <user>/<password> @for.sql 0 10 2
iteration counter: 0
iteration counter: 2
iteration counter: 4
iteration counter: 6
iteration counter: 8

Command line menu

The following script menu.sql gives you an example of a command line menu built in SQL*Plus

-- activate substitution variables
SET define ON
-- suppress echo for substitution variables
SET verify OFF
-- activate stdout
SET serveroutput ON

-- create menu structure
PROMPT
PROMPT Please enter the character inside the parenthesis
PROMPT and press Enter
PROMPT
PROMPT (1) Option 1
PROMPT (2) Option 2
PROMPT (3) Option 3
PROMPT (x) Exit

-- read the input to the substitution variable CHOICE
ACCEPT CHOICE
PROMPT

-- bind a substitution variable SELECTION to a SQL column
col SELECTED new_val SELECTION
-- do if then else with SQL
-- the selected value is the name of the script to be executed

set term off
SELECT
  CASE
    WHEN '1'='&CHOICE' THEN 'option1.sql'
    WHEN '2'='&CHOICE' THEN 'option2.sql'
    WHEN '3'='&CHOICE' THEN 'option3.sql'
    WHEN 'x'='&CHOICE' THEN 'exit.sql'
    ELSE 'error.sql'
  END AS SELECTED
FROM dual;

set term on
-- execute the selected script
@ &SELECTION

-- after the task has finished present the menu again
@ menu.sql
EXIT

You will also need to have the files option1.sql, option2.sql, option3.sql and exit.sql. option1 to option3 do some tasks. exit.sql may also do some task BUT must contain at least the keyword EXIT.

A sample session looks like this

[ ~]$ sqlplus -S <user>/<password> @menu.sql

Please enter the character inside the parenthesis
and press Enter

(1) Option 1
(2) Option 2
(3) Option 3
(x) Exit
2

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Time limit is exhausted. Please reload the CAPTCHA.