Usage of the Oracle substitution variable (SubstitutionVariable)

1. DEFINE substitution variable 1. Set the exact value definemyv through DEFINE

1. DEFINE substitution variable 1. Set the exact value define myv = through DEFINE

I. Define substitution Variables

1. Set the exact value through DEFINE

Define myv = 'King ';

The value of the variable myv is King.

2. Define a variable using ACCEPT and prompt you to enter its value

Accept myv char prompt 'enter a last name :'

The value of the variable myv must be input by the user.
3. Define a variable through & and require you to enter its value

Select first_name from employees where last_name = '& myuser ';

The value of the variable myuser must be input by the user.
4. Use COLUMN NEW_VALUE to define

Column last_name new_value mynv
Select last_name from employees where employee_id = 100;
The value of the variable mynv is the record stored in the field last_name.

Ii. Use Substitution Variables

If the substitution variable has been defined or assigned a value, you can add "&" before it to call it.

Select employee_id from employees where last_name = '& myv ';
3. query Defined variables

With the define command, you can query all the defined substitution variables and their values and types without adding any parameters.

Define
Then we will get

Define myv = "King" (CHAR)
...
4. Insert the character "&" into the data

Sometimes we need to insert the character "&" in the data, instead of using it as the call symbol to replace the variable. We can achieve this through the following methods:

1. set define off to disable the substitution variable function;

2. set escape \ to set the escape Character "\", so that "\ &" is used to indicate the character "&".

5. Add the current time in the spool script
You can add the time in the spool script by defining the substitution variable for sysdate.
Column dcol new_value mydate noprint
Select to_char (sysdate, 'yyyymmdd') dcol from dual;

Spool &mydate.report.txt
-- My report goes here
Select last_name from employees;
Spool off
In this way, the mydate variable will pass the system time to the spool script. The noprint ensures that the select statement will not output the value of mydate. If the following selectsyntax does not return any result, then the first "in &mydate.report.txt" will be executed. "only the Terminator that replaces the variable cannot be used as a character. If the value of" mydate.txt "is" "," &mydate.report.txt "is output to the spool script as" 20100124report.txt ".
6. You can use the defined substitution variable "."
Define mycity = Melbourne
Spool &mycity.w.alia.txt
Then the output is changed to melbournew.alia.txt ".
7. Add the character "." After the value of the substitution variable
Define mycity = Melbourne
Spool & mycity .. log
The output is "Melbourne. log ".
8. Use Value substitution variables in TTITLE, BTITLE, REPHEADER, and REPFOOTER
Define dept = '60'
Ttitle left 'salaries for department & dept'
Select last_name, salary from employees where department_id = & dept;
9. Replace variable with variable values in TTITLE, BTITLE, REPHEADER, and REPFOOTER
Column department_id new_value dv noprint
Ttitle left 'members of dis' dv
Break on department_id skip page
Select department_id, last_name from employees order by department_id, last_name;
Instead of adding "&" before replacing the variable, you can place it outside the string to implement output of different values.
In BTITLE and REPFOOTER, you must replace "COLUMN OLD_VALUE" with "COLUMN NEW_VALUE ".
10. Use the Bind Variable in the SQL * Plus command
These SQL * Plus commands, such as SPOOL, SET, and TTITLE, are executed in the SQL * Plus program, rather than transmitted to the data for execution. Therefore, they cannot identify the bound variables.
-- Set a bind variable to a text string
Variable mybindvar varchar2 (20)
Begin
: Mybindvar: = 'myspoolfilename ';
End;

-- Transfer the value from the bind variable to the substitution variable
Column mc new_value mysubvar noprint
Select: mybindvar mc from dual;

-- Use the substitution variable
Spool &mysubvar..txt
Select * from employees;
Spool off