Shulou(Shulou.com)06/01 Report--

This document introduces two things, one is to replace variables, the other is to understand whether hard parsing and soft parsing are good for variables declare definition or variable definition is good in oracle, for a submitted sql statement, there are two optional parsing processes, one is called hard parsing, the other is called soft parsing. A hard parsing requires parsing, establishing execution paths, optimizing access plans, and many other steps. Hard interpretation not only consumes a lot of cpu, but also occupies important latch resources, seriously affects the expansion of the system (that is, limits the concurrent distribution of the system), and the problems can not be solved by increasing the number of memory sticks and cpu. This is because the latch is set for sequential access and modification of memory areas that cannot be modified at the same time. When a sql statement is submitted, oracle first checks the shared buffer pool (shared pool) for exactly the same statement, and if so, it only needs to perform soft analysis, otherwise hard analysis is required. The only way to enable oracle to reuse execution plans is to use bound variables. The essence of a bound variable is a substitute variable that is used to replace constants in sql statements. Binding variables can make the sql statement submitted exactly the same each time. Two days before the connection, I saw someone on pub asking about the difference between the variables defined by define and variable in sqlplus. In fact, I understand that the definition of define is not a variable but a character constant. After being defined by define, there is no need to enter it through & or & & reference, that's all. Oracle is automatically replaced with values when it is executed; variable defines binding variables, as mentioned above. Bind variable references with ":" and replace variable references with "&" Bind variable initialization exec: num1:=2, replacement variable default type is char replacement variable (only for SQL * Plus or for development tools with the same principle as SQL * Plus): temporary storage value can be used to create a general script, it can be used to interact with users, so the format, also known as interactive command replacement variable in SQL * Plus, adds a & to the variable name. To prompt the user for replacement data when running the SQL command, and then run the SQL command syntax according to the input data: (1) &: "& variable name" eg:&name Life cycle: in a single reference, no declaration is required. If you replace a character or date type, it is best to expand the scope of use with single quotes: where, order by, column expression, table name, www.2cto.com (2) &: "& & variable name" in the entire SELECT statement eg:&&name; life cycle: entire session (session connection), no declaration is required (3) define: "define variable name = variable value" eg:DEFINE a = clark Life cycle: entire session, pre-declared, used & reference declared variable define variable= user-created CHAR type value: define variable name = value Define variable name: view variable command. Undefine variable name: clear variable define: view all replacement variables and their values in the current session (4) accept life cycle: the entire session is pre-declared, prompts can be customized, and variables declared with & references are used. Definition: accept variable name name number/char/date prompt 'prompt message content', that is, ACC [EPT] variable [NUM [BER] | CHAR | DATE] [form [format] [DEF [AULT] default] [PROMPT text | NOPROMPT] [HIDE] explanation: ACC [EPT] variable [NUMBER] | ACC [EPT] [NUMBER] [Numer] [Numer] [format] [AULT] [def [AULT] OMPT] [HIDE] explanation: ACC command: used to output information that prompts the user In order to make the user understand the function and operation of the script file PAUSE command: to pause the operation of the script file HIDE option: to hide the user's input so that others are not visible, the security command means: when the plsql segment executes to the variable name, the user interaction is required to continue execution, and the plsql segment displays "prompt message content" for the user to enter the relevant information (if you specify the hide option) Then the user input will be displayed with an asterisk to increase security, a bit like entering a password), the user input will be received and paid to name, about the type of content entered by the user under the "prompt message content", the plsql segment developer will specify through number/char/date, and after the variable name gets the correct value, continue to execute the following program! Example: accept a char prompt 'Please enter the employee's employment time (yyyy-mm-dd):' hide example: accept a char prompt 'input input' hide www.2cto.com set verify on/off; # verify: whether to give the original value and the new value prompt. For more information, please refer to the following example: plsql program 1: [SQL] declare v_sal number (6 ename 2); v_ename emp.ename%type:='&ename'; begin select sal into v_sal from emp where lower (ename) = lower (v_ename); if v_sal

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.