📄 ch20.htm
字号:
<H5>ANALYSIS:<TT></TT></H5>
<P><TT>DESC</TT> displays each column name, which columns must contain data (<TT>NULL</TT>/<TT>NOT
NULL</TT>), and the data type for each column. If you are writing many queries, you
will find that few days go by without using this command. Over a long time, this
command can save you many hours of programming time. Without <TT>DESCRIBE</TT> you
would have to search through project documentation or even database manuals containing
lists of data dictionary tables to get this information.
<H2><FONT COLOR="#000077">The SHOW Command</FONT></H2>
<P>The <TT>SHOW</TT> command displays the session's current settings, from formatting
commands to who you are. <TT>SHOW ALL</TT> displays all settings. This discussion
covers some of the most common settings.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>show all</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">appinfo is ON and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT statement
closecursor OFF
colsep " "
cmdsep OFF
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
copytypecheck is ON
crt ""
define "&" (hex 26)
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
feedback ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
linesize 100
lno 6
long 80
longchunksize 80
maxdata 60000
newpage 1
null ""
numformat ""
numwidth 9
pagesize 24
pause is OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 703020200
repheader OFF and is NULL
repfooter OFF and is NULL
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 1007
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "SQL"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the 1st few characters of the next SELECT statement
underline "-" (hex 2d)
user is "RYAN"
verify ON
wrap : lines will be wrapped
</FONT></PRE>
<P>The <TT>SHOW </TT>command displays a specific setting entered by the user. Suppose
you have access to multiple database user IDs and you want to see how you are logged
on. You can issue the following command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>show user</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">user is "RYAN"
</FONT></PRE>
<P>To see the current line size of output, you would type:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>show linesize</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">linesize 100
</FONT></PRE>
<H2><FONT COLOR="#000077">File Commands</FONT></H2>
<P>Various commands enable you to manipulate files in SQL*Plus. These commands include
creating a file, editing the file using a full-screen editor as opposed to using
the SQL*Plus buffer, and redirecting output to a file. You also need to know how
to execute an SQL file after it is created.
<H3><FONT COLOR="#000077">The SAVE, GET, and EDIT Commands</FONT></H3>
<P>The <TT>SAVE</TT> command saves the contents of the SQL statement in the buffer
to a file whose name you specify. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>select *</B>
2 <B>from products</B>
3 <B>where unit_cost < 25</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">SQL> <B>save query1.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Created file query1.sql
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>After a file has been saved, you can use the <TT>GET</TT> command to list the
file. <TT>GET</TT> is very similar to the <TT>LIST</TT> command. Just remember that
<TT>GET</TT> deals with statements that have been saved to files, whereas <TT>LIST</TT>
deals with the statement that is stored in the buffer.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> get query1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 1 select *
2 from products
3* where unit_cost < 25
</FONT></PRE>
<P>You can use the <TT>EDIT</TT> command either to create a new file or to edit an
existing file. When issuing this command, you are taken into a full-screen editor,
more than likely Notepad in Windows. You will find that it is usually easier to modify
a file with <TT>EDIT</TT> than through the buffer, particularly if you are dealing
with a large or complex statement. Figure 20.1 shows an example of the <TT>EDIT</TT>
command.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>edit query1.sql</B>
</FONT></PRE>
<P><A NAME="01"></A><A HREF="01-5.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/01-5.htm"><B>Figure 20.1.<BR>
</B></A><I>Editing a file in SQL*Plus.</I></P>
<H3><FONT COLOR="#000077">Starting a File</FONT></H3>
<P>Now that you know how to create and edit an SQL file, the command to execute it
is simple. It can take one of the following forms:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">START filename
</FONT></PRE>
<P>or</P>
<PRE><FONT COLOR="#0066FF">STA filename
</FONT></PRE>
<P>or</P>
<PRE><FONT COLOR="#0066FF">@filename
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Commands are not case sensitive.
<HR>
</P>
</BLOCKQUOTE>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>start query1.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You do not have to specify the file
extension <TT>.sql</TT> to start a file from SQL*Plus. The database assumes that
the file you are executing has this extension. Similarly, when you are creating a
file from the <TT>SQL></TT> prompt or use <TT>SAVE</TT>, <TT>GET</TT>, or <TT>EDIT</TT>,
you do not have to include the extension if it is <TT>.sql</TT>.
<HR>
</P>
</BLOCKQUOTE>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> @query1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>run query1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 1 select *
2 from products
3* where unit_cost < 25
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
</FONT></PRE>
<P>Notice that when you use <TT>RUN</TT> to execute a query, the statement is echoed,
or displayed on the screen.
<H3><FONT COLOR="#000077">Spooling Query Output</FONT></H3>
<P>Viewing the output of your query on the screen is very convenient, but what if
you want to save the results for future reference or you want to print the file?
The <TT>SPOOL</TT> command allows you to send your output to a specified file. If
the file does not exist, it will be created. If the file exists, it will be overwritten,
as shown in Figure 20.2.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>spool prod.lst</B>
SQL> <B>select *</B>
2 <B>from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
7 rows selected.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>spool off</B>
SQL> <B>edit prod.lst</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The output in Figure 20.2 is an SQL*Plus file. You must use the <TT>SPOOL OFF</TT>
command to stop spooling to a file. When you exit SQL*Plus, <TT>SPOOL OFF</TT> is
automatic. But if you do not exit and you continue to work in SQL*Plus, everything
you do will be spooled to your file until you issue the command <TT>SPOOL OFF</TT>.</P>
<P><A NAME="02"></A><A HREF="02-4.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/02-4.htm"><B>Figure 20.2.<BR>
</B></A><I>Spooling your output to a file.</I></P>
<H2><FONT COLOR="#000077">SET Commands</FONT></H2>
<P><TT>SET</TT> commands in Oracle change SQL*Plus session settings. By using these
commands, you can customize your SQL working environment and invoke options to make
your output results more presentable. You can control many of the <TT>SET</TT> commands
by turning an option on or off.</P>
<P>To see how the <TT>SET</TT> commands work, perform a simple <TT>select</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>select *</B>
2 <B>from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The last line of output</P>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<P>is called feedback, which is an SQL setting that can be modified. The settings
have defaults, and in this case the default for <TT>FEEDBACK</TT> is <TT>on</TT>.
If you wanted, you could type</P>
<PRE><FONT COLOR="#0066FF">SET FEEDBACK ON
</FONT></PRE>
<P>before issuing your select statement. Now suppose that you do not want to see
the feedback, as happens to be the case with some reports, particularly summarized
reports with computations.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>set feedback off</B>
SQL> <B>select *</B>
2 <B>from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -