Oracle SQL*Plus: The Definitive Guide

Branching in SQL*Plus

SQL*Plus has no IF statement. This is a very vexing thing. Script writing is similar to programming. It’s natural to want to take different actions depending on user input or some other condition. Imagine how frustrated you would be if your favorite programming language suddenly lost its IF statement! Despite the lack of an IF statement in SQL*Plus, there are some approaches you can take to get equivalent results. Some are more straightforward than others. All involve some compromises.

Approaches to Branching

There are at least six approaches you can take to the problem of conditional execution. These are:

• Simulate branching by adjusting the WHERE clause in a query.

• Use REFCUSOR variables.

• Use a multilevel file structure.

• Use SQL to write SQL.

• Use PL/SQL for conditional logic.

• Use an operating-system scripting language.

Some of these approaches are very specific to certain types of problems. Using REFCURSOR variables, for example, is a good solution when you simply need to choose which query to run based on user input or some other condition. Other approaches, such as the use of a multilevel file structure for your script, are more general in nature, and can be used for any type of branching.

Simulating Branching by Adjusting the WHERE Clause

Suppose you are writing a script to delete all data from the project_hours table. Before you delete the data, you want to ask the user to confirm the operation. You really want to write something like this:

ACCEPT s_delete_confirm ...