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

2.7 Line Editing

The concept of line-editing goes way
back to the days when all many people had to work with were
dumb terminals that didn't
allow full-screen editing, and connection speeds were so slow that
full-screen editing would have been very painful anyway. A good line
editor will allow you to work productively at connection speeds as
low as 300 bits per second. While working at that speed
isn't much of a concern today, it accurately
reflects the environment at the time SQL*Plus was first conceived.

The line-editing process in SQL*Plus follows these steps:

Enter a SQL statement or PL/SQL block, which SQL*Plus stores in the
buffer.

List the contents of the buffer to the screen.

Enter SQL*Plus commands telling SQL*Plus to make changes to the
statement or block in the buffer.

List the buffer again.

If you like what you see, execute the statement or block; otherwise,
you go back to step 3 and make some more changes.

I can remember that in my younger days my fellow programmers and I
always took great pride in the number of line-editing changes we
could make and visualize in our heads before we had to break down and
list our code again.

2.7.1 The Current Line

When working with the line editor in
SQL*Plus, you must understand the concept of the current
line . The current line is the one that you have most
recently "touched." When you are
entering a statement, the most recently entered line is the current
line.

The statement shown in Example 2-9 is six lines
long. Line 7 doesn't count and is not added to the
buffer because that's where the blank line is used
to terminate entry of the statement. In this case, the last line
"touched" happens to be the last
line entered, so line 6 is the current line.

Example 2-9. Line 5 is the current line

SQL> SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6 GROUP BY employee_name, project_name
7
SQL>

Most line-editing commands, by default, operate on the current line.
Some commands, such as LIST and DEL, allow you to specify a line
number. When you specify a line number for an editing command, the
command is executed, and that line then becomes the new current line.
You'll see how this works as you read through the
examples that follow.

2.7.2 Line-Editing Commands

SQL*Plus
implements a number of useful line-editing commands, some of which
have several variations. Most of these commands may be abbreviated to
one letter. Table 2-1 describes each of these
commands and shows the abbreviations and variations for each one.

Notice that two of the commands, LIST and DEL, allow you to specify a
line number or a range of line numbers. For these two commands, there
are two special keywords you can use in place of a number. These
keywords are * and LAST, and have the following meanings:

*
An
asterisk always refers to the current line.

LAST
The keyword LAST refers to the last line in the buffer.

You will see examples of how these elements are used as you read more
about each of the commands.

2.7.2.1 Getting a statement into the buffer

To put a SQL statement into the
buffer, enter the statement and terminate it with a blank line, as
shown in Example 2-10.

Example 2-10. Entering a SQL statement into the buffer

SQL> SELECT *
2 FROM project
3
SQL>

The statement is inserted into the buffer one line at a time as you
enter it. Pressing Enter on a blank line tells SQL*Plus to leave the
statement in the buffer without transmitting it to the server. PL/SQL
blocks are entered the same way except that you terminate them by
entering a period on the last line. Example 2-11
shows one of the shortest PL/SQL block you can write.

Example 2-11. A very short PL/SQL block

SQL> BEGIN
2 NULL;
3 END;
4 .
SQL> Terminating the block with a period tells SQL*Plus not to send it to
the database, but to keep it in the buffer.

2.7.2.2 LIST

The LIST command shows you the current contents of the buffer. It is
fundamental to the use of the other line-editing commands. Use LIST
to view your SQL statement as it currently exists to see if any
changes need to be made. Use LIST after making changes to be sure
that they were made correctly.

Look at Example 2-12, which shows a SQL statement
being entered into SQL*Plus, and then shows the LIST command being
used to display it again.

Example 2-12. Listing the buffer

SQL> SELECT employee_name, time_log_name, project_name
2 FROM employee JOIN
3 ON employee.employee_num = time_log.employee_num
4 JOIN project
5 ON time_log.project_id = project.project_num
6 HAVING employee_num = project_name
7 GROUP BY employee_name, project_name
8
SQL> LIST
1 SELECT employee_name, time_log_name, project_name
2 FROM employee JOIN
3 ON employee.employee_num = time_log.employee_num
4 JOIN project
5 ON time_log.project_id = project.project_num
6 HAVING employee_num = project_name
7* GROUP BY employee_name, project_name Notice the asterisk marking line 7. The asterisk indicates the
current line, which LIST always sets to be the last line displayed.
You can display just the current line by using LIST *, as in the
following example:

