Replace variable & amp; and & amp;

Replace variables &&
Replace variables can be seen as placeholders. Single & Symbol replacement the most basic and common form of element replacement in SQL statements is single & Symbol replacement. & Symbol is used to specify the replacement variable symbol in the statement. When the statement is executed, the Oracle server processes the statement and finds that the variable is replaced, and tries to use one of the following two methods to parse the value of the variable. First, it checks whether the variable (DEFINE command) is defined in the user session ). If no, the user process will prompt you to enter a value, which will replace the variable. Copy the code SQL> select ename, sal from emp where empno = & number; Enter value for number: 7934old 1: select ename, sal from emp where empno = & numbernew 1: select ename, sal from emp where empno = 7934 ename sal ---------- MILLER 1300 copy Code 2, & replace if the same replacement variable is used in multiple queries, the value of the variable to be replaced remains unchanged and can be replaced. When the oracle server encounters a & replace variable, the process will prompt you to enter a value, which is the session value of the variable. When a subsequent query references the replace variable, will be directly replaced with the variable session value, the user process no longer prompts to enter the value. Copy the code SQL> select ename, sal from emp where empno = & number; --> note that & Symbol replacement appears for the first time, enter value for number: 7934 --> input value old 1: select ename, sal from emp where empno = & amp; numbernew 1: select ename, sal from emp where empno = 7934 ename sal ---------- MILLER 1300 SQL> select ename, sal from emp where empno = & number; --> direct reference, no longer prompt to enter the new value old 1: select ename, sal from emp where empno = & numbernew 1: Select ename, sal from emp where empno = 7934 ename sal ---------- MILLER 1300 SQL> select ename, sal from emp where empno = & number; --> the second occurrence, similarly, the input value is no longer displayed. The number is always valid for the old 1: select ename, sal from emp where empno = & numbernew 1: select ename, sal from emp where empno = 7934 ename sal ---------- MILLER 1300 copy code 3. Replace the column name. The above substitution variables Replace the elements in the where clause, but in fact, any element in the SQL statement can be replaced. As shown below, the third column is replaced: copy the code SQL> select empno, ename, & col from emp order by & col; --> the third column is the replacement variable Enter value for col: sal --> here, we can select either sal or hiredateold 1: select empno, ename, & col from emp order by & colnew 1: select empno, ename, sal from emp order by sal empno ename sal ----- ---------- -------- 7369 SMITH 800 7900 JAMES 950 ADAMS 7876 1100 WARD 7521... copy Code 4. Replace expressions and text can replace almost any element of an SQL statement at runtime. The condition is that Oracle requires at least the first word to be static. In a SELECT statement, at least the SELECT keyword remains unchanged, as shown below: copy the code SQL> select & rest_of_statements; Enter value for rest_of_statements: ename from emp where empno = 7839old 1: select & rest_of_statementsnew 1: select ename from emp where empno = 7839 ENAME----------KING copy Code 5. DEFINE when the same variable appears multiple times in the statement, you can use & replace to avoid repeated input. When & is replaced, the variable is saved as the session variable. As the statement continues, the saved session variable is used to automatically parse all subsequent variables. This is not necessarily required by the user. In this case, UNDEFINE variable can be used to remove it, as shown in UNDEFINE col and UNDEFINE number above. The DEFINE command has two purposes: it can be used to retrieve the list of all variables currently defined in an SQL session; it can also be used to explicitly define the value of a variable referenced as a replacement variable in one or more statements during a session. The syntax is as follows: DEFINE; DEFINE variable = value; the prefix used to replace variables is usually & or &&. This is the default character that identifies the replacement variable. In SQL * PLus, you can use a special SET command option to change the default character (&) to another character, or disable the replace variable feature. The syntax of the SET command is as follows: set define character; set define on; set define off; the first SET command option changes the prefix of the replace variable from & to other characters. This character cannot be a number or a blank character. The second command option and the Third Command Option Control whether SQL * Plus will find the replacement variable. In addition, the ON option will modify the replacement character &. 6. Run the VERIFY command to operate the Oracle server using two types of commands: SQL language commands and SQL customer control commands. The SELECT statement is a language command, while the SET command controls the SQL client environment. There are many different languages and control commands available, but the control commands related to replacement are DEFINE and VERIFY. The VERIFY command controls whether the submitted replacement variable is displayed on the screen to VERIFY that the replacement is correct. The displayed message is composed of the old clause and the new clause that contains the input value of the replacement variable. Copy the code SQL> select ename, sal from emp where empno = & number; Enter value for number: 7788old 1: select ename, sal from emp where empno = & numbernew 1: select ename, sal from emp where empno = 7788 ename sal ---------- SCOTT 3000 SQL> set verify offSQL> select ename, sal from emp where empno = & number; Enter value for number: 7788 ename sal ---------- SCOTT 3000