ch20.htm

Learn SQL in an easy way...

80 characters. Unless you are using wide computer paper (11 x 14), you may want to
landscape print your report if you are using a line size greater than 80. The following
example shows the use of <TT>LINESIZE</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>set linesize 40</B>
SQL&gt; <B>/</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">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
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">    99.99
</FONT></PRE>
<P>You can also adjust the size of each page of your output by using the setting
<TT>PAGESIZE</TT>. If you are simply viewing your output on screen, the best setting
for <TT>PAGESIZE</TT> is <TT>23</TT>, which eliminates multiple page breaks per screen.
In the following example <TT>PAGESIZE</TT> is set to a low number to show you what
happens on each page break.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>set linesize 80</B>
SQL&gt; <B>set heading on</B>
SQL&gt; <B>set pagesize 7</B>
SQL&gt; <B>/</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

PRO PRODUCT_NAME                   UNIT_COST
-- ------------------------------ --------
P05 NATURE CALENDAR                    12.99
P06 SQL COMMAND REFERENCE              29.99
P07 BLACK LEATHER BRIEFCASE            99.99
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Using the setting of <TT>PAGESIZE 7</TT>, the maximum number of lines that may
appear on a single page is seven. New column headings will print automatically at
the start of each new page.</P>
<P>The <TT>TIME</TT> setting displays the current time as part of your <TT>SQL&gt;</TT>
prompt.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>set time on</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">08:52:02 SQL&gt;
</FONT></PRE>
<P>These were just a few of the <TT>SET</TT> options, but they are all manipulated
in basically the same way. As you saw from the vast list of <TT>SET</TT> commands
in the earlier output from the <TT>SHOW ALL</TT> statement, you have many options
when customizing your SQL*Plus session. Experiment with each option and see what
you like best. You will probably keep the default for many options, but you may find
yourself changing other options frequently based on different scenarios.
<H2><FONT COLOR="#000077">LOGIN.SQL File</FONT></H2>
<P>When you log out of SQL*Plus, all of your session settings are cleared. When you
log back in, your settings will have to be reinitialized if they are not the defaults
unless you are using a <TT>login.sql</TT> file. This file is automatically executed
when you sign on to SQL*Plus. This initialization file is similar to the <TT>autoexec.bat</TT>
file on your PC or your <TT>.profile</TT> in a UNIX Korn Shell environment.</P>
<P>In Personal Oracle7 you can use the <TT>EDIT</TT> command to create your <TT>Login.sql</TT>
file, as shown in Figure 20.3.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 20.3.<BR>
</B></A><I>Your <TT>Login.sql</TT> file.</I></P>
<P>When you log on to SQL*Plus, here is what you will see:</P>
<PRE><FONT COLOR="#0066FF">SQL*Plus: Release 3.3.2.0.2 - Production on Sun May 11 20:37:58 1997

Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.

Enter password: ****

Connected to:
Personal Oracle7 Release 7.3.2.2.0 - Production Release
With the distributed and replication options
PL/SQL Release 2.3.2.0.0 - Production


'HELLO!
-------
HELLO !

20:38:02 SQL&gt;
</FONT></PRE>
<H2><FONT COLOR="#000077">CLEAR Command</FONT></H2>
<P>In SQL*Plus, settings are cleared by logging off, or exiting SQL*Plus. Some of
your settings may also be cleared by using the <TT>CLEAR</TT> command, as shown in
the following examples.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>clear col</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">columns cleared</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>clear break</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">breaks cleared</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>clear compute</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">computes cleared
</FONT></PRE>
<H2><FONT COLOR="#000077">Formatting Your Output</FONT></H2>
<P>SQL*Plus also has commands that enable you to arrange your output in almost any
format. This section covers the basic formatting commands for report titles, column
headings and formats, and giving a column a &quot;new value.&quot;
<H3><FONT COLOR="#000077">TTITLE and BTITLE</FONT></H3>
<P><TT>TTITLE</TT> and <TT>BTITLE</TT> enable you to create titles on your reports.
Previous days covered queries and output, but with SQL*Plus you can convert simple
output into presentable reports. The <TT>TTITLE</TT> command places a title at the
top of each page of your output or report. <TT>BTITLE</TT> places a title at the
bottom of each page of your report. Many options are available with each of these
commands, but today's presentation covers the essentials. Here is the basic syntax
of <TT>TTITLE</TT> and <TT>BTITLE</TT>:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">TTITLE [center|left|right] 'text' [&amp;variable] [skip n]
BTITLE [center|left|right] 'text' [&amp;variable] [skip n]</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>ttitle 'A LIST OF PRODUCTS'</B>
SQL&gt;<B> btitle 'THAT IS ALL'</B>
SQL&gt; <B>set pagesize 15</B>
SQL&gt; <B>/</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Wed May 07  
page    1
                               A LIST OF PRODUCTS

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

                                  THAT IS ALL

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The title appears at the top of the page and at the bottom. Many people use the
bottom title for signature blocks to verify or make changes to data on the report.
Also, in the top title the date and page number are part of the title.
<H3><FONT COLOR="#000077">Formatting Columns (COLUMN, HEADING, FORMAT)</FONT></H3>
<P><I>Formatting columns</I> refers to the columns that are to be displayed or the
columns that are listed after the <TT>SELECT</TT> in an SQL statement. The <TT>COLUMN</TT>,
<TT>HEADING</TT>, and <TT>FORMAT</TT> commands rename column headings and control
the way the data appears on the report.</P>
<P>The <TT>COL[UMN]</TT> command is usually used with either the <TT>HEADING</TT>
command or the <TT>FORMAT</TT> command. <TT>COLUMN</TT> defines the column that you
wish to format. The column that you are defining must appear exactly as it is typed
in the <TT>SELECT</TT> statement. You may use a column alias instead of the full
column name to identify a column with this command.</P>
<P>When using the <TT>HEADING</TT> command, you must use the <TT>COLUMN</TT> command
to identify the column on which to place the heading.</P>
<P>When using the <TT>FORMAT</TT> command, you must use the <TT>COLUMN</TT> command
to identify the column you wish to format.</P>
<P>The basic syntax for using all three commands follows. Note that the <TT>HEADING</TT>
and <TT>FORMAT</TT> commands are optional. In the <TT>FORMAT</TT> syntax, you must
use an <TT>a</TT> if the data has a character format or use <TT>0</TT>s and <TT>9</TT>s
to specify number data types. Decimals may also be used with numeric values. The
number to the right of the <TT>a</TT> is the total width that you wish to allow for
the specified column.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">COL[UMN] column_name HEA[DING] &quot;new_heading&quot; FOR[MAT] [a1|99.99]
</FONT></PRE>
<P>The simple <TT>SELECT</TT> statement that follows shows the formatting of a column.
The specified column is of <TT>NUMBER</TT> data type, and we want to display the
number in a decimal format with a dollar sign.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>column unit_cost heading &quot;PRICE&quot; format $99.99</B>
SQL&gt;<B> select product_name, unit_cost</B>
  2 <B> from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRODUCT_NAME                     PRICE
------------------------------ -------
MICKEY MOUSE LAMP               $29.95
NO 2 PENCILS - 20 PACK           $1.99
COFFEE MUG                       $6.95
FAR SIDE CALENDAR               $10.50
NATURE CALENDAR                 $12.99
SQL COMMAND REFERENCE           $29.99
BLACK LEATHER BRIEFCASE         $99.99
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Because we used the format <TT>99.99</TT>, the maximum number that will be displayed
is <TT>99.99</TT>.</P>
<P>Now try abbreviating the commands. Here's something neat you can do with the <TT>HEADING</TT>
command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>col unit_cost hea &quot;UNIT|COST&quot; for $09.99</B>
SQL&gt; <B>select product_name, unit_cost</B>
  2  <B>from products;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PRODUCT_NAME                 UNIT COST
