More on variables with Oracle's SQL*Plus:
As discussed in the previous section, you can request input of data by putting an & or && in
front of the variable name. The single & is for this particular select while the && alsows
you to reuse the variable that has been saved without prompting the user each time. In the
first example, I used the single & and entered itemname as the field I wanted to order by.
In the second example, I used the && and entered price as the field I wanted to order by.
This field was now stored in the variable. I went on and did other things and came back and
used the same variable that I held with the && to order by and it simply did the query without
prompting.
SQL CODE:
SQL> SELECT *
2 FROM inven
3 ORDER BY &ordercol;
Enter value for ordercol: itemname
old 3: ORDER BY &ordercol
new 3: ORDER BY itemname
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
3333 Basketball 24 25 50 14 17.99 SP BK Y200
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
8 rows selected.
SQL> SELECT *
2 FROM inven
3 ORDER BY &&orderhold;
Enter value for orderhold: price
old 3: ORDER BY &&orderhold
new 3: ORDER BY price
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
3333 Basketball 24 25 50 14 17.99 SP BK Y200
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
8 rows selected.
SQL> SELECT itemno, itemname, cost, price
2 FROM inven
3 ORDER BY &&orderhold;
old 3: ORDER BY &&orderhold
new 3: ORDER BY price
ITEM ITEMNAME COST PRICE
---- --------------- --------- ---------
1111 Good Night Moon 8 12.99
1234 Adven Reddy Fox 9 14.75
1212 Heidi 10 14.99
3333 Basketball 14 17.99
2121 Teddy Bear 15 19.95
3456 Net/Hoop 25 27.95
2222 Building Blocks 23 27.98
2345 Doll House 45 55.98
8 rows selected.
You can also use the DEFINE and ACCEPT commands in SQL*Plus to predefine variables. The DEFINE
variable with a value assigned to it (Syntax: DEFINE variable = value) puts user input into a
CHAR datatype field while the DEFINE variable with no value assigned to it simply displays the
value and its datatype. The ACCEPT takes user input and stores it in the variable (Syntax:
ACCEPT variable [datatype][FORMAT format][PROMPT text][Hide]). Notice that everything is
optional accept the variable. The datatype can be NUMBER, CHAR or DATE. The HIDE suppresses
the user entry as in the entry of a password - HIDE is only available in later versions, not
in the test version you are using. Here are some examples.
In this first example, I used &&orderhold to define the variable. The second SQL statement is
this example simply does a DEFINE on orderhold. It comes back with the fact that orderhold is
defined as cost and it is a CHAR field.
SQL CODE:
SQL> SELECT *
2 FROM inven
3 ORDER BY &&orderhold;
Enter value for orderhold: cost
old 3: ORDER BY &&orderhold
new 3: ORDER BY cost
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
3333 Basketball 24 25 50 14 17.99 SP BK Y200
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
8 rows selected.
SQL> DEFINE orderhold;
DEFINE ORDERHOLD = "cost" (CHAR)
In this example, I am putting a value into the variable deptname. The value I am putting in
is TY. I then ask that it show me the contents of the deptname just to double check. I then
use deptname in a select.
SQL CODE:
SQL> DEFINE deptname = TY;
SQL> DEFINE deptname;
DEFINE DEPTNAME = "TY" (CHAR)
SQL> SELECT *
2 FROM inven
3 WHERE dept = '&deptname';
old 3: WHERE dept = '&deptname'
new 3: WHERE dept = 'TY'
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
In the example above I was storing the actual value that I wanted to compare against in
deptname so when I compared I put the &deptname in single quotes. In the example below, I
am storing the name of a field in sortord. When I used it in the ORDER BY clause, I use it
as &sortord without the single quotes surrounding it.
SQL CODE:
SQL> DEFINE sortord = dept;
SQL> DEFINE sortord;
DEFINE SORTORD = "dept" (CHAR)
SQL> SELECT *
2 FROM inven
3 ORDER BY &sortord;
old 3: ORDER BY &sortord
new 3: ORDER BY dept
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
8 rows selected.
Even though the define sets up a char field, Oracle can put a number in that field and compare
it correctly with the number field cost.
SQL CODE:
SQL> DEFINE costhold = 10;
SQL> DEFINE costhold;
DEFINE COSTHOLD = "10" (CHAR)
SQL> SELECT *
2 FROM inven
3 WHERE cost > '&costhold';
old 3: WHERE cost > '&costhold'
new 3: WHERE cost > '10'
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
Variables can be deleted or erased by using the UNDEFINE command on the variable or by leaving
SQL*Plus. In other words, they will not be available in subsequent sessions. After undefining
costhold, I checked to make sure it worked by using DEFINE costhold.
SQL CODE:
SQL> UNDEFINE costhold;
SQL> DEFINE costhold;
symbol costhold is UNDEFINED
In the examples below, I first asked to see what was in deptname. In contained TY as a result
of previous work I had done. I then asked it to ACCEPT deptname and it moved to the next line
and waited. I entered BK. Then the SQL> prompt came up and I said DEFINE deptname to see what
it contained. It contained the BK. This example convinced me of the value of the prompt, so
on my next attempt I used the prompt. When the prompt appeared I entered SP which the DEFINE
shows is now in deptname.
SQL CODE:
SQL> DEFINE deptname;
DEFINE DEPTNAME = "TY" (CHAR)
SQL> ACCEPT deptname;
BK
SQL> DEFINE deptname;
DEFINE DEPTNAME = "BK" (CHAR)
SQL> ACCEPT deptname PROMPT 'Please enter the name of the department you are using: ';
Please enter the name of the department you are using: SP
SQL> DEFINE deptname;
DEFINE DEPTNAME = "SP" (CHAR)
In the example below I am using the ACCEPT to set up or define a variable called newreordpt as
a NUMBER field. The PROMPT is also used. I then use this new field to compare against in the
select. Notice that no quotes are needed since this is a numeric field.
SQL CODE:
SQL> ACCEPT newreordpt NUMBER PROMPT 'Enter reorderpt to compare against: ';
Enter reorderpt to compare against: 25
SQL> DEFINE newreordpt;
DEFINE NEWREORDPT = 25 (NUMBER)
SQL> SELECT *
2 FROM inven
3 WHERE reordpt >= &newreordpt;
old 3: WHERE reordpt >= &newreordpt
new 3: WHERE reordpt >= 25
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
This example takes in new price as a number formatted with two decimal places. I then used
this in the WHERE statement to find matching prices.
SQL CODE:
SQL> ACCEPT newprice NUMBER FORMAT 9999.99 PROMPT 'Enter price to compare against: ';
Enter price to compare against: 12.99
SQL> DEFINE newprice;
DEFINE NEWPRICE = 12.99 (NUMBER)
SQL> SELECT *
2 FROM inven
3 WHERE price = &newprice;
old 3: WHERE price = &newprice
new 3: WHERE price = 12.99
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100