SQL> LIST *
7* GROUP BY employee_name, project_name You can display one specific line by specifying the line number as an
argument to the LIST command. The next example shows how to list line
3:

SQL> LIST 3
3* ON employee.employee_num = time_log.employee_num Notice the asterisk. By listing line 3 you have made it the current
line for editing purposes.

The keyword LAST may be used to display the last line in the buffer:

SQL> LIST LAST
7* GROUP BY employee_name, project_name You may specify a range of lines to be displayed. Do this by
specifying the starting and ending lines as arguments to the LIST
command. Either or both of these arguments may be the keyword LAST or
*. Following are several different ways to display a range of lines
using LIST:

SQL> LIST 1 3 List lines 1 through 3
1 SELECT employee_name, time_log_name, project_name
2 FROM employee JOIN
3* ON employee.employee_num = time_log.employee_num
SQL> LIST * LAST List everything beginning from the current line
3 ON employee.employee_num = time_log.employee_num
4 JOIN project
5 ON time_log.project_id = project.project_num
6 HAVING employee_num = project_name
7* GROUP BY employee_name, project_name
SQL> LIST 4 * List from line 4 through 7 (the current line)
4 JOIN project
5 ON time_log.project_id = project.project_num
6 HAVING employee_num = project_name
7* GROUP BY employee_name, project_name
SQL> LIST * * A one-line range, same effect as LIST *
7* GROUP BY employee_name, project_name
SQL> LIST LAST LAST A one-line range, same as LIST LAST
7* GROUP BY employee_name, project_name As a shortcut to using the LIST command, if you are only interested
in one line, you can list it by entering the line number and then
pressing Enter. This won't work for a range of
lines, but it will work for just one. Here's an
example:

SQL> 3
3* ON employee.employee_num = time_log.employee_num On a seven-line statement, you might wonder why you would ever bother
to list just one line or a range of lines. Remember, line speeds were
slow when SQL*Plus was first developed. In addition, SQL statements
and PL/SQL blocks are often much longer than seven lines. Listing a
range allows you to focus on one area at a time while you fix it.

Keep the SQL statement from Example 2-12 in the
buffer (or at least in mind) as you read about the rest of the
line-editing commands. It has several mistakes that
we'll fix using the other commands.

2.7.2.3 APPEND

Use the APPEND command to add text onto the end of a line. It works
on the current line, so you must first decide which line you want to
change and then make that line current. Use the LIST command to
review the SQL statement currently in the buffer:

SQL> LIST
1 SELECT employee_name, time_log_name, project_name
2 FROM employee JOIN
3 ON employee.employee_num = time_log.employee_num
4 JOIN project
5 ON time_log.project_id = project.project_num
6 HAVING employee_num = project_name
7* GROUP BY employee_name, project_name I intended this SELECT statement to join all three sample tables, but
if you look at line 2, you will see that I forgot to include the
project_hours table. This can be corrected by
first making line 2 the current line and then using the APPEND
command to add the third table to the join. The first step is to LIST
line 2 in order to make it current:

SQL> L 2
2* FROM employee JOIN Now that line 2 is the current line, the APPEND command may be used
to add project_hours to the join:

SQL> A project_hours
2* FROM employee JOIN project_hours It's a bit difficult to see from the example, but
two spaces follow the A (for APPEND) command. The
first space separates the command from the text you wish to append.
SQL syntax requires a space following the keyword JOIN, so my text to
append consisted of a space followed by the table name. Now the
SELECT statement in the buffer joins all three tables.

2.7.2.4 CHANGE

The CHANGE command searches the current line for a specified string
and replaces that string with another. CHANGE replaces only the first
occurrence it finds, so if you need to change multiple occurrences of
a string in the same line, you will need to execute the same CHANGE
command several times. CHANGE may also be used to simply delete text
from a line.

List the contents of the buffer again. Your output should match that
shown below:

