📄 ch20.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 20 -- SQL*Plus</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ch19.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch19.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch21.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch21.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 20 -<BR>
SQL*Plus</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>Today you will learn about SQL*Plus, the SQL interface for Oracle's RDBMS. By
the end of Day 20, you will understand the following elements of SQL*Plus:</P>
<UL>
<LI>How to use the SQL*Plus buffer<BR>
<BR>
<LI>How to format reports attractively
<P>
<LI>How to manipulate dates
<P>
<LI>How to make interactive queries
<P>
<LI>How to construct advanced reports<BR>
<BR>
<LI>How to use the powerful <TT>DECODE</TT> function
</UL>
<H2><FONT COLOR="#000077">Introduction</FONT></H2>
<P>We are presenting SQL*Plus today because of Oracle's dominance in the relational
database market and because of the power and flexibility SQL*Plus offers to the database
user. SQL*Plus resembles Transact-SQL (see Day 19, "Transact-SQL: An Introduction")
in many ways. Both implementations comply with the ANSI SQL standard for the most
part, which is still the skeleton of any implementation.</P>
<P>SQL*Plus commands can enhance an SQL session and improve the format of queries
from the database. SQL*Plus can also format reports, much like a dedicated report
writer. SQL*Plus supplements both standard SQL and PL/SQL and helps relational database
programmers gather data that is in a desirable format.
<H2><FONT COLOR="#000077">The SQL*Plus Buffer</FONT></H2>
<P>The SQL*Plus buffer is an area that stores commands that are specific to your
particular SQL session. These commands include the most recently executed SQL statement
and commands that you have used to customize your SQL session, such as formatting
commands and variable assignments. This buffer is like a short-term memory. Here
are some of the most common SQL buffer commands:
<UL>
<LI><TT>LIST line_number</TT>--Lists a line from the statement in the buffer and
designates it as the current line.
<P>
<LI><TT>CHANGE/old_value/new_value</TT>--Changes <TT>old_value</TT> to <TT>new_value</TT>
on the current line in the buffer.
<P>
<LI><TT>APPEND text</TT>--Appends <TT>text</TT> to the current line in the buffer.
<P>
<LI><TT>DEL</TT>-- Deletes the current line in the buffer.
<P>
<LI><TT>SAVE newfil</TT>e--Saves the SQL statement in the buffer to a file.
<P>
<LI><TT>GET filename</TT>--Gets an SQL file and places it into the buffer.
<P>
<LI>/--Executes the SQL statement in the buffer.
</UL>
<P>We begin with a simple SQL statement:</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>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
</FONT></PRE>
<P>The <TT>LIST</TT> command lists the most recently executed SQL statement in the
buffer. The output will simply be the displayed statement.</P>
<PRE><FONT COLOR="#0066FF">SQL><B> list</B>
1 select *
2 from products
3* where unit_cost > 25
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice that each line is numbered. Line numbers are important in the buffer; they
act as pointers that enable you to modify specific lines of your statement using
the SQL*PLUS buffer. The SQL*Plus buffer is not a full screen editor; after you hit
Enter, you cannot use the cursor to move up a line, as shown in the following 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>
4 <B> /</B>
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>As with SQL commands, you may issue
SQL*Plus commands in either uppercase or lowercase.
<HR>
</P>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>You can abbreviate most SQL*Plus
commands; for example, <TT>LIST</TT> can be abbreviated as <TT>l</TT>.
<HR>
</BLOCKQUOTE>
<P>You can move to a specific line from the buffer by placing a line number after
the <TT>l</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> l3</B>
3* where unit_cost > 25
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice the asterisk after the line number 3. This asterisk denotes the current
line number. Pay close attention to the placement of the asterisk in today's examples.
Whenever a line is marked by the asterisk, you can make changes to that line.</P>
<P>Because you know that your current line is 3, you are free to make changes. The
syntax for the <TT>CHANGE</TT> command is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CHANGE/old_value/new_value </FONT></PRE>
<P><FONT COLOR="#000000">or</FONT><FONT COLOR="#0066FF"></FONT>
<PRE><FONT COLOR="#0066FF">C/old_value/new_value</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL><B> c/>/<</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 3* where unit_cost < 25</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>l</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>
<H5>ANALYSIS:</H5>
<P>The greater than sign (<TT>></TT>) has been changed to less than (<TT><</TT>)
on line 3. Notice after the change was made that the newly modified line was displayed.
If you issue the <TT>LIST</TT> command or <TT>l</TT>, you can see the full statement.
Now execute the statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> /</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>ANALYSIS:</H5>
<P>The forward slash at the <TT>SQL></TT> prompt executes any statement that is
in the buffer.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>l</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>Now, you can add a line to your statement by typing a new line number at the <TT>SQL></TT>
prompt and entering text. After you make the addition, get a full statement listing.
Here's an example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> 4 order by unit_cost</B>
SQL> <B>1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 1 select *
2 from products
3 where unit_cost < 25
4* order by unit_cost
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Deleting a line is easier than adding a line. Simply type DEL 4 at the <TT>SQL></TT>
prompt to delete line 4. Now get another statement listing to verify that the line
is gone.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> DEL4
SQL> l</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>Another way to add one or more lines to your statement is to use the <TT>INPUT</TT>
command. As you can see in the preceding list, the current line number is 3. At the
prompt type input and then press Enter. Now you can begin typing text. Each time
you press Enter, another line will be created. If you press Enter twice, you will
obtain another <TT>SQL></TT> prompt. Now if you display a statement listing, as
in the following example, you can see that line 4 has been added.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>input</B>
4i <B>and product_id = 'P01'</B>
5i
SQL> <B>l</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 1 select *
2 from products
3 where unit_cost < 25
4 and product_id = 'P01'
5* order by unit_cost
</FONT></PRE>
<P>To append text to the current line, issue the <TT>APPEND</TT> command followed
by the text. Compare the output in the preceding example--the current line number
is 5--to the following example.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>append desc</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 5* order by unit_cost desc
</FONT></PRE>
<P>Now get a full listing of your statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL><B> l</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 1 select *
2 from products
3 where unit_cost < 25
4 and product_id = 'P01'
5* order by unit_cost desc
</FONT></PRE>
<P>Suppose you want to wipe the slate clean. You can clear the contents of the SQL*Plus
buffer by issuing the command <TT>CLEAR</TT> <TT>BUFFER</TT>. As you will see later,
you can also use the <TT>CLEAR</TT> command to clear specific settings from the buffer,
such as column formatting information and computes on a report.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>clear buffer</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">buffer cleared</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>l</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">No lines in SQL buffer.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Obviously, you won't be able to retrieve anything from an empty buffer. You aren't
a master yet, but you should be able to maneuver with ease by manipulating your commands
in the buffer.
<H2><FONT COLOR="#000077">The DESCRIBE Command</FONT></H2>
<P>The handy <TT>DESCRIBE</TT> command enables you to view the structure of a table
quickly without having to create a query against the data dictionary.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DESC[RIBE] table_name
</FONT></PRE>
<P>Take a look at the two tables you will be using throughout the day.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>describe orders</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name Null? Type
------------------------------- -------- ----
ORDER_NUM NOT NULL NUMBER(2)
CUSTOMER NOT NULL VARCHAR2(30)
PRODUCT_ID NOT NULL CHAR(3)
PRODUCT_QTY NOT NULL NUMBER(5)
DELIVERY_DATE DATE
</FONT></PRE>
<P>The following statement uses the abbreviation <TT>DESC</TT> instead of <TT>DESCRIBE</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>desc products</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name Null? Type
------------------------------- -------- ----
PRODUCT_ID NOT NULL VARCHAR2(3)
PRODUCT_NAME NOT NULL VARCHAR2(30)
UNIT_COST NOT NULL NUMBER(8,2)
</FONT></PRE>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -