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.

 

DBMS_OUTPUT and Blank Lines

Using DBMS_OUTPUT.PUT_LINE, you can't normally generate blank lines in your output stream. None of the following invocations will result in any output:

DBMS_OUTPUT.PUT_LINE('');

DBMS_OUTPUT.PUT_LINE(' ');

DBMS_OUTPUT.PUT_LINE(' ');
 

Yet Example 11-28 manages to generate blank lines using the DBMS_OUTPUT package. How is that done? Example 11-28 accomplishes the effect of blank lines by sending tab characters , which are CHR(9) , to the output stream. A tab character isn't a blank, so it isn't ignored. However, the effect on most displays is to move the cursor to the right, an effect that is, ultimately, is invisible. The net effect is a blank line.

You can issue the command SET SERVEROUTPUT ON FORMAT WRAPPED, which preserves any blank lines in the output stream but at the cost of potentially wrapping a line in the middle of a word (the default FORMAT is WORD_WRAPPED).


     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net