Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  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

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.