ch20.htm

Learn SQL in an easy way...

</UL>

<P>Suppose you want to create a report that lists the information from the <TT>PRODUCTS</TT>
table and computes the average product cost on the report.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>break on report</B>
SQL&gt; <B>compute avg of unit_cost on report</B>
SQL&gt; <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.50
P05 NATURE CALENDAR                    12.99
P06 SQL COMMAND REFERENCE              29.99
P07 BLACK LEATHER BRIEFCASE            99.99
                                   --------- 
avg                                    27.48
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You can obtain the information you want by breaking on <TT>REPORT</TT> and then
computing the <TT>avg</TT> of the <TT>unit_cost</TT> on <TT>REPORT</TT>.</P>
<P>Remember the <TT>CLEAR</TT> command? Now clear the last compute from the buffer
and start again--but this time you want to compute the amount of money spent by each
customer. Because you do not want to see the average any longer, you should also
clear the computes.</P>
<H5>INPUT:</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>
<P>Now clear the last <TT>BREAK</TT>. (You don't really have to clear the <TT>BREAK</TT>
in this case because you still intend to break on report.)</P>
<H5>INPUT:</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>
<P>The next step is to reenter the breaks and computes the way you want them now.
You will also have to reformat the column <TT>unit_cost</TT> to accommodate a larger
number because you are computing a sum of the <TT>unit_cost</TT> on the report. You
need to allow room for the grand total that uses the same format as the column on
which it is being figured. So you need to add another place to the left of the decimal.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>col unit_cost hea 'UNIT|COST' for $099.99</B>
SQL&gt; <B>break on report on customer skip 1</B>
SQL&gt;<B> compute sum of unit_cost on customer</B>
SQL&gt; <B>compute sum of unit_cost on report</B>
</FONT></PRE>
<P>Now list the last SQL statement from the buffer.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>l</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">  1  select o.customer, p.product_name, p.unit_cost
  2  from orders o,
  3       products p
  4  where o.product_id = p.product_id
  5* order by customer
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Now that you have verified that this statement is the one you want, you can execute
it:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> /</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">                                                                  UNIT
CUSTOMER                       PRODUCT_NAME                       COST
------------------------------ ------------------------------ --------
JONES and SONS                 MICKEY MOUSE LAMP               $029.95
                               NO 2 PENCILS - 20 PACK          $001.99
                               COFFEE MUG                      $006.95
******************************                                --------
sum                                                            $038.89

PARAKEET CONSULTING GROUP      MICKEY MOUSE LAMP               $029.95
                               NO 2 PENCILS - 20 PACK          $001.99
                               SQL COMMAND REFERENCE           $029.99
                               BLACK LEATHER BRIEFCASE         $099.99
                               FAR SIDE CALENDAR               $010.50
******************************                                --------
sum                                                            $172.42

PLEWSKY MOBILE CARWASH         MICKEY MOUSE LAMP               $029.95
                               BLACK LEATHER BRIEFCASE         $099.99
                               BLACK LEATHER BRIEFCASE         $099.99
                               NO 2 PENCILS - 20 PACK          $001.99
                               NO 2 PENCILS - 20 PACK          $001.99
******************************                                --------

                                                                  UNIT
CUSTOMER                       PRODUCT_NAME                       COST
-----------------------------  ------------------------------ --------
sum                                                            $233.91
                                                              --------
sum                                                            $445.22

13 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example computed the total amount that each customer spent and also calculated
a grand total for all customers.</P>
<P>By now you should understand the basics of formatting columns, grouping data on
the report, and performing computations on each group.
<H2><FONT COLOR="#000077">Using Variables in SQL*Plus</FONT></H2>
<P>Without actually getting into a procedural language, you can still define variables
in your SQL statement. You can use special options in SQL*Plus (covered in this section)
to accept input from the user to pass parameters into your SQL program.
<H3><FONT COLOR="#000077">Substitution Variables (&amp;)</FONT></H3>
<P>An ampersand (<TT>&amp;</TT>) is the character that calls a value for a variable
within an SQL script. If the variable has not previously been defined, the user will
be prompted to enter a value.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2 <B> from &amp;TBL</B>
  3 <B> /</B>

Enter value for tbl: <B>products</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">The user entered the value &quot;products.&quot;</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">old   2: from &amp;TBL
new   2: from 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

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The value <TT>products</TT> was substituted in the place of <TT>&amp;TBL</TT>
in this &quot;interactive query.&quot;
<H3><FONT COLOR="#000077">DEFINE</FONT></H3>
<P>You can use <TT>DEFINE</TT> to assign values to variables within an SQL script
file. If you define your variables within the script, users are not prompted to enter
a value for the variable at runtime, as they are if you use the <TT>&amp;</TT>. The
next example issues the same <TT>SELECT</TT> statement as the preceding example,
but this time the value of <TT>TBL</TT> is defined within the script.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>define TBL=products</B>
SQL&gt; <B>select *</B>
  2 <B> from &amp;TBL;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">old   2: from &amp;TBL
