Oracle substitution variable

Oracle substitution variable

When querying or updating data, users enter data by themselves and use & tag in ORACLE, such

Select ename, job, sal, hiredate from emp where sal> & inputjob an input prompt box appears when you run SQL Developer, in this way, you can enter the sal> value to be queried to query.

The input data is rounded up. What if it is a string? For example, for ename = & inputename, we need to enter 'data', that is, the string must be included with '', which obviously does not meet our requirements.

We can use ename = UPPER ('& inputename') so that we don't have to worry about ''or case sensitivity, because in ORACLE, fields are capitalized.

For SQL Developer, the display effect is the pop-up input box, while SQLPlus prompts the input directly in the dos window. The display effect is different, but the implementation function is the same.

You can also use substitution variables to replace the search keyword ename LIKE '% & inputkeyword %' or the date hiredate <TO_DATE ('& inputhiredate', 'yyyy-mm-dd '), these are just alternative variables,

We can also use multiple substitution variables at the same time, such as job = UPPER ('& inputjob') AND sal> & inputsal. At this time, we will prompt two inputs for the user to input.

Besides, substitution variables are not only available in WHERE, but can also be used in SELECT, FROM, order by, group by, and other statements.

It is worth noting that when using group by, the SELECT statement may have the same input content as the GROUP field: for example

Select & inputGroupByColumn, SUM (sal), AVG (sal) from emp e group by & inputGroupByColumn

At this time, it is obvious that the user is required to input the same data twice, which makes it quite troublesome. Why do you need to input the same value twice? In this case, we can replace the first one with & inputGroupByColumn.

This is equivalent to defining an inputGroupByColumn variable. The system will automatically read this value for the first input. However, if you want to make the defined variable disappear, we need to use

UNDEFINE inputGroupByColumn or close the SQLPlus window directly

In addition to replace variables with & definitions, you can also use DEFINE variable name = value to create a variable. For example, DEFINE inputdname = 'accounting' defines an inputdname variable whose value is the subsequent string.

You can use DEFINE inputdname to query defined substitution variables.

Once the substitution variable is defined, it can be used in the SQL statement. You do not need to input the data select * from dept where dname = '& inputdname'

The replaced variable is UNDEFINE inputdname.

All the above replace variables use the system format, and the prompt information of the replace variable is not very clear. We can modify our prompt information through ACCEPT.

Syntax: ACCEPT replace variable name [data type] [FORMAT] [PROMPT 'info'] [HIDE]

ACCEPT usage: Create a script file. SQL file, write the command to the file, and run the command in SQLPlus using the @ + file path.

The preceding replace variable name does not need to be prefixed. The data type can be NUMBER, VARCHAR, or DATE, and the FORMAT model (such as A10 or 9.99) is specified)

PROMPT indicates the PROMPT information, while HIDE indicates that the password is not displayed.

For example: ACCEPT inputDate date format 'yyyy-MM-DD 'prompt' employee DATE: 'hide, then you can execute your own defined inputDate

Select empno, ename, job, hiredate FROM emp e WHERE hiredate = TO_DATE ('inputdate', 'yyyy-MM-DD ')