Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

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

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

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata