more on variables with oracle’s sql*plus as always, speaker notes will contain additional...

More on variables with Oracle’s SQL*Plus

As always, speaker notes will contain additional information!

VariablesVariables

SQL> SELECT * 2 FROM first_pay 3 ORDER BY &ordcol1, &ordcol2;Enter value for ordcol1: jobcodeEnter value for ordcol2: bonusold 3: ORDER BY &ordcol1, &ordcol2new 3: ORDER BY jobcode, bonus

PAY_ NAME JO STARTDATE SALARY BONUS---- -------------------- -- --------- --------- ---------3333 Susan Ash AP 05-FEB-00 25000 5001111 Linda Costa CI 15-JAN-97 45000 10005555 Richard Jones CI 30-OCT-92 50000 20007777 Donald Brown CI 05-NOV-99 450004444 Stephen York CM 03-JUL-97 42000 20002222 John Davidson IN 25-SEP-92 40000 15006666 Joanne Brown IN 18-AUG-94 48000 20008888 Paula Adams IN 12-DEC-98 45000 2000

In this example, the user will enter the field for the primary sort and the field for the secondary sort.

SQL> SELECT &col, COUNT(&col) 2 FROM first_pay 3 GROUP BY &col;Enter value for col: jobcodeEnter value for col: jobcodeold 1: SELECT &col, COUNT(&col)new 1: SELECT jobcode, COUNT(jobcode)Enter value for col: jobcodeold 3: GROUP BY &colnew 3: GROUP BY jobcode

JO COUNT(JOBCODE)-- --------------AP 1CI 3CM 1IN 3

SQL> SELECT &&col, COUNT(&col) 2 FROM first_pay 3 GROUP BY &col;Enter value for col: bonusold 1: SELECT &&col, COUNT(&col)new 1: SELECT bonus, COUNT(bonus)old 3: GROUP BY &colnew 3: GROUP BY bonus

BONUS COUNT(BONUS)--------- ------------ 500 1 1000 1 1500 1 2000 4 0

VariablesVariables

In the example to the left, I used &col. Notice that the user had to enter jobcode three separate times.

In the example below, I used &&col at the beginning of the select. Notice that the user only had to enter the value once. The stored variable was used from that point on.

Predefine variables - definePredefine variables - define

SQL> DEFINE coldef = jobcode;SQL> SELECT name, salary, &coldef 2 FROM first_pay;old 1: SELECT name, salary, &coldefnew 1: SELECT name, salary, jobcode

NAME SALARY JO-------------------- --------- --Linda Costa 45000 CIJohn Davidson 40000 INSusan Ash 25000 APStephen York 42000 CMRichard Jones 50000 CIJoanne Brown 48000 INDonald Brown 45000 CIPaula Adams 45000 IN

In this example, I used DEFINE to predefine the field coldef.

I then went on and used &coldef in a SELECT statement and no user entry was required.

SQL> SELECT name, &&colin 2 FROM first_pay;Enter value for colin: salaryold 1: SELECT name, &&colinnew 1: SELECT name, salary

NAME SALARY-------------------- ---------Linda Costa 45000John Davidson 40000Susan Ash 25000Stephen York 42000Richard Jones 50000Joanne Brown 48000Donald Brown 45000Paula Adams 45000

8 rows selected.

SQL> DEFINE colin;DEFINE COLIN = "salary" (CHAR)

VariablesVariables

The variable colin was defined by using the &&colin in a SELECT.

The user was then prompted to enter the value for colin.

To check the definition, I can simply enter DEFINE followed by the name of the variable. It will return the contents and the type which is CHAR

SQL> DEFINE colin;DEFINE COLIN = "salary" (CHAR)

SQL> DEFINE jobci = CI;SQL> DEFINE jobci;DEFINE JOBCI = "CI" (CHAR)SQL> SELECT * 2 FROM first_pay 3 WHERE jobcode = '&jobci';old 3: WHERE jobcode = '&jobci'new 3: WHERE jobcode = 'CI'

PAY_ NAME JO STARTDATE SALARY BONUS---- -------------------- -- --------- --------- ---------1111 Linda Costa CI 15-JAN-97 45000 10005555 Richard Jones CI 30-OCT-92 50000 20007777 Donald Brown CI 05-NOV-99 45000

DefineDefineThis is the definition of colin from the previous slide.

Now I am setting up a definition of jobci and giving it the value of CI.

When I look at this definition, it returns jobci = CI in a CHAR field.

