Database Reference
In-Depth Information
Let's say you want to revise the query we have been using so that it dis-
plays only the artists and songs for one CD at a time. You must revise the
query's WHERE clause. First, change the query so that it retrieves only the
songs for the CD with MUSICCD_ID = 1. An editing session can be initi-
ated from within SQL*Plus using the EDIT command.
EDIT
In the background, SQL*Plus writes a file named AFIEDT.BUF and
then opens the file with the editor. The exact editor used depends on plat-
form and configuration. For Windows, it is usually Notepad and for UNIX
quite often VI. Add a WHERE clause to the query.
SELECT M.TITLE, A.NAME, S.TITLE
FROM MUSICCD M JOIN CDTRACK T ON (M.MUSICCD_ID=T.MUSICCD_ID)
JOIN SONG S ON (T.SONG_ID=S.SONG_ID)
JOIN ARTIST A ON (S.ARTIST_ID=A.ARTIST_ID)
WHERE M.MUSICCD_ID = 1
ORDER BY 1,2,3
/
The file can then be saved and the editor exited. Execution immediately
returns to SQL*Plus with the modified query loaded into the workspace, as
shown in Figure 8.10.
Let's say that you want to be able to tell SQL*Plus which CD to report
each time you run this query. Add a variable in the WHERE clause to
accomplish this task.
You can place the query in your editor again by typing ED (the shortcut
for the command EDIT). Then, change the WHERE clause so that instead
Figure 8.10
An Edited Script Is
Passed Back to
SQL*Plus.