SQL> LIST
1 SELECT employee_name, time_log_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_num = time_log.employee_num
4 JOIN project
5 ON time_log.project_id = project.project_num
6 HAVING employee_num = project_name
7* GROUP BY employee_name, project_name Line 1 references a column that does not exist. A little later you
will see how to remove that column reference with the CHANGE command.
Next, the two ON clauses contain four mistakes: the table name
time_log is used twice instead of
project_hours, and employee_num
is used twice when it really should be
employee_id. The CHANGE command can be used to fix
these problems. To start with, here's how to change
time_log to project_hours:

SQL> L 3
3* ON employee.employee_num = time_log.employee_num
SQL> C /time_log/project_hours/
3* ON employee.employee_num = project_hours.employee_num
SQL> L 5
5* ON time_log.project_id = project.project_num
SQL> c /time_log/project_hours/
5* ON project_hours.project_id = project.project_num In this example, the LIST command is first used to make line 3 the
current line. Then the CHANGE command, abbreviated to C, is used to
change the table name. After the edit is complete, the line is
automatically redisplayed so you can see the effects of the change.
The process is repeated to make the same change to line 5. You can
change only one line at a time.

Next, the employee_num field name needs to be
corrected. It should be employee_id. Although the
two occurrences of employee_num are in the same
line, CHANGE will have to be executed twice. The following example
shows this:

SQL> L 3
3* ON employee.employee_num = project_hours.employee_num
SQL> c /employee_num/employee_id/
3* ON employee.employee_id = project_hours.employee_num
SQL> c /employee_num/employee_id/
3* ON employee.employee_id = project_hours.employee_id Notice that the CHANGE command searched the current line from left to
right. The leftmost occurrence of employee_num was
the first to be changed. Notice also that the CHANGE command had to
be retyped each time. SQL*Plus does not have any command-recall
capability.

While SQL*Plus itself has no command-recall capabilities, your operating system shell may. Run command-line SQL*Plus from a Windows XP command-prompt window, and you'll be able to use the up and down arrows to move back and forth through your recently entered commands.

Now that line 3 is fixed up, take another look at line 1. This time,
omit the L command, and just type the line number in order to list
the line:

SQL> 1
1* SELECT employee_name, time_log_name, project_name Line 1 contains a bad column name, which needs to be deleted. A
variation of the CHANGE command, where you don't
supply any replacement text, can be used to do this. The following
example shows how:

SQL> C /time_log_name, //
1* SELECT employee_name, project_name At first glance, the use of the CHANGE command to delete text may not
seem very intuitive. Think in terms of searching for a string, in
this case for "time_log_name,
" and replacing it with nothing.

With the CHANGE command, you can use delimiters other than the
forward slash character. You simply need to be consistent within the
command. SQL*Plus interprets the first non-space character following
the CHANGE command as the delimiter character. The following
commands, for example, are all equivalent:

C /FRUB/FROM/
C *FRUB*FROM*
C XFRUBXFROMX The only time you would ever need to use a delimiter other than / is
if you need to include a / as part of the text to be searched for or
replaced. You have the option of leaving off the trailing delimiter
as long as you aren't trying to include trailing
spaces in your substitution string. The following two commands are
equivalent:

C /FRUB/FROM/
C /FRUB/FROM However, if your substitution strings contain spaces, you do need to
include the trailing delimiter. The following two commands will
not produce equivalent results:

C / FRUB / FROM /
C / FRUB / FROM It's probably easiest to be in the habit of
including the trailing delimiter all the time.
You'll make fewer mistakes that way.

2.7.2.5 DEL

Use the DEL command to erase one or more lines from the buffer. Used
by itself, DEL erases the current line. You may specify a line, or a
range of lines, as an argument to the DEL command. Unlike the other
line-editing commands, DEL cannot be abbreviated. This is perhaps a
safety measure to keep you from accidentally deleting a line.

Be careful that you do not spell out the command as DELETE instead of DEL. SQL*Plus will interpret DELETE as a new SQL statement, and will place it in the buffer in place of the statement that you are editing. You will then have lost your statement.

If you have been following along through all the line-editing
examples, use the L command to list the buffer. You should see the
following output:

