More on variables with Oracle’s SQL*Plus As always, speaker notes will contain additional information!
Variables In this example, the user will enter the field for the primary sort and the field for the secondary sort. SQL> SELECT * 2 FROM first_pay 3 ORDER BY &ordcol 1, &ordcol 2; Enter value for ordcol 1: jobcode Enter value for ordcol 2: bonus old 3: ORDER BY &ordcol 1, &ordcol 2 new 3: ORDER BY jobcode, bonus PAY_ ---3333 1111 5555 7777 4444 2222 6666 8888 NAME ----------Susan Ash Linda Costa Richard Jones Donald Brown Stephen York John Davidson Joanne Brown Paula Adams JO -AP CI CI CI CM IN IN IN STARTDATE SALARY BONUS ---------05 -FEB-00 25000 500 15 -JAN-97 45000 1000 30 -OCT-92 50000 2000 05 -NOV-99 45000 03 -JUL-97 42000 25 -SEP-92 40000 1500 18 -AUG-94 48000 2000 12 -DEC-98 45000 2000
Variables SQL> SELECT &col, COUNT(&col) 2 FROM first_pay 3 GROUP BY &col; Enter value for col: jobcode old 1: SELECT &col, COUNT(&col) new 1: SELECT jobcode, COUNT(jobcode) Enter value for col: jobcode old 3: GROUP BY &col new 3: GROUP BY jobcode JO COUNT(JOBCODE) -- -------AP 1 CI 3 CM 1 IN 3 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. SQL> SELECT &&col, COUNT(&col) 2 FROM first_pay 3 GROUP BY &col; Enter value for col: bonus old 1: SELECT &&col, COUNT(&col) new 1: SELECT bonus, COUNT(bonus) old 3: GROUP BY &col new 3: GROUP BY bonus BONUS COUNT(BONUS) -----------500 1 1000 1 1500 1 2000 4 0
Predefine variables - define 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> DEFINE coldef = jobcode; SQL> SELECT name, salary, &coldef 2 FROM first_pay; old 1: SELECT name, salary, &coldef new 1: SELECT name, salary, jobcode NAME SALARY JO ---------- -Linda Costa 45000 CI John Davidson 40000 IN Susan Ash 25000 AP Stephen York 42000 CM Richard Jones 50000 CI Joanne Brown 48000 IN Donald Brown 45000 CI Paula Adams 45000 IN
Variables SQL> SELECT name, &&colin 2 FROM first_pay; Enter value for colin: salary old 1: SELECT name, &&colin new 1: SELECT name, salary NAME SALARY ----------Linda Costa 45000 John Davidson 40000 Susan Ash 25000 Stephen York 42000 Richard Jones 50000 Joanne Brown 48000 Donald Brown 45000 Paula Adams 45000 8 rows selected. SQL> DEFINE colin; DEFINE COLIN 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 = "salary" (CHAR)
Define This is the definition of colin from the previous slide. 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_ ---1111 5555 7777 NAME ----------Linda Costa Richard Jones Donald Brown When I use &jobci in a SELECT there is no prompt for user entry since jobci has been previously defined. JO -CI CI CI 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. STARTDATE SALARY BONUS ---------15 -JAN-97 45000 1000 30 -OCT-92 50000 2000 05 -NOV-99 45000
Accept 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> DEFINE jobci; DEFINE JOBCI = "CI" (CHAR) SQL> ACCEPT jobci; IN SQL> 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: CM SQL> Define jobci; DEFINE JOBCI = "CM" (CHAR)
Accept 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. SQL> ACCEPT v_bonus NUMBER PROMPT 'Please enter bonus to check against: ' Please enter bonus to check against: 2000 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_bonus new 3: WHERE bonus = 2000 PAY_ ---4444 5555 6666 8888 NAME ----------Stephen York Richard Jones Joanne Brown Paula Adams JO -CM CI IN IN STARTDATE SALARY BONUS ---------03 -JUL-97 42000 30 -OCT-92 50000 2000 18 -AUG-94 48000 2000 12 -DEC-98 45000 2000
Accept SQL> SELECT * FROM inven; ITEM ---1111 1212 1234 2121 2222 2345 3333 3456 ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA --------- --------- -- -- ---Good Night Moon 24 30 40 8 12. 99 BK BY X 100 Heidi 12 25 25 10 14. 99 BK CH X 112 Adven Reddy Fox 5 0 10 9 14. 75 BK CH X 100 Teddy Bear 5 20 40 15 19. 95 TY CH X 115 Building Blocks 4 0 15 48 51. 99 TY CH Z 200 Doll House 2 5 10 45 55. 98 TY CH Z 212 Basketball 24 25 50 14 17. 99 SP BK Y 200 Net/Hoop 12 0 25 25 27. 95 SP BK Y 200 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. SQL> ACCEPT v_price NUMBER FORMAT 9999. 99 PROMPT 'Enter price to compare against: ' Enter price to compare against: 14. 95 SQL> SELECT * Then I did a select for all records where the 2 FROM inven price was greater than or equal to &v_price. 3 WHERE price >= &v_price; old 3: WHERE price >= &v_price new 3: WHERE price >= 14. 95 ITEM ---1212 2121 2222 2345 3333 3456 ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA --------- --------- -- -- ---Heidi 12 25 25 10 14. 99 BK CH X 112 Teddy Bear 5 20 40 15 19. 95 TY CH X 115 Building Blocks 4 0 15 48 51. 99 TY CH Z 200 Doll House 2 5 10 45 55. 98 TY CH Z 212 Basketball 24 25 50 14 17. 99 SP BK Y 200 Net/Hoop 12 0 25 25 27. 95 SP BK Y 200
Set verify off SET VERIFY OFF suppresses the display of old and new. SQL> 2 3 SET VERIFY OFF SELECT * FROM inven WHERE price >= &v_price; ITEM ---1212 2121 2222 2345 3333 3456 ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA --------- --------- -- -- ---Heidi 12 25 25 10 14. 99 BK CH X 112 Teddy Bear 5 20 40 15 19. 95 TY CH X 115 Building Blocks 4 0 15 48 51. 99 TY CH Z 200 Doll House 2 5 10 45 55. 98 TY CH Z 212 Basketball 24 25 50 14 17. 99 SP BK Y 200 Net/Hoop 12 0 25 25 27. 95 SP BK Y 200 SQL> 2 3 old new SET VERIFY ON SELECT * FROM inven WHERE price >= &v_price; 3: WHERE price >= &v_price 3: WHERE price >= 14. 95 ITEM ---1212 2121 2222 2345 3333 3456 ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA --------- --------- -- -- ---Heidi 12 25 25 10 14. 99 BK CH X 112 Teddy Bear 5 20 40 15 19. 95 TY CH X 115 Building Blocks 4 0 15 48 51. 99 TY CH Z 200 Doll House 2 5 10 45 55. 98 TY CH Z 212 Basketball 24 25 50 14 17. 99 SP BK Y 200 Net/Hoop 12 0 25 25 27. 95 SP BK Y 200 SET VERIFY ON continues the display of old and new.
Скачать презентацию More on variables with Oracle s SQL Plus As always
08edd1a0012317fd8f1741663c1cdbee.ppt