new   2: from 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

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Both queries achieved the same result. The next section describes another way
to prompt users for script parameters.
<H3><FONT COLOR="#000077">ACCEPT</FONT></H3>
<P><TT>ACCEPT</TT> enables the user to enter a value to fill a variable at script
runtime. <TT>ACCEPT</TT> does the same thing as the <TT>&amp;</TT> with no <TT>DEFINE</TT>
but is a little more controlled. <TT>ACCEPT</TT> also allows you to issue user-friendly
prompts.</P>
<P>The next example starts by clearing the buffer:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>clear buffer</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">buffer cleared
</FONT></PRE>
<P>Then it uses an <TT>INPUT</TT> command to enter the new SQL statement into the
buffer. If you started to type your statement without issuing the <TT>INPUT</TT>
command first, you would be prompted to enter the value for <TT>newtitle</TT> first.
Alternatively, you could go straight into a new file and write your statement.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>input</B>
  1  <B>accept newtitle prompt 'Enter Title for Report: '</B>
  2  <B>ttitle center newtitle</B>
  3  <B>select *</B>
  4  <B>from products</B>
  5
SQL&gt; <B>save prod</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">File &quot;prod.sql&quot; already exists.
Use another name or &quot;SAVE filename REPLACE&quot;.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Whoops...the file <TT>prod.sql</TT> already exists. Let's say that you need the
old <TT>prod.sql</TT> and do not care to overwrite it. You will have to use the replace
option to save the statement in the buffer to <TT>prod.sql</TT>. Notice the use of
<TT>PROMPT</TT> in the preceding statement. <TT>PROMPT</TT> displays text to the
screen that tells the user exactly what to enter.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>save prod replace</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Wrote file prod
</FONT></PRE>
<P>Now you can use the <TT>START</TT> command to execute the file.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>start prod</B>

Enter Title for Report: A LIST OF PRODUCTS</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">                          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

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The text that you entered becomes the current title of the report.</P>
<P>The next example shows how you can use substitution variables anywhere in a statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>input</B>
  1  <B>accept prod_id prompt 'Enter PRODUCT ID to Search for: '</B>
  2  <B>select *</B>
  3 <B> from products</B>
  4  <B>where product_id = '&amp;prod_id'</B>
  5
SQL&gt; <B>save prod1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Created file prod1</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>start prod1</B>

Enter PRODUCT ID to Search for: <B>P01</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">old   3: where product_id = '&amp;prod_id'
new   3: where product_id = 'P01'

                     A LIST OF PRODUCTS

PRO PRODUCT_NAME                   UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP                  29.95
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You can use variables to meet many needs--for example, to name the file to which
to spool your output or to specify an expression in the <TT>ORDER BY</TT> clause.
One of the ways to use substitution variables is to enter reporting dates in the
<TT>WHERE</TT> clause for transactional quality assurance reports. If your query
is designed to retrieve information on one particular individual at a time, you may
want to add a substitution variable to be compared with the <TT>SSN</TT> column of
a table.
<H3><FONT COLOR="#000077">NEW_VALUE</FONT></H3>
<P>The <TT>NEW_VALUE</TT> command passes the value of a selected column into an undefined
variable of your choice. The syntax is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">COL[UMN] column_name NEW_VALUE new_name
</FONT></PRE>
<P>You call the values of variables by using the <TT>&amp;</TT> character; for example:</P>
<PRE><FONT COLOR="#0066FF">&amp;new_name
</FONT></PRE>
<P>The <TT>COLUMN</TT> command must be used with <TT>NEW_VALUE</TT>.</P>
<P>Notice how the <TT>&amp;</TT> and <TT>COLUMN</TT> command are used together in
the next SQL*Plus file. The <TT>GET</TT> command gets the file.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>get prod1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">line 5 truncated.
  1  ttitle left 'Report for Product:   &amp;prod_title' skip 2
  2  col product_name new_value prod_title
  3  select product_name, unit_cost
  4  from products
  5* where product_name = 'COFFEE MUG'</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>@prod1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Report for Product:   COFFEE MUG

PRODUCT_NAME                    UNIT_COST
------------------------------ ----------
COFFEE MUG                           6.95
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The value for the column <TT>PRODUCT_NAME</TT> was passed into the variable <TT>prod_title</TT>
by means of <TT>new_value</TT>. The value of the variable <TT>prod_title</TT> was
then called in the <TT>TTITLE</TT>.</P>
<P>For more information on variables in SQL, see Day 18, &quot;PL/SQL: An Introduction,&quot;
and Day 19.
<H2><FONT COLOR="#000077">The DUAL Table</FONT></H2>
<P>The <TT>DUAL</TT> table is a dummy table that exists in every Oracle database.