Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

11.3 Looping in SQL*Plus

There is no way to write a real loop using SQL*Plus. Your best
option, if you need to do something iteratively, is to use PL/SQL.
PL/SQL, however, doesn't allow you any interaction
with the user, so it's not always suitable for the
task at hand. Your next bet is to look into using your operating
system's scripting language, if there is one.

This said, you can do a couple of things in SQL*Plus that might get
you the same result as writing a loop:

Using recursive execution Generating a file of commands, and then executing it
The first option has some severe limitations, and I
don't recommend it. The second option I use all the
time, especially when performing database maintenance tasks.

11.3.1 Recursive Execution

You can't loop, but you
can execute the same script recursively. Suppose you have a script
that displays some useful information, and you want to give the user
the option of running it again. You can do that by recursively
executing the script. Take a look at the following interaction, in
which the user is looking at indexes for various tables. It looks
like a loop. Each time through, the user is prompted for another
table name, and the indexes on that table are displayed.

SQL> @ex11-27 employee
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEE_PK EMPLOYEE_ID
EMPLOYEE_BY_NAME EMPLOYEE_NAME
Next table >project
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
PROJECT_PK PROJECT_ID
Next table >project_hours
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
PROJECT_HOURS_PK PROJECT_ID
EMPLOYEE_ID
TIME_LOG_DATE
Next table >
Goodbye!

It sure does look like a loop, but it's not. Example 11-27 shows the script that is being run.

Example 11-27. Using recursion to simulate a loop

SET ECHO OFF
SET VERIFY OFF
COLUMN index_name FORMAT A30
COLUMN column_name FORMAT A30
BREAK ON index_name NODUPLICATES
SELECT index_name, column_name
FROM user_ind_columns
WHERE table_name = UPPER('&1');
--Ask the user if he wants to do this again.
PROMPT
ACCEPT s_next_table PROMPT 'Next table >'
--Execute either list_indexes.sql or empty.sql,
--depending on the user's response.
COLUMN next_script NOPRINT NEW_VALUE s_next_script
SET TERMOUT OFF
SELECT DECODE ('&&s_next_table',
'','ex11-27_empty.sql',
'ex11-27.sql ' || UPPER('&&s_next_table')) next_script
FROM dual;
SET TERMOUT ON
@&&s_next_script The key to the looping is in the last part of the script, following
the ACCEPT statement. If you enter another table name, the SELECT
statement will return another call to the
ex11-27.sql script. So, when you type
"project" in response to the
prompt, the s_next_script substitution variable
ends up being this:

ex11-27.sql PROJECT The only thing missing is the at sign, and that is supplied by the
command at the bottom of Example 11-27. In this case,
the command:

@&&s_next_script will be translated to:

@ex11-27.sql PROJECT If you don't enter a table name at the prompt, the
s_next_table variable will be null, and the DECODE
statement will return
"ex11-27_empty.sql". The
ex11-27_empty.sql script is necessary because
the @ command must be executed.
ex11-27_empty.sql gives you a clean way out of
the recursion. In this case, ex11-27_empty.sql
displays a goodbye message:

PROMPT
PROMPT Goodbye!
PROMPT Recursive execution is a limited technique. You
can't nest scripts forever. You can only go 20
levels deep, and on some older versions of SQL*Plus the limit may be
as low as 5. Exceed that limit, and you will get the following
message:

SQL*Plus command procedures may only be nested to a depth of 20.

Still, recursion can be useful. What are the odds that you will want
to type in 20 table names in one sitting? In this case, the
convenience may outweigh any chance of exceeding that limit on
nesting scripts. And if you do exceed the limit, so what? You can
rerun the script.

11.3.2 Looping Within PL/SQL

You should consider PL/SQL when you need to
implement any type of complex procedural logic, and that includes
looping. Because PL/SQL executes in the database, you
can't use it for any loop that requires user
interaction. Example 11-27, which repeatedly prompts
for another table name, could never be implemented in PL/SQL.
It's also impossible to call another SQL*Plus script
from PL/SQL. However, if you can get around those two limitations,
PL/SQL may be the best choice for the task. Example 11-28 shows a script that uses a PL/SQL block to
display indexes on all tables that you own, having names matching a
pattern that you specify.

Example 11-28. Looping is often best done in PL/SQL

SET ECHO OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
ACCEPT table_name PROMPT 'Show indexes on what table >'
DECLARE
BEGIN
IF '&table_name' IS NOT NULL THEN
--Loop for each table and index selected by the user
FOR xtable IN (
SELECT table_owner, table_name, index_name
FROM user_indexes
WHERE table_name LIKE UPPER('&table_name')
ORDER BY table_owner, table_name, index_name) LOOP
--Display the table and index names
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Index ' || xtable.index_name || ' on '
|| xtable.table_owner || '.' || xtable.
table_name);
--Loop through each column in the index
FOR xcolumn IN (
SELECT column_name
FROM user_ind_columns
WHERE index_name = xtable.index_name
ORDER BY column_position) LOOP
DBMS_OUTPUT.PUT_LINE(' ' || xcolumn.column_name);
END LOOP;
END LOOP;
END IF;
END;
/ Output from Example 11-28 looks like this:

SQL> @ex11-28
Show indexes on what table >employee
Index EMPLOYEE_BY_NAME on GENNICK.EMPLOYEE
EMPLOYEE_NAME
Index EMPLOYEE_PK on GENNICK.EMPLOYEE
EMPLOYEE_ID Example 11-28 prompts once at the beginning of the
script. Then control falls into a PL/SQL block that uses an outer
loop to go through each matching table and index, and an inner loop
to display each column from each index. PL/SQL can't
write to your display. Instead, Example 11-28 uses the
DBMS_OUTPUT package to write output to an in-memory buffer on the
database server. When the block completes, SQL*Plus reads and
displays the contents of that buffer.

SQL*Plus will not display the contents of the DBMS_OUTPUT buffer unless you have first issued the command SET SERVEROUTPUT ON. If you expect to need more than the default 2000-character buffer, use SET SERVEROUTPUT ON SIZE xxx , in which xxx is the number of bytes to allocate, up to 1,000,000.