[PLSQL] variable binding, dynamic SQL, hard parsing and soft parsing, plsqlsql
**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************1.1 Variables
Variables defined in anonymous blocks or stored procedures are local variables, and the scope is in the entire anonymous block or stored procedure. If the running is over, the variable does not exist;
Bind Variable
Directly bind the variable bv_name in PLSQL, which does not need to be defined.
1.1.1 definition of variable binding
-- Variable binding definition
Variable I number
Begin
For I in 1 .. 1000 loop
Execute immediate 'insert into test values (: I) 'using I;
: I: = I; --- assign a value to the bound variable
End loop
Dbms_output.put_line (: I );
Commit;
End;
Print I
Exec: I =:= 10000 --- assign a value to the bound variable
Print: I;
3.3 SQL classification of PLSQL
Static SQL:
-- The SQL statements used in PLSQL blocks are clear during compilation, and the SQL statements are compiled in PLSQL editing;
Dynamic SQL:
-- When PLSQL is compiled, the SQL statement is uncertain. For example, different operations are performed based on different user input parameters,
The Compilation Program does not process dynamic statements. When running, it dynamically creates SQL statements.
3.3.1 static SQL
-- One hard analysis, one soft analysis, and 1000 executions
Create or replace procedure proc1
Ls
Begin
For I in 1 .. 1000 loop
Insert into test values (I );
End loop;
Commit;
End;
Begin proc1 end;
3.3.2 local dynamic SQL statement (no bound variable is used)
1000 this hard analysis, 1000 this soft analysis, 1000 executions
Create procedure proc1 ls
Begin
For I In 1 .. 1000 loop
Execute immediate 'insert into test values ('| I | ')';
End loop;
Commit;
End;
--- Do not compile SQL statements during the compilation process
Begin proc1 end;
3.3.3 local dynamic SQL statement (using Bound variables)
-- One hard analysis, 1000 soft analysis, and 1000 execution
Create procedure proc1 ls
Begin
For I In 1 .. 1000 loop
Execute immediate 'insert into test values (: I) 'using I;
End loop;
Commit;
Begin proc1 end;
3.4 SQL statement Processing
Syntax check: syntax check
Semantic check: semantic check
-- Such as checking whether access objects in SQL statements are stored, and whether the user has the corresponding permissions;
Parse the SQL statement parse
-- Use internal algorithms to parse SQL statements and generate parse tree and execution plan)
Execute the SQL statement and return the result: execute and return;
3.4.1 hard parsing and soft Parsing
Oracle uses the internal hash algorithm to obtain the hash value of the SQL statement, and then finds whether the hash value exists in the library cache;
If the SQL statement exists, the SQL statement is compared with that in the cache. If the SQL statement is "the same", the existing number of resolutions and execution plans are used, and the related work of the optimizer is ignored, this is the process of soft resolution;
If one of the above two reducers fails, the optimizer will create a resolution tree to generate the action of the Execution Plan. This process is called hard parsing;
3.5 operations corresponding to the stored procedure
Create and compile the process:
Create or replace procedure
Compilation process
Alter procedure procname compile;
Call Process
Use an anonymous subroutine to directly write the process name
Use a well-known subroutine to directly write the process name;
Deletion process:
Drop procedure
3.5.1 Use Cases of the process
Create a stored procedure
Create or replace procedure p_account
(P_id number, p_realname out varchar2, p_age out number)
Ls
Begin
Select real_name, round (sysdate-birthdate)/360) into p_realname, p_age
From account
Where id = p_id;
Exception
When no_data_found then
P_realname: = 'no account ';
P_age: = 0;
End;
Famous subroutine call:
Declare
V_realname varchar2 (20 );
V_age number;
Begin
P_account (1011, v_realname, v_age );
Dbms_output.put_line (v_realname | ''| v_age );
End;
Anonymous subroutine call and variable binding:
Variable B _realname varchar2 (20)
Variable B _age number
Begin
P_account (1011,: B _realname,: B _age );
End;
Print B _realname;
Print B _age;
3.6 static SQL in PLSQL
During SQL parsing, oracle converts the variables defined in PLSQL into the bound variables insert into test values (: b1), reducing the number of hard parsing times;
Server process caches the executed SQL statements and does not close them. When the SQL statement is executed again, no soft resolution is required;
Parameters in the process are automatically converted to bound variables;
**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************