Sql*Plus User’s Guide and Reference
Download 5.34 Mb. Pdf ko'rish
|
E18404-12
|
Example 5–10
Using Double Ampersands To expand the script STATS using double ampersands and then run the file, first suppress the display of each line before and after substitution: SET VERIFY OFF JOB_ID MAXIMUM ---------- ---------- AC_ACCOUNT 8300 AC_MGR 12000 AD_ASST 4400 AD_PRES 24000 AD_VP 17000 FI_ACCOUNT 9000 FI_MGR 12000 HR_REP 6500 IT_PROG 9000 MK_MAN 13000 MK_REP 6000 JOB_ID MAXIMUM ---------- ---------- PR_REP 10000 PU_CLERK 3100 PU_MAN 11000 SA_MAN 14000 SA_REP 11500 SH_CLERK 4200 ST_CLERK 3600 ST_MAN 8200 19 rows selected. Using Substitution Variables Using Scripts in SQL*Plus 5-15 Now retrieve and edit STATS by entering the following commands: GET STATS 2 APPEND , CHANGE/&/&& I MIN (&&NUMBER_COL) MINIMUM, SUM(&&NUMBER_COL) TOTAL, AVG(&&NUMBER_COL) AVERAGE 1 CHANGE/&/&& 7 SELECT &GROUP_COL, MAX(&NUMBER_COL) MAXIMUM FROM &TABLE GROUP BY &GROUP_COL 2* MAX(&NUMBER_COL) MAXIMUM 2* MAX(&NUMBER_COL) MAXIMUM, 2* MAX(&&NUMBER_COL) MAXIMUM, 3i 4i 5i 6i 1* SELECT &GROUP_COL, 1* SELECT &&GROUP_COL, 7* GROUP BY &GROUP_COL Using Substitution Variables 5-16 SQL*Plus User's Guide and Reference CHANGE/&/&&/ SAVE STATS2 Finally, run the script STATS2 and respond to the prompts as follows: START STATS2 Enter value for group_col: JOB_ID Enter value for number_col: SALARY Enter value for table: EMP_DETAILS_VIEW SQL*Plus displays the following output: Note that you were prompted for the values of NUMBER_COL and GROUP_COL only once. If you were to run STATS2 again during the current session, you would be prompted for TABLE (because its name has a single ampersand and the variable is therefore not DEFINEd) but not for GROUP_COL or NUMBER_COL (because their names have double ampersands and the variables are therefore DEFINEd). Before continuing, set the system variable VERIFY back to ON: SET VERIFY ON 7* GROUP BY &&GROUP_COL Created file STATS2 JOB_ID MAXIMUM MINIMUM TOTAL AVERAGE ---------- ---------- ---------- ---------- ---------- AC_ACCOUNT 8300 8300 8300 8300 AC_MGR 12000 12000 12000 12000 AD_ASST 4400 4400 4400 4400 AD_PRES 24000 24000 24000 24000 AD_VP 17000 17000 34000 17000 FI_ACCOUNT 9000 6900 39600 7920 FI_MGR 12000 12000 12000 12000 HR_REP 6500 6500 6500 6500 IT_PROG 9000 4200 28800 5760 MK_MAN 13000 13000 13000 13000 MK_REP 6000 6000 6000 6000 JOB_ID MAXIMUM MINIMUM TOTAL AVERAGE ---------- ---------- ---------- ---------- ---------- PR_REP 10000 10000 10000 10000 PU_CLERK 3100 2500 13900 2780 PU_MAN 11000 11000 11000 11000 SA_MAN 14000 10500 61000 12200 SA_REP 11500 6100 250500 8350 SH_CLERK 4200 2500 64300 3215 ST_CLERK 3600 2100 55700 2785 ST_MAN 8200 5800 36400 7280 19 rows selected. Passing Parameters through the START Command Using Scripts in SQL*Plus Download 5.34 Mb. Do'stlaringiz bilan baham: 
|