ch20.htm

21精通SQL

                     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.
This table is composed of one column called <TT>DUMMY</TT> whose only row of data
is the value <TT>X</TT>. The <TT>DUAL</TT> table is available to all database users
and can be used for general purposes, such as performing arithmetic (where it can
serve as a calculator) or manipulating the format of the <TT>SYSDATE</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> desc dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> Name                            Null?    Type
 ------------------------------- -------- ----
 DUMMY                                    VARCHAR2(1)</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select *</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">D
-
X
</FONT></PRE>
<P>Take a look at a couple of examples using the <TT>DUAL</TT> table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SYSDATE
--------
08-MAY-97</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select 2 * 2</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">      2*2
 --------
        4
</FONT></PRE>
<P>Pretty simple. The first statement selected <TT>SYSDATE</TT> from the <TT>DUAL</TT>
table and got today's date. The second example shows how to multiply in the <TT>DUAL</TT>
table. Our answer for <TT>2 * 2</TT> is <TT>4</TT>.
<H2><FONT COLOR="#000077">The DECODE Function</FONT></H2>
<P>The <TT>DECODE</TT> function is one of the most powerful commands in SQL*Plus--and
perhaps the most powerful. The standard language of SQL lacks procedural functions
that are contained in languages such as COBOL and C.</P>
<P>The <TT>DECODE</TT> statement is similar to an <TT>IF...THEN</TT> statement in
a procedural programming language. Where flexibility is required for complex reporting
needs, <TT>DECODE</TT> is often able to fill the gap between SQL and the functions
of a procedural language.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DECODE(column1, value1, output1, value2, output2, output3)
</FONT></PRE>
<P>The syntax example performs the <TT>DECODE</TT> function on <TT>column1</TT>.
If <TT>column1</TT> has a value of <TT>value1</TT>, then display <TT>output1</TT>
instead of the column's current value. If <TT>column1</TT> has a value of <TT>value2</TT>,
then display <TT>output2</TT> instead of the column's current value. If <TT>column1</TT>
has a value of anything other than <TT>value1</TT> or <TT>value2</TT>, then display
<TT>output3</TT> instead of the column's current value.</P>
<P>How about some examples? First, perform a simple select on a new table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select * from states;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ST
--
IN
FL
KY
IL
OH
CA
NY

7 rows selected.
</FONT></PRE>
<P>Now use the <TT>DECODE</TT> command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select decode(state,'IN','INDIANA','OTHER') state</B>
  2  <B>from states;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">STATE
------
INDIANA
OTHER
OTHER
OTHER
OTHER
OTHER
OTHER

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Only one row met the condition where the value of state was <TT>IN</TT>, so only
that one row was displayed as <TT>INDIANA</TT>. The other states took the default
and therefore were displayed as <TT>OTHER</TT>.</P>
<P>The next example provides output strings for each value in the table. Just in
case your table has states that are not in your <TT>DECODE</TT> list, you should
still enter a default value of <TT>'OTHER'</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select decode(state,'IN','INDIANA',</B>
  2                    <B>  'FL','FLORIDA',</B>
  3                     <B> 'KY','KENTUCKY',</B>
  4                    <B>  'IL','ILLINOIS',</B>
  5                  <B>    'OH','OHIO',</B>
  6                   <B>   'CA','CALIFORNIA',</B>
  7                  <B>    'NY','NEW YORK','OTHER')</B>
  8 <B> from states;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">DECODE(STATE)
----------
INDIANA
FLORIDA
KENTUCKY
ILLINOIS
OHIO
CALIFORNIA
NEW YORK

7 rows selected.
</FONT></PRE>
<P>That was too easy. The next example introduces the <TT>PAY</TT> table. This table
shows more of the power that is contained within <TT>DECODE</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; col hour_rate hea &quot;HOURLY|RATE&quot; for 99.00
SQL&gt; col date_last_raise hea &quot;LAST|RAISE&quot;
SQL&gt; select name, hour_rate, date_last_raise
  2  from pay;</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">                     HOURLY LAST
NAME                   RATE RAISE
-------------------- ------ --------
JOHN                  12.60 01-JAN-96
JEFF                   8.50 17-MAR-97
RON                    9.35 01-OCT-96
RYAN                   7.00 15-MAY-96
BRYAN                 11.00 01-JUN-96
MARY                  17.50 01-JAN-96
ELAINE                14.20 01-FEB-97

7 rows selected.
</FONT></PRE>
<P>Are you ready? It is time to give every individual in the <TT>PAY</TT> table a
pay raise. If the year of an individual's last raise is 1996, calculate a 10 percent
raise. If the year of the individual's last raise is 1997, calculate a 20 percent
raise. In addition, display the percent raise for each individual in either situation.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>col new_pay hea 'NEW PAY' for 99.00</B>
SQL&gt; <B>col hour_rate hea 'HOURLY|RATE' for 99.00</B>
SQL&gt; <B>col date_last_raise hea 'LAST|RAISE'</B>
SQL&gt; <B>select name, hour_rate, date_last_raise,</B>
  2       <B>  decode(substr(date_last_raise,8,2),'96',hour_rate * 1.2,</B>
  3                                           <B> '97',hour_rate * 1.1) new_pay,</B>
  4        <B> decode(substr(date_last_raise,8,2),'96','20%',</B>
  5                                           <B> '97','10%',null) increase</B>
  6  <B>from pay;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">                     HOURLY LAST
NAME                   RATE RAISE     NEW PAY INC
-------------------- ------ --------- ------- ---
JOHN                  12.60 01-JAN-96   15.12 20%
JEFF                   8.50 17-MAR-97    9.35 10%
RON                    9.35 01-OCT-96   11.22 20%
RYAN                   7.00 15-MAY-96    8.40 20%
BRYAN                 11.00 01-JUN-96   13.20 20%
MARY                  17.50 01-JAN-96   21.00 20%
ELAINE                14.20 01-FEB-97   15.62 10%

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>According to the output, everyone will be receiving a 20 percent pay increase
except Jeff and Elaine, who have already received one raise this year.
<H2><FONT COLOR="#000077">DATE Conversions</FONT></H2>
<P>If you want to add a touch of class to the way dates are displayed, then you can
use the <TT>TO_CHAR</TT> function to change the &quot;date picture.&quot; This example
starts by obtaining today's date:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SYSDATE
--------
08-MAY-97
</FONT></PRE>
<P>When converting a date to a character string, you use the <TT>TO_CHAR</TT> function
with the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">TO_CHAR(sysdate,'date picture')
</FONT></PRE>
<P><TT>date picture</TT> is how you want the date to look. Some of the most common
parts of the date picture are as follows: <TT>Month</TT> The current month spelled
out.</P>
<P>
<TABLE BORDER="1">
	<TR>
		<TD><TT>Mon</TT></TD>
		<TD>The current month abbreviated.</TD>
	</TR>
	<TR>
		<TD><TT>Day</TT></TD>
		<TD>The current day of the week.</TD>
	</TR>
	<TR>
		<TD><TT>mm</TT></TD>
		<TD>The number of the current month.</TD>
	</TR>
	<TR>
		<TD><TT>yy</TT></TD>
		<TD>The last two numbers of the current year.</TD>
	</TR>
	<TR>
		<TD><TT>dd</TT></TD>
		<TD>The current day of the month.</TD>
	</TR>
	<TR>
		<TD><TT>yyyy</TT></TD>
		<TD>The current year.</TD>
	</TR>
	<TR>
		<TD><TT>ddd</TT></TD>
		<TD>The current day of the year since January 1.</TD>
	</TR>
	<TR>
		<TD><TT>hh</TT></TD>
		<TD>The current hour of the day.</TD>
	</TR>
	<TR>
		<TD><TT>mi</TT></TD>
		<TD>The current minute of the hour.</TD>
	</TR>
	<TR>
		<TD><TT>ss</TT></TD>
		<TD>The current seconds of the minute.</TD>
	</TR>
	<TR>
		<TD><TT>a.m.</TT></TD>
		<TD>Displays a.m. or p.m.</TD>
	</TR>
</TABLE>