Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Branching in SQL*Plus

SQL*Plus has no IF statement, which is vexing. 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, you can take some approaches to get equivalent results. Some are more straightforward than others. All involve some compromises.

You can take at least six approaches to the problem of conditional execution:

• 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 specific to certain types of problems. Using REFCURSOR variables, for example, is a good solution when you 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 want to write something like the following:

ACCEPT s_delete_confirm PROMPT 'Delete project hours data (Y/N)?' IF s_delete_confirm = 'Y' THEN DELETE ...