When I use &jobci in a SELECT there is no prompt for user entry since jobci has been previously defined.

AcceptAccept

SQL> DEFINE jobci;DEFINE JOBCI = "CI" (CHAR)SQL> ACCEPT jobci;INSQL> DEFINE jobci;DEFINE JOBCI = "IN" (CHAR)SQL> ACCEPT jobci PROMPT 'Please enter the new jobcode you are processing: 'Please enter the new jobcode you are processing: CMSQL> Define jobci;DEFINE JOBCI = "CM" (CHAR)

First I checked to see what value was stored in jobci. I did this by entering DEFINE jobci. It returned the definition as CI (CHAR).

Next I keyed in ACCEPT jobci which will allow me to enter in a new value to be stored in the variable jobci. The cursor moved down a line and waited. I entered IN.

Then I wanted to see if the change had happened. I keyed in DEFINE jobci; and it returned the definition which now contained IN.

When I did ACCEPT the first time, it simply waited for me to key in something else. I decided I wanted a prompt to tell me what to enter and to tell me it was time to enter data. I entered the prompt and when it executed it came up with the prompt. I responded with CM.

I then did a DEFINE jobci to make sure the CM was there. It was.

SQL> ACCEPT v_bonus NUMBER PROMPT 'Please enter bonus to check against: 'Please enter bonus to check against: 2000

AcceptAccept

SQL> DEFINE v_bonus;DEFINE V_BONUS = 2000 (NUMBER)

SQL> SELECT * 2 FROM first_pay 3 WHERE bonus = &v_bonus;old 3: WHERE bonus = &v_bonusnew 3: WHERE bonus = 2000

PAY_ NAME JO STARTDATE SALARY BONUS---- -------------------- -- --------- --------- ---------4444 Stephen York CM 03-JUL-97 42000 20005555 Richard Jones CI 30-OCT-92 50000 20006666 Joanne Brown IN 18-AUG-94 48000 20008888 Paula Adams IN 12-DEC-98 45000 2000

First I used the accept to setup a variable called v_bonus as a NUMBER with a PROMPT. The next line shows the prompt. I entered 2000.

To check, I used the DEFINE v_bonus. The variable v_bonus has been given the value 2000 and defined as a NUMBER.

I then used the variable &v_bonus in the SELECT. Since it was already defined, it does not require user input.

AcceptAccept

SQL> ACCEPT v_price NUMBER FORMAT 9999.99 PROMPT 'Enter price to compare against: 'Enter price to compare against: 14.95SQL> SELECT * 2 FROM inven 3 WHERE price >= &v_price;old 3: WHERE price >= &v_pricenew 3: WHERE price >= 14.95

ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA---- --------------- --------- --------- --------- --------- --------- -- -- ----1212 Heidi 12 25 25 10 14.99 BK CH X1122121 Teddy Bear 5 20 40 15 19.95 TY CH X1152222 Building Blocks 4 0 15 48 51.99 TY CH Z2002345 Doll House 2 5 10 45 55.98 TY CH Z2123333 Basketball 24 25 50 14 17.99 SP BK Y2003456 Net/Hoop 12 0 25 25 27.95 SP BK Y200

SQL> SELECT * FROM inven;

ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA---- --------------- --------- --------- --------- --------- --------- -- -- ----1111 Good Night Moon 24 30 40 8 12.99 BK BY X1001212 Heidi 12 25 25 10 14.99 BK CH X1121234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X1002121 Teddy Bear 5 20 40 15 19.95 TY CH X1152222 Building Blocks 4 0 15 48 51.99 TY CH Z2002345 Doll House 2 5 10 45 55.98 TY CH Z2123333 Basketball 24 25 50 14 17.99 SP BK Y2003456 Net/Hoop 12 0 25 25 27.95 SP BK Y200

First I defined v_price as a number, gave it a format and include a prompt. When the prompt came up, I entered 14.95. This assigns this amount to v_price.

Then I did a select for all records where the price was greater than or equal to &v_price.

Set verify offSet verify off

SQL> SET VERIFY OFFSQL> SELECT * 2 FROM inven 3 WHERE price >= &v_price;


SQL> SET VERIFY ONSQL> SELECT * 2 FROM inven 3 WHERE price >= &v_price;old 3: WHERE price >= &v_pricenew 3: WHERE price >= 14.95


SET VERIFY OFF suppresses the display of old and new.

SET VERIFY ON continues the display of old and new.

more on variables with oracle’s sql*plus as always, speaker notes will contain additional...

Documents