Sql*Plus User’s Guide and Reference
Using Predefined Variables
Download 5.34 Mb. Pdf ko'rish
|
E18404-12
- Bu sahifa navigatsiya:
- Using Substitution Variables
- Where and How to Use Substitution Variables
Using Predefined Variables
There are eight variables containing SQL*Plus information that are defined during SQL*Plus installation. These variables can be redefined, referenced or removed the same as any other variable. They are always available from session to session unless you explicitly remove or redefine them. Using Substitution Variables Suppose you want to write a query like the one in SALES (see Example 5–1, "Using a System Editor to Write a SQL Script" ) to list the employees with various jobs, not just those whose job is SA_MAN. You could do that by editing a different value into the WHERE clause each time you run the command, but there is an easier way. By using a substitution variable in place of the text, SA_MAN, in the WHERE clause, you can get the same results you would get if you had written the values into the command itself. A substitution variable is preceded by one or two ampersands (&). When SQL*Plus encounters a substitution variable in a command, SQL*Plus executes the command as though it contained the value of the substitution variable, rather than the variable itself. For example, if the variable SORTCOL has the value JOB_ID and the variable MYTABLE has the value EMP_DETAILS_VIEW, SQL*Plus executes the commands SELECT &SORTCOL, SALARY FROM &MYTABLE WHERE SALARY>12000; as if they were SELECT JOB_ID, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000; Where and How to Use Substitution Variables You can use substitution variables anywhere in SQL and SQL*Plus commands, except as the first word entered. When SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus prompts you for the value. You can enter any string at the prompt, even one containing blanks and punctuation. If the SQL command containing the reference should have quote marks around the variable and you do not include them there, the user must include the quotes when prompted. See Also: Predefined Variables on page 12-45 for a list of the predefined variables and examples of their use. Using Substitution Variables Using Scripts in SQL*Plus 5-13 SQL*Plus reads your response from the keyboard, even if you have redirected terminal input or output to a file. If a terminal is not available (if, for example, you run the script in batch mode), SQL*Plus uses the redirected file. After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF. Download 5.34 Mb. Do'stlaringiz bilan baham: |
Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2024
ma'muriyatiga murojaat qiling
ma'muriyatiga murojaat qiling