Sqlplus Environmental Settings Detailed

One, the substitution variable

1. When we execute the command in Sql*plus, we can use the substitution variable to send different data for the statement before the user executes the statement. For example:

Gyj@ocm> SELECT * from t1 where id=&id;
Enter value for id:1--oracle first let us enter the value of the ID, here enter 1
Old 1:select * from T1 where id=&id
New 1:select * from T1 where id=1
ID NAME
---------- ----------
1 gyj1

After you enter the value, Oracle will &id to the value you entered, and then follow the values you entered. This is the substitution variable. The same statement, you can enter a different value each time, return is a different result. In fact, a substitution variable is similar to a simple form of a variable in a programming language.
2. In a statement, you can have multiple substitution variables, and in a statement, in addition to the first word, any part of the statement can be a substitution variable. Like what:

Gyj@ocm> SELECT * from t1 where &a &b &c;
Enter value for A:id
Enter value for B: =
Enter value for C:1
Old 1:select * T1 where &a &b &c
New 1:select * from t1 where id = 1
ID NAME
---------- ----------
1 gyj1

(shows the same result as above)

3. In addition to the first word of the statement, I can even set the entire statement as a substitution variable:

Gyj@ocm> Select &a &b &c &d &e &f &g;
Enter value for a: *
Enter value for B:from
Enter value for C:T1
Enter value for D:where
Enter value for E:id
Enter value for f: =
Enter value for G:1
Old 1:select &a &b &c &d &e &f &g
New 1:select * from t1 where id = 1
ID NAME
---------- ----------

1 gyj1

Note that the select must not be a substitution variable. The first word in the statement cannot be a substitution variable, and the other parts are OK. Therefore, the use of substitution variables is very flexible.

4. For character-type data, pay attention to the issue of single quotes, I want to display the name equals a person's line, you can use the following statement:

Gyj@ocm> SELECT * from t1 where name= ' &n ';
Enter value for N:gyj1
Old 1:select * T1 where name= ' &n '
New 1:select * from T1 where name= ' gyj1 '
ID NAME
---------- ----------

1 gyj1

Note: I have a single quote in ' &n ', so I don't have to gyj1 the outside to add a single quote when I enter a value for N.

If &n outside, I do not add single quotes, as follows:

Gyj@ocm> SELECT * from t1 where name=&n;
Enter value for N: ' gyj1 '
Old 1:select * from T1 where name=&n
New 1:select * from T1 where name= ' gyj1 '
ID NAME
---------- ----------

1 gyj1

(Show results Ibid.)
Note: There is no single quotation mark outside the &n, so when you enter GYJ1, you should add a single quote outside the GYJ1.
Ii. definition and cancellation of substitution variables
We can use the Define variable = value to define the substitution variable beforehand. such as sql> define a=1, this statement can be added after ";", or not add ";" Resolution

After you define a variable, you can refer to the variable anywhere by &a, such as:

Gyj@ocm> Define A=1
Gyj@ocm> SELECT * from t1 where id=&a;
Old 1:select * from T1 where id=&a
New 1:select * from T1 where id=1
ID NAME
---------- ----------

1 gyj1

Instead of using a substitution variable, "Enter a value:" This time, the value of a has previously been defined with define and no longer needs to be entered.
Variable A can be reused until you use the undefine variable command to cancel it. Try it next.

I first output a value with the following statement:

Gyj@ocm> select &a from dual;
Old 1:select &a from dual
New 1:select 1 from dual
1
----------

1

The value of a is still present. When used, you do not need to enter a value for a first. Next I cancel a.
Sql> Undefine A

After canceling, I execute the same command as above, outputting the value of a:

Gyj@ocm> Undefine A
Gyj@ocm> select &a from dual;
Enter value for a:
(This time I was asked to enter a value because A has been canceled)
The other thing to explain is that the substitution variable is for only one session, the variable of the value defined in session a, the value of which is not accessed in the B session. Let's try the following:
In session 1:sql> define A=1
In the session 1:sql> select &a from dual;
Old 1:select &a from dual
New 1:select 1 from dual
1
----------

1

You can already use variable a. Now for session 2:
In the session 2:sql> select &a from dual;
Enter a value: (Requires you to enter a value again, the value defined for the variable in session 1, not accessed in session 2)
Third, "&&" and substitution variables:

A substitution variable for the "&" number, if not previously define defined. After this use is done, it will be canceled automatically. and the substitution variable of double "&&", after this use, the value entered will remain until the undefine is canceled. The test is as follows:

Gyj@ocm> select &&a from dual;
Enter value for A:1
Old 1:select &&a from dual
New 1:select 1 from dual
1
----------

1

For the first time, enter a value of 1 for &&a, showing the value of a again, whether it is a select &&a from dual; or select &a from dual; You do not need to enter a value for a again. Until Undefine.
Four, SET VERIFY on | Off
When a substitution variable is in use, a "original value" is displayed each time, and then the display is replaced with a "new value", which can be turned on or off using set VERIFY. The default state is open. I turned it off to look at the effect:
Sql> Set Verify off
Explain, ordinary order, at the end should have a ";" semicolons, and commands that begin with define, Undefine, and set, which are specific to sql*plus and are not available in other environments. These exclusive sql*plus commands do not have to be separated by a semicolon at the end. Of course, if the end plus a semicolon, there will be no error.