Replace variable, variable name, and variable name in Oracle

Replace the variable (only for SQL * Plus or for development tools with the same principle as SQL * Plus): the temporary storage value can be used to create a common script.

Replace the variable (only for SQL * Plus or for development tools with the same principle as SQL * Plus): the temporary storage value can be used to create a common script.

Replace variables (only used for SQL * Plus or development tools with the same principle as SQL * Plus ):
Temporary storage value
It can be used to create a general script.
It can be used to interact with users, so it is also called interactive commands in SQL * Plus.

Replace the variable format by adding an & before the variable name to prompt the user to enter the replacement data when running the SQL command, and then run the SQL command according to the input data
Syntax:
(1) &: "& variable name" eg: & name;
Life cycle: it is in a single reference and does not need to be declared. If you replace the character or date type, it is best to extend it with single quotes.
Usage scope: where, order by, column expression, table name, entire SELECT statement

(2) &: "& variable name" eg: & name;
Life cycle: entire session (session connection), no need to declare

(3) define: "define variable name = variable value" eg: DEFINE a = clark;
Life cycle: indicates the entire session. It is pre-declared and used to reference declared variables.
Define variable = user-created CHAR type value: define variable name = value;
Define variable name: View variable commands.
Undefine variable name: Clear variable
Define: view all substitution variables and their values in the current session

(4) accept

Lifecycle: entire session
Pre-declaration. You can customize the prompt information and use & reference declared variables for use.
Definition:
Accept variable name number/char/date prompt 'prompt message content' is: ACC [EPT] variable [NUM [BER] | CHAR | DATE] [FOR [MAT] format] [DEF [AULT] default] [PROMPT text | NOPR [OMPT] [HIDE]
Explanation:
PROMPT command: this command is used to output user information, so that you can understand the functions and running status of the script file.
PAUSE command: Used to PAUSE the running of script files
HIDE options: used to HIDE user input so that others are invisible and secure
This command means that when the plsql program segment executes the variable name, user interaction is required to continue the execution, the plsql program section displays "prompt information content" for users to enter relevant information (if the hide option is specified, the next step is to display the information entered by the user with an asterisk to increase security, A bit like a password). The user-entered content is received and paid to the name. For the type of user-entered content under "prompt information content, the developer of the plsql program segment can specify it through number/char/date. After the variable name gets the correct value, continue to execute the following programs!
For example: accept a char prompt, enter the employee's employment time (yyyy-mm-dd): 'hide
Example: accept a char prompt 'input a: 'hide

Verify: indicates whether the original value and new value are displayed.

Set verify on/off;

For more information, see the following example:

Plsql program 1:

Declare

V_sal number (6, 2 );

/

Plsql Program 2:

Declare

V_sal number (6, 2 );

/

In a session of secureCRT, first Execute Program 2 and then execute program 1. You will find that PL/SQL procedure successfully completed is directly executed.
Instead of letting me enter the ename, neither can set verify off.
After another set verify off is enabled, every execution of program 1 will allow you to enter the ename.
This means that the ename is saved as the session variable when the previous session executes program 2, rather than the plsql program variable.

For more information about Oracle, see the Oracle topic page? Tid = 12