Replace variable in Oracle, & amp; variable name, & amp; variable name Analysis

Replace variables (only used for SQL * Plus or development tools with the same principle as SQL * Plus ): the temporary storage value can be used to create a general script. It can be used to interact with users, therefore, the format of the interactive command replace variable in SQL * Plus is added before the variable name to prompt the user to enter the replacement data when running the SQL command, then run the SQL command syntax according to the input data: (1) &: "& variable name" eg: & name; lifecycle: in a single reference, no declaration is required, if you replace the character or date type, it is best to use single quotes to expand the scope of use: where, order by, column expression, table name, the entire SELECT statement www.2cto.com (2 )&&: "& variable name" eg: & name; lifecycle: entire session (session connection), no need to declare (3) define: "define variable name = variable value" eg: DEFINE a = clark; life cycle: entire session, pre-declared, used with & reference declared variable define var Iable = user-created CHAR type value: define variable name = value; define variable name: View variable command. Undefine variable name: Clear variable define: View All replace variables and their values in the current session (4) accept lifecycle: the entire session is declared in advance and prompt information can be customized, use & reference declared variables for use. Definition: accept variable name number/char/date prompt 'message content: 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 information prompting the user so that the user can understand the functions and running status of the script file. PAUSE command: Used to PAUSE running HIDE of the script file option: used to HIDE user input, to make it invisible to others, the safe 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 content input by the user under "prompt information content", the plsql program section developers will specify the content through number/char/date. After the variable name gets the correct value, continue to execute the following related programs! Example: accept a char prompt: Enter the employee's employment time (yyyy-mm-dd): 'hide example: accept a char prompt 'input a: 'hide www.2cto.com verify: whether the original value and new value are displayed. Set verify on/off; For details, see the following example: plsql program 1: [SQL] declare v_sal number (6, 2); v_ename emp. ename % type: = '& ename'; begin select sal into v_sal from emp where lower (ename) = lower (v_ename ); if v_sal <2000 then update emp set sal = v_sal + 200 where lower (ename) = lower (v_ename); end if; end;/plsql Program 2: [SQL] declare v_sal number (6, 2); v_ename emp. ename % type: = '& ename'; begin select sal into v_sal from emp where lowe R (ename) = lower (v_ename); if v_sal <2000 then update emp set sal = v_sal + 200 where lower (ename) = lower (v_ename); end if; www.2cto.com end; in A/secureCRT session, first Execute Program 2 and then execute program 1. You will find that PL/SQL procedure successfully completed is directly executed. if you do not want to input the ename, set verify off will not enable another session and set verify off, you will be asked to input the ename each time you execute program 1. This means that the ename is saved as the session variable when the previous session executes program 2, rather than the plsql program variable. Author luolunz