Re: Variables in SQL*Plus (Beginner's question)
Date: 1997/07/08
Message-ID: <5puciu$lrt$1_at_inet16.us.oracle.com>#1/1
In article <33C2AC2B.21D6_at_shore.net>, David Bradford <mitimktg_at_shore.net> writes:
|> Hi,
|>
|> We're evaluating Oracle vs SQL server and Sybase, and the other
|> databases provide this facility for us:
|>
|> declare ncurdate char(4)
|> ncurdate = TO_CHAR (TO_NUMBER (TO_CHAR (SYSDATE, 'YYMM'))-300)
|> (this will yield '9704')
|>
|> followed by
|>
|> SELECT * FROM TEST WHERE MORTGAGE_DATE >= ncurdate
|>
|> This is a simple way to select mortgages in the last three years, given
|> that the mortgages are stored in YYMM format.
|>
|> We haven't been able to come up with a good alternative in Oracle. The
|> DEFINE command only lets us assign literals rather than expressions, and
|> trying to do anything in PL/SQL requires a SELECT INTO command, and the
|> use of cursors to get anything done.
|>
|> Is there any way to declare variables in SQL*Plus, and then reference
|> them in a SELECT statement?
Sure is. Check out the SQL*Plus documentation for more info, but here's a brief example:
SQL> variable ncurdate varchar2(4)
SQL> begin
2 :ncurdate := TO_CHAR (TO_NUMBER (TO_CHAR (SYSDATE, 'YYMM'))-300);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from test where mortgage_date > :ncurdate;
Basicly you use the VARIABLE SQL*Plus command to declare the variable, and then refer to it with a leading colon in subsequent SQL and PL/SQL blocks.
|>
|> Thanks in advance.
|>
|> Dave
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Tue Jul 08 1997 - 00:00:00 CEST