SQL> L
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_num
6 HAVING employee_num = project_name
7* GROUP BY employee_name, project_name Line 6, with its HAVING clause, is completely spurious. It can be
erased by specifying the DEL command as follows:

SQL> DEL 6
SQL> L *
6* GROUP BY employee_name, project_name SQL*Plus doesn't echo anything back at you, but line
6 has been erased. Notice that L * was used
following the delete to list the current line, which is now line 6.
Why line 6? Because 6 was the number of the line most recently
touched by an editing command. In this case, the original line 6 was
erased, what was line 7 became line 6, and the new line 6 became
current.

The DEL command may be used to erase a range of lines. As with LIST,
the keywords LAST and * may be used to specify the
last line in the buffer and the current line, respectively. The
following example shows how to erase lines 4 through the current
line, which is line 6:

SQL> DEL 4 *
SQL> L *
3* ON employee.employee_id = project_hours.employee_id Because line 6 was current, the DEL command just shows erased lines 4
through 6. The new current line would ordinarily still be line 4
because that was the last number line touched (erased) but, in this
case, because only three lines are left in the buffer, the last line
becomes current.

2.7.2.6 INPUT

The INPUT command is used to insert one or more lines of text into
the buffer. The INPUT command with a text argument allows you to
insert only one line, which is placed into the buffer following the
current line. The INPUT command with no arguments puts you into a
multiline input mode where you can type as many lines as desired,
ending with a blank line. These lines are inserted into the buffer
following the current line.

List the buffer again. You can see that we have done serious damage
to our SELECT statement by our most recent, and evidently careless,
deletion:

SQL> L
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3* ON employee.employee_id = project_hours.employee_id The original intent was to list each employee together with all
projects to which the employee actually charged hours. To do that,
the join to project and the GROUP BY clause need
to be put back in. The following example shows how to insert the
GROUP BY clause by using the INSERT command with a text argument:

SQL> L Make line 3 current, in order to insert after it
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3* ON employee.employee_id = project_hours.employee_id
SQL> I GROUP BY employee_name, project_name
SQL> L
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4* GROUP BY employee_name, project_name An easier alternative, when you have several lines to insert, would
be to use the INPUT command with no arguments. This places you into
input mode , in which you can type as many lines
as you like. Pressing a blank line exits input mode, and terminates
the entry. Here's how to put back the join to the
project table using this method:

SQL> L 3
3* ON employee.employee_id = project_hours.employee_id
SQL> I
4i JOIN project
5i ON project_hours.project_id = project.project_id
6i
SQL> L
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6* GROUP BY employee_name, project_name The LIST command was used to make line 3 current, so that new lines
will be inserted after it. Then the I (for INPUT) command was used by
itself to enter input mode, and the two lines defining the join to
project were entered into the buffer. The prompt
included an "i" following the line
number to remind you that you were inserting lines into an existing
statement.

If you are picky about formatting, use the second form of the INPUT
command shown above. That will let you enter leading spaces to make
things line up nicely. INPUT
text will trim off leading spaces before
text is inserted.

To add lines at the end of a buffer, first do a LIST or a LIST LAST
to make the last line current. Then use the INPUT command to put
yourself into input mode. Any lines you type will be appended onto
the end of the buffer.

To add a line to the beginning of the buffer, prior to the first
line, add it as line 0:

SQL> SELECT * FROM dually
2
SQL> L
1* SELECT * FROM dually
SQL> 0 WITH dually AS (SELECT * FROM dual)
SQL> L
1 WITH dually AS (SELECT * FROM dual)
2* SELECT * FROM dually

2.7.2.7 Retyping a line

Using the line editor, you can completely replace a line in the
buffer by entering the desired line number followed by a new version
of the line. Following is our now executable statement:

SQL> L
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6* GROUP BY employee_name, project_name Suppose that for reasons of aesthetics, or perhaps to follow your
site's coding standards, you wish to make the JOIN
keyword flush-left. You can do that by retyping the entire line in
one go, as follows:

SQL> 4 JOIN project
SQL> l
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6* GROUP BY employee_name, project_name Notice that line 4 has been replaced by the text that was typed after
the numeral 4 on the first line of this example. You can replace any
line in this way. If you want to preserve the indenting, you can
insert extra spaces following the line number.