?? 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="../buttonart/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/ch19.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch21/ch21.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/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. Bythe 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 relationaldatabase market and because of the power and flexibility SQL*Plus offers to the databaseuser. 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 mostpart, which is still the skeleton of any implementation.</P><P>SQL*Plus commands can enhance an SQL session and improve the format of queriesfrom the database. SQL*Plus can also format reports, much like a dedicated reportwriter. SQL*Plus supplements both standard SQL and PL/SQL and helps relational databaseprogrammers 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 yourparticular SQL session. These commands include the most recently executed SQL statementand commands that you have used to customize your SQL session, such as formattingcommands and variable assignments. This buffer is like a short-term memory. Hereare 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.95P06 SQL COMMAND REFERENCE 29.99P07 BLACK LEATHER BRIEFCASE 99.99</FONT></PRE><P>The <TT>LIST</TT> command lists the most recently executed SQL statement in thebuffer. 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; theyact as pointers that enable you to modify specific lines of your statement usingthe SQL*PLUS buffer. The SQL*Plus buffer is not a full screen editor; after you hitEnter, 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 afterthe <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 currentline 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. Thesyntax 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.99P03 COFFEE MUG 6.95P04 FAR SIDE CALENDAR 10.5P05 NATURE CALENDAR 12.99</FONT></PRE><H5>ANALYSIS:</H5><P>The forward slash at the <TT>SQL></TT> prompt executes any statement that isin 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 lineis gone.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> DEL4SQL> 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 theprompt type input and then press Enter. Now you can begin typing text. Each timeyou press Enter, another line will be created. If you press Enter twice, you willobtain another <TT>SQL></TT> prompt. Now if you display a statement listing, asin 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> 5iSQL> <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 followedby the text. Compare the output in the preceding example--the current line numberis 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*Plusbuffer 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'ta master yet, but you should be able to maneuver with ease by manipulating your commandsin 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 tablequickly 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><H5>ANALYSIS:<TT></TT></H5><P><TT>DESC</TT> displays each column name, which columns must contain data (<TT>NULL</TT>/<TT>NOTNULL</TT>), and the data type for each column. If you are writing many queries, youwill find that few days go by without using this command. Over a long time, thiscommand can save you many hours of programming time. Without <TT>DESCRIBE</TT> youwould have to search through project documentation or even database manuals containinglists 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 formattingcommands to who you are. <TT>SHOW ALL</TT> displays all settings. This discussioncovers some of the most common settings.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL> <B>show all</B></FONT></PRE>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -