 |
|
Oracle Tips by Burleson |
SQL*Plus Variables
Sometimes you want to run a query over and over
again with different data. SQL*Plus provide two
methods to pass variables to your query. These are called SQL*Plus
variables because the database never sees them. SQL*Plus makes the
substitution before sending the query to the database. You define a
SQL*Plus variable using the ampersand (&) followed by a name. When
SQL*Plus encounters the ampersand, it will ask for the value.
SQL> SELECT
2 author_last_name "Last Name"
3 FROM
4 author
5 WHERE
6 author_state = &state;
Enter value
for state: 'MO'
old 6: author_state = &state
new 6: author_state = 'MO'
Last Name
----------------------------------------
jones
hester
weaton
Notice that SQL*Plus shows you
the old and new line in the buffer where the variable was
substituted. Also, I was required to place single quotes around the
state. I could have written line six as author_state = ‘&state’;.
SQL*Plus defaults to VERIFY
=ON which is why it shows you the
old and new line. To turn that feature off set VERIFY=OFF. Each time
SQL*Plus encounters the &state variable, it will ask for input. If
you use the variable more than once, use the && for each occurrence.
That tells SQL*Plus to reuse the already entered variable, or if one
has not been entered, ask for it.
You can also define a variable in the script
using the DEFINE
command.
SQL> DEFINE
state = MO
SQL> SELECT
2 author_last_name "Last Name"
3 FROM
4 author
5 WHERE
6 author_state = '&state';
old 6: author_state = '&state'
new 6: author_state = 'MO'
Last Name
----------------------------------------
jones
hester
weaton
Here, I defined the state at the beginning of my
script and SQL*Plus simply substituted it as the query executed. The DEFINE command
set the variable, and it stays set until you set DEFINE OFF or you
exit SQL*Plus.
Restricting SQL Output
So far, our SQL queries have returned all the
rows in the table. As your tables grow large, this becomes a
problem. In this section, we will discuss reducing the rows returned
to just the ones we want.
The Distinct Clause
Many times there are multiple rows with the same
value, and we want to return only one copy of the row. If the boss
wants a list of the states where the authors live, we can query that
from the AUTHOR table.
SQL> SELECT
2 author_state
3 FROM
4 author;
AU
--
MO
MO
MO
CA
IL
TX
WI
KY
LA
MA
10 rows
selected.
We have ten authors, and we got ten rows back.
But, notice that some authors live in the same state. What we want is
a list of distinct stats (one row for each state). SQL provides the
DISTINCT clause for this result.
SQL> SELECT
2 DISTINCT (author_state)
3 FROM
4 author;
AU
--
CA
IL
KY
LA
MA
MO
TX
WI
8 rows
selected.
The DISTINCT clause removed the
duplicate rows.
The WHERE Clause
The WHERE
clause also limits the number of
rows in the results set. The WHERE clause is a logical comparison and
returns the row if the WHERE clause is true and excludes the row is
the clause is false. To list the author last name of those authors
that live in MO, use the clause: WHERE author_state = ‘MO’; :
SQL> SELECT
2 author_last_name
3 FROM
4 author
5 WHERE author_state = 'MO';
AUTHOR_LAST_NAME
----------------------------------------
jones
hester
weaton
Notice that the column used in the WHERE clause
is not one of my selected columns. It can be, but there is no
requirement for it to be selected. Also, note that I capitalized the
state (MO). The author_state column is a varchar2 or a character
string. Character strings are enclosed in single quotes. Although
capitalization does not matter in the SQL command syntax, it does
matter with data. The state is stored in the database as MO. If I
query ‘mo’ or ‘Mo,’ I would get no rows returned. In the comparison,
the database is making exact comparisons. In chapter 2, we discuss
functions that allow you to query data if you do not know how it is
stored.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
