[PLSQL] variable binding, dynamic SQL, hard parsing and soft parsing, plsqlsql

[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**************************************** ********************************