---------------------------- ---------
MICKEY MOUSE LAMP               $29.95
NO 2 PENCILS - 20 PACK          $01.99
COFFEE MUG                      $06.95
FAR SIDE CALENDAR               $10.50
NATURE CALENDAR                 $12.99
SQL COMMAND REFERENCE           $29.99
BLACK LEATHER BRIEFCASE         $99.99

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The pipe sign (<TT>|</TT>) in the <TT>HEADING</TT> command forces the following
text of the column heading to be printed on the next line. You may use multiple pipe
signs. The technique is handy when the width of your report starts to push the limits
of the maximum available line size. The format of the unit cost column is now <TT>09.99</TT>.
The maximum number displayed is still <TT>99.99</TT>, but now a <TT>0</TT> will precede
all numbers less than <TT>10</TT>. You may prefer this format because it makes the
dollar amounts appear uniform.
<H2><FONT COLOR="#000077">Report and Group Summaries</FONT></H2>
<P>What would a report be without summaries and computations? Let's just say that
you would have one frustrated programmer. Certain commands in SQL*Plus allow you
to break up your report into one or more types of groups and perform summaries or
computations on each group. <TT>BREAK</TT> is a little different from SQL's standard
group functions, such as <TT>COUNT( )</TT> and <TT>SUM( )</TT>. These functions are
used with report and group summaries to provide a more complete report.
<H3><FONT COLOR="#000077">BREAK ON</FONT></H3>
<P>The <TT>BREAK ON</TT> command breaks returned rows of data from an SQL statement
into one or more groups. If you break on a customer's name, then by default the customer's
name will be printed only the first time it is returned and left blank with each
row of data with the corresponding name. Here is the very basic syntax of the <TT>BREAK
ON</TT> command:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">BRE[AK] [ON column1 ON column2...][SKIP n|PAGE][DUP|NODUP]
</FONT></PRE>
<P>You may also break on <TT>REPORT</TT> and <TT>ROW</TT>. Breaking on <TT>REPORT</TT>
performs computations on the report as a whole, whereas breaking on <TT>ROW</TT>
performs computations on each group of rows.</P>
<P>The <TT>SKIP</TT> option allows you to skip a number of lines or a page on each
group. <TT>DUP</TT> or <TT>NODUP</TT> suggests whether you want duplicates to be
printed in each group. The default is <TT>NODUP</TT>.</P>
<P>Here is an example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>col unit_cost head 'UNIT|COST' for $09.99</B>
SQL&gt; <B>break on customer</B>
SQL&gt; <B>select o.customer, p.product_name, p.unit_cost</B>
  2 <B> from orders o,</B>
  3      <B> products p</B>
  4  <B>where o.product_id = p.product_id</B>
  5  <B>order by customer;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">CUSTOMER                       PRODUCT_NAME                 UNIT COST
------------------------------ ---------------------------- ---------
JONES and SONS                 MICKEY MOUSE LAMP               $29.95
                               NO 2 PENCILS - 20 PACK          $01.99
                               COFFEE MUG                      $06.95
PARAKEET CONSULTING GROUP      MICKEY MOUSE LAMP               $29.95
                               NO 2 PENCILS - 20 PACK          $01.99
                               SQL COMMAND REFERENCE           $29.99
                               BLACK LEATHER BRIEFCASE         $99.99
                               FAR SIDE CALENDAR               $10.50
PLEWSKY MOBILE CARWASH         MICKEY MOUSE LAMP               $29.95
                               BLACK LEATHER BRIEFCASE         $99.99
                               BLACK LEATHER BRIEFCASE         $99.99
                               NO 2 PENCILS - 20 PACK          $01.99
                               NO 2 PENCILS - 20 PACK          $01.99

13 rows selected.
</FONT></PRE>
<P>Each unique customer is printed only once. This report is much easier to read
than one in which duplicate customer names are printed. You must order your results
in the same order as the column(s) on which you are breaking for the <TT>BREAK</TT>
command to work.
<H3><FONT COLOR="#000077">COMPUTE</FONT></H3>
<P>The <TT>COMPUTE</TT> command is used with the <TT>BREAK ON</TT> command. <TT>COMPUTE</TT>
allows you to perform various computations on each group of data and/or on the entire
report.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">COMP[UTE] function  OF column_or_alias  ON column_or_row_or_report
</FONT></PRE>
<P>Some of the more popular functions are

<UL>
	<LI><TT>AVG</TT>--Computes the average value on each group.
	<P>
	<LI><TT>COUNT</TT>--Computes a count of values on each group.
	<P>
	<LI><TT>SUM</TT>--Computes a sum of values on each group.